AlwaysOn Availability Groups Designs

Here are some thoughts on how AlwaysOn Availability Groups on either a single or multi-subnet should be designed if 2 hosts are used.

 

image_1
 

image_2

 

Only the Host IP address is manually set in Network Connections on the Host and it typically has NIC teaming for redundancy.
 

The AG Listener and WSFC IP addresses can be thought of as virtual and are automatically created in DNS as part of their setup.

SQL Server 2016 Retirement of x86 (32 bit) Instance Features

The current SQL Server 2016 Release Notes indicate that the SQL Server x86 (32 bit) features have come to an end.

 

This step was inevitable and was just a matter of when, rather than if, it would occur.

 

Retirement of x86 Instance Features

 

The x86 (32 bit) version of SQL Server 2016 Setup no longer includes instance features. The following Instance Features are now available only in the x64 (64 bit) version of SQL Server Setup:

 

•Database Engine

•Analysis Services

•Reporting Services – Native Mode

 

The x86 Setup is still available for installing tools and other shared Features.

 

Instance features installed from previous CTP releases of SQL Server 2016 will no longer be updated. It is recommended that previous CTP x86 installations are uninstalled and Instance Features are reinstalled using the x64 version of SQL Server 2016 Setup. Previous x86 installations with only Shared Features will continue to upgrade without issue.

WMI Performance Adapter service

You may see the following error events regularly in your Windows Application Event Log:

 

The Open Procedure for service “WmiApRpl” in DLL “C:\Windows\system32\wbem\wmiaprpl.dll” failed. Performance data for this service will not be available. The first four bytes (DWORD) of the Data section contains the error code.

image01

 

The Open Procedure for service “BITS” in DLL “C:\Windows\System32\bitsperf.dll” failed. Performance data for this service will not be available. The first four bytes (DWORD) of the Data section contains the error code.

image02

 

One possible solution to resolve them is to check the WMI Performance Adapter service. If this service disabled, change it to have a start type of Manual and see if that resolves the issue. It is noted that even with this service enabled, you may still see these errors shortly after a restart of the server.

image03

SQL Server and Transparent Data Encryption (TDE)

The following walks through:

 

 

Note: This feature is currently only available with SQL Server Enterprise Edition, so you may never get to use it.

 
If this is your first TDE setup on a SQL Server it is best that you set aside 4 strong passwords for the various objects we will interact with through this example e.g.

 

  • Service Master Key backup file: br_y2s6tR?3taket?3@uvUk6
  • Database Master Key: X2t$Ay&7rapHA!?jE3uW3taJ
  • Database Master Key backup file: f3av#vamEVemesaMuWr7sWuP
  • Certificate Private Key backup file: te5pepUr*n$PhexE2!a2r@#r

 
Note: The only password above you will always require to restore a TDE database on another instance is the Certificate Private Key backup file password. I’m covering the others as you never know when you may need them.

 
If you already have TDE databases on your instance you will likely not need to create any further passwords.

 

Setting up Transparent Data Encryption (TDE) for a database

 

Firstly it is good to know that TDE typically relies on:

 

  • a Database Encryption Key (one key is created in each TDE database) and is associated with;
  • a Certificate (only one is created for the whole instance and exists in the master database), this Certificate is associated with;
  • a Database Master Key (only one is created for the whole instance and exists in the master database) and this Database Master Key relies on;
  • a Service Master Key (also only one for the whole instance and it exists in the master database).

 
Knowing the above and as a best practice, let’s make sure we backup the Service Master Key which I suspect you may never have done before.
Every SQL Server instance has a Service Master Key as shown below:

SELECT * FROM master.sys.symmetric_keys;

image1

To backup the Service Master Key you would use:

BACKUP SERVICE MASTER KEY TO FILE = 'C:\SQLBackups\TDE\SQL01D_service_master_key'
ENCRYPTION BY PASSWORD = 'br_y2s6tR?3taket?3@uvUk6';

image2
Next we will check if a Database Master Key exists in the master database using the following code and as shown below it does not exist:

SELECT * FROM master.sys.symmetric_keys;

image3
If you have an entry called ##MS_DatabaseMasterKey## you can skip this step.
 

As in this case it does not exist we will create it using the following and we can now see the Database Master Key in the master database:

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'X2t$Ay&7rapHA!?jE3uW3taJ';
SELECT * FROM master.sys.symmetric_keys;

image4

 

Now we know it exists and as best practice let’s make a backup of the key:

USE master;
BACKUP MASTER KEY TO FILE = 'C:\SQLBackups\TDE\SQL01D_master_key'
ENCRYPTION BY PASSWORD = 'f3av#vamEVemesaMuWr7sWuP';

