Migrating Production SQL Servers (Availability Group) from one data center to another - questions We have two SQL servers combined into Availability Group in data center 1 (dc1):

dc1 server 1: SQL Server 2012 SP3 on Windows Server 2016
dc1 server 2: SQL Server 2012 SP3 on Windows Server 2012 R2

and async replica in Azure

azure server 3: SQL Server 2012 SP4 on Windows Server 2012 R2

We have a new data center (dc2) where we need to migrate production SQL availability group to

The end goal is to have two SQL Server 2017 machines
(server 4 and server 5)  in new data center (dc2) on Windows Servers 2016, combined in AG

After successful migration to dc2 (server 4, server 1 and server 2 in dc1 are going to be abandoned, we do not need to keep server 1 and 2 as replicas in AG after production db is switched to be server 4 and 5

We have below questions:

if deploy server 4 and server 5 at dc2 as SQL Servers 2017, and add them to existing AG as secondaries, will they be kept synchronized with primary (server 1) without any problem, despite difference in SQL Server and Windows versions ?
will adding server 4 and server 5 to AG and then manual failover to server 4 to be new primary (when we are ready to switch applications to use server 4 / listener new IP as production) work fine ?
And then server 4 and server 5 will be synchronizing, and server 1,2,and 3 will break and stop syncing ?
Is my understanding correct ?
are there any better options of migrating production from dc1 to dc2 with short downtime?
is it OK to deploy server 4 and server 5 as virtual machines in dc2 ?
Or it is better to buy dedicated physical machines ?
We want a potential automatic failover from server 4 to server 5 to work perfectly in future. Can having SQL Servers virtualized introduce potential problems with automatic failover function?

(you have lot of sub questions as part of main question .. will get straight to answering your questions ... )

  if deploy server 4 and server 5 at dc2 as SQL Servers 2017, and add them to existing AG as secondaries, will they be kept synchronized with primary (server 1) without any problem, despite difference in SQL Server and Windows versions ?

Yes, they will ... BUT you will have limit on number of replicas - synchronous with automatic failover :

SQL Server 2012 AG Limits: 1 primary replica, 4 secondary replicas, 2 of the replicas can be synchronous.

So practically, if you were to add additional replica in your current topology, you would be able to add only 1 server (2 you have in DC1, 1 cloud and 1 in DC2 (either server 4 or 5) - this will make 4 replicas which is max).

You could then pre-stage server 5 in DC2 to be logshipped. SO when you cutover to server 4, you can easily join server 5 dbs to AOAG without taking full & log backup of your databases.

  will adding server 4 and server 5 to AG and then manual failover to server 4 to be new primary (when we are ready to switch applications to use server 4 / listener new IP as production) work fine ? And then server 4 and server 5 will be synchronizing, and server 1,2,and 3 will break and stop syncing ? Is my understanding correct ?

See my answer 1. Once you failover to 2017, you wont be able to switch back to 2012. Remember that it will have the same old IP if listener. You can technically add 2nd listener (as client access point) with an OR dependency of your AG on both the listeners. This way once you decom you old listener, your AG will not go offline.

  are there any better options of migrating production from dc1 to dc2 with short downtime?

You should explore setting mirroring between DC1 and DC2 leaving the AlwaysON bit separate. This way, you just cutover from DC1 to DC2 and point your application to the new listener in DC2. This will be more staright forward and easy to rollback since both DC1 and DC2 are decoupled and failover and failback (point to old DC1) will be more manageable.

This is an upgrade as well (2012 --> 2017). I have written very detailed steps in my answer here.

  is it OK to deploy server 4 and server 5 as virtual machines in dc2 ? Or it is better to buy dedicated physical machines ? We want a potential automatic failover from server 4 to server 5 to work perfectly in future. Can having SQL Servers virtualized introduce potential problems with automatic failover function?

I would highly suggest to be a 1-1 match. If old servers are physical, go with physical with same or more resources (CPU, RAM and Disk (match mountpoints, folder layout, etc)). Physical vs Virtual is a whole different debate with many facets and it would be unfit in the current scope of the question.

Note: If you still have questions, I would highly suggest hire a professional as you might run into issues if you do not do a dry-run  end-to-end migration test.


How to use aes-encryption in PostgreSQL? I tried aes-encryption by using following statement:

SELECT encrypt('test', 'key', 'aes');

which worked, but I am not able to decrypt the value. I inserted it in a field of datatype bytea but I'm not sure if that was the right way.

SELECT decrypt(pw, 'key', 'aes') FROM table WHERE ID = 1;

gives me the error

  ERROR:  function decrypt(bytea, unknown, unknown) does not exist
  LINE 1: SELECT decrypt(pw, 'key', 'aes') FROM tabelle WHERE ID = 7;
                 ^
  HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Does that really mean that encrypt() is an existing function, but not decrypt()? How else could I retreive aes-encrypted values?

\df *crypt in psql reveals the argument types of the pgcrypto encrypt and decrypt functions (as do the PgCrypto docs):

                                List of functions
 Schema |      Name       | Result data type |   Argument data types    |  Type
--------+-----------------+------------------+--------------------------+--------
 ...
 public | decrypt         | bytea            | bytea, bytea, text       | normal
 public | encrypt         | bytea            | bytea, bytea, text       | normal
 ...

so both the encrypt and decrypt functions expect the key to be bytea. As per the error message, "you might need to add explicit type casts".

However, it works fine here on Pg 9.1, so I suspect there's more to it than you've shown. Perhaps you have another function also named encrypt with three arguments?

Here's how it works on a clean Pg 9.1:

regress=# create table demo(pw bytea);
CREATE TABLE
regress=# insert into demo(pw) values ( encrypt( 'data', 'key', 'aes') );
INSERT 0 1
regress=# select decrypt(pw, 'key', 'aes') FROM demo;
  decrypt
------------
 \x64617461
(1 row)

regress=# select convert_from(decrypt(pw, 'key', 'aes'), 'utf-8') FROM demo;
 convert_from
--------------
 data
(1 row)

Awooga! Awooga! Key exposure risk, extreme admin caution required!

BTW, please think carefully about whether PgCrypto is really the right choice. Keys in your queries can be revealed in pg_stat_activity and the system logs via log_statement or via crypto statements that fail with an error. IMO it's frequently better to do crypto in the application.

Witness this session, with client_min_messages enabled so you can see what'd appear in the logs:

regress# SET client_min_messages = 'DEBUG'; SET log_statement = 'all';
regress=# select decrypt(pw, 'key', 'aes') from demo;
LOG:  statement: select decrypt(pw, 'key', 'aes') from demo;
LOG:  duration: 0.710 ms
  decrypt
------------
 \x64617461
(1 row)

Whoops, key possibly exposed in the logs if log_min_messages is low enough. It's now on the server's storage, along with the encrypted data. Fail. Same issue without log_statement if an error occurs to cause the statement to get logged, or possibly if auto_explain is enabled.

Exposure via pg_stat_activity is also possible.. Open two sessions, and:

S1: BEGIN;
S1: LOCK TABLE demo;
S2: select decrypt(pw, 'key', 'aes') from demo;
S1: select * from pg_stat_activity where current_query ILIKE '%decrypt%' AND procpid <> pg_backend_pid();

Whoops! There goes the key again. It can be reproduced without the LOCK TABLE by an unprivileged attacker, it's just harder to time it right. The attack via pg_stat_activity can be avoided by revoking access to pg_stat_activity from public, but it just goes to show that it might not be best to send your key to the DB unless you know your app is the only thing ever accessing it. Even then, I don't like to.

If it's passwords, should you store them at all?

Furthermore, if you're storing passwords, don't two-way encrypt them; if at all possible salt passwords then hash them and store the result. You usually don't need to be able to recover the password cleartext, only confirm that the stored hash matches the password the user sends you to log in when it's hashed with the same salt.

If it's auth, let someone else do it for you

Even better, don't store the password at all, authenticate against LDAP, SASL, Active Directory, an OAuth or OpenID provider, or some other external system that's already designed and working.

Resources

Secure Password authentication explained simply - CodeProject
Wikipedia: Challenge-response authentication
Wikipedia: Cryptographic hash function
Wikipedia: Salt (cryptography)
Wikipedia: Password

and lots more.


Kafka Internal Data structure vs LSM tree I was going through Database storage engines and found out about LSM trees. I had also read about Kafka architecture and know that Kafka internally is a commit log. I want to know if Kafka internally uses LSM data structure for append-only store or uses some other data structure for storing data?

No Kafka , does not use LSM trees or any tree based data structure for data representation. It relies upon Sequential IO so that it is not relying upon JVM to maintain  cache which would then be presenting as an overhead.
Kafka generally implements a queue based structure rather than a tree based structure (memory + merge sort on disk , something like sstables as disk store format).
Hope this helps a bit


Move data from MySQL 5.0 to other server with MySQL 5.7 We are switching servers from a Windows Server 2003 (32-bit) with a MySQL 5.0.51b to a Windows Server 2016 (64-bit) on which I will install the latest MySQL version (5.7).

The database contains just tables (no views, foreign keys...) which I want to move to the new database. Should I be able to use mysqldump and mysqlimport (as per the documentation) even though the difference in MySQL versions?

mysqldump and mysqlimport (as described by the documentation you cited) should be fine for your regular tables, especially since you don't have any foreign keys.

However, due to the differences in MySQL versions, the tables in the mysql database will be different, so I suspect migrating that database probably can't be done with this particular method.

If you need to migrate the mysql database, there are better ways of doing that.


Get Headers from SQL Table I am looking for a way to dynamically get the headers from a table without having to write them (select 'header1' as "header1"...., 'header50' as "header50" from MyTable)
It doesn't work above a certain number of columns, and/or the fixed limit of characters in a query

The goal is to export the headers and the data from the table to a text file or an excel file with a BCP command.

Can you help me out, please ?

If using xp_cmdshell and Powershell is an option (rather than BCP), you could achieve your goal as follows:

--Demo setup to create and load a table with some data
SET NOCOUNT ON
DROP TABLE IF EXISTS [dbo].[CUSTOMER];
CREATE TABLE [dbo].[CUSTOMER](
    [CustomerID] [int] NOT NULL,
    [FirstName] [varchar](20) NOT NULL,
    [LastName] [varchar](20) NOT NULL,
    [Email] [varchar](30) NOT NULL,
    [PhoneNo] [int] NOT NULL,
    [StreetAddress] [varchar](40) NULL,
    [City] [varchar](20) NULL,
    [OrderID] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[CUSTOMER] ([CustomerID], [FirstName], [LastName], [Email], [PhoneNo], [StreetAddress], [City], [OrderID]) VALUES (13579246, N'David', N'Casey', N'dcasey@gmail.com', 12826363, N'34 Bray Rd', N'Dublin', 94724274)
GO
INSERT [dbo].[CUSTOMER] ([CustomerID], [FirstName], [LastName], [Email], [PhoneNo], [StreetAddress], [City], [OrderID]) VALUES (16579946, N'Donal', N'Smith', N'dsmith@gmail.com', 13826563, N'78 Ballybrack Rd', N'Dublin', 14724274)
GO
INSERT [dbo].[CUSTOMER] ([CustomerID], [FirstName], [LastName], [Email], [PhoneNo], [StreetAddress], [City], [OrderID]) VALUES (17579946, N'Steve', N'Reidy', N'sreidy@gmail.com', 13827563, N'22 Conna Rd', N'Cork', 64724274)
GO
INSERT [dbo].[CUSTOMER] ([CustomerID], [FirstName], [LastName], [Email], [PhoneNo], [StreetAddress], [City], [OrderID]) VALUES (18579946, N'Michael', N'Yule', N'myule@gmail.com', 14827563, N'44 Blasket Rd', N'Kilkenny', 44424274)
GO

A possible downside to this approach is because ConvertTo-CSV automatically surrounds output columns with double quotes. The follow script handles the elimination of ALL double quotes.  This could be problematic if your data actually contains double quotes.

--The solution
--The output goes to C:\Test\test.txt
--Make sure that the parameter value for -ServerInstance below is equal to the Server Name
--of the SQL Server instance you want to access.  The example below is using a single period
--which indicates the default instance on the computer where the Powershell script is actually running.

DECLARE @cmd VARCHAR(4000)
set @cmd = 'powershell.exe "Invoke-Sqlcmd -ServerInstance . -Query ''Select * from Test.dbo.customer'' | ConvertTo-Csv -NoTypeInformation -Delimiter "`t"  | % {$_ -replace ''""'',''''} | Out-File (''C:\Test\test.txt'') "'
print @cmd
exec xp_cmdshell @cmd

Note that the ConvertTo-Csv cmdlet allows you to specify a Delimiter.  If you don't provide that parameter, the default is a comma (as represented by the output below.

Results in C:\Test\test.txt:

CustomerID,FirstName,LastName,Email,PhoneNo,StreetAddress,City,OrderID
13579246,David,Casey,dcasey@gmail.com,12826363,34 Bray Rd,Dublin,94724274
16579946,Donal,Smith,dsmith@gmail.com,13826563,78 Ballybrack Rd,Dublin,14724274
17579946,Steve,Reidy,sreidy@gmail.com,13827563,22 Conna Rd,Cork,64724274
18579946,Michael,Yule,myule@gmail.com,14827563,44 Blasket Rd,Kilkenny,44424274

Here is an example where I'm using backtick t (`t) to export a tab delimited file.

DECLARE @cmd VARCHAR(4000)
set @cmd = 'powershell.exe "Invoke-Sqlcmd -ServerInstance . -Query ''Select * from Test.dbo.customer'' | ConvertTo-Csv -NoTypeInformation -Delimiter "`t"  | % {$_ -replace ''""'',''''} | Out-File (''C:\Test\test.txt'') "'
print @cmd
exec xp_cmdshell @cmd


Failover Availability Group from linked Server I am hoping you can all help me in what feels like a really simple problem, but one I am struggling to find a solution for.

We are running SQL Server Standard 2016 and utilize availability groups between the 2 nodes. Being on standard, I can only have 1 database per Availability group.

Once a month, we need to patch our server, and it's an absolute pain to manually fail-over each group and then back for patching.
My solution is that I have a 3rd server, which has Nodes 1 and 2 as linked servers and this helps me manage the 50 odd availability groups as well as some other application related things we do.

I have a connection to the 2 nodes, and as a job, I collect and update a table on server 3 with each database, availability group name, node name, etc for the 2 main nodes. this runs every 5 minutes and alerts me of issues.

My final step is to now have a stored procedure on server 3, that will simply allow me to failover the availability groups or just a single one ect. It works perfectly except on the one node and on the other, i get this error :

  Availability-group DDL operations are permitted only when you are using the master database.  Run the USE MASTER command, and retry your availability-group DDL command.

My code for this command is as follwos :

SET @Query = 'EXEC (''ALTER AVAILABILITY GROUP [' + @CurrentAG + '] FAILOVER'') AT [' + @FailoverServer + '];'
        EXEC sp_executesql @Query

Which ends up with this command :

EXEC ('ALTER AVAILABILITY GROUP [MyAvailGroup] FAILOVER') AT [DestinationLinkedServerNode];

Thank you for any help on this.

To answer your question, it should be as below provided you are executing below on primary replica.

SET @Query = 'EXEC (''use master; ALTER AVAILABILITY GROUP [' + @CurrentAG + '] FAILOVER'') AT [' + @FailoverServer + '];'
        EXEC sp_executesql @Query

A better solution would be to use dbatools to initiate failover Invoke-DbaAgFailover . This way, you dont have to use any extra server or linked server. It will be much flexible solution as well.


we still can't find backup archivelog in local controlfile Oracle database is Oracle 11.2.0.3 version.
Every one hour, archivelog will backup to netbackup tape.
But it is strange, even when we use resync catalog command, we still can't find the backed up archivelog file in the local controlfile; but in nbu catalog database, we can find backup archivelog info.
Could you please check if we are doing anything wrong?
list backup of archivelog from logseq 382594 until logseq 382595;

when in local control ,list backup archivelog info is as below; no backed up archivelog found .

Recovery Manager complete.
[opreb@srebdb01 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Oct 23 10:21:38 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: REB (DBID=2063200898)

RMAN> list backup of archivelog from logseq 382594 until logseq 382595;                                    <-

using target database control file instead of recovery catalog
specification does not match any backup in the repository

RMAN> exit

Recovery Manager complete.

Use catalog database to check if we can find backuped archivelog info;

[opreb@srebdb01 ~]$ rman target / catalog rman11g/rman11g@cat11g

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Oct 23 10:21:52 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: REB (DBID=2063200898)
connected to recovery catalog database
list backup of archivelog from logseq 382594 until logseq 382595;                                 <-
RMAN>

List of Backup Sets
===================

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
128764956 6.50M SBT_TAPE 00:00:09 22-OCT-20
BP Key: 128764959 Status: AVAILABLE Compressed: NO Tag: TAG20201022T215336
Handle: al_73143_1_1054504417 Media: @aaabh

List of Archived Logs in backup set 128764956
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 382594 5576720225 22-OCT-20 5576727105 22-OCT-20
1 382595 5576727105 22-OCT-20 5576733606 22-OCT-20

Then we try to  RESYNC CATALOG

RMAN> RESYNC CATALOG
2>
3> ;

starting full resync of recovery catalog
full resync complete

RMAN> exit

Recovery Manager complete.

Event then, we still can't find backup archive log info in local controlfile.

[opreb@srebdb01 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Oct 23 10:22:26 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: REB (DBID=2063200898)

RMAN> list backup of archivelog from logseq 382594 until logseq 382595;                               <-

using target database control file instead of recovery catalog
specification does not match any backup in the repository

RMAN> exit

Thanks for your feedback.

If the backups are going straight to NetBackup, try specifying DEVICE TYPE SBT in your LIST command. The default may be DEVICE TYPE DISK. For example
list backup of archivelog all device type sbt;

HTH


what does it mean if the last execution time of a stored procedure is empty I recently ran some scripts to get the last execution time of my DB stored procedures, and the code is below:

SELECT d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name',
d.cached_time, d.last_execution_time, d.total_elapsed_time,
d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
d.last_elapsed_time, d.execution_count
FROM sys.dm_exec_procedure_stats AS d
where  DB_Name(d.database_id) = 'MyDB'
ORDER BY [proc name] ;

But I noticed that some of my stored procedure are not listed. Then I use the code below to check particularly one of them:

select s.last_execution_time
from sys.dm_exec_procedure_stats s
where OBJECT_NAME(object_id, database_id) = 'my_stored_procedure'

and I get empty result (but nothing shows in the column).

Just want to know if this means the stored procedure has never executed before, or something else?

Thank you

sys.dm_exec_procedure_stats is not a persisted dataset.

It only shows details for queries that currently exist in the Cache. If the SP hasn't been run for some time then the SP will be removed from the cache in favour of more frequently executed queries.

Also the Cache can be emptied by events such as a Server Reboot, and if the SP hasn't been run since the reboot then it won't be there.


Concern regarding auto_increment in MySQL InnoDB I'm developing and implementing CFR Part 11 compliant system. In each table I create a primary key that is used for audit trail. I implement triggers fired on create, update and delete operations.

I use MySQL 5.7 with InnoDB engine on JawsDB DaaS.

A few days ago I came across the following article: https://desmart.com/blog/be-careful-with-mysql-s-auto_increment-how-we-ended-up-losing-data

The author shared his concern regarding auto_increment mechanism in MySQL. Here is the issue in a few words:

You create 10 employee records with auto_increment PK (let's call it "recordID"). The system assigns recordID numbers from 1 to 10.
Employee "9" does something in the system and his actions are recorded in audit trail table with his recordID.
Employee 9 and 10 are deleted . You delete 2 last records (9 and 10)
You restart MySQL server
You insert new record and the DB system assigns the newly inserted record's recordID value "9".

The problem is that we already have audit trail on the recordID "9" in the audit trail table.

Here are my questions:

Have you ever came across the situation?
Is MySQL 5.7 affected by the bug? Shall I upgrade to 8.0 since it is stated here that the bug was fixed in 8.0?
What would you suggest to mitigate the risk? Shall I pick another DB engine? MyISAM?
Is there any way to ensure that auto_increment does not increment sequentially if last record was deleted?

Don't delete records for which there is a logical collation that you want to keep. Rather than marking deleting employees, mark them as terminated, keeping the row there. Otherwise you'll have an audit record without knowing who it corresponds too.

Option b), for an Audit record, denormalize it, if necessary create a shadow table for the employee. The audit should reflect the employee record at the time it occurred.

No, you shouldn't use MyISAM.

MySQL-8.0 changes behaviour as its manual states (the primary source you should consider for important decisions). If in doubt, test it.


Postgresql Replication - Pacemaker vs DRBD I need to setup a Highly available Postgresql Cluster. So I found Pacemaker and DRBD with pgpool load balancer are the best options.

So the architecture will look like this.

As of my knowledge, DRBD is for Block level replication (works on storage) and Pacemaker is Service Cluster (works on service level). So which is the best one to use and best HA solution?

And another question is Im using AWS EC2, So how can I manage VIP (I mean if the primary goes down how the PGpool will switch the VIP to Standby also its and in AWS)?

Usually, you use them both. DRBD to replicate the data synchronously, and Pacemaker to manage where the services become active. Pacemaker will control promoting/demoting DRBD, and starting your database and virtual IP address.

Pacemaker uses resource-agents to manage cluster services. One of those, is a resource-agent for moving a secondary IP between AWS nodes:
https://github.com/ClusterLabs/resource-agents/blob/master/heartbeat/awsvip

Hope that leads you down the right path.


Help with a table having fixed number of self-referencing columns I'm trying to design an electronic toll collection system, where I have stumbled on decision how to design road section. Each road section is designed in a way that it has references to other road sections that lie ahead(RSA) , behind(RSB) and across(ORS). I tried to visually demonstrate them in figure below.

Visual presentation of Road Section with their primary keys is given below.

   |     |    || Road     |
   |          || Section  |
   |     |    || Ahead    |
   |    (1)   ||   (6)    |
------------------------------
   |     |    ||    |     |
   |          ||          |
   | Opposite || Road     |
   | Road     || Section  |
   | Section  ||          |
   |     |    ||    |     |
   |    (2)   ||   (5)    |
---------|----------|---------
   |          ||          |
   |     |    || Road     |
   |    (3)   || Section  |
   |     |    || Behind   |
   |     |    ||   (4)    |

I have two approaches in mind in designing such entity. The first one is using same table and have 3 columns referencing other rows in the same table.

Road Section Table
---------------------------------------------------
| PK_RS | FK_ORS | FK_RSA | FK_RSB | Other data
---------------------------------------------------
|   5   |   2    |   6    |   4    |  ...
|   4   |   3    |   5    |  null  |  ...

The second one is to create separate table for road section relationships, called Road Section Mapping

Road Section Table
-----------------------------
| PK_RS | Other data
-----------------------------
|   4   |  ...
|   5   |  ...
Road Section Map Table
------------------------------------
| PK_RS | FK_ORS | FK_RSA | FK_RSB |
------------------------------------
|   5   |   2    |   6    |   4    |
|   4   |   3    |   5    |  null  |

In the first case I will need to create rows with null references at first and update foreign keys later. In second a can create relations after creating road sections first.

What is the best practice in such kind of models?

First approach seems to be more logical.
Because ManyToOne usually stored in same table.


Granting minimum select permission on system "sys" tables in every database Our development team has created some SSRS reports that retrieve metadata only (no 'real' data) from every database. I am trying to lock down the service account running these reports to the minimum privileges required.

The queries only select from the system tables in each database such as:

 sys.tables
 sys.extended_properties
 SYS.objects
 SYS.schemas
 sys.dm_db_partition_stats
 sys.indexes

I thought the following would do it, but no such luck.

 GRANT VIEW ANY DATABASE TO [DOMAIN\LOGIN]
 GRANT VIEW SERVER STATE TO [DOMAIN\LOGIN]
 GRANT VIEW DATABASE STATE TO [DOMAIN\LOGIN]

Querying system tables gives the error:

 The server principal "DOMAIN\LOGIN" is not able to access the database "DATABASENAME" under the current security context.

I can't seem to find a way around this other than creating a user for the login for every single database and then granting permissions to the system tables explicitly. This presents a new set of challenges for all future databases. Am I missing something or is there no simple way to grant this access across the board to all databases without granting the "sysadmin" server-level role?

I can't seem to find a way around this other than creating a user for
  the login for every single database

Starting with SQL Server 2014 the new CONNECT ANY DATABASE permission can be used to accomplish this

  ...and then granting permissions to the system tables explicitly.

You have no need to grant permissions on system tables, they are visible to any user. But the visibility of objects they contain depends on the permissions the user has: he can see only those objects on which it has permissins: Metadata Visibility Configuration

And this can be solved by giving to your login VIEW ANY DEFINITION permission

Also, there was no need to give VIEW ANY DATABASE TO permission because it's already granted to public, and  VIEW SERVER STATE TO is not required for the metadata you described retrieving.

So, all you need are these permissions:

CONNECT ANY DATABASE to access all the databases
VIEW DATABASE STATE for querying sys.dm_db_partition_stats
VIEW ANY DEFINITION for have the rights on any metadata


Four db servers all identical, one is much slower than the others The setup

We have an application loading a lot of transaction data for later querying. It's sharded on 4 SQL Server 2008R2 Enterprise Edition on Windows Server 2008 64 bits. All servers are configured exactly the same (or so I'm told). 72GB of memory, 2x6 core CPU, each attached to their own SAN. The databases are currently about 2TB in size.

They all load about the same number of transactions daily (+/- 10%), it's mostly inserts. Almost no updates. The process is I/O bound, the CPUs sits at 10-15% usage.

The problem

One of the servers is much slower than all the others. It only inserts transactions at a quarter of the speed of the other three. It has always been like this. It eventually catches up during quieter hours, but the other servers keep up with traffic at all time.

The SAN people have told us that all four SANs are performing the same, with no errors. Looking at the I/O graph on the Activity Monitor, the I/O is about the same on all servers.

I have extracted the schema for the databases and compared them, no difference. There doesn't seem to be any difference in the SQL Server configs either. They all use about the same amount of memory with the same distribution.

The question

What else should I be looking at?

We have been looking at this for months and can't come up with anything. There must be some explanation. I've looked at a lot of different performance metrics and I can't seem to find any glaring difference except that the overall inserts are slower.

I'd be inclined to point a finger at the storage of the poorly performing server.

Sounds like you're in the unfortunate position of having a storage admin telling you everything is ok when it quite possibly isn't. Only way to be sure is to prove it.

If you have the luxury of a maintenance window, run a batch of SQLIO tests against each server and compare the results. @BrentOzar has a good intro article on SQLIO which also covers some alternative tools such as Crystal Diskmark, which might be enough to prove the point in your case. Run the tests in collaboration with the storage admin, so they can witness the issues first hand. Perhaps arm yourself with How to Prove It’s a SAN Problem before hand.

If IO testing proves fruitless or impossible, throw sp_whoisactive or a similar monitoring solution at the problem. Kendra Little's Collecting Data from sp_WhoIsActive in a Table would be the best approach for a long running ETL process.

Before doing any of the above, take 1 minute to check something very very basic that crops up time and time again... the NTFS allocation unit size on each of the arrays.


Is there any way to collect script of running SP with it's input value using DMVs? I am using below script to collect information about active sessions. It returns Query/SP (script), it's estimated E-Plan, resource consumption and other information.

I know to use profiler to collect script of SP with it's input value but because performance of SP varies on different input value, `it's not good idea to run profile for long time, so I want to collect it using DMV.

Is there any way to get it (exec sp_name input1, input2, input3, ..) using DMV?

select
            db_name(sp.dbid),sp.spid,er.wait_type,er.wait_time,er.wait_resource,er.total_elapsed_time,st.text,qp.query_plan
            ,ec.net_packet_size,ec.client_net_address,es.host_name,es.program_name,es.client_interface_name
            ,es.status,es.cpu_time,qmg.granted_memory_kb,es.total_scheduled_time,es.total_elapsed_time
            ,es.reads,es.writes,es.logical_reads

from
                sys.dm_exec_requests er
inner join      master.dbo.sysprocesses sp
on              er.session_id=sp.spid
inner join      sys.dm_exec_connections ec
on              er.session_id=ec.session_id
inner join      sys.dm_exec_sessions es
on              ec.session_id=es.session_id
inner join      sys.dm_exec_query_memory_grants qmg
on er.session_id=qmg.session_id
cross apply     (select text from sys.dm_exec_sql_text(er.sql_handle)) st
cross apply     (select * from sys.dm_exec_query_plan(er.plan_handle)) qp

Thanks

Have a look at a possible method here - Getting the Query Plans and Parameters Directly from the Plan Cache


Has somebody got a function that will extend hashbytes to strings of arbitrary length? I want to apply it to procedure definitions.

My basic idea is to split the string of type nvarchar(max) into chunks of nvachar(4000) and concatenate the results of HashBytes('MD5',chunk).

I can't imagine that this is not done yet.

If you insist, then

CREATE FUNCTION dbo.GetMyLongHash(@data VARBINARY(MAX))
RETURNS VARBINARY(MAX)
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
    DECLARE @res VARBINARY(MAX) = 0x
    DECLARE @position INT = 1, @len INT = DATALENGTH(@data)

    WHILE 1 = 1
    BEGIN
        SET @res = @res + HASHBYTES('MD5', SUBSTRING(@data, @position, 8000))
        SET @position = @position+8000
        IF @Position > @len
          BREAK
    END
    RETURN @res
END

BUT may be better to use this CLR function and hash data of any length into real MD5 hash?

using System;
using System.Data.SqlTypes;
using System.IO;

namespace ClrHelpers
{
    public partial class UserDefinedFunctions {
        [Microsoft.SqlServer.Server.SqlFunction]
        public static Guid HashMD5(SqlBytes data) {
            System.Security.Cryptography.MD5CryptoServiceProvider md5 = new System.Security.Cryptography.MD5CryptoServiceProvider();
            md5.Initialize();
            int len = 0;
            byte[] b = new byte[8192];
            Stream s = data.Stream;
            do {
                len = s.Read(b, 0, 8192);
                md5.TransformBlock(b, 0, len, b, 0);
            } while(len > 0);
            md5.TransformFinalBlock(b, 0, 0);
            Guid g = new Guid(md5.Hash);
            return g;
        }
    };
}


SQL Server 2016 Availability Group on DPM 2016 I'm trying to backup my availability group in SQL Server 2016 using DPM 2016, but after I add the cluster in DPM, availability group show nothing to back up. I've set every secondary node to be readable secondary and add sysadmin role to \SYSTEM user. How can I fix this?

If you have added the cluster, as in the WSFC computer name, to DPM then this may not be correct according to the MS documentation and may explain why you have nothing to backup - https://docs.microsoft.com/en-us/system-center/dpm/back-up-sql-server?view=sc-dpm-1807

Try adding the Availability Group Replicas individually. DPM should detect the AG configuration automatically and, provided all replicas are readable, it should backup up according to the AG backup preferences.


SQL Creating Error Log Table Benefits We are conducting a datawarehouse project, moving data from Staging database to Kimball Data Warehouse.

What is the purpose of creating a custom stored procedure Error Log Table?

I am seeing a lot of websites recommend this.
SQL server already has an Error log table in SSMS (Option a).

So why create another error log table, seems like additional coding? (Option b). I can even customize error messages using both options, SELECT @ErrMsg = 'testabcd'. Can someone explain? Thank you,

Note:
Is there any automatic functionality in SSMS which logs errors for dbas? Is the only way to capture these errors is custom table? We are running code through sql agent jobs, and SSIS packages, thought we saw errors in agent job history and SSIS project catalog? issues like not null insert, value type conversion issues.

(a) SSMS Error Log Table

(b) Table and Stored Procedure
https://www.tech-recipes.com/rx/53462/sql-server-error-logging-and-reporting-within-a-stored-procedure/

CREATE TABLE [dbo].[ErrorLogTable]
(
    [ErrorID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [ErrorNumber] [nvarchar](50) NOT NULL,
    [ErrorDescription] [nvarchar](4000) NULL,
    [ErrorProcedure] [nvarchar](100) NULL,
    [ErrorState] [int] NULL,
    [ErrorSeverity] [int] NULL,
    [ErrorLine] [int] NULL,
    [ErrorTime] [datetime] NULL
);

CREATE PROCEDURE [dbo].[ErrorLogInsert]
AS

     INSERT INTO [ErrorLogTable]
         (
         ErrorNumber
        ,ErrorDescription
        ,ErrorProcedure
        ,ErrorState
        ,ErrorSeverity
        ,ErrorLine
        ,ErrorTime
       )
       VALUES
       (
         ERROR_NUMBER()
        ,ERROR_MESSAGE()
        ,ERROR_PROCEDURE()
        ,ERROR_STATE()
        ,ERROR_SEVERITY()
        ,ERROR_LINE()
        ,GETDATE()
       );

The picture (a) shows us not "SSMS error log table", it's the content of SQL Server error log file.

The errors occured at the service and server level are stored here.

The examples are:
service could not start because the port was alredy occupied,
system database files were not found, max server memory was configured to 0, etc. The errors ot this type cannot be catch by your procedure just because no procedure can be executed as service could not start.

Other kinds of errors stored in error log such as login failed or session was killed also cannot be catch by your code.

Your error logging proc is intended to catch other types of error such as constraint violation, conversion errors, permission errors, all these types of error are NOT logged in the SQL Server error log as they are simply programming errors.

You did not say that your code is executed in jobs and that there are ssis packages. In SSIS you can enable logging (it's not enableds by default), besides your code can have try..catch blocks where you want to suppress the errors and continue code execution.

There is no other functionality in SSMS which logs errors for dbas. SQL Server error log supplies errors for DBA regarding server functionality. The procedure that you showed is for developers, the errors it can catch do not influence server life, they regard only your code execution, and there is no "default" logging mechanism that catches and logs programming errors


Use 'default value' in table when NULL I am building a config table and want one row to be the default value. For example, I have a list table of all my databases and a table called msg which has all my return values. In the msg table, I have one row that is 'alldbs' and I want that value returned if there isn't another row for the joined database.

So the output should be:

--LIST TABLE
dbname                                             createdate
-------------------------------------------------- -----------------------
master                                             2015-06-23 10:05:14.363
model                                              2015-06-23 10:05:14.363
msdb                                               2015-06-23 10:05:14.363
tempdb                                             2015-06-23 10:05:14.363
dummy                                              2015-06-23 10:05:14.363

--MSG Table
dbname                                             msgval
-------------------------------------------------- --------------------
alldbs                                             Message 1
dummy                                              Message 2

--DESIRED OUTPUT
dbname                                             msgval
-------------------------------------------------- --------------------
dummy                                              Message 2
master                                             Message 1
model                                              Message 1
msdb                                               Message 1
tempdb                                             Message 1

Now I can get the result, but the query is clunky and it seems like there should be a better way:

select
    a.dbname
    ,b.msgval
from
    list a
    join msg b on a.dbname = b.dbname
union all
select
    a.dbname
    ,b.msgval
from
    list a
    ,msg b
where
    a.dbname not in (select dbname from msg)
    and b.dbname = 'alldbs'

Is there a more efficient way to write this query?

SQLFiddle Link using SQL 2014.

Using another left join:

select
    a.dbname
    , coalesce(b.msgval, d.msgval) as msgval
from
    list a
    left join msg b on a.dbname = b.dbname
    left join msg d on  b.dbname is null
                    and d.dbname = 'alldbs' ;

or a cross join (this requires an 'alldbs' row to be present, otherwise it will return 0 results.):

select
    a.dbname
    , coalesce(b.msgval, d.msgval) as msgval
from
    msg d
    cross join list a
    left join msg b on a.dbname = b.dbname
where d.dbname = 'alldbs' ;

SQlfiddle

Both queries assume there is only one row with dbname = 'alldbs'. If not, use the apply version provided by AndriyM.


How to prepend an element to a JSON array? I have a plpgsql function in PostgreSQL 9.6, which at some point has a text variable _x (guaranteed to not be NULL) and another variable _y which is a JSONB array of text (which might be empty).

How can I prepend the value of _x to the array _y?

I was trying to do it like this:

SELECT jsonb_build_array(_x, VARIADIC array_agg(value))
FROM jsonb_array_elements_text(_y)
INTO _y;

which is wrong. I was trying to use info about VARIADIC from this answer.

Burns down to a simple solution:

_y := to_jsonb(ARRAY[_x]) || _y;

This is concatenating two jsonb arrays with the || operator.. You need to wrap the simple text value _x into a jsonb array for this. There are various way, to_jsonb(ARRAY[_x]) is one.

Or even just:

_y := to_jsonb(_x) || _y;

The manual:

  The || operator concatenates the elements at the top level of each of
  its operands. [...]

So this happens to work for element-to-array concatenation as well.

You could unnest the array and reconstruct it (like you were trying), but that's more expensive. And might be tricky since a JSON array can hold elements of various types, unlike a Postgres array. (Which would not be a problem for your JSONB array of text.)

Your attempt failed because (as commented at the related answer), jsonb_build_array() expects a list of values, which can be replaced with a single array following the keyword VARIADIC - but not with a single element, followed by VARIADIC array.


Referencing a stored procedure by a name description across all databases in a SQL Server 2000 Instance Does anyone have a valid method in referencing system information to search for a stored procedure across all databases in a SQL Server 2000 instance? I am currently tasked with migrating crystal reports to SSRS and the customer has roughly 500 or more crystal reports with stored procedures spread over multiple databases. I know I can filter each database's programmability folder by text but I was looking for an outcome in which I can query master.dbo.sysdatabases and reference that to syscomments in which I can specify text and find a stored procedure tied to a database.

If anyone is curious how the cursor code would look, from this MSDN post:

DECLARE @SearchString VARCHAR(255)
DECLARE @name VARCHAR(50)
DECLARE @db_name1 VARCHAR(50)
DECLARE @db_name2 VARCHAR(50)
DECLARE @sql VARCHAR(100)
SET @SearchString = 'a_ras_Arlenes_ship_sched_sp'

DECLARE db_cursor CURSOR FOR

SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor

FETCH NEXT FROM db_cursor WITH (NOLOCK) INTO @name

While @@FETCH_STATUS = 0
BEGIN
  SET @db_name1 =@name+'.dbo.syscomments'
  SET @db_name2 =@name+'.dbo.sysobjects'

  SELECT @sql = 'SELECT DISTINCT b.name FROM ' + @db_name1
              + ' a JOIN ' + @db_name2 + ' b ON a.id = b.id'
              +' WHERE a.text like %' + @SearchString + '% ORDER BY b.name'

  EXEC(@sql)

  FETCH NEXT FROM db_cursor WITH (NOLOCK) INTO @name

END
Close db_cursor
DEALLOCATE db_cursor


Exporting 30 million rows to CSV I have a query that hits multiple tables in an Oracle database (11g). I don't have access to the server itself. I use SQL Developer or SQL*Plus to connect.

There are about 31 million rows, and 7 columns, and I want to dump it into a CSV file.

How can I achieve this?

Asuming this is to transport data to an other system. It that case this will work:

set colsep ";"
set linesize 9999
set trimspool on
set heading off
set pagesize 0
set wrap off
set feedback off
set newpage 0
set arraysize 5000
spool you csv_file.csv
select rows from your tables;
spool off

If you don't want a header line, change to heading off

If this is to analyze the data in excel, please wake up and learn sql. SQL, especially Oracle, has many great tools to help analyzing your data. By the time the data is transported into the csv file, Oracle analytics has already done some great analysis.Data Warehousing and Business Intelligence but for many tasks plain SQL can be good enough. Start reading about Oracle Analytics.


Why is a round robin distribution better for a skewed dataset? I have a skewed dataset, where most rows fall into the largest 10 values of my best candidate distribution key. My data is made up of two large tables, that only share two keys - my best candiate key, plus one other, but that one is null 80% of the time, so I have discounted it as an option.

Conventional wisdom says that if the data is skewed, I should use a round robin distribution. Looking at the explain plans produced by joins on the tables, I see my candidate column is the shuffle key for the shuffle move. This makes me question if I should change the distribution from round robin, to hash distributed, saving the time it takes to move data on every execution.

Is my logic correct? I feel like this is against the conventional wisdom when working with distributed sql. I don't expect any queries where this join isn't required, so that may be where others would see the benefit.

Round robin always entails data movement because of its nature, but it needn't be catastrophic for your performance.  The reason you use it for skewed data is because, when you distribute by hash, a unique hash is generated for each value and the rows distributed across the 60 distributions accordingly.  In  your example, most of your data would end up on one (or only a few) distribution(s) and you are therefore not taking advantage of the compute available to you.  Let's say you only have the opportunity of taking advantage of 20% of the compute resources available to you and the rest is idle.

The requirements for good hash columns are: they should not be updateable, not NULLable, should have large number of distinct values and even distribution.

Do you have an option to create a concatenated key from the others?  That could help create a more even distribution and would be useful as long as you used it in joins between the two tables.

Just some other advice, design for your key queries, use some of the other features available in SQL DW like the right DWU, resource classes, non-clustered indexes, auto-statistics.  Please also note that generation 2 of SQL DW is now available.

HTH


How to check connection string in SSMS2012? I'm connected to database. I use db by Management Studio 2012 Express. Can I check connection string by click something in Management Studio?

Within SSMS, I'm not sure there's an easy way to do that.  But a simple query will return you the information (without the password of a connection string, obviously):

select
    'data source=' + @@servername +
    ';initial catalog=' + db_name() +
    case type_desc
        when 'WINDOWS_LOGIN'
            then ';trusted_connection=true'
        else
            ';user id=' + suser_name()
    end
from sys.server_principals
where name = suser_name()

Note, db_name() will return the current database name if there is no parameter specified, so that is dependent on your current scope.


mysqldump --single-transaction, yet update queries are waiting for the backup If I use mysqldump --single-transaction, according to the docs it should do flush tables with read lock to get a consistent state and then start a transaction and no writers should be waiting.

However, I have caught the following situation last night:

excerpt from show full processlist:

hundreds of those...

   Command: Query
   Time: 291
   State: Waiting for table flush
   Info: insert into db_external_notification.....

then this:

Command: Query
Time: 1204
State: Sending data
Info: SELECT /*!40001 SQL_NO_CACHE */ * FROM `db_external_notification`

and the rest of the threads are in Sleep

does anyone have any idea what are these inserts waiting for? I don't see any FLUSH tables or DDL or anything mentioned in the manual that can cause the queries to wait.

full mysqldump command

mysqldump --quick --add-drop-table --single-transaction --master-data=2 -uxx -pxx dbname

I guess --quick is redundant here, probably a leftover from earlier times, this script is very old, but shouldn't hurt anyting

The --single-transaction option of mysqldump does do a FLUSH TABLES WITH READ LOCK prior to starting the backup job but only under certain conditions.  One of those conditions is when you also specify the --master-data option.

In the source code, from mysql-5.6.19/client/mysqldump.c at line 5797:

if ((opt_lock_all_tables || opt_master_data ||
     (opt_single_transaction && flush_logs)) &&
    do_flush_tables_read_lock(mysql))
  goto err;

To get a solid lock on the precise binlog coordinates prior to starting the repeatable-read transaction, the --master-data option triggers this lock to be obtained and then released once the binlog coordinates have been obtained.

In fact, mysqldump does a FLUSH TABLES followed by a FLUSH TABLES WITH READ LOCK because doing both things allows the read lock to be obtained faster in cases where the initial flush takes some time.

...however...

As soon as it has obtained the binlog coordinates, mysqldump issues an UNLOCK TABLES statement, so there shouldn't be anything blocking as a result of the flush you started.  Neither should any threads be Waiting for table flush as a result of the transaction that mysqldump is holding.

When you see a thread in the Waiting for table flush state, that should mean that the FLUSH TABLES [WITH READ LOCK] statement was issued and was still running when the query started -- so the query has to wait for the table flush, before it can execute.  In the case of the processlist you've posted, mysqldump is reading from this same table, and the query has been running for a while, yet the blocking queries haven't been blocking for all that long.

This all suggests that something else has happened.

There's a long-standing issue explained in Bug #44884 with the way FLUSH TABLES works, internally.  I would not be surprised if the issue still persists, I would be surprised if this issue is ever "fixed" because it is a very complex issue to resolve -- virtually impossible to truly fix in a high concurrency environment -- and any attempt at fixing it carries a significant risk of breaking something else, or creating new, different, and still undesirable, behavior.

It seems likely that this will be the explanation for what you're seeing.

Specifically:

if you have a long-running query running against a table, and issue FLUSH TABLES, then the FLUSH TABLES will block until the long-running query completes.
additionally, any queries that begin after the FLUSH TABLES is issued will block until the FLUSH TABLES is complete.
additionally, if you kill the FLUSH TABLES query, the queries that are blocking will still block on the original long-running query, the one that was blocking the FLUSH TABLES query, because even though the killed FLUSH TABLES query didn't finish, that table (the one, or more, involved with the long-running query) is still in the process of being flushed, and that pending flush is going to happen as soon as the long-running query finishes -- but not before.

The likely conclusion here is that another process -- perhaps another mysqldump, or an ill-advised query, or a poorly-written monitoring process tried to flush a table.

That query was subsequently killed or timed out by an unknown mechanism, but its after-effects lingered until mysqldump finished reading from the table in question.

You can replicate this condition by trying to FLUSH TABLES while a long-running query is in process.  Then start another query, which will block.  Then kill the FLUSH TABLES query, which won't unblock the latest query.  Then kill the first query, or let it finish, and the final query will successfully run.

As an afterthought, this is unrelated:

Trx read view will not see trx with id >= 1252538405, sees < 1252538391

That's normal, because mysqldump --single-transaction issues a START TRANSACTION WITH CONSISTENT SNAPSHOT, which prevents it from dumping data that was changed while the dump was in progress.  Without that, the binlog coordinates obtained at the start would be meaningless, since the --single-transaction would not be what it claims to be.  This should not in any sense be related to the Waiting for table flush issue, as this transaction obviously holds no locks.


How can I drop a column from a large (700M rows) hot (multiple transactions per second) table with minimal downtime? My research suggests I can drop a column on a table in a matter of moments (provided I obtain an exclusive lock) because it is only a metadata change.

The table has two different services constantly inserting and updating records.  It is very hot.  Without developing a full reproduction environment of this level of activity, how can I ensure a statement like

ALTER TABLE x DROP COLUMN y

will successfully obtain an exclusive lock, do the drop quickly, and then unlock the table?

How will SQL Server queue this request (eg First In First Out)?
Can I be sure the DROP COLUMN will truly only take a few moments?

Locking this table in PROD for an extended time will be unacceptable so I am trying to avoid surprises.

Other notes:
Later I will re-index ONLINE to defragment the index and reclaim the space.

How will SQL Server queue this request (eg First In First Out)? Can I
  be sure the DROP COLUMN will truly only take a few moments?

The way the locking works for ALTER TABLE is that you need a Schema Modification Lock (Sch-M) to perform the ALTER.  Every other query that touches the table needs a Schema Stability Lock (Sch-S). Sch-M and Sch-S are incompatible.

Also while you are waiting to acquire the Sch-M lock, you block any new requests for a Sch-S lock.  So you wait for all in-flight queries and transactions to release their Sch-S locks on the table, and block all new queries while you wait.  This is the normal way DDL locking works, and so long as you don't have any long-running queries on your table it works fine.

But if you have long running queries that your DDL can get stuck behind, your DDL in turn blocks all new queries.  So running DDL on a busy system with both short-running and long-running operations was a problem.

Low Priority Waits solves this problem by allowing your DDL statement to wait at reduced priority, not blocking new queries.  The DDL session will just wait for a "window" where no other session has any lock on the table, and then run.  But since the DDL session is not blocking new queries, it might wait forever.

Unfortunately low priority waits have not been enabled for dropping columns.  You can create a feedback item to ask for it to be implemented here.  Has been enabled for online index rebuild and partition switching.

You can mostly emulate this behavior with LOCK_TIMEOUT and retries.  So configure your DDL session with a lock timeout, so it will abandon the ALTER TABLE if the lock can't be acquired quickly.

So run the alter like this:

set lock_timeout 1000 --1 second
alter table SomeTable
drop column if exists ColumnToDrop

And it will either succeed quickly or fail with a lock timeout error.  If you want to run this in an unattended job, you can use a retry loop to keep trying until the ALTER TABLE succeeds quickly.

Something like this:

set lock_timeout 1000 --1 second
declare @tries int = 0

while 1=1
begin
    begin try
        set @tries = @tries + 1;

        alter table SomeTable
        drop column if exists ColumnToDrop

        print 'complete'
        break;
    end try
    begin catch
      if @tries > 10 or error_number() <> 1222 throw;

      declare @msg nvarchar(2000) = concat('retry ', @tries);
      print @msg
    end catch
end


Does a Levenshtein distance involve some computation for each single row? Let's say I have, in a SQL DB, a table with 2 columns: id integer, description text and 100,000 rows. The column description has always < 20 characters.

If I want to:

SELECT * FROM mytable WHERE levenshtein_dist(description, "hello world") < 7

does this require to compute the distance for each of the 100,000 rows (heavy computation!) for any implementation of Levenshtein string similarity distance, because this is inherent to the method

or

is there some clever implementation or clever technique (involving an index maybe?) that can avoid to have to loop over each single row of the table?

Context: I wanted to know if MySQL (or even sqlite) had an efficient mean to find string similarity. FTS (Full text search) is one but unfortunately (it's for sqlite here, but it might be the same for MySql?):

  SQLite's FTS engine is based on tokens - keywords that the search engine tries to match.
  A variety of tokenizers are available, but they are relatively simple. The "simple" tokenizer simply splits up each word and lowercases it: for example, in the string "The quick brown fox jumps over the lazy dog", the word "jumps" would match, but not "jump". The "porter" tokenizer is a bit more advanced, stripping the conjugations of words, so that "jumps" and "jumping" would match, but a typo like "jmups" would not.

The latter (the fact that "jmups" cannot be found as similar to "jumps") makes it unpractical for my use case, sadly.

PostgreSQL

Using PostgreSQL, you can create a trigram index on a column with the pgtrgm module and do a similarity search on it with the <-> operator,

  text <-> text Returns the “distance” between the arguments, that is one minus the similarity() value.

note there is also word_similarity. I would highly suggest checking out the module, as it explicitly addresses your concerns,

  Trigram matching is a very useful tool when used in conjunction with a full text index. In particular it can help to recognize misspelled input words that will not be matched directly by the full text search mechanism.


MySQL: How think about Null insert to foreign key column I'm designing table relationship. I have an one question.

Parent: A table

Child: B table

Table: A
PK: id

---

Table: B
PK: id
Column: A_id(NOT NULL), desk, chair...
Option: CONSTRAINT `fk_B_A_id` FOREIGN KEY (`A_id`) REFERENCES `A` (`id`)
        ON DELETE NO ACTION ON UPDATE CASCADE

My case is, B table sometimes has A_id, sometimes doesn't have A_id.

So I'm trying to insert 0 for A_id when B table doesn't need the A_id value. But A table doesn't have id 0. As a result error occurs.

I'm thinking two solutions:

A_id can have null.
Dummy data (A.id=0) insert to A table when A table create.

If you have other idea Please tell me.

Thank you.

If the relationship for child B against A isn't mandatory then just allow NULL on the foreign key column. It's very common to do this.

Don't use a default record (like ID = 0) to match against when there shouldn't really be one, it will bring you more trouble that solutions (what data will this record have? what happens with NOT NULL columns?)


Failure to detach database I used sp_detach_db to detach SQL Server 2005 databases. This works properly in Windows XP and Windows 7.

However, the same fails in Windows Server 2008.
The error message states:

  The process cannot access the file SimpleProject_device.ldf as it is being used by another process

But, I am able to delete both .mdf and .ldf files manually inspite of getting this error. I guess, if the database was still attached, delete operation should not have been allowed by the OS.

Edit:
The problem was that the Connection object had been disposed before detach. (God knows how).
Therefore, I have added a condition to ensure that the connection exists before trying to detach the database.

Currently, I am getting another error.

An error occurred during database detach:  "A transport-level error has
occurred when sending the request to the server. (provider: Shared Memory
Provider, error: 0 - No process is on the other end of the
pipe.)",30791590,8688858,5/22/2012,9:37:05 PM

Does anyone know why I'm getting this error?

You or some other user is probably still connected to the database (it could be your current query window, or Management Studio's Object Explorer). To break this you can set the database to single user mode, which rolls back any work they were doing (which shouldn't matter, since you're detaching the database anyway).

USE [master];
GO
ALTER DATABASE SimpleProject_Device SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

Now you should be able to detach.

However if you can delete the file then my guess is you are talking about two different physical files. Are you using SQL Server Express and the User Instances / attachDbFileName features perhaps? If so you can search the system for files named Simple_Project_device.ldf - I bet there are more than one, and the one you're trying to detach is not the same one you're trying to delete.

In any event, detach is not the right way to move a database: BACKUP / RESTORE is better. Why? Because if something goes wrong with the backup or the restore, you still have the original. If something goes wrong during detach, you now have ZERO usable copies of your database. With backup/restore, after the restore is successful, you can just drop the source database.


Printing \G on shell using mysql client..? It is really a a headache for me from some hours i am struggling on the problem.I have also asked it on stack-overflow but not got any answer yet so i am asking here.Sorry for Not a good question.

I am Executing a Command on Linux shell.

mysql -uroot -proot -Bse "select Concat('SHOW CREATE TABLE ',TABLE_SCHEMA ,'.',TABLE_NAME,'\\G') from information_schema.tables where TABLE_SCHEMA Not IN('mysql','information_schema','performance_schema');"

I want output as

SHOW CREATE TABLE sakila.category\G
SHOW CREATE TABLE sakila.city\G
SHOW CREATE TABLE sakila.country\G
SHOW CREATE TABLE sakila.customer\G

But I am getting as

SHOW CREATE TABLE sakila.categoryG
SHOW CREATE TABLE sakila.cityG
SHOW CREATE TABLE sakila.countryG
SHOW CREATE TABLE sakila.customerG

Why it is so..?

This has always been a real pain-in-the-neck for me.

The mysql client seems to interpret the \\G as  \G
The OS seems to take that output and interpret \G as G

Unfortunately, this is the sequence that always happens.

I use the sed command to get around this as follows:

mysql -uroot -proot -Bse "select Concat('SHOW CREATE TABLE ',TABLE_SCHEMA ,'.',TABLE_NAME,' G') from information_schema.tables where TABLE_SCHEMA Not IN('mysql','information_schema','performance_schema');" | sed 's/ G$/\\G/g'

Notice that I replaced \\G with G (blank followed by G)

I have the sed command translate G$ (which means the blank capital G at the end of of line) and convert it to \G. Notice that sed is doing this conversion with OS output and mysql output.

You no longer have to deal with that \G issue. Simply plan for a blank capital G to be transalted by sed to \G.


how to add muliple rows with insert in order I have table that have the following columns:

  id, state_id, name

the id is auto_increment

now I want to insert into it multiple rows at once as following:

insert into tbl (name, state_id)
         Values ('b',4121),
                ('a',4121);

what I want is that those values get added in order that correspond to the name so for example the first row will have id 1 and name a and second row will have id 2 and name b.

how to do this?

I believe that, in most cases, your specific example (two rows inserted via a single INSERT ... VALUES statement, will not produce the results you're asking for. If you listed the names in alphabetic order, then they'd get assigned as "a" = 1 and "b" = 2 - as you've shown it, you'll get "b" = 1 and "a" = 2.

That said, as others have noted, why do you care? Is it important somehow that the rows get specific ID values, or that ID values are sequential, with no gaps?

If so, then an auto-increment key may not be your best solution.

An auto-increment key works best when you don't care what the ID is, just that it's unique. As your primary key, an auto-increment column simply ensures that each new row will have a unique ID value, nothing else. That ID value may be used to tie rows in other tables back to this table; that's all the value needs to do if that's the case. If you want data from both tables, use a JOIN when you select them.

Often, your data may have other values that provide some sense of order (for instance, the name itself). Don't confuse order with the ID value; they're two separate things.

Yes, when a table has a clustered index, then the data is stored in order based on that index (which is usually, but not always, the primary key). However, that's only really useful for putting queries together.

The order of the data returned by a query is only guaranteed to be meaningful if the query has an ORDER BY clause. Just because the data is stored in a particular order does not mean that's the order the results of a SELECT query will come back in.

Plus, of course, if name in your first two rows happen to be "Aaron Aardvark" and "Zeb Zebra", and these get IDs 1 and 2, respectively - what happens when a third row with name "Penelope Parrot" is added? Now, the IDs are out of alphabetic order by name.

In the very limited case of:

You'll only load data into your table once; and
For some reason, you need the IDs to match the order of some other column in the table;

You can try sorting the data based on that column, and loading it in like that. From a single INSERT statement, the only INSERT you ever intend to run, the data should wind up getting assigned IDs in whatever order the data is presented.

That said, it's not the best way to run a database.


MySQL Trigger IF Statement I am writing a trigger to audit the MySQL database. IF statement doesn't work. Where am i doing wrong?

Also, can you guide me on the best trigger for mysql audit.

DELIMITER $$

CREATE
    DEFINER = 'sqlsa'@'%'
TRIGGER AuthenticationLog_U
    AFTER UPDATE
    ON AuthenticationLog
    FOR EACH ROW
BEGIN
    DECLARE Type varchar(1);
    DECLARE TableId tinyint;
    DECLARE IsActive tinyint;
    SET @Type='U';
    SET @TableId = 2;
    SET @IsActive = (SELECT IsActive FROM AuditDB.ExportLogTableDefinition WHERE TableId=@TableId);
    IF (@IsActive=1) THEN
        INSERT INTO AuditDB.ExportLog (DBName,TableId,OperationType,HostName,UserName,PrimaryKey,OldData,NewData)
        VALUES (DATABASE(),@TableId,@Type,@@hostname,CURRENT_USER(),OLD.PKAuthenticationLogID,
        CONCAT_WS('',
          '[Key]=',IFNULL(OLD.Key,'NULL')
          ,'[Secret]=',IFNULL(OLD.Secret,'NULL')
          ,'[Token]=',IFNULL(OLD.Token,'NULL')
          ,'[ApplicationId]=',IFNULL(OLD.ApplicationId,'NULL')
          ,'[IpAddress]=',IFNULL(OLD.IpAddress,'NULL')
          ,'[Message]=',IFNULL(OLD.Message,'NULL')
          ,'[CreatedOn]=',IFNULL(OLD.CreatedOn,'NULL')
          ,'[IsSuccess]=',IFNULL(EXPORT_SET(OLD.IsSuccess,'1','0','',1),'NULL')
          ,'[LogType]=',IFNULL(OLD.LogType,'NULL')
          ),
            CONCAT_WS('',
          '[Key]=',IFNULL(NEW.Key,'NULL')
          ,'[Secret]=',IFNULL(NEW.Secret,'NULL')
          ,'[Token]=',IFNULL(NEW.Token,'NULL')
          ,'[ApplicationId]=',IFNULL(NEW.ApplicationId,'NULL')
          ,'[IpAddress]=',IFNULL(NEW.IpAddress,'NULL')
          ,'[Message]=',IFNULL(NEW.Message,'NULL')
          ,'[CreatedOn]=',IFNULL(NEW.CreatedOn,'NULL')
          ,'[IsSuccess]=',IFNULL(EXPORT_SET(NEW.IsSuccess,'1','0','',1),'NULL')
          ,'[LogType]=',IFNULL(NEW.LogType,'NULL'))
        );
      END IF;
END
$$

DELIMITER ;

There is no error message. But I try query, nice work.

SET @IsActive = 1;
    IF (@IsActive=1) THEN

After some experimentation, I figured out that the code works if I don't prefix my variable names with a '@':

DECLARE D_Type varchar(1);
DECLARE D_TableId tinyint;
DECLARE D_IsActive bigint;
SET D_Type='U';
SET D_TableId = 2;
SET D_IsActive = (SELECT IsActive FROM AuditDB.ExportLogTableDefinition WHERE TableId=D_TableId);
IF D_IsActive=1 THEN


Calculate Return Value - 1,3,6 Month Returns How do I calculate the return for 1mth, 3mths, and 6mths?

I have this query but ValuationDate will not be exactly X months ago.

e.g. 2015-01-30 returns 2014-10-30 in the calculation. However, only 2014-10-31 exists in the table. So how do I return the latest date in the table for that month?

SELECT PriceValue FROM @Output WHERE ValuationDate = DATEADD(MONTH,-3,ValuationDate)

ValuationDate is DATETIME. Using SQL Server 2008R2

So in the below table data, I want to return the PriceValue for 2014-10-31 on the row with ValuationDate 2015-01-30

Table Data:

I don't think your code will return any rows as the WHERE clause will never return true.

SELECT PriceValue FROM @Output WHERE ValuationDate = DATEADD(MONTH,-3,ValuationDate)

This would only make sense if the ValuationDate value inside the DATEADD function was a literal value or a variable like below:

SELECT PriceValue FROM @Output WHERE ValuationDate = DATEADD(MONTH,-3, '20160930')

or

SELECT PriceValue FROM @Output WHERE ValuationDate = DATEADD(MONTH,-3, @MyValuationDate)

I'm also assuming that ValuationDate is a DATE type column and not a DATETIME or DATETIME2.

But I think I understand your question and the code below should be what you need:

SELECT PriceValue
FROM @Output
WHERE ValuationDate = EOMONTH(DATEADD(MONTH, -3, @MyValuationDate))


When does a table get an oid? I'm wondering when a table acquires an oid. Is it only when the transaction commits? Or does it have a hidden oid prior to commit? Especially in the context of a CREATE TABLE AS SELECT.

Table OIDs are assigned quite early in table creation, and are definitely present by the time the CREATE TABLE finishes (before xact commit), since the row is visible in pg_class by then.

The new pg_class row (and its oid) are not visible to concurrent transactions until commit, though.

It's not clear why you care, though. It shouldn't matter.


How to fix MongoDB connection issue I've got a MongoDB running on a medium EC2 instance and everything seems fine apart from the fact that I keep getting this error:

Could not connect to a primary node for replica set #<Moped::Cluster:70157124022400 @seeds=[<Moped::Node resolved_address="my_ip:my_port">]>

This seems to happen only when Sidekiq get a big loads of queued jobs, so the question is, is this a scalability issue?

Looking at the EC2 instance however, it rarely goes over 30% of CPU usage or 40% memory used.

As that error message say: It can not find IP address to "my_ip:my_port" named machine! ;-)

Check your configuration. Somewhere there is written that "my_ip:my_port"


Database Design For reporting comparison results I'm going to design an table architecture. Here I wanted to compare same data coming from different sources say Source_A and Source_B. I have to compare few attributes and identify

Mismatches
Data that are missing in source_a
Data that are missing in Source_B.

Finally i have report to the same in PowerBI with charts. For now I have 2 tables A_DATA and B_DATA and both are having below structure (this is just a sample, I have lot more columns)
+---------------+
| Columns       |
+---------------+
| Material_ID   |
+---------------+
| Material_Name |
+---------------+
| Material_Type |
+---------------+
| Quantity      |
+---------------+

Now I'm confused whether should I create separate table for 3 cases(Mismatch,Source_A missing,Source_B Missing) or In single table I should have one more column saying Status and keep everything there. For reporting in PowerBI (like out of 1K rows, 5K are mismatches). Please suggest. Im really confused.

This is common task which may be solved using FULL OUTER JOIN by any NOT NULL expression which is unique over each separate table and is present in both tables. Mismatches are detected by column compare, missing rows - by NULL value for joining expression in according table.
SELECT COALESCE(t1.id, t2.id) id,
       CASE WHEN t1.id IS NULL
                 THEN 'Absent in TableA'
            WHEN t2.id IS NULL
                 THEN 'Absent in TableB'
            WHEN t1.columnX != t2.ColumnX
                 THEN 'Differs in ColumnX at least' -- may differ in another columns too
            WHEN t1.columnY != t2.ColumnY
                 THEN 'Differs in ColumnY at least' -- but identical by columnX due to previous condition
            -- and so on
            ELSE 'Are identical'
            END AS MismatchType
FROM tableA t1
FULL OUTER JOIN tableB t2 ON t1.id = t2.id

If you need detailed difference diagnosis then you must build complex conditions included all columns in interest, like
WHEN t1.columnX != t2.ColumnX AND t1.columnY != t2.ColumnY AND t1.columnZ = t2.ColumnZ
    THEN 'Differs in ColumnX and ColumnY'


Insert values in table from front-end CategoryId I want to add a value from front end into a table. I want to do it in asp.net. I have a table which is as below:-

CategoryId     int
CategoryName   nvarchar(50)   not null
ParentId       int            null

Also see the code:-

<asp:TextBox ID="txtCategory" runat="server"></asp:TextBox>

<asp:Button ID="btnCategoryUpload" runat="server" Text="Upload Category" Width="110" />

I tried with my code like this:-

<form id="form1" runat="server">
    <asp:TextBox ID="txtCategory" runat="server"></asp:TextBox>
    <asp:Button ID="btnSubmit" runat="server" Text="Submit" Width="110" OnClick="btnSubmit_Click" />
</form>

Code behind for the same:-

SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultSQLConnectionString"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {

    }
}

protected void btnSubmit_Click(object sender, EventArgs e)
{
    SqlCommand cmd = new SqlCommand("Insert into CategoriesForMerchant values (@CategoryName, @ParentId)", conn);
    cmd.Parameters.AddWithValue("@CategoryName", txtCategory.Text);
    cmd.Parameters.AddWithValue("@ParentId", "");
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();
    Response.Write("<script>alert('File uploaded successfully');</script>");
}

But I got the below error, please help.

  The INSERT statement conflicted with the FOREIGN KEY SAME TABLE
  constraint "fk_subcategories". The conflict occurred in database
  "Test", table "dbo.CategoriesForMerchant", column 'CategoryId'. The
  statement has been terminated.

Please help

Table code:-

 create table [dbo].[categories](
    id int identity(1,1) not null,
    name nvarchar(50) not null,
    parent int null,
 constraint [pk_categories] primary key clustered ( id asc))

go

alter table [dbo].[categories]  with check add  constraint [fk_subcategories] foreign key(parent)
references [dbo].[categories] ([id])
go

alter table [dbo].[categories] check constraint [fk_subcategories]
go

On Button Click, I got to do something like this, as suggested by @MarkSinkinson.

protected void btnSubmit_Click(object sender, EventArgs e)
{
    SqlCommand cmd = new SqlCommand("Insert into CategoriesForMerchant (CategoryName) values (@CategoryName)", conn);
    cmd.Parameters.AddWithValue("@CategoryName", txtCategory.Text);
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();
    Response.Write("<script>alert('File uploaded successfully');</script>");
}


why am I getting a warning of Depreciation of INT(11) in latest version of MySQL? When I try to create a table with a column of type INT, it automatically gets allocated as INT(11) and this Int length will get depreciated in the latest version of MySQL. How can I declare the column as INT except of INT(11)?

Just because it is deprecated does not mean it won't work.
Just don't explicitly specify length anymore when creating new tables.
Let MySQL figure out how the length will be handled.
Personally, I never used the length operator in my years as a DBA. Besides, the length operator (11) simply stands for the display on a signed INT.
What is the range of a signed INT ??? -2147483648 to 2147483647. Please note that -2147483648 is 11 characters. That's why 11 will appear in SHOW CREATE TABLE if you created the table without a length operator.
If you are concerned with how MySQL Workbench handled it, you may need to consider:

Upgrading to the latest version of MySQL Workbench
Switching to another up-to-date MySQL GUI

For now, the best thing is to run SHOW CREATE TABLE and ALTER TABLE commands without length operators. Let MySQL deprecation happen and don't look back.


Daisy chaining two is-a relationships? I'm currently dabbling in ER modeling and wondering if it is possible to create a chain of is-a relationships. Example:

Here I'm basically trying to model specific in-stock items available for rent which are products in general and and a product type in particular.

Would this ER model be legal and is there a better way to express such a relationship?

Thank you in advance.

Matt

The basic answer is yes, it is possible to chain is-a relationships.  In your case the relationship between Product and Product type 1 or Product type 2 is a case of subtypes.  See the questions tagged with this tag for answers to previously asked questions.   The relationship between Item and Product is, perhaps, a trivial case of subtyping, because there is only one subtype in your model (up to this point).

In the world of ER modeling, subtypes go by the terms "generalization and specialization".  You can look up these two terms for more background info. What you may find is that this feature of ER modeling was added to the ER model at some point in history, and this was known as "Extended ER"  (EER).

It's in the world of relational data modeling that things start to get really interesting.  If you are building a relational database, you are going to want to transform your ER model into a relational model before actually building the database.  In simple learning exercises, this can be done without drawing a separate diagram, just by adding details like foreign keys to the ER diagram.  In cases where there are over a hundred different relational tables and many relationships, a more formal approach may be necessary.

The basic relational model has no mechanism for inheritance.  Therefore, when you go to implement a case of generalization/specialization, you have to create tables that somehow mimic the behavior of inheritance well enough for data management purposes.

Two design patterns are available as alternatives.  They are summarized by Martin Fowler here:

http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html
http://martinfowler.com/eaaCatalog/classTableInheritance.html

Notice that in Fowler's example,  is-a relationships have been chained, where cricketer is a subtype of player and a supertype of bowler.  That's the short answer to your question.

Another design pattern that is often useful with is-a relationships is shared primary key.  Here are some questions over in SO tagged with this pattern:

https://stackoverflow.com/tags/shared-primary-key/hot

You can use shared primary key with is-a relationships even if it's not really a case of subtyping.


MySQL-How to find that value in a column is subset with all other values in same column multiple time I have a table named as Team where table contain 3 column (ID,Team,Task). Its datatypes are (INT,VARCHAR(255),INT). Below you will find a sample table where I would like to perform the query on column team.

   -----------------------------
   |ID|        Team      |Task |
   -----------------------------
   |1 |team1,team2,team5 |23   |
   |2 |team3,team2       |34   |
   |3 |team1,team5,team3 |45   |
   |4 |team2             |2    |
   |5 |team1,team3       |65   |
   |6 |team2,team5       |4    |
   |7 |team1             |34   |
   |8 |team5             |67   |
   -----------------------------

My question is that i like to check whether value in column team is subset of any of the value in same column team (It should not check with it own value in the column team), if that value comes out to be true then i would like to delete that row which has that value.It should run for all values of column team.

OUTPUT table will be like this

   ---------------------------
   |ID|    Team          |Task|
   ---------------------------
   |1 |team1,team2,team5 |23  |
   |2 |team3,team2       |34  |
   |3 |team1,team5,team3 |45  |
   ----------------------------

As you can see in team5 is subset of {team2,team5},{team1,team5,team3} and {team1,team2,team5}, Same with team1 ,{team2,team5} ,{team1,team3}, team2 they will also get removed because they are subset of some value in column team.

If there is any other approach to do this then do tell me. In initial Table i used group_concat function on it to get all values related to particular ID.

As DBNull pointed out, you are experiencing this challenge because the Team column is breaking First Normal Form. The values in that column should be atomic.

You have two options here. The first is to follow DBNull's recommendation of having the teams registered in their own Team table and having a separate TeamGroup table to store Team Groups. While you're at it you could throw in the third table to store the Tasks that the Team Groups are bound to (trusting they are bound to be changed from time to time and you'd like to know that history).

The second option is to write a script that does pretty much what you'd be doing in the first option except it reflects the change back to the table in the de-normalized format.

This isn't trivial. You'll need to loop over each row, split the column into the atomic team names and then query the table using a FIND_IN_SET() for each team name plus ensuring not to match the row your cursor is pointing to. Every time the table changes you'll need to re-run the query.

Here is some SQL that demonstrates normalizing the data. It has foreign keys for data consistency. The task IDs are not stored in the team_group table in this example instead a separate table exists which allows for multiple tasks.

CREATE TABLE `team` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL COMMENT 'Name of Team',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `team_group` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL DEFAULT '' COMMENT 'Optional Name of Team Group',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='a team group can be one or more teams';

CREATE TABLE `team_group_map` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `team_id` int(10) unsigned NOT NULL,
  `group_id` int(10) unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_team_group` (`team_id`,`group_id`),
  KEY `group_id` (`group_id`),
  CONSTRAINT `team_group_map_ibfk_1` FOREIGN KEY (`team_id`) REFERENCES `team` (`id`) ON DELETE CASCADE,
  CONSTRAINT `team_group_map_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `team_group` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Binds teams to a team group';

CREATE TABLE `task` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `task_group_map` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `task_id` mediumint(8) unsigned NOT NULL,
  `group_id` int(10) unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_group_task` (`group_id`,`task_id`),
  KEY `task_id` (`task_id`),
  CONSTRAINT `task_group_map_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `task` (`id`) ON DELETE CASCADE,
  CONSTRAINT `task_group_map_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `team_group` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Binds tasks to a team group';

-- Add a new team
INSERT INTO team (name) VALUES ('Team1');

-- Add a new group and add an existing team to it
INSERT INTO team_group (name) VALUES ('Euro east group');
-- Get ID for this group
SELECT id FROM team_group WHERE name = 'Euro east group';
-- Get team's ID
SELECT id FROM team WHERE name = 'Team1';
-- Add team to the group (team_id = 1, group_id = 1)
INSERT INTO team_group_map (team_id, group_id) VALUES (1, 1);

-- Add a task and set it for a group
INSERT INTO task (name) VALUES ('Win Finals');
-- Get ID for this task
SELECT id FROM task WHERE name = 'Win Finals';
-- Get team's ID
SELECT id FROM team WHERE name = 'Team1';
-- Add task to the group (task_id = 1, group_id = 1)
INSERT INTO task_group_map (task_id, group_id) VALUES (1, 1);

-- Select the teams and group IDs
SELECT team.name, group_id
FROM team_group_map
INNER JOIN team ON (team.id = team_id);

-- Select the teams and group names
SELECT team.name, group.name
FROM team_group_map
INNER JOIN team ON (team.id = team_id)
INNER JOIN team_group AS `group` ON (group.id = group_id);

-- Select the groups, their task IDs and when the tasks were added to the group
SELECT group.name, task_id, map.updated_at
FROM task_group_map AS map
INNER JOIN team_group AS `group` ON (group.id = group_id);

-- Select the groups and their task Names + when tasks were added to group
SELECT group.name, task.name, map.updated_at
FROM task_group_map AS map
INNER JOIN task ON (task.id = task_id)
INNER JOIN team_group AS `group` ON (group.id = group_id);

-- Select the most recent task for a group
SELECT task.name, map.updated_at
FROM task_group_map AS map
INNER JOIN task ON (task.id = task_id)
WHERE group_id = 1
ORDER BY map.updated_at DESC
LIMIT 1;


last modified rows timestamp for SQL Server 2016 Is there a feature/script to check when was the last time a row has been modified/changed in SQL 2016+ tables?Does SQL track this automatically or do we have a feature which gives this info? Please let me know your thoughts/

Noting done by default. You can turn on stuff like temporal (probably overkill) or create a trigger to update the table.
SQL Server keeps track of all modification internally, in the transaction log, for its own purposes (like rollback and crash recovery). But the log is volatile and even if you get there before the info is gone, you'd have to decode the info yourself using unsupported means (like fn_dblog).
[Edit] Adding in the options Change Tracking (lower overhead) and Change Data Capture (higher overhead and uses an Agent job but gives more info than CT), as suggested in the comments below by Dominique.


SP returns positive RecordsAffected value even when TSQL statement rolled back I'm attempting to build an all-or-nothing TSQL statement, something like this:

BEGIN TRANSACTION
BEGIN TRY
    -- Loop over a TVP passed as a param
    DECLARE i CURSOR LOCAL FAST_FORWARD FOR SELECT [stmt] FROM TVP param;
    OPEN i;
    FETCH NEXT FROM i INTO @iDB, @iTBL, @iATTRS, @iVALS;
    WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @SQL = next row of TVP
            EXECUTE sys.sp_executesql @SQL
        END
    CLOSE i;
    DEALLOCATE i;
    COMMIT TRANSACTION
END TRY

BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
ROLLBACK TRANSACTION
END CATCH

The SP generally works as intended, in that the transactions are all or nothing, and running the SP from SSMS returns the error as selected in the catch statement.

But on the C# side, the dbResponse.RecordsAffected value is always incremented.

Why is this? Also, how can I get this SP to return the correct rows effected value? (i.e. the number of rows that were effected by the SP on success)

Is it possible to reset this value in the CATCH statement?

on the C# side, the dbResponse.RecordsAffected value is always incremented.

That's just a total of the row count messages returned from SQL Server.  These are sent as queries are run, before the transaction has been committed or rolled back.

  how can I get this SP to return the correct rows effected value?

Keep track of the row count in a variable, and return that to the client with an output parameter or a result set.

  Is it possible to reset this value in the CATCH statement?

As @Kin suggested, you can SET NOCOUNT ON and no rowcount messages will be sent to the client.  Then after you COMMIT you can turn NOCOUNT OFF and run a query to that generates the desired rowcount message. Perhaps something cheezy like:

set nocount on
--do stuff
declare @rows int = 25

set nocount off
select top (@rows) 1 a
into #bitbucket
from sys.objects o, sys.columns c


How to design database for DNS? What tables will be there.Also as read percentage will be significantly larger than write, is indexing on columns advised? What other precautions are necessary?

For now, I can just think of 1 table containing two columns namely domain and ip address and B+ tree index implemented on both. Is this sufficient?

A simple list of name/address pairs with an index on each would be sufficient if absolutely all you needed to do was lookup one from the other. Unfortunately you will find that the DNS protocol has larger requirements than that so if you intending to interact with the public domain name system you have much more work ahead of you. See http://en.wikipedia.org/wiki/Domain_Name_System for a quick primer and a list of the relevant RFCs (which are essentially the specification you'd be expected to work to as a participant in the public DNS infrastructure (though remember that many features are optional: you may chose to implement an authoritative service only so not need to worry about recursive queries (except knowing the right error response to give when you receive one).

There are a couple of F/OSS DNS servers that use a relational database back-end instead of bind-style zone files, perhaps you could look at their structures as a hint (though if you are building a commercial solution be careful of licensing issues: for instance you might find looking at GPL covered code to be a bad idea if you intend to distribute the results of your project in a non-GPL compatible manner):
MyDNS: http://mydns.bboy.net/
Power DNS: http://en.wikipedia.org/wiki/PowerDNS


BACKUP and RESTORE MySQL Database I have very specific question.
I have seen the instructions on how to backup and restore mysql database using SSH but they barely explain the process when backing up on one server and restoring on another server.

What i need is backup the mysql database on server A and restore the mysql database on server B. How do i do this using SSH command line?

I have seen this http://www.ipragmatech.com/backup-restore-mysql but nothing on backing up and restoring on different servers.

Thanks.

As from your question if the scenario is like that.

You are on your local machine and accessing the remote servers A and B.

Process is simple

Step1 : Connect to server A where A is username of server

ssh A@192.168.9.214

Step2 : Generate the database dump of the server A as

mysqldump -uroot -proot  db1  > database_db1.sql

Step3 : Copy the dump on the server B using SCP as

scp database_db1.sql B@192.168.9.215:/home/user/Desktop

Step4 : Connect to the Server B as

ssh B@192.168.9.215

Step5 : Restore the copied dump here using mysql client utility

mysql -uroot -proot db < /home/user/Desktop/database_db1.sql


Java Spring application Database triggers auditing - how to provide the right user that made the change We are developing a new web application.
one of the most basic requirement is to audit all entities changes into a separate table.

We would like to use DB triggers for that purpose.

We use MySQL as our RDMBS.

The problem we now foresee is that whenever a trigger is pulled, and insert a new entry for the DB, it cant possibly know the (applicative) user that made the change. (all users have different ids, but spring uses a single user account for the db manipulations.)

Any ideas how to resolve this issue?

Unless you create a different database user for each application user -so it is available with the user() or current_user() functions:

mysql> SELECT user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

it is not possible to know this at MySQL level unless it is provided by the application.

You could use SQL comments on application code:

/* user=jynus */ INSERT INTO test values (1);

Which can later be processed using the slow log, if active, or some of the auditing plugins. Please note that the mysql command-line client application deletes comments before sending them to the server.

The only way you can log it using triggers with a single user that I can now think is setting a variable at the start of the connection:

mysql> SET @user := 'jynus';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE test (i int PRIMARY KEY);
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE log (id serial, i int, user varchar(100));
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TRIGGER test_AI AFTER INSERT ON test FOR EACH ROW
       INSERT INTO log (i, user) VALUES (NEW.i, @user);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO test VALUES (5);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM log;
+----+------+-------+
| id | i    | user  |
+----+------+-------+
|  1 |    5 | jynus |
+----+------+-------+
1 row in set (0.00 sec)


Any reason not to add 'rowversion' column? The TechNet article, "Optimizing Microsoft Office Access Applications Linked to SQL Server", recommends adding a rowversion column to SQL Server tables linked from MS Access.  From the Supporting Concurrency Checks section:

  Office Access automatically detects when a table contains this type of column and uses it in the WHERE clause of all UPDATE and DELETE statements affecting that table. This is more efficient than verifying that all the other columns still have the same values they had when the dynaset was last refreshed.

The nice thing is that every UPDATE/DELETE statement and bound form will benefit from this addition without having to make any changes within MS Access (aside from re-linking the tables).

I was unaware of this feature until recently.  I am considering adding a named rowversion column to every table in SQL Server that I link to from MS Access.  Are there any downsides I should be aware of before I do this?

Obviously there will be storage requirements and performance impacts, but I assume these will be negligible.  Also, several of these tables are articles in a merge replication scenario; does that make a difference?

Other than the minor things you've already picked up on (additional storage requirements, and potential performance differences as a result of requiring more I/O for the same number of rows), no, I can't think of any real gotchas with adding a ROWVERSION column to these tables.

One potential issue, though, with merge replication specifically, is if Access grabs a row from one of the servers, then tries to grab a copy of the same row from a different subscriber (not sure if that is possible in your architecture). The reason is that the ROWVERSION values are re-generated at each subscriber, so they won't match the source - this may make it look like the row has changed, when it hasn't. Maybe not possible, or maybe it's ok that such a row always be considered "dirty," but something to keep in mind (and probably test thoroughly).

You might also want to review the merge replication section in this document about schema changes in general.