Can't connect to remote SQL server from some machines Our web server uses SQL Server 2008 R2. I have one development machine that connects to it with the IP address through SSMS and when testing my aspx website. I have several other machines though that can't connect to the IP through SSMS. I'm getting this error:

  A Network-related or instance-specific error occurred while establishing a
  connection to SQL Server.
  The server was not found or was not accessible. Verify the instance name is correct and the SQL Server is configured to allow remote connection.
  (provider: Named Pipes provider:40-could not open a connection to SQL Server)
  (Microsoft SQL Server, Error:53)

I've got TCP/IP enabled, port 1433 open, all of that. Remember that I can connect from one development machine, but not others.

Here are some of the things from my connection issue checklist

Check for an alias on the machine that can't connect
Check for firewall rules
Check and make sure the SQL Server drivers on the machines that don't work are up to date.
Try connecting with servername:port rather than just servername
Try pinging the SQL Server IP address from the machines that can't connect.


Display query which SSMS executes from Interface/WYSIWYG edits If I am to add a new column to a table in SQL Server Management Studio 2008 via the interface/WYSIWYG (as opposed to writing a SQL query) this will, behind the scenes, create a SQL query which it will execute to perform the requested changes.

Is there any way to capture these SQL queries so that I am able to easily execute them on another server, without using SSMS, or re-writing the query.

When you make the changes, there's a menu option "Table Designer".  Under there is the option to Generate a Change Script.

There's an option there to automatically generate the script on future changes too.


Can you time how long an Oracle import takes? I'm on a Windows machine with Oracle Client 11.1.0, and am going to import a database dump. I'm importing from the command line with the "imp" command.

Is there an option to time how long the import takes?  I've searched around but haven't found anything for that yet.

Thanks in advance!

If you just want to time how long it takes, you could call it via a batch file like this:

echo %DATE% %TIME% >> time.log
imp parfile=./file.par etc etc
echo %DATE% %TIME% >> time.log

Once everything has run through, have a look at the time.log file and work out the difference between the 2 values.


Oracle Enterprise Manager - Where do I find the error logs? I'm not familiar with oracle management. The problem is when I try to connect to the enterprise manager through the Web browser I get this error:

In english: "Internal Error has occurred. Check the log file for details."

Where to find the log file to see what's the problem?

I'm using Windows 7 and Oracle 11gR2 EE.

ORACLE_HOME/HOSTNAME_SID/sysman/log

... is where I finally found them. I don't know if this is system dependent; this was on a linux box.


Mysql Innodb Cluster Replication Hello Everyone,
This is the first time I'm doing cluster replication. But I stuck on Adding Instance. I don't know why this error is coming behind. "The error says The START GROUP_REPLICATION command failed as there was an error when initializing the group communication layer".
I am following this tutorial
https://mysqlserverteam.com/mysql-innodb-cluster-8-0-a-hands-on-tutorial/
Thanks in Advance
NOTE: The target instance '*********' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether incremental state recovery can correctly provision it.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of
'********:3306' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recovery method' option to 'clone'.

The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no
purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.

Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone):
Validating instance configuration at *********:3306...
This instance reports its own address as *********
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using '*********:33061'. Use the local address option to override.

A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster...

ERROR: Unable to start Group Replication for instance 'Mysql-Cluster-Test:3306'.
The MySQL error_log contains the following messages:
  2021-02-04 12:14:06.019727 [System] [MY-013587] Plugin group_replication reported: 'Plugin 'group_replication' is starting.'
  2021-02-04 12:14:06.023702 [Warning] [MY-011735] Plugin group_replication reported: '[GCS] Peer address "*******:33061" is not valid.'
  2021-02-04 12:14:06.023739 [Error] [MY-011735] Plugin group_replication reported: '[GCS] None of the provided peer address is valid.'
  2021-02-04 12:14:06.023957 [Error] [MY-011674] Plugin group_replication reported: 'Unable to initialize the group communication engine'
  2021-02-04 12:14:06.023981 [Error] [MY-011637] Plugin group_replication reported: 'Error on group communication engine initialization'