image5

 

Now to create the TDE Certificate in the master database which will be associated with the Database Master Key:

USE master;
CREATE CERTIFICATE SQL01D_Certificate WITH SUBJECT = 'SQL01D Certificate', EXPIRY_DATE = '20200131';
SELECT * FROM master.sys.certificates;

image6

 

As this Certificate is typically the only object, besides the TDE database, you will need to recover on another instance, you will need to definitely create a backup of it and store it safely for future use.

 

As shown below this will create 2 files and you will require them both to restore the Certificate:

USE master;
BACKUP CERTIFICATE SQL01D_Certificate TO FILE = 'C:\SQLBackups\TDE\SQL01D_certificate.cer'
WITH PRIVATE KEY ( FILE = 'C:\SQLBackups\TDE\SQL01D_certificate.pvk'
,ENCRYPTION BY PASSWORD = 'te5pepUr*n$PhexE2!a2r@#r');

image7

 

Now to create a Database Encryption Key for our database which will use TDE:

USE AdventureWorks2014;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE SQL01D_Certificate;

SELECT DB_NAME(database_id) AS database_name, encryption_state 
FROM sys.dm_database_encryption_keys; -- encryption_state should say 1 (Unencrypted)

image8

 

Finally we enable TDE encryption in the database. As shown below, the tempdb database will also be encrypted automatically:

USE AdventureWorks2014;
ALTER DATABASE AdventureWorks2014 SET ENCRYPTION ON;

-- encryption_state 1 = Unencrypted, 2 = Encryption in progress, 3 = Encrypted
SELECT DB_NAME(database_id) AS database_name, encryption_state, percent_complete 
FROM sys.dm_database_encryption_keys;

image9

 

For large databases in may take some time for the database to become fully encrypted. The progress can be checked with:

-- encryption_state 1 = Unencrypted, 2 = Encryption in progress, 3 = Encrypted
SELECT DB_NAME(database_id) AS database_name, encryption_state, percent_complete 
FROM sys.dm_database_encryption_keys;

 


Restoring a TDE database

 

Now you have a TDE enabled database and the time arrives to restore the database onto another instance.

 

You are going to need 4 things to restore your database:

  • The database backup file(s) to restore the database
  • The Certificate cer file
  • The Certificate pvk file
  • The password used to encrypt the Certificate Private Key backup file

 

Firstly check that the Database Master Key exists in the master database.

 

As below, only a Service Master Key exists so we will need to create a Database Master Key:

SELECT * FROM master.sys.symmetric_keys;

image10

 

If you have a Database Master Key you can skip this step. The password should be strong and backup the Database Master Key once created as previously demonstrated. The password does not have to be same as the original server.

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'BanetrAje&$ATReMuspA*U2e';
SELECT * FROM master.sys.symmetric_keys;

image11

 

Copy the Certificate cer and pvk files to the location which holds your database mdf files. You could place these files anywhere your SQL Server can access them, but this location should typically always work.

image12

 

Now restore the Certificate:

USE master;
CREATE CERTIFICATE SQL01D_Certificate
FROM FILE ='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\SQL01D_certificate.cer'
WITH PRIVATE KEY(FILE='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\SQL01D_certificate.pvk'
,DECRYPTION BY PASSWORD='te5pepUr*n$PhexE2!a2r@#r');

SELECT * FROM master.sys.certificates;

image13

 

Finally the TDE database can be safely restored and TDE will still be in use:

-- encryption_state 1 = Unencrypted, 2 = Encryption in progress, 3 = Encrypted
SELECT DB_NAME(database_id) AS database_name, encryption_state, percent_complete 
FROM sys.dm_database_encryption_keys;

image14


Removing TDE from a database

For whatever reason, you now need to remove TDE from a database.

Firstly, disable encryption on the database:

USE AdventureWorks2014;
ALTER DATABASE AdventureWorks2014 SET ENCRYPTION OFF;

-- encryption_state 1 = Unencrypted, 3 = Encrypted, 5 = Decryption in progress
SELECT DB_NAME(database_id) AS database_name, encryption_state, percent_complete 
FROM sys.dm_database_encryption_keys;

image15

 

This may take some time and you can continue once the database has an encryption state of 1 (Unencrypted).

USE AdventureWorks2014;

-- encryption_state 1 = Unencrypted, 3 = Encrypted, 5 = Decryption in progress
SELECT DB_NAME(database_id) AS database_name, encryption_state, percent_complete 
FROM sys.dm_database_encryption_keys;

image16

 

