SQL Server Connection String Planning

‘What should I put in the application connection string?’

It sounds like a simple request!

I’d suggest otherwise and some thought and planning is required before you answer.

At some stage in the future the database is likely going to move. The database move could be due to several factors i.e. performance issues, DR failover, new version of SQL Server, hardware upgrades etc.

As a DBA, you probably won’t have access to the application servers or remote clients where the connection name is entered and you’d like to keep it that way.

Let’s go through your options, remembering that you want to:

keep as far away as possible from having to ever update the application connection string.
minimize your future effort requirements when the database moves.

IP Address

Pros:

A server name change doesn’t affect the client.

Cons:

Not friendly to remember.
DR failover/failback can be complicated and time consuming.
Each application will need to be updated if the database moves or the IP address of the server changes.

Log Shipping failover steps example:

The Primary server needs to be off, as you can’t have duplicate IP addresses. It is likely that the Primary server will be off in the event of a DR scenario.
The IP address needs to be changed on the Secondary server so that the applications can connect.

Log Shipping failback steps example:

Change the IP address on the original Primary server, as you can’t have duplicate IP addresses.
Re-establish Log Shipping and failback the databases to the original Primary server.
Remove Log Shipping.
Change the IP address on the original Secondary server and shut it down, as you can’t have duplicate IP addresses.
Change the IP address on the original Primary server, so that the applications can connect.
Start the original Secondary server and re-establish log shipping.

That’s a lot of steps, with a lot of outage windows.

Server Name

Pros:

You don’t have to update the connection string if the IP address changes.

Cons:

Each application will need to be updated if the database moves or the server name changes.

Log Shipping failover steps example:

The Primary server needs to be off, as you can’t have duplicate server names. It is likely that the Primary server will be off in the event of a DR scenario.
The server name needs to be changed on the Secondary server so that the applications can connect.
The SQL Server internal name may also need to changed.

It’s worth noting that if any other applications reply on the server name i.e. monitoring, backups etc, this method will also likely impact them as they connect based on the server name.

Log Shipping failback steps example:

Change the server name on the original Primary server, as you can’t have duplicate server names.
Re-establish Log Shipping and failback the databases to the original Primary server.
Remove Log Shipping.
Change the server name of the original Secondary server and shut it down, as you can’t have duplicate server names.
Change the server name of the original Primary server back to what it was, so the applications can connect.
Update the SQL Server internal name on both servers, if required.
Start the original Secondary server and re-establish log shipping.

That’s a lot of steps, with a lot of outage windows.

Server Name CNAME

Pros:

You don’t have to rename servers when the database moves during a DR failover/failback scenario.
You can re-direct multiple applications with a single DNS change.

Cons:

Each application will need to be updated if the database moves to another server permanently or the main server name changes.

Log Shipping failover steps example:

Update the DNS Alias (CNAME) Record to point to the Secondary server so that the applications can connect.

Log Shipping failback steps example:

Re-establish Log Shipping and failback the databases to the original Primary server.
Update the DNS Alias (CNAME) Record to point to the original Primary server so that the applications can connect.
Re-establish log shipping.

That’s a lot fewer steps, with minimal outage windows.

Application Name CNAME

Pros:

You don’t have to ever update the application connection string. The goal is achieved.

Cons:

In the event of a failover/failback, multiple CNAME records will need to be updated.

Log Shipping failover steps example:

Update the DNS Alias (CNAME) Record to point to the Secondary server so that the applications can connect.

Log Shipping failback steps example:

Re-establish Log Shipping and failback the databases to the original Primary server.
Update the DNS Alias (CNAME) Record to point to the original Primary server so that the applications can connect.
Re-establish log shipping.

CAUTION: If there are a lot of applications, you’ll have a lot of CNAME records to update.

Server Name CNAME plus Application Name CNAME

Pros:

You don’t have to ever update the application connection string. The goal is achieved.
Ultimate flexibility.

Cons:

More CNAME record creations are required.

Log Shipping failover steps example:

Update the DNS Alias (CNAME) Record 1 to point to the Secondary server so that the applications can connect.

Log Shipping failback steps example:

Re-establish Log Shipping and failback the databases to the original Primary server.
Update the DNS Alias (CNAME) Record 1 to point to the original Primary server so that the applications can connect.
Re-establish log shipping.

Database Mirroring

Database Mirroring allows for an initial partner name (Principal server) and a failover partner name (Mirror server) to be specified in the application connection string.

Based on the already covered connection strings, the most flexible option for a Database Mirroring environment is shown below. You don’t have to ever update the application connection string and your goal is achieved.

No DNS or application connection string changes would be required in the event of a DR failover/failback.

Alwayson Availability Groups

Alwayson Availability Groups introduces a listener.

Based on the already covered connection strings, the most flexible option for Alwayson Availability Groups is shown below. You don’t have to ever update the application connection string and your goal is achieved.