Cluster.addInstance: Group Replication failed to start: MySQL Error 3096 (HY000): *******:3306: The START GROUP_REPLICATION command failed. ```

After spending a couple of days. Finally, find the solution. The solution is You have to whitelist IP of a primary and secondary node's in the /etc/hosts file in the primary Node. And Paste this below configuration in the /etc/mysql/my.cnf config file in all secondary node's. Please increment the server_id in all nodes.
etc/hosts file
# Your system has configured 'manage_etc_hosts' as True.
# As a result, if you wish for changes to this file to persist
# then you will need to either
# a.) make changes to the master file in /etc/cloud/templates/hosts.debian.tmpl
# b.) change or remove the value of 'manage_etc_hosts' in
#     /etc/cloud/cloud.cfg or cloud-config from user-data
#
127.0.0.1 .localdomain MYSQL-CLUSTER
127.0.0.1 localhost

123.22.33.44 test1
22.33.44.55  test2
33.44.55.66  test3

#
# The following lines are desirable for IPv6 capable hosts
::1 ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
ff02::3 ip6-allhosts

Primary Node
# General replication settings
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = NONE
log_slave_updates = ON
log_bin = binlog
binlog_format = ROW
transaction_write_set_extraction = XXHASH64
loose-group_replication_bootstrap_group = OFF
loose-group_replication_start_on_boot = OFF
loose-group_replication_ssl_mode = REQUIRED
loose-group_replication_recovery_use_ssl = 1

# Shared replication group configuration
#loose-group_replication_group_name = ""
loose-group_replication_ip_whitelist = "123.22.33.44,22.33.44.55,33.44.55.66"
loose-group_replication_group_seeds = "123.22.33.44:33061,22.33.44.55:33061,33.44.55.66:33061"

# Single or Multi-primary mode? Uncomment these two lines
# for multi-primary mode, where any host can accept writes
#loose-group_replication_single_primary_mode = OFF
loose-group_replication_enforce_update_everywhere_checks = ON

# Host specific replication configuration
server_id = 1
bind-address = 123.22.33.44
report_host = 123.22.33.44
loose-group_replication_local_address = 123.22.33.44:33061

Secondary Node
# General replication settings
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = NONE
log_slave_updates = ON
log_bin = binlog
binlog_format = ROW
transaction_write_set_extraction = XXHASH64
loose-group_replication_bootstrap_group = OFF
loose-group_replication_start_on_boot = OFF
loose-group_replication_ssl_mode = REQUIRED
loose-group_replication_recovery_use_ssl = 1

# Shared replication group configuration
#loose-group_replication_group_name = ""
loose-group_replication_ip_whitelist = "22.33.44.55,123.22.33.44,33.44.55.66"
loose-group_replication_group_seeds = "22.33.44.55:33061,123.22.33.44:33061, 33.44.55.66:33061"

# Single or Multi-primary mode? Uncomment these two lines
# for multi-primary mode, where any host can accept writes
#loose-group_replication_single_primary_mode = OFF
loose-group_replication_enforce_update_everywhere_checks = ON

# Host specific replication configuration
server_id = 2
bind-address = 22.33.44.55
report_host = 22.33.44.55
loose-group_replication_local_address = 22.33.44.55:33061

Third Node
# General replication settings
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = NONE
log_slave_updates = ON
log_bin = binlog
binlog_format = ROW
transaction_write_set_extraction = XXHASH64
loose-group_replication_bootstrap_group = OFF
loose-group_replication_start_on_boot = OFF
loose-group_replication_ssl_mode = REQUIRED
loose-group_replication_recovery_use_ssl = 1

# Shared replication group configuration
#loose-group_replication_group_name = ""
loose-group_replication_ip_whitelist = "22.33.44.55,123.22.33.44,33.44.55.66"
loose-group_replication_group_seeds = "22.33.44.55:33061,123.22.33.44:33061, 33.44.55.66:33061"

# Single or Multi-primary mode? Uncomment these two lines
# for multi-primary mode, where any host can accept writes
#loose-group_replication_single_primary_mode = OFF
loose-group_replication_enforce_update_everywhere_checks = ON

# Host specific replication configuration
server_id = 3
bind-address = 33.44.55.66
report_host = 33.44.55.66
loose-group_replication_local_address = 33.44.55.66:33061

I followed this tutorial https://www.digitalocean.com/community/tutorials/how-to-configure-mysql-group-replication-on-ubuntu-16-04


How do I efficiently emulate JSON_TABLE in Mariadb? I don't have MySql readily available, but from what I understand, the best way to achieve what I want in MySql is to make use of JSON_TABLE. Also, from what I understand, JSON_TABLE is being very actively worked on right now, which reduces the desire to switch to MySql just for this.
I have a work around query, which gives me what I want, but want to know if it is the most efficient way.
create table natural_numbers (primary key (n))
with recursive nn as (
    select 0 as n
    union
    select n + 1
    from nn
    where nn.n < 100
)
select *
from nn;

with all_values as (
    select json_value(pr.record_data, concat('$.*.A.B.C[',nn.n,'].D.*')) x
    from xxx pr
        cross join natural_numbers nn
)
select *
from all_values a
where a.x is not null
;

My concerns with this are that I am building more rows than I need and then filtering out nulls. In this specific case, I think it will be very unlikely to have more than 6 elements, so I might be able to get away with limiting the process to 6 rows at most, but what is the recommended approach in general without making assumptions on the max number of elements.

I was playing around with my query and came up with this. It is much quicker than what I had before. So much so, that I think this might be the best way to do stuff like this.
with recursive array_values as (
    select
        json_extract(pr.record_data, '$.*.A.B.C[*].D.*') val
        , pr.id
        , json_length(json_extract(pr.record_data, '$.*.A.B.C[*].D.*')) n
    from xxx pr
), rec_elems as (
    select av.id, 0 as i, json_value(av.val, '$[0]') val
    from array_values av
    where av.n > 0 is not null
    union all
    select av.id, e.i + 1, json_value(av.val, concat('$[', e.i + 1, ']'))
    from array_values av
        inner join rec_elems e on av.id = e.id
    where (i + 1) < av.n
)
select * from rec_elems

I will leave the answer unaccepted for a while, in case someone else can provide an alternative.


What option we can disable temporary in PostgreSQL make database update faster I want to update 3 million records within some seconds. i have tried doing below things

ALTER TABLE imagingresourceplanning.opnav_fact_revenue_costs SET UNLOGGED ;
ALTER TABLE imagingresourceplanning.opnav_fact_revenue_costs DISABLE TRIGGER ALL;

once the update is done i am reverting this settings. with this change i have noticed that my update time is now 2 minutes which was eariler taking 10 min.

What other settings i can disable in PostgreSQL to make update even more faster. Data in my table is inserted and update by nightly job.

My postgresql version is 9.5

Thanks

You should be able to increase your update speed by "disabling" foreign keys check as explained here.


How to index range condition and ordering by a different colum? I use PostgreSQL and i have a table like this:
create table transactions (
  uuid uuid,
  user_id integer,
  created_at timestamp,
  amount numeric
);
create index idx_transactions_user_id_created_at on transactions (user_id, created_at);

I'd like to get the user's "biggest transactions" for the past N days with a query like this:
select *
from transactions
where user_id=1234
  and created_at >= current_timestamp - interval '7 days'
order by amount desc
limit 10;

The user can choose the number of days and the limit he is interested in.
The query plan is like this:
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Limit  (cost=9.54..9.55 rows=2 width=60)
   ->  Sort  (cost=9.54..9.55 rows=2 width=60)
         Sort Key: amount DESC
         ->  Bitmap Heap Scan on transactions  (cost=4.18..9.53 rows=2 width=60)
               Recheck Cond: ((user_id = 1234) AND (created_at >= (CURRENT_TIMESTAMP - '7 days'::interval)))
               ->  Bitmap Index Scan on idx_transactions_user_id_created_at  (cost=0.00..4.17 rows=2 width=0)
                     Index Cond: ((user_id = 1234) AND (created_at >= (CURRENT_TIMESTAMP - '7 days'::interval)))
(7 rows)

So postgres gets all rows which match the user_id and the created_at range, than sorts all that and returns the top N.
Is there's a better way to index this? So it scales nicely when there are millions of users and millions of transactions per user?
So far my query is not too slow, but i just wonder if it is theoretically possible to index this table in such a way that postgres will know exactly which 10 row to read from the disk for arbitrary created_at condition?

No, you can't do that with any of the core index types.  You might be able to use the RUM extension, but last time I tried something like this with it I couldn't get it to work for this purpose.
Once you have enough data that you actually need improvement (sorting two rows is not laborious, as you note), you might get a big improvement by range partitioning on created_at, and indexing on (user_id, amount).  That way it could consult only the partitions covering possibly-qualifying times, walking each partition in order by the index, and filtering out any rows that didn't actually qualify based on created_at but which was in a partition which couldn't be ruled out en masse.  Each individual partition scan can stop once it meets the LIMIT, and then they can be "merge appended" maintaining the existing order, and have the LIMIT applied again to the merged set.
After playing with this some more, I have to amend my answer.  A GiST index on (user_id, created_at, amount) can do this as long as you can reformulate your query a bit.  You can use the KNN mechanism by comparing "amount" to a 'sentinel' value which is greater than any possible real amount in your table:
select *
from transactions
where user_id=1234
  and created_at >= current_timestamp - interval '7 days'
order by amount <-> 999999999
limit 10;

You do need a real max amount here, you can't use 'Infinity' because the distance between any finite value and infinity is infinite, and they will all be tied.
GiST indexes can be much slower to build and maintain than btree indexes, and that can also be hard to predict.  So even though it is possible, it might not be worthwhile if other techniques with btree indexes will get the job done.


Create a Linked-Server from SQL Server 2008 R2 to AWS RDS SQL Server How do I correctly create a linked-server entry, from an on-prem SQL Server, to an AWS RDS SQL Server instance?  On-prem is 2008 R2, AWS-RDS is running 2012.

This is the error I get when attempting to SELECT from a table in the linked-server:

  OLE DB provider "SQLNCLI10" for linked server "RDS-Q1-LINK" returned message
  "Unable to complete login process due to delay in opening server connection".
  Msg 7303, Level 16, State 1, Line 30
  Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server "RDS-Q1-LINK".

I have tried following instructions here and people in the thread say they've got it working (from on-prem to AWS, not the reverse, which I don't care about), but nobody specified exactly how they did it or what work-arounds or gotchas they encountered.  Another article says the same thing -- they've "had success LINKING TO RDS" but they fail to specify how.

On SQL Server 2008 R2 the default value of remote login timeout (s) is five (5) seconds, I believe, though it is configurable.

Since you are now trying to connect to an AWS RDS SQL Server, have you considered whether the remote login timeout (s) is appropriate for this environment?

Try raising the timeout a bit. You might start with 10 seconds:

sp_configure 'remote login timeout (s)',10;
RECONFIGURE;

I have had to bump up the timeout seconds in some environments, so do not be afraid to try this.  (It is equally simple to set it back to the previous default.)

You can try to configure a longer timeout, if needed.  So far 10 seconds has almost always worked for my environments, but I have gone to 20 seconds a couple of times.


Disk full of mysql .mad and .mai files On a (owned) shared server sometimes mysql writes alott of .mad and .mai.
This happens since i think 2 or 3 months, completely random.
It may happen 3 times a day, then it does not happen for a couple of weeks and then on a monday morning the issue occurs again.

It writes alott of files quickly. The disk is full very fast.
I changed the tmp folder to a different location so it could write more files.
The location it writes now is 16gb and also gets full.

I found this issue on multiple locations and tried some solutions.
I tried to limit tmp files that could be written with tmp_disk_table_size and tmp_table_size but this did not seem to help.

I also tried to enter internal_tmp_disk_storage_engine=MyISAM in my my.cnf but then mysql would not start.
I did this because i googled for a solution to this error in error logs:

Cannot add field images in table database_name.`jospr_tags` because after adding it, the row size is 8769 which is greater than maximum allowed size (8126) for a record on index leaf page.

The problems could maybe stop if all database where changed to innodb, but i cannot do that easily. These are not my databases.

I have alerting on the server, so when the disk is almost full i get a message. When i quickly login, the disk is already full and checking the processlist in mysql does not show a query, problably because it stops because the disk is full.

[ERROR] mysqld: Disk full (/mysqltmp/#sql_3d1b_3.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")

At the moment i am unsure what would help or what i can do to find any cause.
All files that are written are owned by mysql.
When i try to tail a file it only says mysql and when i delete a file the process stops and all files are gone.

The only solution i see at the moment is mounting an additional large disk so it can write longer so we can find the cause. However it could take weeks before this issue occurs again and i really would like to solve this quicker or more efficient.

I really hope some people have more information for me what could cause this and ofcourse any solutions.

BTW: All site using MyIsam seem to be Joomla (3.x) sites.

Files like #sql_* are temp files used by things like ALTER TABLE.  When successfully finished they are removed.  So, I conclude that and ALTER (or some other admin query) was rudely aborted (such as with a crash).

They can safely be deleted.

You 'should' switch to InnoDB.


How to know which website is causing the sql server to stop responding? I have multiple databases on the same sql server 2008. Some times the server stops working and I should restart it to make it work again. Is there an easy way to know which application or website caused this stop?
Or can I know which database takes the most of the server power?

1) Computer was restarted (one of causes).
Use Sql Server Configuration Manager to set Start Mode to Automatic

2) Use Event viewer (Windows Logs/System) to get more information about stops/starts of service:


T-SQL insert value based on identity column Is it possible to have a table with PK based on Identity columns?

Lets say I have a table of customers, two fields are important for our case Id which is PK nvarchar(20) and SequenceId which is int identity(1,1). I want to have a stored procedure that will have(among others) a parameter @CustomerType and based on this parameter will generate the value for Id and insert a new row into the table. Something like this:

CREATE PROCEDURE createCustomer
@CustomerType int,
@GivenName nvarchar(20),
@Surname nvarchar(20),
@BirthDate date = null
...
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @prefix nvarchar(1) = N'x'
    IF @CustomerType = 1
    BEGIN
        SET @prefix = N'P'
    END
...
    ELSE
    BEGIN
        SET @prefix = N'x'
    END

    INSERT INTO [dbo].[Customers]
           ([Id],
            [GivenName],[Surname],[BirthDate])
    VALUES
           (@prefix + (SequenceId + 1000000),
        @GivenName,@Surname,@BirthDate);

    SELECT INSERTED.Id AS CustomerId;
END

You never set the SequenceId to anything.  It is not possible to set the value of a column during an insert using an expression that references the value of an IDENTITY column on that same table that will be set upon that same insert operation.  If you think about it this makes sense because the row doesn't exist until you insert it, so there is no IDENTITY value at the time that the insert begins.

If what you want is to create a column that concatenates a prefix and an IDENTITY value then you should use a computed column, for example:

CREATE TABLE dbo.Customers
( Sequence   int  IDENTITY(1,1) NOT NULL
, Prefix     nchar(1) NOT NULL
, CustomerID AS Prefix + CAST(Sequence + 1000000 as nvarchar(8))
, GivenName  nvarchar(50)
... etc ...
, CONSTRAINT UN_CustomersSequence UNIQUE (Sequence)
, CONSTRAINT PK_Customers PRIMARY KEY (CustomerID)
)

You should be able to make CustomerID in the above table your primary key if you want.  Since the Sequence value is already unique (by virtue of being IDENTITY) I would question why you want to make a compound primary key.  Are you sure that this is the design you want?


Querying Extended Events in SQL Server 2008 I have created the following event session using a script I got from Kimberly Tripp's SQL Server 2008 Internals book.  It has been modified to send to a file instead of the ring buffer.

CREATE EVENT SESSION
[statement_completed_file]
ON SERVER
ADD EVENT
    sqlserver.sp_statement_completed,
ADD EVENT
    sqlserver.sql_statement_completed
    (
        ACTION
        (
            sqlserver.sql_text
        )
        WHERE
        (
            sqlserver.session_id=53
        )
    )
ADD TARGET package0.asynchronous_file_target
    (
        set filename=N'C:\MSSQL\TEMP\stats.xel',
        metadatafile=N'C:\MSSQL\TEMP\stats.xem'
    )
WITH
(
    MAX_MEMORY=4096KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=1 SECONDS,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=OFF
)

Here is the original script to query the data.

SELECT
theNodes.event_data.value('(data/value)[1]', 'bigint') AS source_database_id,
theNodes.event_data.value('(data/value)[2]', 'bigint') AS object_id,
theNodes.event_data.value('(data/value)[3]', 'bigint') AS object_type,
theNodes.event_data.value('(data/value)[4]', 'bigint') AS cpu,
theNodes.event_data.value('(data/value)[5]', 'bigint') AS duration,
theNodes.event_data.value('(data/value)[6]', 'bigint') AS reads,
theNodes.event_data.value('(data/value)[7]', 'bigint') AS writes,
theNodes.event_data.value('(action/value)[1]', 'nvarchar(max)') AS sql_text
FROM
(
    SELECT
        CONVERT(XML, st.target_data) AS ring_buffer
        FROM sys.dm_xe_sessions s
        JOIN sys.dm_xe_session_targets st ON s.address = st.event_session_address
        WHERE s.name = 'statement_completed'
) AS theData
CROSS APPLY
theData.ring_buffer.nodes('//RingBufferTarget/event') theNodes (event_data)
WHERE theNodes.event_data.value('(action/value)[1]', 'nvarchar(max)') is not null

How could I modify it to read from the two files xel and xem instead of the ring buffer.  I have been googling around but the learning curve has been rough.  Thank you in advance for your help.

Try to play with this example:

CREATE EVENT SESSION
[statement_completed_file]
ON SERVER
ADD EVENT
    sqlserver.sp_statement_completed,
ADD EVENT
    sqlserver.sql_statement_completed
    (
        ACTION
        (
            sqlserver.sql_text
        )
        WHERE
        (
            sqlserver.session_id <> 53
        )
    )
ADD TARGET package0.asynchronous_file_target
    (
        set filename=N'C:\stats.xel',
        metadatafile=N'C:\stats.xem'
    )
WITH
(
    MAX_MEMORY=4096KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=1 SECONDS,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=OFF
)
GO
ALTER EVENT SESSION [statement_completed_file]
ON SERVER
STATE=START
GO
SELECT event_data,
    event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    event_data.value('(event/data/value)[1]', 'bigint') AS source_database_id,
    event_data.value('(event/data/value)[2]', 'bigint') AS object_id,
    event_data.value('(event/data/value)[3]', 'bigint') AS object_type,
    event_data.value('(event/data/value)[4]', 'bigint') AS cpu,
    event_data.value('(event/data/value)[5]', 'bigint') AS duration,
    event_data.value('(event/data/value)[6]', 'bigint') AS reads,
    event_data.value('(event/data/value)[7]', 'bigint') AS writes,
    event_data.value('(event/action/value)[1]', 'nvarchar(max)') AS sql_text
FROM
(
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file('C:\stats*.xel', 'C:\stats*.xem', null, null)
) tbl
GO
DROP EVENT SESSION [statement_completed_file] ON SERVER
GO


Concurrent MySQL updates hang with InnoDB (on Amazon RDS) I'm running into a problem where multiple MySQL updates being performed at the same time will lock up and takes several minutes to finish. I'm using InnoDB, so I'm confused as to why this could be happening since each update is updating only 1 row. I'm also using a m2.4xlarge RDS instance (the largest they come).

Here's what I'm doing: I have a table with about 100M rows in it, with "views" being a column (which is indexed), and I want to update the views on about 1M rows. On several different servers I have a loop like this where each server has it's own set of rows to be updated (pseudo code):

mysql("set autocommit=0");
mysql("start transaction");

foreach($rows as $row) {
    mysql("update table set views=views+1 where id=$row[id]");
}

mysql("commit");

This loops through all the rows that need to be updated. It works perfectly when the number of servers is small, like around 4, but when it grows to 10+ the updates start to hang in the "Updating" state all at once. Nothing says that it's waiting on a lock, it's just "Updating". This happens for about 5 minutes, where it will finally make the updates and continue through the loop and eventually happen again.

I'm not looking for alternative ways to do the updates. Having things like a tmp table and

update table,tmp_table set table.views = table.views+tmp_table.views where
  table.id = tmp_table.id

lock all the rows that are being updated until they all finish (which could be hours), which won't work for me. They MUST be in these awful loops.

I'm wondering why they could be getting stuck in the "Updating" state, and what I can do to prevent it.

tldr; Having 10+ "update" loops will eventually lock up all the updates being done, at the same time, for an unknown reason until they decide to finally make updates and continue through the loops, only for it to happen again seconds later.

SHOW VARIABLES: http://pastebin.com/NdmAeJrz

SHOW ENGINE INNODB STATUS: http://pastebin.com/Ubwu4F1h

I'm not looking for alternative ways to do the updates. Having things
like a tmp table [will] lock all the rows that are being updated until
they all finish (which could be hours), which won't work for me. They
MUST be in these awful loops.

I disagree.
The strength of an RDBMS is in performing set operations like "update all these rows plz". Given this, your intuition should tell you that these "awful loops" are not the best way to go except under very rare circumstances.
Let's take a look at your current update logic and understand what it's doing.
First off, the set autocommit=0 line in your script is unnecessary.  Because you explicitly open a transaction immediately after that with start transaction, autocommit automatically becomes disabled until you end the transaction with COMMIT or ROLLBACK.
Now for the meat of the logic: You've wrapped all these individual updates inside the loop in one big transaction. If your intention behind the iterative updates was to reduce locking and increase concurrency, the wrapped transaction defeats that intention. MySQL must maintain locks on every row it updates until the transaction commits so it can roll them all back at once if the transaction fails or is cancelled. Furthermore, instead of knowing in advance that it is about to lock this range of rows (which would enable MySQL to issue locks with the appropriate granularity) the engine is forced to issue a large number of row-level locks in rapid-fire. Given that you are updating 1 million rows, this is a massive burden on the engine.
I propose two solutions:

Turn autocommit on and remove the transaction wrapper.  MySQL will then be able to release every row lock right after it finishes updating the row. It is still forced to issue and release a massive number of locks in a short period of time, so I doubt this will be an appropriate fix for you. Furthermore, if some error occurs halfway through the loop, nothing will be rolled back since the work is not transaction-bound.

Batch your updates in a temp table. You mentioned and then dismissed this solution, but I bet it will perform best. Have you already tried it? I would first test the full million-row update.  If that takes too long then batch the work into progressively smaller chunks until you've found the sweet spot: the batches are big enough to get the total work done quickly, but no individual batch blocks other processes for too long. This is a common technique DBAs use when they have to modify a large number of rows during live operations. Remember, since your goal is to maximize your concurrency, keep autocommit on and don't wrap any of this work into a massive transaction so MySQL releases its locks as soon as possible.
Notice that as the batches become progressively smaller, this solution eventually approximates the first one. That is why I am confident this solution will perform better: When the database engine can group its work into chunks, it flies.


What are alternatives to Replication to distibute data (Microsoft SQL Server) Is anyone knowledgeable of alternatives to SQL Server replication?

I have a scenario where separate databases will share a lot of the same data which complex queries will match against.
We want to separate the databases out from each other (i.e. not on the same SQL Server) so we can ensure the server resources are not a bottleneck.

What techniques allow fast and efficient data sharing, fairly instantly.

Replication seems to be quite a slow and cumbersome technology?

Are there any new features in SQL 2012 and 2014 that cater for this kind of scenario?

AlwaysOn Availability groups doesnt quite cut it as these are readonly replicas.

I need a read/write replica where multiple data sources interact with each other

Server A        Server B      Server C

All have read/write transactions, all require as instant
as possible sharing of data between them of the results of the transactions.

Thanks for your help

The reality is that SQL Server does not have a read-write scalability story in the same way that Oracle does in RAC so whatever you choose, you will always needing to consider that data-sets in one place or the other could be slightly behind. Availability Groups are probably the easiest solution to manage but then the only thing you are scaling out is readable replicas. Merge or Peer-peer replication probably provides much of what you need out of the box, but can be a bit of a headache especially in the wrong hands. You also need to think about avoiding conflicts.

I wouldn't attempt to roll out something yourself (i.e through SB or CDC) because this would ultimately become an administrative nightmare to manage in years to come and probably not provide you with richer functionality than is already provided.


Creating a View using a query created by sp_executesql I posted a question about creating a dynamic sql statement and got the following code which fulfills this task:

DECLARE @TableName nvarchar(400)
DECLARE @DynSQL nvarchar(MAX)
Set @DynSQL = ''

DECLARE cursor1 CURSOR FOR
    select name
    from sys.tables
    where name like 'DDOS_WAF_ACCOUNT_%'

OPEN cursor1

FETCH NEXT FROM cursor1 INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Add the select code.
    Set @DynSQL = @DynSQL + 'Select * from ' + @TableName
    FETCH NEXT FROM cursor1
    INTO @TableName

    -- If the loop continues, add the UNION ALL statement.
    If @@FETCH_STATUS = 0
    BEGIN
        Set @DynSQL = @DynSQL + ' UNION ALL '
    END

END

CLOSE cursor1
DEALLOCATE cursor1

Print @DynSQL
exec sp_executesql @DynSQL

Now executing this code displays the table which I want to store for later use. I was told that I should use a view that stores these results but I dont know how to do that. Can I create a view that is populated by this dynamic sql statements and sotre it for repeated use?
Thank you all.

So - expanding on your original script, the following checks to see if the view already exists (and if it does, checks to see if the view definition will be changing).  There isn't much sense in dropping the view and recreating it if it isn't really changing.  If the view already exists and the new view will be different, then the current view is dropped.

The script may need a little 'clean-up'

/*
--initially create 2 tables and run the script code
CREATE TABLE dbo.xx_table01 (col1 int)
CREATE TABLE dbo.xx_table02 (col1 int)

--add another table and run the script code
CREATE TABLE dbo.xx_table03 (col1 int)

--add another table and run the script code
CREATE TABLE dbo.xx_table04 (col1 int)

--add another table and run the script code
CREATE TABLE dbo.xx_table05 (col1 int)
*/
--
DECLARE @CurrentView nvarchar(MAX) = null
DECLARE @SchemaName nvarchar(400)
DECLARE @TableName nvarchar(400)
DECLARE @DynSQL nvarchar(MAX)
DECLARE @DynDROP nvarchar(MAX) = 'DROP VIEW XX_VIEW'
SET NOCOUNT ON
Set @DynSQL = 'CREATE VIEW XX_VIEW AS '

set @CurrentView = (select VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA='dbo' and TABLE_NAME='XX_VIEW')

DECLARE cursor1 CURSOR FOR
    select TABLE_SCHEMA,TABLE_NAME
    from INFORMATION_SCHEMA.TABLES
    where
    TABLE_SCHEMA='dbo' AND
    TABLE_NAME like 'xx_table%'

OPEN cursor1

FETCH NEXT FROM cursor1 INTO @SchemaName, @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Add the select code.
    Set @DynSQL = @DynSQL + 'Select * from ' + @SchemaName +'.' + @TableName
    FETCH NEXT FROM cursor1
    INTO @SchemaName, @TableName

    -- If the loop continues, add the UNION ALL statement.
    If @@FETCH_STATUS = 0
    BEGIN
        Set @DynSQL = @DynSQL + ' UNION ALL '
    END

END

CLOSE cursor1
DEALLOCATE cursor1

IF @CurrentView = @DynSQL
    PRINT 'VIEW IS THE SAME, NEW VIEW WASN''T CREATED'
ELSE
    BEGIN
        if @CurrentView is not null
        BEGIN
        print @DynDROP
            exec sp_executesql @DynDROP
        END
        PRINT @DynSQL
        exec sp_executesql @DynSQL
    END


Get size of a mongo database in a clustered enviroment As far as I can see there is a command to get the size of the database by using the db.stats() command. My question: is there a way to get the total size of all nodes in the cluster for a specific database?

AFAIK, no. You'll have to write a script around db.stats().


Why is an entire index that is infrequently used stored in the buffer pool? I have been looking at the memory usage of a server and noticed something odd when drilling into the details. One index is eating up ~15GB of the buffer pool. That happens to be the size of the entire index. A few more notes on this specific index:

It is not a compound index. Only one field is included
That field has relatively low cardinality (55 distinct values)
The index exist on a table with half a billion records
The sys.dm_db_index_usage_stats DMV shows 51 combined user_scans and user_seeks
The sys.dm_db_index_usage_stats DMV also shows 1,159,987 user_updates

Leading to a few questions:

Why would this infrequently used index be taking up so much space in the buffer pool?
Doesn't the high number of user_updates compared to the low number of scans and seeks make this a candidate for removal due to maintenance overhead?
Should a field with such low cardinality be indexed in the first place?
Is the only way to clear this from the buffer pool to drop and recreate? Clearing the entire cache or buffer pool is obviously not possible on a production box

I am a developer trying to be a DBA, so bear with me ;)

Don't blindly rely on the sys.dm_db_index_usage_stats as it does not tell you the entire story.

Refer to Joe Sack's

Exceptions – what sys.dm_db_index_usage_stats doesn’t tell you
Exceptions–what sys.dm_db_index_usage_stats doesn’t tell you (Part II)

  Why would this infrequently used index be taking up so much space in the buffer pool?

In simple terms, SQL Server tends to rely on Buffer Pool to do its normal operations to avoid expensive disk I/O's. If you have enough RAM and have set up MAX Memory appropriately, then all the queries that are ran across your database will CACHE the objects that are required by your queries.

  Doesn't the high number of user_updates compared to the low number of scans and seeks make this a candidate for removal due to maintenance overhead?

It can be the case where the index from your perspective is not useful, but there might be queries that require that index or part of that Index.

I agree that caching such a huge index and if it is really not useful - is a waste of Buffer Pool and you might run into performance issues like more I/O's or excessive plan recompilations if the plan cache is too constrained.

  Should a field with such low cardinality be indexed in the first place?

Not really as maintaining an index - it costs you for modifications like insert, update and deletes as well as disk space. Also, low  cardinality indexes might be ignored by the Query Optimizer as it wont help generate an efficient query plan - its not just selective enough.Try to look into Filtered indexes.

  Is the only way to clear this from the buffer pool to drop and recreate? Clearing the entire cache or buffer pool is obviously not possible on a production box.

Unfortunately, DBCC DROPCLEANBUFFERS does not have any parameters for specific database or objects.

Best is to rebuild the index.


SQL Server - difference between instance name and instance id I'm a bit confused with SQL Server instances. The Instance Name and Instance ID are different properties, yet Instance ID tends to be defaulted to a capitalized version of the Instance Name. The installation allows you to set a different value for the ID than the Name, although typically they're the same.

Obviously, ID refers to internal reference and Name refers to user-friendly reference. But what are the fundamental differences between the two? When should one be used rather than the other?

I need to understand this while deploying / installing SQL Server 2008 R2 Express on our clients' server machines from a custom installation process.

By default, the instance name is used as the Instance ID. Instance ID is used to identify installation directories and registry keys for your instance of SQL Server. This is the case for default instances and named instances

(source: http://msdn.microsoft.com/en-us/library/ms143531(v=sql.120).aspx )

Install SQL 2008 r2 steps: http://blogs.msdn.com/b/petersad/archive/2009/12/17/sql-server-2008-r2-sysprep-step-by-step.aspx


Time dimension or timestamp in fact table? Which would you use, and why? A separate time dimension or putting a timestamp in a fact table? Or perhaps both?

I am building a data warehouse, and need to represent the time of day that events occur at, down to the one second granularity. I want to roll data up; plotting a graph of number of events per hour of the day, for example.

Kimball's "The Data Warehouse Toolkit", has a design for a time of day dimension. A more recent blog post recommends not doing that, and using a timestamp in the fact table instead:

http://www.kimballgroup.com/2004/02/design-tip-51-latest-thinking-on-time-dimension-tables/

If I go with timestamps in the fact table, will it still be easy/fast to roll up by the hour?

Any other trade-offs to consider in making this choice?

I would recommend including both on the fact table. The dimension should be used for filtering and grouping, while the time stamp value can be used in detailed reports/queries.

Unless you care about whether an event occurred at 8 seconds or 42 seconds past the minute, create your time dimension at the grain of 1 minute.

As you did not tag your RDBMS, I thought it prudent to mention that later versions of SQL Server do not allow date + time data type operations; i.e. recreating the time stamp from the date & time dimension business keys.
A work around is convert(datetime, [date column]) + convert(datetime, [time column]), or similar.


Merge two huge tables keeping only the unique rows I have two tables: table1 with 61 million rows and table2 with 59 millions rows. The columns in both are the same (name and type). Both are imported from backup files.

I want to merge these two tables into one table keeping only unique records.

For example, in table1 I have the following records:

NAME    CODE   ...
Name1   001    ...
Name2   002    ...
Name3   003    ...
Name4   004    ...

And in table2:

NAME    CODE   ...
Name1   001    ...
Name2   002    ...
Name5   005    ...
Name2   002    ...

The result table should be something like this:

NAME    CODE   ...
Name1   001    ...
Name2   002    ...
Name3   003    ...
Name4   004    ...
Name5   005    ...

Edit to provide more info as requested by @Erwin Brandstetter:

table1 has 15 GB. I am inserting records on table2 but when I finish it will have almost the same size.
I have more than 300 GB of free space (and an external HD if needed).
Total RAM is 8 GB.
No indexes (i can create if needed).
No concurrent access (it is accessed just by me on local machine).
I can create a new, third table if needed.
Here if the full table definition:

CREATE TABLE inss
(
   nb double precision,
   nome character varying(200),
   nasc double precision,
   cpf double precision,
   especie double precision,
   dib double precision,
   valor double precision,
   banco_pagt double precision,
   ag_banco double precision,
   orgao_pag double precision,
   aps double precision,
   meio_pagto double precision,
   banco_empr double precision,
   contrato character varying(200),
   vl_empres double precision,
   comp_ini_d double precision,
   parcelas double precision,
   vl_parcela double precision,
   tipo_empre double precision,
   endereco character varying(200),
   bairro character varying(200),
   municipio character varying(200),
   uf character varying(2),
   cep double precision,
   sit_empres double precision,
   dt_averb double precision,
   dt_exc double precision,
   id double precision
 );

The id column is filled with a row number, so it could be used to perform batch operations if the full operation becomes too slow.
All columns except the id column should be considered for the unique check.

The basic problem can be solved with various simple queries. Considering all columns:

CREATE TABLE tbl3 AS
TABLE tbl1
UNION TABLE tbl2;

Given this additional information:

  All columns except the id column should be considered for the unique check.

And:

  I don't need to preserve the ID column.

Just drop the id column, then you can proceed with the simple query above.

I would import to temporary tables (much faster, less overhead) and only write the final result (tbl3) to a regular table - in one session because temporary tables are dropped automatically at the end of the session.

CREATE TEMP TABLE tbl1 ( <columns from above, without id> );
COPY tbl1 FROM '/path/to/file1';

CREATE TEMP TABLE tbl2 ( <columns from above, without id> );
COPY tbl2 FROM '/path/to/file2';

Alternatively, to preserve the input tables across sessions, you could use unlogged tables.

For best performance create and fill the target with CREATE TABLE AS and add the PK constraint in the same transaction:

BEGIN;

CREATE SEQUENCE tbl3_tbl3_id_seq;

CREATE TABLE tbl3 AS
SELECT nextval('tbl3_tbl3_id_seq'::regclass)::int AS tbl3_id, *
FROM  (TABLE tbl1 UNION TABLE tbl2 ) sub;

ALTER TABLE tbl3
   ADD CONSTRAINT tbl3_pkey PRIMARY KEY(tbl3_id)
 , ALTER COLUMN tbl3_id SET DEFAULT nextval('tbl3_tbl3_id_seq'::regclass);

ALTER SEQUENCE tbl3_tbl3_id_seq OWNED BY tbl3.tbl3_id;

COMMIT;

Replace all occurrences of "tbl3" with our desired table name.

Detailed explanation in this related answer:

What causes large INSERT to slow down and disk usage to explode?

I added a serial column (tbl3_id) as surrogate PK to the target table. Adding the actual PK constraint at the end (of the same session) is the fastest way.

Related:

Optimizing bulk update performance in PostgreSQL

Before you do it, test whether double precision is the best data type for all those columns. Chances are, some of them could be integer (cheaper for whole numbers) or must really be numeric (loss-less). If so, adapt your temp tables to begin with.


How to combine duplicate parent rows in a relational MySQL table I have my DB structured like this with people.person_id as the foreign key for each row in people_meta.

people table

 ╔═════╦════════╦═══════╗
 ║ id  ║ fname  ║ lname ║
 ╠═════╬════════╬═══════╣
 ║  0  ║ John   ║  Doe  ║
 ║  1  ║ John   ║  Doe  ║
 ║  2  ║  Bob   ║ Smith ║
 ║  3  ║ Dave   ║ Jones ║
 ║  4  ║  Bob   ║ Smith ║
 ║  5  ║  Jen   ║ Smith ║
 ╚═════╩════════╩═══════╝

people_meta table

 ╔═════╦════════════╦════════╦═══════╗
 ║ id  ║ person_id  ║  key   ║ value ║
 ╠═════╬════════════╬════════╬═══════╣
 ║  0  ║ 0          ║ state  ║  NY   ║
 ║  1  ║ 0          ║ age    ║  53   ║
 ║  2  ║ 1          ║ party  ║  D    ║
 ║  3  ║ 1          ║  age   ║  53   ║
 ║  4  ║ 2          ║ state  ║  CA   ║
 ║  5  ║ 4          ║ party  ║  R    ║
 ╚═════╩════════════╩════════╩═══════╝

What I want to do is combine the duplicate people rows and have it cascade to the people_meta table somehow.  Please note that some data in people_meta would be duplicate if they were combined.

The first thing I tried was setting ON DELETE NO ACTION and ON UPDATE CASCADE, then in phpMyAdmin I manually attempted to delete the duplicate parent with the lower ID and update the second duplicate's ID to what the first one was, thinking it would then cascade and give me the desired affect.  -FAIL-  Apparently using InnoDB's ON UPDATE / ON DELETE features won't be helpful for such a task, but at least I've learned more about their purpose.

I'm sort of a newbie for SQL so I'd appreciate any recommendations.

You'll need to change the people_meta.person_id values before deleting the row from the people table, because the engine wouldn't know how to replace the values with the duplicates.

update people_meta set person_id = 1 where person_id = 0;
delete from people where id = 0;


Check if database has changed to determine need to perform backup I want to check SQL server database is modified or not. Reason to check database is modified is for backup(sql database backup) purpose. So I will be doing a full backup first and if any data inserted, modified or any structural or any changes done then I have to identify that there is some changes in databases and backup the database.

The checking for changes in database may be done based on frequency(daily, hourly etc). Modified date of the mdf or ldf file can't be used to check database is modified or not. Differential backup only backups changes since last backup but it's different and it does a backup even if there is no changes since last backup but size will be very less(in kb which you can test easily) .

In windows NTFS file system there exists USN journal for each file by using it we can check file is modified or not and then we can do backup the file if modified. Which is also implemented in most of the backup software.

So how can I check whether any changes done to database after my last backup, so that i can do backup if any chnages are detected?

You can't rely on things like journaling or physical file changes to see if data has changed. I hesitate to mention LSNs because I certainly don't want to encourage any attempt down that road. Trying to do this just to determine whether it might not be worth it to actually go ahead and take a log backup is a disaster waiting to happen.

Take full backups on your current schedule. Take log backups such that your recovery objectives are met. For example, if your business stakeholders state that it is not acceptable to have more than 5 minutes of data loss, back up the log every 5 minutes (and assume that disaster can strike at 4:59.997 since the last log backup).

Sorry, but your approach does not make sense. You are trying to optimize something that is already pretty optimal - SQL Server is pretty good about resource usage and creating small diff/log backup files when there have been few or no changes. What you are attempting to do is like trying to get better gas mileage by wiping a bug off your bumper. Just take the backups. I like the analogy Thomas made: "the juice isn't worth the squeeze."

If nothing else, having a log backup file represented for every 5 minute interval will be consistent and easy to automate solutions around. If you skip some of these backups because (you think) "nothing changed", then it will be harder to automate solutions around them, and troublesome to determine if a backup is missing for that reason or because it is actually missing.


Selecting 3MB (or more/less) data from database server I installed PostgreSQL Server on my Windows 7 machine. I created database and a simple table, and populated the table. Now I want to connect through the network with my Postgres server and select a given amount of data. I'm not interested of the content of the data, in particular, I can even do this:

select * from table;

but I'm interested in the size of the selected data. Something like this (pseudocode):

select * from table while downloaded_data_size < 3MB

Is that possible? I'm not interested in the content of the data, I just want to select / insert some data, no matter what's inside it, the point is, the selected / inserted data must have a given size.

For your purpose @Daniel's simple query should do the trick:

select repeat('A', (3 * 2^20)::int);    -- "3 MB"

"Size" depends. There is the size on disk, with or without overhead, with or without padding, with or without compression, with or without indexes / toast table / free space map ...
There is also the size of RAM needed to store the same data (typically more than on disk), the size of the text representation when sent over the wire, the compressed size, with or without tcp/ip overhead ...

You can use the function pg_column_size() to get an approximation.

Retrieve just enough rows from any table that's big enough:

SELECT *
FROM   any_big_tbl
WHERE  big_id <= (
   SELECT big_id
   FROM  (
      SELECT big_id, sum(pg_column_size(b)) OVER (ORDER BY big_id) AS size
      FROM   any_big_tbl b
      ) sub
   WHERE  size > (3 * 2^20)
   ORDER  BY size
   LIMIT  1
   );

big_id being the pk of any_big_tbl.
This computes a running sum of the number of bytes used to store the rows to find the id of the row reaching the requested size. The outer query returns all rows up to that id.

Or, to measure the size of the text representation, including parentheses for the row and escape characters where needed (typically results in fewer rows):

SELECT *
FROM   any_big_tbl
WHERE big_id <= (
   SELECT big_id
   FROM  (
      SELECT big_id, sum(pg_column_size(b::text)) OVER (ORDER BY big_id) AS size
      FROM   any_big_tbl b
      ) sub
   WHERE  size > (3 * 2^20)   -- "3 MB"
   ORDER  BY size
   LIMIT  1
   );

More about measuring "size":
Measure the size of a PostgreSQL table row


sp_msforeachdb in a stored procedure does not work well I'm facing strange behavior.

Here's how to reproduce: in an instance with multiple databases (SQL Server 2016 in my case), create that stored procedure (in master in my case):

create procedure dbo.Strange WITH EXECUTE AS owner
as
begin
    exec sp_MSforeachdb 'select''?''';
end;

Then call that stored procedure with:

Exec master.dbo.Strange

As you will see, it will only return data for some of the databases but not all (3 out of 11 in my case).

What is happening?

sp_MSforeachdb has lots of known issues.

If you follow the link, you'll get to a post by Aaron Bertrand that details them, and also provides an alternate solution.


What do I need to restore an encrypted MSSQL database? I am trying to get my head around MSSQL TDE encryption.

I have followed these steps to encrypt a database:

1) Create a master key on the master database:

USE master
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='thepbpass';
GO

2) Create certificate:

CREATE CERTIFICATE mycert
WITH SUBJECT='mydb DEK';
GO

3) Associate the certificate to the database:

USE mydb
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE mycert;
GO

4) Encrypt the database:

ALTER DATABASE mydb
SET ENCRYPTION ON;
GO

5) Export the certificate.

BACKUP CERTIFICATE mycert TO FILE = 'path_to_file'
GO

My question is:

If my server died and I needed to restore a backup, do I have everything required for the restore?

I have the following:

A certificate stored in a secure location.
Knowledge of the secure
master key password.

Do I need to worry about private keys or public keys?
I have seen discussion about private and public keys online and I am not sure how they are relevant to my configuration or how they are different from the master key which I created in step 1.

We can't restore a backup on a different instance without Certificates. We have to restore the Cert from the source server, then we should be able to restore the database from the backup.

Obviously your certificate & master key are required for restore.

As MSSQLTREK Blog here To restore the Certificate from source (and you have to create a DMK (Database Master Key) on destination if you don’t have one), the rest is just performing your normal DB restore process what you do on any other non encrypted user database(s).

Create a Master Key (you don’t have to do this if you already have DMK) and create a certificate from the cert backup and provide private Key file and password to decrypt it.

  Backup files of databases that have TDE enabled are also encrypted by
  using the database encryption key. As a result, when you restore these
  backups, the certificate protecting the database encryption key must
  be available. This means that in addition to backing up the database,
  you have to make sure that you maintain backups of the server
  certificates to prevent data loss. Data loss will result if the
  certificate is no longer available.

For your further ref Here and Here

Note : If you enable TDE on any Database on your Instance, Your TEMPDB is automatically Encrypted as well. So there’s a high possibility impacting other databases which are actually not using TDE feature while you are in the process of enabling TDE!


Column in INSERT query needs to increment if matching data on rows is different I tried to simplify what I am trying to do.  This is for work, we sell metal.  When an order is placed material, coating, masking, etc. is chosen.  Then someone allocates material for the order.  They want to create 2 programs.  One where a user will enter in an order number, it will verify it is a correct order  number.  It will then look at 4 tables with existing data.  It needs to take certain specs requested from those 4 tables and put them in 4 other tables that are currently empty.  Bottom line is they want to find out what was allocated for the order.  If that particular combination has already been requested on a different order number they just want to add a 1 to times_used column.  End result would be a different program that looks through these new tables.  They want to be able to determine what needs to be allocated for an order by looking at what was previously allocated on other orders. Is there any way this can be one program and not two?  The information is already in the tables I don' know why I couldn't just do this with 1 program.

This is where views come in handy, and an understanding of relational databases. You can store each entry in a table dedicated for that. Each entry can have its own PK value that lets you differentiate between it. However, each entry can refer to a separate table to identify the Customer, Order, LineSeq. If you need to insert an entry for a Cus+Ord+LS that doesn’t exist yet, you create a new one, with its own PK value. Then you create a view which joins those two tables, including a COUNT_BIG(*) aggregate to count how many times they’re used. You could even index the view so that the values are worked out ahead of time.

To use this the way you want, you could put an “instead of trigger” on your view that handles the inserts/deletes into your entries table. That’s essentially a procedure that you interact with by doing inserts into a view, even though your view can’t handle inserts.


Bulk update of all columns I am wondering if PostgreSQL has an update query somewhat like their insert values syntax.

I have an updated set of data in this form currently:

INSERT INTO bought_in_control_panel(ID,PARENT_ID,BOUGHT_IN_FORM_TYPE_ID,PRIORITY,NAME,HEADING,DESCRIPTION,ICON,BOUGHT_IN_CONTROL_PANEL_FILE_ID)
VALUES(109,1,28,100,'Tooling','Tooling','Enter your Machine Tools here','tooling.png',null);

and I want to update the rows that are there already to the new data, I'm looking for something like the following where I can update all rows without repeating myself:

UPDATE ON ID
bought_in_control_panel(ID,PARENT_ID,BOUGHT_IN_FORM_TYPE_ID,PRIORITY,NAME,HEADING,DESCRIPTION,ICON,BOUGHT_IN_CONTROL_PANEL_FILE_ID)
VALUES(
        (109,1,28,100,'Tooling','Tooling','Enter your Machine Tools here','tooling.png',null),
        (1,0,1,200,'Bought In','Bought In','','boughtin.png',null)
);

The above would check for matching ID values, and update the matches with the new data.

Note: I am really looking to avoid manually mapping all the column names. PostgreSQL already knows my columns, and I've already mapped them on the data. Why do that again in a longer format?

If you only want to update data, I'm not sure what the INSERT statement is for in your question.

If you just want to update several rows with a single statement, you might be looking for this:

with update_values (ID,PARENT_ID,BOUGHT_IN_FORM_TYPE_ID,PRIORITY,NAME,HEADING,DESCRIPTION,ICON,BOUGHT_IN_CONTROL_PANEL_FILE_ID) as
(
  VALUES
     (109,1,28,100,'Tooling','Tooling','Enter your Machine Tools here','tooling.png',null),
     (1,0,1,200,'Bought In','Bought In','','boughtin.png',null)
)
update bought_in_control_panel
   set parent_id = ud.parent_id,
       bought_in_form_type_id = ud.bought_in_form_type_id,
       ....
from update_values ud
where ud.id = bought_in_control_panel.id;


Using GROUP BY and ORDER BY in my query works in MySQL but not in SQL Server I have a query which runs correctly on MySQL server. In SQL Server it gives error:

  "Column 'Tovar.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" .

I don't know how to convert it in to SQL Server. Looking forward
for the help from guru's.

SELECT B.BBarcode,
       Name, C.Country,
       Manufacturer,W.id,
       Packtype,
       B.TovarId,
       SUM(WCount) as count,
       Price,
       Series,
       ExpDate
FROM Warehouse W, Tovar T, Barcodes B, Countries C
WHERE C.CId=T.Country
   AND B.WarehouseId=W.id
   AND WCount>0
   AND T.id=W.TovarId +'=@param'
GROUP BY W.ID,B.BBarcode
ORDER BY Name

To make this run all of the columns in the select have to be in the group by portion of the query.

Select B.BBarcode , Name, C.Country, Manufacturer,W.id,
 Packtype, B.TovarId, sum(WCount) as count, Price, Series, ExpDate
  from Warehouse W, Tovar T, Barcodes B,
  Countries C
  where C.CId=T.Country and
   B.WarehouseId=W.id and WCount>0 and
   T.id=W.TovarId +'=@param'
   GROUP BY B.BBarcode , Name, C.Country, Manufacturer,W.id,
 Packtype, B.TovarId, Price, Series, ExpDate
   ORDER BY Name

However I’m not sure the query will run with the where syntax you have, specifically the T.id=W.TovarId +'=@param' portion.

That can be refactored to be either

T.id=W.TovarId and T.id = @param

Or

T.id=W.TovarId and W.TovarId= @param

Version that should execute

Select B.BBarcode , Name, C.Country, Manufacturer,W.id,
 Packtype, B.TovarId, sum(WCount) as count, Price, Series, ExpDate
  from Warehouse W, Tovar T, Barcodes B,
  Countries C
  where C.CId=T.Country and
   B.WarehouseId=W.id and WCount>0 and
   T.id=W.TovarId and T.Id = @param
   GROUP BY B.BBarcode , Name, C.Country, Manufacturer,W.id,
 Packtype, B.TovarId, Price, Series, ExpDate
   ORDER BY Name

I would also look at some documents on how sql server does joins and the group by. While I believe sql server will do the joins correctly for an inner join between the tables based on how you’ve written them, the syntax structure they have make it a little more clear what is joining to what as well as the options to do other types of joins


Loading subscription slow in SSRS I have a SQL Server 2014 test box with SSRS installed. The site loads ok and reports are quick. When I try to go to Subscribe the page takes over a minute to load. From there if I pick a schedule it also takes minutes to load. This is a fresh box with nothing else installed. There's not antivirus installed.

The box has 2 x xeon @ 2.66 and 6GB of memory. I'm the only user on this box as it is just for testing.

This may be too late but try looking at the File settings for ReportServer and ReportServerTempDB, just like you normally would through SSMS on the instance. They may be defaulted to a few MB with a 1MB autogrowth.


Postgres requested WAL segment has already been removed (however it is actually in the slave's directory) I am using repmgr as my replication tool. On the slave I keep getting an error:

  requested WAL segment  has already been removed

When I check the Master indeed it is not there; however, it is in the slave's directories both in pg_xlogs and pg_xlogs/archive_status. I can't understand why it would be looking for this file if it's already in the slave?

In fact it has xlogs going past the requested one. The solutions in What to do with WAL files for Postgres Slave reset are for a slightly different problem. They seem to be for a scenario where the master deletes a log file before the slave receives it. In my case it is very much present on the slave and several other files in the sequence after the one being requested.

This also tells me I do not need to increase the keep wal segments option as it didn't seem to fall behind?

I simply took a gamble and copied the one file back to the master which was missing. It worked i still don't know what caused the issue.


PostgreSQL selecting empty fields of integer type I have a table and I need to select all the rows with an empty value for the fk_fc_id field (as a prelude to deleting them),

    Column     |            Type             |                         Modifiers
---------------+-----------------------------+------------------------------------------------------------
 di_timestamp  | timestamp without time zone |
 di_item_value | character varying(10)       |
 fk_fc_id      | integer                     |
 di_id         | integer                     | not null default nextval('data_item_di_id_seq1'::regclass)

However this doesn't work,

# select fk_fc_id,di_timestamp,di_item_value from data_item where fk_fc_id="";
ERROR:  zero-length delimited identifier at or near """"
LINE 1: ...di_timestamp,di_item_value from data_item where fk_fc_id="";
                                                                    ^

Trying Null doesn't work either.

If anyone has any suggestions on how to sort this, I would be very grateful.

This is not really database administration related, nor is it really about PostgreSQL, but as @foibs answered, you should have a look at IS NULL:

 SELECT fk_fc_id,
        di_timestamp,
        di_item_value
 FROM data_item
 WHERE fk_fc_id IS NULL


MySQL replication slave VERY slow I am working on a replication problem that completely baffles me! This client has two MySQL replication clusters on HUGE bare metal HW. See Environment below.
The IO_Tread of the slave is WAY behind, several hours or more. Yes, the IO_tread, not the SQL_tread. Why is it so hard to download not all that large binlog records and write them to disk. I tried finding a resource bottleneck, but given the massive HW none can be found.
The only strange observation is that the slave has 8x the IO OPS than the master. But even this does not really overload the SSD disk. Packet traces show the SLAVE often setting the TCP window to zero. Why, there are plenty resources?
Anyone with ideas what could be causing this strange behaviour? Why do I have more IO on the slave? What can cause the IO_tread to slow down?
Environment:
Both machines:
Bare metal DELL,   MySQL 5.6.30,   12CPUs,   128GB mem,
datadir on SSD, Net I/F:  Emulex 10Gb,  ROW based binlog FMT
Symptoms:
MASTER:
CPU:  67%  1 processor lightly used,   MEM:  70% used, 30% free,
IO OPS: ~2500 tps,  30% util on SSD,   slave client tread: Send binlog to slave.
SLAVE:
CPU:  40%  1 processor lightly used,   MEM:  70% used, 30% free,   IO OPS:   ~16000 tps,  70% util on SSD,   Error counters on net I/F are 0 (zero),
TCP window is often set to 0 on IO_tread,
Slave IO_tread is VERY slow. Lags more than an hour!
Another slave on SAME master has no trouble at all! This slave has much lower HW spec!
Trouble downloading master binlog.
Why this insanely high IO rate?
Stopping the slave also stops the IO OPS. (As expected, the OPS are from MySQL)
Copying large amounts of data from master to slave over the network (using ncat) show performance as expected.
Other observations:
When reversing the roles the problem stays the same.
Another replication cluster with same HW has no trouble. IO OPS in this cluster on slave are slightly less than on master. This cluster uses STATEMENT based binlog

Sorry, I need a 50 rep to comment, so...  Here's a couple of things to start.

Are you by chance using ZFS for your filesystem (it can throttle the disk unexpectedly)?
Are your config files (nearly) identical (with exception of replication master/slave settings)?  The config should be the same as the other slave except maybe memory buffers (sounded like the server HW was different).  https://www.percona.com/doc/percona-toolkit/2.2/pt-config-diff.html
(Run on the slave, and post) SHOW SLAVE STATUS\G
(Run on the master, and post) SHOW MASTER STATUS\G
Check the Slave Status a few times (maybe a minute apart). Are both Exec_Master_Log_Pos and Read_Master_Log_Pos (or Retrieved_Gtid_Set and Executed_Gtid_Set), moving (ideal is if they are catching up)?
Are the relay logs being stored as tables or files?

Edit
After reading Rick's post, I went back and noticed (thought at first both were SBR) your cluster that runs faster is running SBR (statement based replication), and the slow one is RBR (row based).  Rick's question about the type of queries and how many binlogs you generate in say an hour is important.

Sometimes RBR is faster, and sometimes SBR wins.  Always important to test and benchmark all scenarios.


Force some composite type fields to be unique? I want to create multiple image paths based on the language and size of the image. I decided to CREATE TYPE and then use it in the table.
CREATE TABLE asset (
  id bigserial not null constraint primary key,
  main_image img[]
);

CREATE TYPE img AS (
  lang varchar(2) not null,
  size varchar(32) not null,
  img_path varchar(255) not null
);

How do I make something like this: unique(lang, size) for my composite type? I want this combo to be unique.
How do I restrict any inserts that try to add an img_path for lang & size combo that already has img_path defined? I have tried to CREATE DOMAIN, but it seems like it is not intended for it.

First of all, you can't add constraints to types therefore the type definition you've provided will error out.
Domains as you've already observed won't work since they can only accomodate a single type at a go rather than a composition of types.
You also don't have to add a not null constraint to a primary key - it's redundant, Postgres by default disallows null values for primary key columns.
The best approach will be to normalize the table asset so that you can maintain the restrictions you require since both domain definitions and type defintions can't accomodate these restrictions.
For the sake of completion, here's a suggestion for the table defintions.
Given that an asset has multiple images but an image can only belong to one asset, a one-to-many relationship captures this perfectly as shown below. Furthermore, given how your code is currently structured, different assets can have the same (lang, size) combo. Therefore, the unique constraint is added across both the asset_id, lang and size
create table asset (
  id bigserial primary key,
  /* other asset columns ... */
);

create table img (
    asset_id bigint references asset(id) not null,
    lang varchar(2) not null,
    size varchar(32) not null,
    img_path varchar(255) not null,
    unique(asset_id, lang, size)
);

Note, the unique constraint creates an index hence the checks will be fast. Therefore, as expected, the following insert fails:
insert into img
values
    (1, 'en', '32 kb', 'bar path'),
    (1, 'en', '32 kb', 'foo path');


What are the most effective techniques to reduce SQL Server disk space use when updating all rows in a very large table? Given a SQL Server table with

a large number of rows
no columns with large-value data types
multiple indexes
more allocated space than available for the largest possible transaction log size
a single-column primary key with clustered index (optional consideration for this question)
an average record size of 1k (optional consideration for this question)

and an update statement which

needs to be run against every row
sets a value on a non-indexed column (optional consideration for this question)

What techniques can be employed to reduce the peak disk space consuption (including data files, log file and tempdb - if applicable) required to do this update?

For purposes of this question, the following is allowed:

applying changes in batches
run in single-user mode
change recovery model

I've just gone through a similar process just couple of weeks ago. After several tries, with couple of the bigger tables (one of them more than 100million rows, near 80Gb) I came up with these steps to speed up things and keep transaction log small:

ALTER DATABASE [my_db] SET RECOVERY SIMPLE WITH NO_WAIT (check here for more info)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED (check here for more info)
drop nonclustered indexes: this step will avoid the hassle with the need of updating them on each operation (check Brent Ozar blog for more details)
use batches: as suggested by mrdenny, do whatever you need to do, update, delete, inserts, in smaller chunks.

Here is a sample of batch processing for an update, 1000 rows at a time:

 UPDATE TOP(1000) your_table
 SET    col1 = new_value
 WHERE  <your_condition>
 WHILE  @@rowcount > 0
 BEGIN
     UPDATE TOP(1000) your_table
     SET    col1 = new_value
     WHERE  <your_condition>;
 END;
 GO

restore nonclustered indexes
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
ALTER DATABASE [my_db] SET RECOVERY FULL;


Delete from table where multiple fields match select subquery from other table I want to delete an entry in a table where multiple fields match the results of another select subquery which takes data from another table.

This is what I have so far, though it doesn't work:

DELETE FROM table1
WHERE table1.id IN
   (SELECT id
   FROM table1 a JOIN table2 b
   ON a.field1 = b.field1
   AND a.field2 = b.field2
   AND a.field3 = b.field3
   AND b.id = ?
   )

Perhaps the DELETE JOIN would be preferable

DELETE A.* FROM table1 A INNER JOIN table2 b
ON  A.id = B.id
AND a.field1 = b.field1
AND a.field2 = b.field2
AND a.field3 = b.field3
AND b.id = ?;

I wrote about why DELETE with WHERE involving a subquery is sort of unhealthy to deal with in a past post of mine back on Feb 22, 2011: Problem with MySQL subquery

Even though there have been great strides in improving the Query Optimizer, evaluation of subqueries could make keys unavailable for key comparisons downstream.

ALTERNATIVE

Try gathering the keys you know need to be deleted and do the DELETE JOIN with it:

CREATE TABLE DeleteIDs SELECT id FROM table1 WHERE 1=2;
INSERT INTO table1
    SELECT A.id FROM table1 A INNER JOIN table2 b
    ON  A.id = B.id
    AND a.field1 = b.field1
    AND a.field2 = b.field2
    AND a.field3 = b.field3
    AND b.id = ?
;
ALTER TABLE DeleteIDs ADD PRIMARY KEY (id);
DELETE B.* FROM DeleteIDs A INNER JOIN table1 B;
DROP TABLE DeleteIDs;

SUGGESTION

Maybe an index would help

ALTER TABLE table1 ADD INDEX id123 (id,field1,field2,field3);
ALTER TABLE table2 ADD INDEX id123 (id,field1,field2,field3);

Give it a Try !!!


Is it possible to Clone a RedShift Database? For some test and development work we would like the ability to routinely clone existing RedShift databases (all schema and all data).  Something like a backup-restore.

The best I can find starts by creating a snapshot of the entire cluster, which is not practical at this point in time.

We do have scripts for creating the database and the schema, but we don't have a simple/tidy mechanism for populating the tables from another AWS RedShfit database.  (We've found that we can't use INSERT INTO db_new.schema.table SELECT * FROM db_old.schema.table as cross-database syntax is not supported)

Does anyone have any suggestions?

You can do the following:

UNLOAD your data to S3 (http://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html)
Run your deployment scripts against the new database
COPY the data into the new database (http://docs.aws.amazon.com/redshift/latest/dg/r_COPY.html)

This (UNLOAD/COPY) will be fairly quick for a development size dataset.


Create a database and make it "current" I have an sql-script that recreates my unit-testing database. At the moment it's run with \i <path_to_my_script>. It goes like

// create user, give him some priveleges

CREATE DATABASE my_database;

CREATE TABLE my_table (
  id UUID primary key,
  data json
);

But my_table is created in postgres database, not in my_database.

How can I specify that my_table should be created in my_database?

I use postgresql 10.5.

In 'psql', you can change the database you are connected to by using the \c metacommand.  So it would look like this:

CREATE DATABASE my_database;
\c my_database
CREATE TABLE my_table (
  id UUID primary key,
  data json
);

This works from '\i' included files.  When the \i file is done, you will be left in the new database unless you do something to change back to the old one.


Array column vs another table when the relationship is quite small The Data

I am creating a table of cities and their zip codes for the United States. Some zip codes have a list of acceptable cities, as designated by the USPS. For example, the zip code 36613 is associated with Eight Mile, AL, but the University of Mobile is in 36613 and it's address is in Mobile, AL. A look at USPS data and I see that Mobile is on the acceptable cities list for zip code 36613.

The Question

Now, I have a few routes that I can take:

I can create a new table called acceptable_city with a FK/PK relationship on city.
Create a column in city called acceptable_city that is defined as an array (acceptable_city text[]), and create an index on this column.

So, in a case like this which option is more advantageous? What are the pros and cons of each approach?

There is no clear answer to that. Both variants have pros and cons. Many here will advice to properly normalize data with a separate table.

You might get best of both worlds with a MATERIALIZED VIEW:
Properly normalize your data model, and offer an aggregated view on the data in the materialized view.


Import lots of mysqldumps with missing CREATE DATABASE I have a bunch of mysqldumps of my old server with one database each.

Unfortunately we forgot to add

CREATE DATABASE ...

in all those dumps.

How can I import those files in one batch?

This script creates all databases accordingly to the names of the files:

PW=your password here
for i in *.sql; do db=$(echo $i|sed s/.sql$//;);
  echo $db ...;
  mysql -u root -p$PW -e "create database $db";
  mysql $db -u root -p$PW < $i;
done


Merge Replication - Merge initial data contained on subscriber I have two identically structured databases on two separate servers.  Both have different data within them.

I have set one of them up with merge replication as a publisher and I want the other to be a subscriber.

The problem is that when I create the subscription, the publisher sends its snapshot to the subscriber and it clears all of the data from the subscriber database before it begins the sync process.

How can I preserve the data in both databases and set them up so that one is the publisher and the other is the subscriber?

You will need to manually merge the existing databases and initialize from that source.

One challenge you might face in manually merging the databases is conflicting primary keys.  If so, this is typically handled by adding a location-specific identifier column and extending the primary key to include this column.  This is known as partitioning your data.


SQL To Return True False base on condition where it either matches the condition or is completely null I have written an SQL query which I am thinking might not be right.
I have Table A(Column A_PK, B, C, D), Table B(Column A_PK, C_PK), Table C(C_PK,..)
Table B is associative entity for A and C.
i,e it has two columns A_PK from Table A and C_PK from table B.

I want to select All As where it has an entry in B and matches my search param for C_PK.

  SELECT * FROM
  A a
  WHERE EXISTS(SELECT 1 FROM B b WHERE A_PK = a.A_PK AND b.C_PK = @mysearch)
  OR NOT EXISTS(SELECT 1 FROM B WHERE A_PK = a.A_PK

Is this fine, OR I should use left join.?

Thanks in advance for looking at it.

Yes, this code will work, although I would put the NOT EXISTS check before the EXISTS check if the NOT EXISTS condition will be matched very frequently (more than 10-20% of the time, depending on actual performance). Other than that, this query should do what you need it to do.


Index design question This query runs for 60 seconds:

SELECT mychecklis0, ....
FROM dbo.MyChecklistStatus mychecklis0_
WHERE mychecklis0_.AppFormSubSectionId=@P0 AND mychecklis0_.ProfileId=@P1

There is an index created on profileid and AppFormSubSectionId

My question:

Does the arrangement of columns in the where statement comparing the column id in the sys.index_columns table matter?

Which is better, creating an index for each col, or 1 index for multiple columns?

It can't use two separate indexes to satisfy that WHERE clause - it will have to pick one or the other. If you want to enable seeks you will need to make a single index with both columns, and order them from most selective to least selective. Think about designing indexes in such a way that SQL Server can eliminate as many rows as possible, as quickly as possible, rather than the traditional way most people think about it (finding as few rows as possible).

You should also consider adding any other columns from the SELECT list to the INCLUDE list of the index (making it "covering"), this way you can avoid key lookups. So, perhaps, drop the two indexes you have and create (guessing at selectivity):

CREATE INDEX IX_BetterIndex
  ON dbo.MyChecklistStatus -- My? Really? Why?
    (ProfileId, AppFormSubSectionId)
  INCLUDE(mychecklist0, ...);

(Note that you don't have to include the clustered index key column(s), since they exist in all non-clustered index pages anyway.)

This is a general recommendation for something to try. It could very well be that selectivity is high enough that the number of key lookups does not justify using space in the index pages for the covering columns. It could also be that this query isn't run often enough to optimize the indexes on the table to help this query in a vacuum - there may be other queries that are run more frequently that would benefit from a different index. And it may be that your SELECT list has columns in it that aren't really necessary, which can change the formula further. There is no one right answer.

And no, the order of columns in the index has no relevance whatsoever to the ColumnId order in sys.columns.


using foreign key as primary key MySQL:

If I have a user table and multiple other tables like seller, buyer etc, is it okay to use user_id foreign key as primary key inside tables like seller, buyer?

That'll leave gaps in primary key field of such tables. Would that result in slower queries? Also, will mysql attempt to use up the gaps by inserting new items in middle (which I don't want)?

Primary keys must be unique. As long as any particular user/user_id can't become more than a single seller/buyer (each), you will be fine. Whether or not that restriction does hold, depends on your domain. It would, e.g., fail, if a user can run multiple shops with each shop's owner being (considered) a distinct seller. (You might, e.g., want to attach contact information to a seller, which could be different from shop to shop - but specific to the seller, not to the shop. Just think of co-owned businesses…)
In case of doubt, you might want to play safe, and not make the foreign key the primary key to save you from the trouble of propagating a redesign through your schema some time in the future.

As for MySQL trying to attempt to fill in the gaps: If it is a foreign key, you(r application) will be the only one to insert records. Thus: No.


How user options works at SQL Server? I'm searching one way to set ARITHABORT ON for all opened connections, and I found this question.

Some answers says to run the following command, and it really works:

EXEC sys.sp_configure N'user options', N'64'
GO
RECONFIGURE WITH OVERRIDE
GO

But, for me run this command in my company I need some explanations, and I searched a lot of about user options, but I don't understand some things:

Why the value needs to be 64?
How does it works?

User options is a combination of bit values, for example:

16    ANSI_PADDING
32    ANSI_NULLS
64    ARITHABORT

To turn on only ARITABORT the value should be 64, and for example for ANSI_NULLS and ARITHABORT it should be 96.

Full list of values available here


Number of data files in tempdb in Availability Group We have configured AlwaysOn Availability Group with 3 node in Sync mode. The primary server got 16 core while the replicas have 10 core each. We have tempdb contention issue, just wondering if we can have different number of tempdb data files on nodes in Availability Group or do we have to have same number on each node. It is SQL Server 2016 Enterprise Edition.
Thanks in advance for help.

There is not a problem with having different numbers of tempdb files on each machine,  you just need to be aware that should you failover to one of the machines with fewer files that you will run into the contention problem that you worked to alleviate on the new server.


How much memory will a MEMORY table take up? Let's say I have a MyISAM table with a data length of 4.8GB and an index length of 6.2GB. So, a total data size of eleven gig. How much memory would this require, were I to convert it to a MEMORY table? 11 gig, or more?

The exact memory requirement of a row is calculated from the following formula:

SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) × 4)
+ SUM_OVER_ALL_HASH_KEYS(sizeof(char*) × 2)
+ ALIGN(length_of_row+1, sizeof(char*))

[src]

  ALIGN() represents a round-up factor to cause the row length to be an exact multiple of the char pointer size. sizeof(char*) is 4 on 32-bit machines and 8 on 64-bit machines.

So, on a 64-bit machine, replace sizeof(char*) with 8.

You can get an estimate of the length_of_row from the Information Schema:

SELECT TABLE_ROWS, AVG_ROW_LENGTH FROM information_schema.tables WHERE  table_schema='foo' AND table_name='bar';

Then you add up all your BTREE keys and then HASH keys. Note that it might be worth it space-wise to convert any keys to HASH, as they require less memory.

I was going to mention the limitation of maximum MEMORY size dependent on max_heap_table_size, but gbn beat me to it.


SQL - Need some advices for building a large db currently, as I understand, to build a large db, we need to pay attention to its size and performance. Below are what to reach:

Size: HDD has > 4tb to store filegroup if using partition tables.
Performance: Using partition tables. In diagram, we will not create relationship among tables. I heard that it will help us query data faster.

But, in reality, I don't know it is good solution or not, please advice me with some concerns:

if I have 100 milion records in each tables, and using parttion table, each quarter is 1 table/part. So, when I need to report in 1 year, how is its performance to query/filter data?
With 100 milion records, it is posible to query data faster with no relationship?
How about insert data with checking exist data first? it will take a long time to check duplicate before insert?
How about replicate data by using partition table?

I know that, some ways, this is the job of DBA, but I readly want to understand them, please advise me.

I'm very appriciate for you helps.

Thanks.

I think you're having a hard time with this because I'm not sure you're looking at it from the right perspective. Database performance is a big topic and generally depends on an enumerable number of factors. But table partitioning is only addresses parallelism across cores vs. parallelism across threads. Generally speaking, the bottle-neck is not going to be there.

Let me give you some advice. Don't worry about partitioning - leave it in whatever state it's currently in - but do focus on the following.

What's more important, reads or writes? If it's reads then ensure the queries you are performing against the database are optimized by building covering indexes where you can - reading the data page is expensive. If it's writes then be careful how many indexes you build because of the shear size of your data, so only build what you have to because rebuilding the index is expensive if there are a lot of transactions.
Key constraints. Do not go down the road of not building proper foreign key constraints. When you build a foreign key it will build an index, you do not want to build in the referential integrity yourself, there is no performance bottle-neck with foreign keys. If you are using a database engine where foreign keys are a bottle-neck you should probably consider a different engine.
Last but not least, tune your database to your usage. A substantial amount of usage statistics are needed to understand exactly how to tune your database and because of the size of your database it will need tuned regularly.

I'm sorry if this feels nebulous, but you have a huge task before you and database optimization is a regular process on a database of that size. So, in short, the aforementioned items will start you down the right path but there is no answer to your whoas.


Error 1366: Incorrect string value: '\xF0\x9F\x98\x8A\x0AC…' for column 'text_body' at row 1 I'm trying to store email content in mysql.

But i'm facing errors while trying to insert the row.

I have done some research and changed the column type to utf8mb4. But I'm still facing the problem.

Can someone tell me what i'm missing?

This is the error I'm getting

Error 1366: Incorrect string value: '\xF0\x9F\x98\x8A\x0AC...' for column 'text_body' at row 1

This is the character set of my table DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

The CHARACTER SET of the column needs to be utf8mb4.  Please provide SHOW CREATE TABLE for confirmation.

Also, the connection needs to be utf8mb4 (or UTF-8, depending on the client language).  Let's see the connection parameters.


query to determine forceencryption state I'm trying to confirm whether or not force encryption is currently set and/or if the current connection is secure or not.

Is there a SQL query I can run that will give me that information or do I need to log onto the machine and interrogate it from the OS?

To determine the setting you need to read from the registry. Replace your version/instancename in the query below:

EXEC [master].[dbo].[xp_regread]    @rootkey='HKEY_LOCAL_MACHINE',
                                    @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL<version>.<instancename>\MSSQLServer\SuperSocketNetLib',
                                    @value_name='ForceEncryption'

You should be able to get the correct path from dm_server_registry which unfortunately doesn't give me the ForceEncryption property.

SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry

To determine the current connections encryption state you can just select them from sys.dm_exec_connections

SELECT encrypt_option, *
FROM sys.dm_exec_connections