Once the database is fully decrypted you lastly need to remove the Database Encryption Key using:

USE AdventureWorks2014;
DROP DATABASE ENCRYPTION KEY;

-- encryption_state 1 = Unencrypted, 3 = Encrypted, 5 = Decryption in progress
SELECT DB_NAME(database_id) AS database_name, encryption_state, percent_complete 
FROM sys.dm_database_encryption_keys;

image17

 

Once TDE has been removed from the last database, you will notice that tempdb is still encrypted.The tempdb database will only be decrypted when the SQL Server instance is restarted.


Removing TDE created objects

 

To tidy up a SQL Server instance once TDE is no longer be used, the following steps are used.

 

Note: Ensure you have a secure copy of any created TDE Certificate(s) in case you need to restore a previously TDE encrypted backup. Backups were covered previous.

 

First identify the Certificate to remove using:

SELECT * FROM master.sys.certificates;

image18

 

Remove the Certificate using:

USE master;
DROP CERTIFICATE SQL01D_Certificate;
SELECT * FROM master.sys.certificates;

image19

 

Finally identify and drop the Database Master Key if you know it is no longer used.

Below shows the Database Master Key exists in the master database:

SELECT * FROM master.sys.symmetric_keys;

image20

 

 
The following removes the Database Master Key and leaves the Server Master Key which remains on the instance:

USE master;
DROP MASTER KEY;
SELECT * FROM master.sys.symmetric_keys;

image21

SQL Server and Kerberos

As a DBA you’ll be wanting to promote the use of Kerberos authentication rather than NTLM authentication.

 

DBAs don’t have to be concerned with the whole Kerberos chain, rather you’ll primarily need to ensure your instances are able to service Kerberos authentication requests.

 
Let’s get started.

 
Using Sql Server Configuration Manager, look at the Protocols being used by your instance(s).

 
As shown below, this server has 2 instances, a default and a named instance called NI1.

 
Both instances are configured to listen on Shared Memory, Named Pipes and TCP/IP.

image01

image02

Verify which TCP/IP port the instance is listening on.

 
In this example the default instance is listening on TCP port 1433.

image03

The named instance NI1 is listening on TCP port 2433.

 
NOTE: For named instances you’ll want to keep away from using TCP Dynamic Ports as a restart of the SQL Server service may impact your Kerberos authentication.

image04

Next, check which service account the instances are using for the SQL Server service.

 
Below we can see the default instance is using MYDOMAIN\zSQL01D_SS and the named instance NI1 is using MYDOMAIN\zSQL01D_SS1.

image05

Kerberos relies on a Service Principal Name (SPN) to identify the SQL Server instance.

 
You would use the following syntax to verify if these services have a SPN associated.

setspn /L MYDOMAIN\zSQL01D_SS
setspn /L MYDOMAIN\zSQL01D_SS1

NOTE: I’m using a / (forward slash) instead of – (dash) as you may find you run into issues if you copy/paste a setspn command using a dash.

 
As shown, currently the accounts don’t have any SPNs associated so Kerberos authentication cannot be used.

image06

To verify this, I’ve RDPed to another server SQL02D and then connected to the default instance on SQL01D using the TCP/IP protocol and run the following command:

SELECT net_transport,auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@spid

TIP: Using tcp: in front of the instance name forces TCP/IP and hence the connection below shows the use of tcp:SQL01D

image07

image08

TIP: Using np: in front of the instance name forces use of Named Pipes and hence the connection below shows the use of np:SQL01D

image09

image10

So, we know Kerberos is not available and we are using NTLM.

 
To add a SPN to the service accounts, you’ll need a Domain Administrator to run the following commands.

 
NOTE: The Domain Administrator, does not need to be on the SQL server as the command simply registers the SPN in AD against the service account.

 
We’ll start with setting the most common TCP/IP protocol SPN.

setspn /S MSSQLSvc/SQL01D.MyDomain.local:1433 MYDOMAIN\zSQL01D_SS
setspn /S MSSQLSvc/SQL01D.MyDomain.local:2433 MYDOMAIN\zSQL01D_SS1

image11

Once the Domain Administrator has run the above commands you can verify that you now have a SPN associated with the service accounts.

setspn /L MYDOMAIN\zSQL01D_SS
setspn /L MYDOMAIN\zSQL01D_SS1

 

NOTE: You don’t have to restart the SQL Server service for the SPN to come into effect.

image012
Now if we retest a TCP connection you should see KERBEROS authentication being used.

SELECT @@SERVERNAME AS instance,net_transport,auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@spid

image13

The Named Pipes will still show NTLM.

image14