No DNS or application connection string changes would be required in the event of a DR failover/failback.

Summary

Planning your connection string can save you a lot of hassle and it’s not as simple as just giving the server name, which is done far too frequently.

The above is the start and next I’ll cover SPNs which you’ll likely find are a critical part of your connection string planning.

How to identify your Azure SQL Database’s Tier and Performance Level

If you are looking to quickly check your Azure SQL Database’s Tier and Performance Level, the following may assist you:

First connect to the database and then run the following code:

SELECT
 PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(20)),4) AS Product_Version
,COALESCE(DATABASEPROPERTYEX(DB_NAME(),'Edition'),'NA') AS Tier
,COALESCE(DATABASEPROPERTYEX(DB_NAME(),'ServiceObjective'),'NA') AS Performance_Level

If the Performance Level doesn’t show, you are likely on an older SQL Server 2012 database (Product_Version 11) as these databases do not currently appear to report the value correctly.

Another method to remove a quote from a string

I suspect mostly everyone is aware of using REPLACE to remove a quote from a string e.g.

DECLARE @info varchar(100)
SET @info = 'Login failed for user ''abc''.'
SELECT REPLACE(@info,'''','')

But, what if you’re unable to manipulate the string before using REPLACE i.e. you cannot escape the quote?

One solution is to use SET QUOTED_IDENTIFIER OFF.

SET QUOTED_IDENTIFIER OFF;
DECLARE @info varchar(100)
SET @info = "Login failed for user 'abc'."
SELECT REPLACE(@info,'''','')

Database mirroring failover and recovery scripts

Check which databases have mirroring configured

This can be run on either the principal or mirror instance.

SELECT DB_NAME(database_id) AS these_databases_have_mirroring_configured
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
ORDER BY DB_NAME(database_id);

Check which databases do not have mirroring configured

This can be run on either the principal or mirror instance.

SELECT DB_NAME(database_id) AS these_databases_do_not_have_mirroring_configured
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NULL
AND DB_NAME(database_id) NOT IN ('master','model','msdb','tempdb')
ORDER BY DB_NAME(database_id);

Check which mirrored databases are in synchronous mode

Only Enterprise Edition allows asynchronous mode.
This can be run on either the principal or mirror instance.

SELECT DB_NAME(database_id) AS these_databases_are_in_synchronous_mode
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
--AND mirroring_role_desc = 'PRINCIPAL'
--AND mirroring_role_desc = 'MIRROR'
AND mirroring_safety_level_desc = 'FULL'
ORDER BY DB_NAME(database_id);

Check which mirrored databases are not in synchronous mode

Only Enterprise Edition allows asynchronous mode.
This can be run on either the principal or mirror instance.

SELECT DB_NAME(database_id) AS these_databases_are_not_in_synchronous_mode
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
--AND mirroring_role_desc = 'PRINCIPAL'
--AND mirroring_role_desc = 'MIRROR'
AND mirroring_safety_level_desc <> 'FULL'
ORDER BY DB_NAME(database_id);

Check which mirrored databases are in asynchronous mode

Only Enterprise Edition allows asynchronous mode.
This can be run on either the principal or mirror instance.

SELECT DB_NAME(database_id) AS these_mirrored_databases_are_in_asynchronous_mode
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
--AND mirroring_role_desc = 'PRINCIPAL'
--AND mirroring_role_desc = 'MIRROR'
AND mirroring_safety_level_desc = 'OFF'
ORDER BY DB_NAME(database_id);

Check which mirrored databases are not in asynchronous mode

Only Enterprise Edition allows asynchronous mode.
This can be run on either the principal or mirror instance.

SELECT DB_NAME(database_id) AS these_mirrored_databases_are_not_in_asynchronous_mode
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
--AND mirroring_role_desc = 'PRINCIPAL'
--AND mirroring_role_desc = 'MIRROR'
AND mirroring_safety_level_desc <> 'OFF'
ORDER BY DB_NAME(database_id);

Check which mirrored databases are synchronized

This can be run on either the principal or mirror instance.

SELECT 
 DB_NAME(database_id) AS these_databases_are_fully_synchronized
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
--AND mirroring_role_desc = 'PRINCIPAL'
--AND mirroring_role_desc = 'MIRROR'
AND mirroring_state_desc = 'SYNCHRONIZED' 
ORDER BY DB_NAME(database_id);

Check which mirrored databases are not synchronized

This can be run on either the principal or mirror instance.

SELECT 
 DB_NAME(database_id) AS these_databases_are_not_fully_synchronized
 ,mirroring_state_desc 
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
--AND mirroring_role_desc = 'PRINCIPAL'
--AND mirroring_role_desc = 'MIRROR'
AND mirroring_state_desc <> 'SYNCHRONIZED' 
ORDER BY DB_NAME(database_id);

Check which mirrored databases have mirroring paused

This can be run on either the principal or mirror instance.

SELECT DB_NAME( database_id ) AS these_databases_have_mirroring_paused
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
--AND mirroring_role_desc = 'PRINCIPAL'
--AND mirroring_role_desc = 'MIRROR'
AND mirroring_state_desc = 'SUSPENDED'
ORDER BY DB_NAME( database_id );

Check which mirrored databases do not have mirroring paused

This can be run on either the principal or mirror instance.

SELECT DB_NAME( database_id ) AS these_databases_do_not_have_mirroring_paused
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
--AND mirroring_role_desc = 'PRINCIPAL'
--AND mirroring_role_desc = 'MIRROR'
AND mirroring_state_desc <> 'SUSPENDED'
ORDER BY DB_NAME( database_id );

Check what the mirroring ping timeout value is for the mirrored databases

This can be run on either the principal or mirror instance.

SELECT DB_NAME(database_id) AS mirrored_database
,mirroring_connection_timeout AS mirroring_connection_timeout_value_in_seconds
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
ORDER BY DB_NAME(database_id);

Change mirrored databases to asynchronous mode

Only Enterprise Edition allows asynchronous mode
Run on which ever instance contains the principal database(s) you would like to have asynchronous mirroring mode set.

SELECT 
  'ALTER DATABASE [' + DB_NAME(database_id) + '] SET PARTNER SAFETY OFF;'
+ ' PRINT ''[' +  DB_NAME(database_id) + '] has been set to asynchronous mirroring mode.'';'
  AS command_to_set_mirrored_database_to_use_synchronous_mirroring_mode
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
AND mirroring_role_desc = 'PRINCIPAL'
AND mirroring_safety_level_desc = 'FULL'
ORDER BY DB_NAME(database_id);

Change mirrored databases to synchronous mode

Run on which ever instance contains the principal database(s) you would like to have synchronous mirroring mode set.

SELECT 
  'ALTER DATABASE [' + DB_NAME(database_id) + '] SET PARTNER SAFETY FULL;'
+ ' PRINT ''[' +  DB_NAME(database_id) + '] has been set to synchronous mirroring mode.'';'
  AS command_to_set_mirrored_database_to_use_synchronous_mirroring_mode
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
AND mirroring_role_desc = 'PRINCIPAL'
AND mirroring_safety_level_desc = 'OFF'
ORDER BY DB_NAME(database_id);

Change the mirroring ping timeout value for the mirrored databases

Run on which ever instance contains the principal database(s) you would like to have the mirroring ping timeout value changed.

SELECT 
  'ALTER DATABASE [' + DB_NAME(database_id) + '] SET PARTNER TIMEOUT 90;'
+ ' PRINT ''The mirroring ping timeout value for [' +  DB_NAME(database_id) + '] has been changed.'';'
  AS command_to_change_the_mirroring_ping_timeout_value_for_the_mirrored_database
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
AND mirroring_role_desc = 'PRINCIPAL'
ORDER BY DB_NAME(database_id);

Pause mirroring for mirrored databases

Run on which ever instance contains the principal database(s) you would like to have mirroring paused.

SELECT 
 'ALTER DATABASE [' + DB_NAME( database_id ) + '] SET PARTNER SUSPEND;'
+ ' PRINT ''[' +  DB_NAME(database_id) + '] has had mirroring paused.'';' 
  AS command_to_pause_mirroring_for_the_mirrored_database 
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
AND mirroring_role_desc = 'PRINCIPAL'
AND mirroring_state_desc <> 'SUSPENDED'
ORDER BY DB_NAME(database_id);

Resume mirroring for mirrored databases

Run on which ever instance contains the principal database(s) you would like to have mirroring resumed.

SELECT 
 'ALTER DATABASE [' + DB_NAME( database_id ) + '] SET PARTNER RESUME;'
+ ' PRINT ''[' +  DB_NAME(database_id) + '] has had mirroring resumed.'';' 
  AS command_to_resume_mirroring_for_the_mirrored_database 
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
AND mirroring_role_desc = 'PRINCIPAL'
AND mirroring_state_desc = 'SUSPENDED'
ORDER BY DB_NAME(database_id);

Manually failover mirrored databases

Run on which ever instance contains the principal database(s) you want to manually failover.
Make sure you are in the master database, all mirrored databases to failover are fully synchronized and not paused.

SELECT 
 'ALTER DATABASE [' + DB_NAME(database_id) + '] SET PARTNER FAILOVER;'
+ ' PRINT ''[' +  DB_NAME(database_id) + '] has been been manually failed over.'';' 
  AS command_to_manually_failover_the_mirrored_database
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
AND mirroring_role_desc = 'PRINCIPAL'
AND mirroring_safety_level_desc = 'FULL'
AND mirroring_state_desc = 'SYNCHRONIZED'
ORDER BY DB_NAME(database_id);

Force recovery of mirrored databases in the event the principal instance is not available

Run on which ever instance contains the mirrored database(s) you want to recover.

SELECT 
 'ALTER DATABASE [' + DB_NAME( database_id ) + '] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;'
+ ' PRINT ''[' +  DB_NAME(database_id) + '] has been recovered and mirroring suspended. Restart the principal instance when it becomes available and resume mirroring, or remove mirroring from the principal and mirror instances.'';' 
  AS command_to_force_recovery_of_a_mirrored_database 
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
AND mirroring_role_desc = 'MIRROR'
AND mirroring_state_desc = 'DISCONNECTED'
ORDER BY DB_NAME(database_id);

Remove database mirroring from mirrored databases

This can be run on either the principal or mirror instance that contains the database(s) you want mirroring removed.

SELECT 
 'ALTER DATABASE [' + DB_NAME( database_id ) + '] SET PARTNER OFF;'
+ ' PRINT ''[' +  DB_NAME(database_id) + '] has had mirroring removed.'';' 
  AS command_to_remove_mirroring_from_the_mirrored_database 
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
ORDER BY DB_NAME(database_id);

Viewing sample rows in all used tables

During database migration projects there is sometimes the need to search through databases looking for clues to assist with the migration i.e. connection strings, configuration settings etc.

This can be a daunting task, especially when a database holds hundreds or even thousands of tables.

The following query may help if you find yourself in this situation.

1. Run the following query against the database. It should identify non-empty tables and generate the relevant TSQL select queries.

SELECT 'SELECT TOP 5 ''[' + OBJECT_SCHEMA_NAME(a.object_id) + '].[' + a.name + ']'' AS Table_Name, * FROM [' + DB_NAME() + '].[' + OBJECT_SCHEMA_NAME(a.object_id) + '].[' + a.name + '] WITH (nolock);'  
FROM sys.objects a, sysindexes b
WHERE 1=1
AND a.object_id = b.id -- match objects
AND b.indid IN (0,1) -- 0 is heap 1 is clustered
AND a.type_desc = 'USER_TABLE' -- only user tables
AND b.dpages <> 0 -- data in the table
ORDER BY a.name;

2. Run the generated TSQL select queries and progress with your investigations.

SQL Collation Names Guide

Pre SQL Server 2008, matching a SQL collation to an existing build could be a bit of a challenge. Typically you would have to research which long SQL collation name would give you the short SQL collation name:

As shown below, in SQL Server 2008 onwards you now get presented the short name with the associated long name to make the process simpler:

The following table made up from here and here, may help you pick the correct one; or you could try one of these queries:

SELECT * 
FROM master.sys.fn_helpcollations()
WHERE name LIKE 'SQL%'

SELECT * 
FROM ::fn_helpcollations()
WHERE name LIKE 'SQL%'

ID	Short Name	Long Name
30	SQL_Latin1_General_Cp437_BIN	Binary order, for use with the 437 (U.S. English) character set.
31	SQL_Latin1_General_Cp437_CS_AS	Dictionary order, case-sensitive, for use with the 437 (U.S. English) character set.
32	SQL_Latin1_General_Cp437_CI_AS	Dictionary order, case-insensitive, for use with the 437 (U.S. English) character set.
33	SQL_Latin1_General_Pref_CP437_CI_AS	Dictionary order, case-insensitive, uppercase preference, for use with the 437 (U.S. English) character set.
34	SQL_Latin1_General_Cp437_CI_AI	Dictionary order, case-insensitive, accent-insensitive, for use with the 437 (U.S. English) character set.
40	SQL_Latin1_General_Cp850_BIN	Binary order, for use with the 850 (Multilingual) character set.
41	SQL_Latin1_General_Cp850_CS_AS	Dictionary order, case-sensitive, for use with the 850 (Multilingual) character set.
42	SQL_Latin1_General_Cp850_CI_AS	Dictionary order, case-insensitive, for use with the 850 (Multilingual) character set.
43	SQL_Latin1_General_Pref_CP850_CI_AS	Dictionary order, case-insensitive, uppercase preference, for use with the 850 (Multilingual) character set.
44	SQL_Latin1_General_Cp850_CI_AI	Dictionary order, case-insensitive, accent-insensitive, for use with the 850 (Multilingual) character set.
49	SQL_1Xcompat_CP850_CI_AS	Strict compatibility with version 1.x case-insensitive databases, for use with the 850 (Multilingual) character set.
51	SQL_Latin1_General_Cp1_CS_AS	Dictionary order, case-sensitive, for use with 1252 character set.
52	SQL_Latin1_General_Cp1_CI_AS	Dictionary order, case-insensitive, for use with 1252 character set.
53	SQL_Latin1_General_Pref_CP1_CI_AS	Dictionary order, case-insensitive, uppercase preference, for use with 1252 character set.
54	SQL_Latin1_General_Cp1_CI_AI	Dictionary order, case-insensitive, accent-insensitive, for use with 1252 character set.
55	SQL_AltDiction_Cp850_CS_AS	Alternate dictionary order, case-sensitive, for use with the 850 (Multilingual) character set.
56	SQL_AltDiction_Pref_CP850_CI_AS	Alternate dictionary order, case-insensitive, uppercase preference, for use with the 850 (Multilingual) character set.
57	SQL_AltDiction_Cp850_CI_AI	Alternate dictionary order, case-insensitive, accent-insensitive, for use with the 850 (Multilingual) character set.
58	SQL_Scandinavian_Pref_Cp850_CI_AS	Scandinavian dictionary order, case-insensitive, uppercase preference, for use with the 850 (Multilingual) character set.
59	SQL_Scandinavian_Cp850_CS_AS	Scandinavian dictionary order, case-sensitive, for use with the 850 (Multilingual) character set.
60	SQL_Scandinavian_Cp850_CI_AS	Scandinavian dictionary order, case-insensitive, for use with the 850 (Multilingual) character set.
61	SQL_AltDiction_Cp850_CI_AS	Alternate dictionary order, case-insensitive, for use with the 850 (Multilingual) character set.
81	SQL_Latin1_General_Cp1250_CS_AS	Dictionary order, case-sensitive, for use with the 1250 (Central European) character set.
82	SQL_Latin1_General_Cp1250_CI_AS	Dictionary order, case-insensitive, for use with the 1250 (Central European) character set.
83	SQL_Czech_Cp1250_CS_AS	Czech dictionary order, case-sensitive, for use with the 1250 (Central European) character set.
84	SQL_Czech_Cp1250_CI_AS	Czech dictionary order, case-insensitive, for use with the 1250 (Central European) character set.
85	SQL_Hungarian_Cp1250_CS_AS	Hungarian dictionary order, case-sensitive, for use with the 1250 (Central European) character set.
86	SQL_Hungarian_Cp1250_CI_AS	Hungarian dictionary order, case-insensitive, for use with the 1250 (Central European) character set.
87	SQL_Polish_Cp1250_CS_AS	Polish dictionary order, case-sensitive, for use with the 1250 (Central European) character set.
88	SQL_Polish_Cp1250_CI_AS	Polish dictionary order, case-insensitive, for use with the 1250 (Central European) character set.
89	SQL_Romanian_Cp1250_CS_AS	Romanian dictionary order, case-sensitive, for use with the 1250 (Central European) character set.
90	SQL_Romanian_Cp1250_CI_AS	Romanian dictionary order, case-insensitive, for use with the 1250 (Central European) character set.
91	SQL_Croatian_Cp1250_CS_AS	Croatian dictionary order, case-sensitive, for use with the 1250 (Central European) character set.
92	SQL_Croatian_Cp1250_CI_AS	Croatian dictionary order, case-insensitive, for use with the 1250 (Central European) character set.
93	SQL_Slovak_Cp1250_CS_AS	Slovak dictionary order, case-sensitive, for use with the 1250 (Central European) character set.
94	SQL_Slovak_Cp1250_CI_AS	Slovak dictionary order, case-insensitive, for use with the 1250 (Central European) character set.
95	SQL_Slovenian_Cp1250_CS_AS	Slovenian dictionary order, case-sensitive, for use with the 1250 (Central European) character set.
96	SQL_Slovenian_Cp1250_CI_AS	Slovenian dictionary order, case-insensitive, for use with the 1250 (Central European) character set.
105	SQL_Latin1_General_Cp1251_CS_AS	Dictionary order, case-sensitive, for use with the 1251 (Cyrillic) character set.
106	SQL_Latin1_General_Cp1251_CI_AS	Dictionary order, case-insensitive, for use with the 1251 (Cyrillic) character set.
107	SQL_Ukrainian_Cp1251_CS_AS	Ukrainian dictionary order, case-sensitive, for use with the 1251 (Cyrillic) character set.
108	SQL_Ukrainian_Cp1251_CI_AS	Ukrainian dictionary order, case-insensitive, for use with the 1251 (Cyrillic) character set.
113	SQL_Latin1_General_Cp1253_CS_AS	Dictionary order, case-sensitive, for use with the 1253 (Greek) character set.
114	SQL_Latin1_General_Cp1253_CI_AS	Dictionary order, case-insensitive, for use with the 1253 (Greek) character set.
120	SQL_MixDiction_Cp1253_CS_AS	Mixed dictionary order, for use with the 1253 (Greek) character set.
121	SQL_AltDiction_Cp1253_CS_AS	Dictionary order, case-sensitive, accent-sensitive, for use with the 1253 (Greek) character set.
124	SQL_Latin1_General_Cp1253_CI_AI	Dictionary order, case-insensitive, accent-insensitive, for use with the 1253 (Greek) character set.
129	SQL_Latin1_General_Cp1254_CS_AS	Dictionary order, case-sensitive, for use with the 1254 (Turkish) character set.
130	SQL_Latin1_General_Cp1254_CI_AS	Dictionary order, case-insensitive, for use with the 1254 (Turkish) character set.
137	SQL_Latin1_General_Cp1255_CS_AS	Dictionary order, case-sensitive, for use with the 1255 (Hebrew) character set.
138	SQL_Latin1_General_Cp1255_CI_AS	Dictionary order, case-insensitive, for use with the 1255 (Hebrew) character set.
145	SQL_Latin1_General_Cp1256_CS_AS	Dictionary order, case-sensitive, for use with the 1256 (Arabic) character set.
146	SQL_Latin1_General_Cp1256_CI_AS	Dictionary order, case-insensitive, for use with the 1256 (Arabic) character set.
153	SQL_Latin1_General_Cp1257_CS_AS	Dictionary order, case-sensitive, for use with the 1257 (Baltic) character set.
154	SQL_Latin1_General_Cp1257_CI_AS	Dictionary order, case-insensitive, for use with the 1257 (Baltic) character set.
155	SQL_Estonian_Cp1257_CS_AS	Estonian dictionary order, case-sensitive, for use with the 1257 (Baltic) character set.
156	SQL_Estonian_Cp1257_CI_AS	Estonian dictionary order, case-insensitive, for use with the 1257 (Baltic) character set.
157	SQL_Latvian_Cp1257_CS_AS	Latvian dictionary order, case-sensitive, for use with the 1257 (Baltic) character set.
158	SQL_Latvian_Cp1257_CI_AS	Latvian dictionary order, case-insensitive, for use with the 1257 (Baltic) character set.
159	SQL_Lithuanian_Cp1257_CS_AS	Lithuanian dictionary order, case-sensitive, for use with the 1257 (Baltic) character set.
160	SQL_Lithuanian_Cp1257_CI_AS	Lithuanian dictionary order, case-insensitive, for use with the 1257 (Baltic) character set.
183	SQL_Danish_Pref_Cp1_CI_AS	Danish/Norwegian dictionary order, case-insensitive, uppercase preference, for use with 1252 character set.
184	SQL_SwedishPhone_Pref_Cp1_CI_AS	Finnish-Swedish (Phone), case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 184 on Code Page 1252 for non-Unicode Data.
185	SQL_SwedishStd_Pref_Cp1_CI_AS	Finnish-Swedish (Standard), case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 185 on Code Page 1252 for non-Unicode Data.
186	SQL_Icelandic_Pref_Cp1_CI_AS	Icelandic dictionary order, case-insensitive, uppercase preference, for use with 1252 character set.
210	SQL_EBCDIC037_CP1_CS_AS	Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 210 on Code Page 1252 for non-Unicode Data
211	SQL_EBCDIC273_CP1_CS_AS	German-PhoneBook, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 211 on Code Page 1252 for non-Unicode Data
212	SQL_EBCDIC277_CP1_CS_AS	Danish-Norwegian, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 212 on Code Page 1252 for non-Unicode Data
213	SQL_EBCDIC278_CP1_CS_AS	Finnish-Swedish, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 213 on Code Page 1252 for non-Unicode Data
214	SQL_EBCDIC280_CP1_CS_AS	Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 214 on Code Page 1252 for non-Unicode Data
215	SQL_EBCDIC284_CP1_CS_AS	Modern-Spanish, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 215 on Code Page 1252 for non-Unicode Data
216	SQL_EBCDIC285_CP1_CS_AS	Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 216 on Code Page 1252 for non-Unicode Data
217	SQL_EBCDIC297_CP1_CS_AS	French, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 217 on Code Page 1252 for non-Unicode Data

Skip Instance IDs for SQL Server 2005 installs

If you ever have the requirement to use a specific ID for a SQL Server 2005 install e.g. MSSQL.5, what can you do?

Good old SQL Server 2005 didn’t give you the option to choice an Instance ID, unlike SQL Server 2008 onwards, so you can either:

1. Install multiple instances until you get to MSSQL.5; or
2. Simply add entries in the Instance Names section of the registry prior to performing your SQL install and remove them afterwards.

An example is shown below and the next SQL install will start at MSSQL.5

A sample registry reg file to perform the task would be

As always, use caution when manually updating the registry.

Quickly script and transfer logins

You may come across the need to migrate one or all logins to another instance.

Here are some scripts that may be able to assist you with that task without you needing to know the login passwords.

Remember to review and exclude the logins you don’t need to transfer.

1. Script all SQL Logins

-- SQL Server 2008 onwards
SELECT 
  'CREATE LOGIN [' + [name] + '] WITH PASSWORD='
+ CONVERT(varchar(MAX),password_hash,1)
+ ' HASHED, DEFAULT_DATABASE=[' + default_database_name 
+ '], DEFAULT_LANGUAGE=[' + default_language_name 
+ '], CHECK_EXPIRATION=' + CASE WHEN is_expiration_checked = 0 THEN 'OFF' ELSE 'ON' END
+ ', CHECK_POLICY=' + CASE WHEN is_policy_checked = 0 THEN 'OFF' ELSE 'ON' END
+ ', SID='
+ CONVERT(varchar(MAX),sid,1)
+ '; ' + CASE WHEN is_disabled = 1 THEN 'ALTER LOGIN [' + name + '] DISABLE;' ELSE '' END
FROM master.sys.sql_logins
WHERE 1=1
AND [name] NOT LIKE '##%'
AND [name] <> 'sa'
ORDER BY [name];

-- Pre SQL Server 2008
SELECT 
  'CREATE LOGIN [' + [name] + '] WITH PASSWORD='
+ master.dbo.fn_varbintohexstr(password_hash)
+ ' HASHED, DEFAULT_DATABASE=[' + default_database_name 
+ '], DEFAULT_LANGUAGE=[' + default_language_name 
+ '], CHECK_EXPIRATION=' + CASE WHEN is_expiration_checked = 0 THEN 'OFF' ELSE 'ON' END
+ ', CHECK_POLICY=' + CASE WHEN is_policy_checked = 0 THEN 'OFF' ELSE 'ON' END
+ ', SID='
+ master.dbo.fn_varbintohexstr(sid)
+ '; ' + CASE WHEN is_disabled = 1 THEN 'ALTER LOGIN [' + name + '] DISABLE;' ELSE '' END
FROM master.sys.sql_logins
WHERE 1=1
AND [name] NOT LIKE '##%'
AND [name] <> 'sa'
ORDER BY [name];

2. Script the sa password to match on the other instance, if required

-- SQL Server 2008 onwards
SELECT 
  'IF (SELECT is_policy_checked FROM master.sys.sql_logins WHERE [name]=''sa'') = 1'
+ ' BEGIN'
+ ' ALTER LOGIN [sa] WITH CHECK_POLICY=OFF, PASSWORD = '	
+ CONVERT(varchar(MAX),password_hash,1)
+ ' HASHED; ALTER LOGIN [sa] WITH CHECK_POLICY=ON;'
+ ' END'
+ ' ELSE'
+ ' BEGIN'
+ ' ALTER LOGIN [sa] WITH PASSWORD =' 
+  CONVERT(varchar(MAX),password_hash,1)
+ ' HASHED;'
+ ' END;'
FROM master.sys.sql_logins
WHERE 1=1
AND [name] = 'sa';

-- Pre SQL Server 2008
SELECT 
  'IF (SELECT is_policy_checked FROM master.sys.sql_logins WHERE [name]=''sa'') = 1'
+ ' BEGIN'
+ ' ALTER LOGIN [sa] WITH CHECK_POLICY=OFF, PASSWORD = '	
+ master.dbo.fn_varbintohexstr(password_hash)
+ ' HASHED; ALTER LOGIN [sa] WITH CHECK_POLICY=ON;'
+ ' END'
+ ' ELSE'
+ ' BEGIN'
+ ' ALTER LOGIN [sa] WITH PASSWORD =' 
+  master.dbo.fn_varbintohexstr(password_hash)
+ ' HASHED;'
+ ' END;'
FROM master.sys.sql_logins
WHERE 1=1
AND [name] = 'sa';

3. Script the Windows logins

SELECT
  'CREATE LOGIN [' + [name] + '] FROM WINDOWS WITH DEFAULT_DATABASE=[' 
+ COALESCE(default_database_name,'master')
+ '], DEFAULT_LANGUAGE=[' + COALESCE(default_language_name,'us_english')
+ ']; ' + CASE WHEN is_disabled = 1 THEN 'ALTER LOGIN [' + name + '] DISABLE;' ELSE '' END
FROM master.sys.server_principals
WHERE 1=1
AND type_desc IN ('WINDOWS_LOGIN','WINDOWS_GROUP')
ORDER BY [name];

4. Script the server role memberships of the logins

-- SQL Server 2012 onwards
SELECT
 'ALTER SERVER ROLE [' + a.[name] + '] ADD MEMBER [' + b.[name] + '];'
FROM master.sys.server_principals a, master.sys.server_principals b, master.sys.server_role_members c
WHERE 1=1
AND a.principal_id = c.role_principal_id
AND b.principal_id = c.member_principal_id
ORDER BY a.name, b.name;

-- Pre SQL Server 2012
SELECT
 'EXEC sp_addsrvrolemember [' + b.[name] + '],[' + a.[name] + '];'
FROM master.sys.server_principals a, master.sys.server_principals b, master.sys.server_role_members c
WHERE 1=1
AND a.principal_id = c.role_principal_id
AND b.principal_id = c.member_principal_id
ORDER BY a.[name], b.[name];

5. Script the server securables

SELECT a.state_desc + ' ' + a.[permission_name] + ' TO [' + b.name + ']' COLLATE Latin1_General_CI_AS AS server_securables
FROM sys.server_permissions a, sys.server_principals b
WHERE 1=1
AND a.grantee_principal_id = b.principal_id
AND a.[permission_name] NOT IN ('CONNECT SQL')
AND b.name NOT LIKE '##%'
AND b.name <> 'public'
ORDER BY b.name;

When a database is restored the users should automatically resolve to the logins you’ve transferred, unless they were orphaned to being with.

The following script shows which database users are orphaned.

DECLARE @orphaned_users TABLE (DatabaseName nvarchar(128), UserName nvarchar(128));

DECLARE item_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT [name] FROM sys.databases WHERE state_desc = 'ONLINE'
OPEN item_cursor
DECLARE @cursor nvarchar(128)
DECLARE @sql nvarchar(MAX);
FETCH NEXT FROM item_cursor INTO @cursor
WHILE @@FETCH_STATUS = 0 
BEGIN

IF (CAST(SERVERPROPERTY('ProductVersion') AS varchar(10))) NOT LIKE '9%'
AND (CAST(SERVERPROPERTY('ProductVersion') AS varchar(10))) NOT LIKE '10%'
BEGIN
SET @sql = 'USE [' + @cursor + '];
SELECT ''' + @cursor + ''', UserName = [name]
FROM sysusers  
WHERE 1=1
AND  issqluser = 1   
AND (sid IS NOT NULL AND sid <> 0x0)  
AND (LEN(sid) <= 16)  
AND SUSER_SNAME(sid) IS NULL
UNION
SELECT ''' + @cursor + ''', UserName = [name]
FROM sys.database_principals
WHERE 1=1
AND authentication_type_desc = ''WINDOWS''
AND  [name] NOT LIKE ''dbo''
EXCEPT
(SELECT ''' + @cursor + ''',DP.[name] FROM sys.database_principals DP
INNER JOIN sys.server_principals SP ON DP.[sid] = SP.[sid] 
WHERE 1=1
AND DP.authentication_type_desc = ''WINDOWS'')';
END
ELSE
BEGIN
SET @sql = 'USE [' + @cursor + '];
SELECT ''' + @cursor + ''', UserName = [name]
FROM sysusers  
WHERE 1=1
AND  issqluser = 1   
AND (sid IS NOT NULL AND sid <> 0x0)  
AND (LEN(sid) <= 16)  
AND SUSER_SNAME(sid) IS NULL
UNION
SELECT ''' + @cursor + ''', UserName = [name]
FROM sys.database_principals
WHERE 1=1
AND type_desc IN (''WINDOWS_USER'',''WINDOWS_GROUP'')
AND  [name] NOT LIKE ''dbo''
EXCEPT
(SELECT ''' + @cursor + ''',DP.[name] FROM sys.database_principals DP
INNER JOIN sys.server_principals SP ON DP.[sid] = SP.[sid] 
WHERE 1=1
AND DP.type_desc IN (''WINDOWS_USER'',''WINDOWS_GROUP''))';

INSERT INTO @orphaned_users (DatabaseName, UserName)
EXEC(@sql);
END

FETCH NEXT FROM item_cursor INTO  @cursor
END
CLOSE item_cursor;  
DEALLOCATE item_cursor; 

SELECT DatabaseName AS database_name, UserName AS orphaned_user
FROM @orphaned_users ORDER BY DatabaseName,UserName;

Maximum server memory change taking a long time, don’t panic

So, you’ve changed the Maximum server memory option and the server property box appears to have hung, what do you do?

The short answer: wait.

You can view the current errorlog e.g. xp_readerrorlog, and once a cachestore flush of ‘Object Plans‘, ‘SQL Plans‘ and ‘Bound Trees‘ has completed, the property box will finally close.

Click to enlarge.

As per the above screenshot, this took well over 30 minutes for this VM which was placed on an over provisioned host.

Quickly find missing indexes in cache

Yes there are caveats with adding indexes ad hoc and there are many posts regarding extracting a relevant query, testing with SET STATISTICS IO … etc to validate the proposed index.

One query to peek into the cache is:

SELECT
 cp.objtype
,cp.usecounts
,cp.refcounts
,cp.size_in_bytes
,qt.text AS query_text
,qp.query_plan AS query_plan
FROM master.sys.dm_exec_cached_plans AS cp (NOLOCK)
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) AS qt
WHERE CAST(query_plan AS nvarchar(MAX)) LIKE N'%MissingIndex%'
ORDER BY cp.usecounts DESC, cp.refcounts DESC
OPTION (RECOMPILE, MAXDOP 1);

When clicking on the query_plan xml you should get something similar to the below.

If you don’t; try place the xml in a text file and give the text file the extension .sqlplan e.g. missing_index.sqlplan

Right click on the reported missing index and select Missing Index Details…

You now need to give the index a name that follows your index naming standards

and run the query

As always, you should validate the effectiveness of the new index i.e. before and after using SET STATISTICS IO ON etc, before deciding to keep it.