Now to enabled Kerberos for Named Pipes the Domain Administrator will need to run the following:

setspn /S MSSQLSvc/SQL01D.MyDomain.local MYDOMAIN\zSQL01D_SS
setspn /S MSSQLSvc/SQL01D.MyDomain.local:NI1 MYDOMAIN\zSQL01D_SS1

image15

Once the Domain Administrator has run the above commands you can verify the SPN exist.

setspn /L MYDOMAIN\zSQL01D_SS
setspn /L MYDOMAIN\zSQL01D_SS1

NOTE: Again no restart of the SQL Server service is required for the instance to take advantage of the SPN.

image16
Now when you retry your Named Pipes test you should see Kerberos in use.

image17

So as above, enabling Kerberos authentication is a fairly straight forward process.

 
Finally, if you find that a SPN needs to be removed for some reason i.e. a port change, the setspn command S is simply replaced with a D and again this has to be run by a Domain Administrator e.g.

setspn /D MSSQLSvc/SQL01D.MyDomain.local:2433 MYDOMAIN\zSQL01D_SS1

image18

Using PowerShell to verify if a remote TCP port is accessible

A handy tip for any DBA out there who would like to check if a remote TCP port can be connected to but they don’t have access to telnet.

 

The catch is that you require a minimum of Windows 8.1, Windows PowerShell 4.0 or Windows Server 2012 R2. Also, notice I didn’t say check a remote UDP port.

 

You can check your PowerShell version using:

$PSVersionTable.PSVersion

image01

 

Simply open a PowerShell prompt and using Test-NetConnection specify the destination server and TCP port e.g.

Test-NetConnection -ComputerName SQLServer01 -Port 1433

image02

 

If that is too long to type, you can use the alias:

TNC -ComputerName SQLServer01 -Port 1433

image03

 

If you don’t want all that information use:

TNC -ComputerName SQLServer01 -Port 1433 -InformationLevel Quiet

image04

 

You may receive a Ping TimedOut warning but the TcpTestSucceeded is True, which seems confusing. image05

 

This simply means that the ICMP Ping is being blocked. Check with your System Administrator if they would like Ping capability enabled. image06

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

image1

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:

  1. 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.
  2. The IP address needs to be changed on the Secondary server so that the applications can connect.

image3_2

Log Shipping failback steps example:

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

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

 

Server Name

image2

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:

  1. 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.
  2. The server name needs to be changed on the Secondary server so that the applications can connect.
  3. 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.

image4

Log Shipping failback steps example:

  1. Change the server name on the original Primary server, as you can’t have duplicate server names.
  2. Re-establish Log Shipping and failback the databases to the original Primary server.
  3. Remove Log Shipping.
  4. Change the server name of the original Secondary server and shut it down, as you can’t have duplicate server names.
  5. Change the server name of the original Primary server back to what it was, so the applications can connect.
  6. Update the SQL Server internal name on both servers, if required.
  7. 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

image5

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:

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

image6

Log Shipping failback steps example:

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

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

 

Application Name CNAME

image7

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:

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

image8

Log Shipping failback steps example:

  1. Re-establish Log Shipping and failback the databases to the original Primary server.
  2. Update the DNS Alias (CNAME) Record to point to the original Primary server so that the applications can connect.
  3. 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

image9

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:

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

image10

Log Shipping failback steps example:

  1. Re-establish Log Shipping and failback the databases to the original Primary server.
  2. Update the DNS Alias (CNAME) Record 1 to point to the original Primary server so that the applications can connect.
  3. 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.

image11

 

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.

image12

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

 
image01
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.
 
image02

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,'''','')

image1

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,'''','')

image2

Database mirroring failover and recovery scripts

Once you start working on instances with tens or hundreds of mirrored databases, you’ll need scripts to manage the databases.

Below are common scripts which can help:

Check which databases have mirroring configured
Check which databases do not have mirroring configured
Check which mirrored databases are in synchronous mode
Check which mirrored databases are not in synchronous mode
Check which mirrored databases are in asynchronous mode
Check which mirrored databases are not in asynchronous mode
Check which mirrored databases are synchronized
Check which mirrored databases are not synchronized
Check which mirrored databases have mirroring paused
Check which mirrored databases do not have mirroring paused
Check what the mirroring ping timeout value is for the mirrored databases
Change mirrored databases to asynchronous mode
Change mirrored databases to synchronous mode
Change the mirroring ping timeout value for the mirrored databases
Pause mirroring for mirrored databases
Resume mirroring for mirrored databases
Manually failover mirrored databases
Force recovery of mirrored databases in the event the principal instance is not available
Remove database mirroring from mirrored databases

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);