SQL Server 2014 | DataGinger.com

Posts Tagged ‘SQL Server 2014’

SQL Server – Configuring custom Load Balancing in SQL Server 2012 and 2014 using Read-Only Routing

Posted in AlwaysOn, Uncategorized, tagged SQL Server 2012, SQL Server 2014 on March 20, 2016| 4 Comments »

Starting with SQL Server 2012, AlwaysOn Availability Groups provided group level high availability for any number of databases for multiple secondaries known as ‘replicas’. The secondary replicas allow direct read-only querying or can enforce connections that specify ‘ReadOnly’ as their Application Intent using the new feature called Read-Only routing which can be leveraged to scale out reporting workloads. However, in SQL Server 2012 and 2014 versions this redirection is only concerned with the first secondary replica defined in the priority list and all the read-only connections are routed only to that one replica by design. This restricts the other secondary replicas from participating in the load distribution process and thereby reducing the load balancing capability. This article provides you with the configuration and testing of read-only routing along with configuring a custom SQL Agent job in an attempt to create an improved load balancing effect.

Read-only routing refers to the ability of SQL Server to route incoming read-intent connection requests, which are directed to an availability group listener, to an available readable secondary replica. One of the pre-requisites to support read-only routing is that the availability replicas must be enabled for read access.

Tip: Use the below script to check if Read-Only Routing is already configured in your server

SELECT ag.name as "Availability Group", ar.replica_server_name as "When Primary Replica Is",
rl.routing_priority as "Routing Priority", ar2.replica_server_name as "RO Routed To",
ar.secondary_role_allow_connections_desc, ar2.read_only_routing_url
FROM sys.availability_read_only_routing_lists rl
             INNER JOIN sys.availability_replicas ar on rl.replica_id = ar.replica_id
             INNER JOIN sys.availability_replicas ar2 on rl.read_only_replica_id = ar2.replica_id
             INNER JOIN sys.availability_groups ag on ar.group_id = ag.group_id 
ORDER BY ag.name, ar.replica_server_name, rl.routing_priority

To make it easy to understand, in this demo, we will use the below terminology:

Availability group named AG
Listener named AGLISTEN
Replicas SQL01A (primary) and SQL01B(secondary)

NOTE: Read-only routing can support ALLOW_CONNECTIONS property set to READ_ONLY or ALL (Graphically shown below)

Once the secondaries are set to readable (Read-Intent only/Yes), the below three steps are required to configure Read-Only Routing –

Define a read-only routing URL
Define a read-only routing List
Update the client’s connection string to specify Application Intent connection property as ‘read-only’

Let’s take a look at the above steps in details.

1. Configure Read-Only routing URL

A read_only_routing_url is the entry point of an application to connect to a readable secondary. It contains the system address or the port number that identifies the replica when acting as a readable secondary. This is similar to the endpoint URL we specify when configuring database mirroring. For each readable secondary replica that is to support read-only routing, you need to specify this routing URL

For example, define a URL SQL01B, so that when SQL01B is in the secondary role, it can accept read-only connections.

ALTER AVAILABILITY GROUP AG MODIFY REPLICA ON N'SQL01A' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQL01A:1433'))
ALTER AVAILABILITY GROUP AG MODIFY REPLICA ON N'SQL01B' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQL01B:1433'))

Tip: Use THIS code to generate routing URLs for each available secondary replicas to use in the above script

2. Configure Read-Only routing List

For each replica that will act as primary, we need to define the corresponding secondary replicas that will act as the routing target for read-only workloads. This means that if the replica is acting as a primary, all read-only workloads will be redirected to the replicas in the read-only routing list. For example, when SQL01A is in the primary role, define our routing list to consist of SQL01B which is where read-only connection requests will be routed first and if it is not available or not synchronizing (Only in SQL Server 2012) connections will go to the next server in the list.

ALTER AVAILABILITY GROUP AG MODIFY REPLICA ON N'SQL01A' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST= ('SQL01B', ‘SQL01A’))); 
GO 
ALTER AVAILABILITY GROUP AG MODIFY REPLICA ON N'SQL01B' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST= ('SQL01A', ‘SQL01B’)));

Tip: Alternatively to automate the above process you can use THIS script to dynamically generate the scripts required mentioned in the above tasks

Unfortunately there is no graphical user interface to perform these tasks in SSMS. The read-only routing URL and the routing list can be performed only through Transact-SQL or PowerShell

NOTE: As a best practice it is always recommended to assign the primary replica name at the end of the routing list separated by comma, in the rare event if all of the available secondary replicas are not available.

3. Update client connection string

Read-only clients must direct their connection requests to this listener, and the client’s connection strings must specify the application intent as “read-only.” That is, they must be read-intent connection requests. This can be seen in the connection string, an example is shown below:

Server=tcp:aglisten,1433;Database=agdb1;IntegratedSecurity=SSPI;
ApplicationIntent=ReadOnly;MultiSubnetFailover=True

Before making client side changes you can confirm this newly configured read-only routing using SQL CMD by specifying application intent option (-K) as shown below

Sqlcmd –S AGLISTEN –E –d AGDB1 –K readonly

Load Balancing using Read-Only Routing List

The read-only routing introduced in SQL Server 2012 is used for redirection and offloading the read queries to the secondary replicas instead of primary replica. However, this redirection is only concerned with the first secondary replica defined in the priority list that we define. Since the primary replica strictly traverses the list and looks for the first replica that can serve the connection request. Once found, all subsequent read-only connections are routed to it. For example, in a multiple secondary architecture, all the read intent queries only hit the first secondary replica in the list while other secondaries do not participate in distributing this load. This limits the load balancing capability among other secondary replicas.

To overcome this situation here a workaround that will modify the Read-Only Routing list periodically to let read intent queries to use all the replicas at certain intervals (every 30 seconds in this case), so as to create a load balancing effect. This is only applicable to SQL Servers running 2012 and 2014 versions since starting with SQL Server 2016, Microsoft changed the game by introducing native load-balancing capabilities which we will look into later in the paper

WHILE 1=1
Begin
If (
SELECT ARS.role_desc
FROM SYS.availability_REPLICAs AR
join sys.dm_hadr_availability_REPLICA_states ARS ON AR.REPLICA_id = ARS.REPLICA_id
WHERE AR.REPLICA_server_name = (select @@SERVERNAME)
) = 'PRIMARY' and (select count(*) from sys.availability_read_ONly_routing_lists) &gt; 1
Begin
ALTER AVAILABILITY GROUP [AGDB1]
MODIFY REPLICA ON N'SQL01A' WITH (Primary_Role (READ_ONLY_ROUTING_LIST =('SQL01B','SQL01A')))
ALTER AVAILABILITY GROUP [AGDB1]
MODIFY REPLICA ON N'SQL01B' WITH (Primary_Role (READ_ONLY_ROUTING_LIST =('SQL01A','SQL01B')))
--print 'changing ROR URL in 30 seconds...'
WAITFOR DELAY '00:00:30'
--print 'Changing ROR URL'
--Run every 30 secONds
ALTER AVAILABILITY GROUP [AGDB1]
MODIFY REPLICA ON N'SQL01A' WITH (Primary_Role (READ_ONLY_ROUTING_LIST =('SQL01A','SQL01B')))
ALTER AVAILABILITY GROUP [AGDB1]
MODIFY REPLICA ON N'SQL01B' WITH (Primary_Role (READ_ONLY_ROUTING_LIST =('SQL01B','SQL01A')))

END
WAITFOR DELAY '00:00:30'
End

Note: You can add additional replica details based on the number of secondary replicas configured in your Read-Only Routing

To complete the procedure run the code from above in a new query window in SSMS for testing purposes and once verified you can then use the code to create a SQL agent job in all the replicas. This job needs to be run continuously on each replica. The code will only run from the instance that is in the PRIMARY role.

The verify the Read-Only Routing is rotating correctly run the below script

SELECT ag.name as "Availability Group", ar.replica_server_name as "When Primary Replica Is",
rl.routing_priority as "Routing Priority", ar2.replica_server_name as "RO Routed To"
FROM sys.availability_read_only_routing_lists rl
    INNER JOIN sys.availability_replicas ar on rl.replica_id = ar.replica_id
    INNER JOIN sys.availability_replicas ar2 on rl.read_only_replica_id = ar2.replica_id
    INNER JOIN sys.availability_groups ag on ar.group_id = ag.group_id
ORDER BY ag.name, ar.replica_server_name, rl.routing_priority

After 30 seconds, notice that the “RO Routed To” column alternates among the available secondary replicas.

As evident from above result, this code modifies Read-Only routing list bringing in a new secondary replica into play periodically to cater for the read intent connections essentially creating a load balancing effect. Load balancing using this technique provides a way to get even more use from server hardware that host secondary’s databases and provide reporting applications with better performance and throughput especially for long and resource intensive queries. Please note that this algorithm is limited but it serves the purpose quite effectively. A similar but much robust algorithm has been built into the native SQL engine starting with SQL Server 2016.

Read Full Post »

SQL Server – Dynamically Generate Scripts for Configuring Read-Only Routing URLs and Lists

Posted in AlwaysOn, T-SQL, tagged SQL Server 2012, SQL Server 2014 on February 2, 2016| 1 Comment »

Read-only routing refers to the ability of SQL Server to route incoming read-intent connection requests, which are directed to an availability group listener, to an available readable secondary replica. I find this little script very useful when I want to automate and and same time while limiting the human input errors during Read-Only Routing configuration.

Make sure to update the parameters’ value as per your AG and server details

USE [master]
GO
 
SET NOCOUNT ON
GO
 
DECLARE   @AGName               VARCHAR(40) = 'Your AG Name'      
        , @PrimaryNodeName      VARCHAR(40) = 'Your Primary Replica'     
        , @SecondaryNodeName    VARCHAR(40) = 'Your Secondary Replica'     
        , @PrimaryNodeIP        VARCHAR(40) = '10.5.6.10'  -- Not required unless marked 1 for @RouteUsingIP below
        , @SecondaryNodeIP      VARCHAR(40) = '10.6.6.11'  -- Not required unless marker 1 for @RouteUsingIP below
        , @Domain		VARCHAR(40) = '.Domain.com'
        , @RouteUsingIP         TINYINT     = 0            -- 1 for True, 0 for False (1 is recommended)
        , @PrimaryRoutingURL    VARCHAR(40) = ''
        , @SecondaryRoutingURL  VARCHAR(40) = ''
        , @SQLCommand           VARCHAR(2000)
 
 
IF @RouteUsingIP > 0
BEGIN
    SET @PrimaryRoutingURL = @PrimaryNodeIP
    SET @SecondaryRoutingURL = @SecondaryNodeIP
END
ELSE
BEGIN
    SET @PrimaryRoutingURL = @PrimaryNodeName + @Domain
    SET @SecondaryRoutingURL = @SecondaryNodeName + @Domain
END
 
SET @SQLCommand = N'
ALTER AVAILABILITY GROUP ['+@AGName+'] MODIFY REPLICA ON
N'''+@PrimaryNodeName+''' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL));'
PRINT @SQLCommand
 
SET @SQLCommand = N'
ALTER AVAILABILITY GROUP ['+@AGName+'] MODIFY REPLICA ON
N'''+@PrimaryNodeName+''' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N''TCP://'+@PrimaryRoutingURL+':1433''));'
PRINT @SQLCommand
 
SET @SQLCommand = N'
ALTER AVAILABILITY GROUP ['+@AGName+'] MODIFY REPLICA ON
N'''+@SecondaryNodeName+''' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL));'
PRINT @SQLCommand
 
SET @SQLCommand = N'
ALTER AVAILABILITY GROUP ['+@AGName+'] MODIFY REPLICA ON
N'''+@SecondaryNodeName+''' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N''TCP://'+@SecondaryRoutingURL+':1433''));'
PRINT @SQLCommand
 
SET @SQLCommand = N'
ALTER AVAILABILITY GROUP ['+@AGName+'] MODIFY REPLICA ON
N'''+@PrimaryNodeName+''' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('''+@SecondaryNodeName+''','''+@PrimaryNodeName+''')));'
PRINT @SQLCommand
 
SET @SQLCommand = N'
ALTER AVAILABILITY GROUP ['+@AGName+'] MODIFY REPLICA ON
N'''+@SecondaryNodeName+''' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('''+@PrimaryNodeName+''','''+@SecondaryNodeName+''')));'
PRINT @SQLCommand

Read Full Post »

SQL Server – Script to generate Read-Only Routing URLs

Posted in AlwaysOn, T-SQL, Uncategorized, tagged SQL Server 2012, SQL Server 2014 on October 1, 2015| 1 Comment »

If you are familiar with the new feature Read-Only Routing introduced in SQL Server 2012 with AlwaysOn then you should keep this script handy to generate URLs to be used in Read-Only Routing configuration instead of typing down yourself to limit any errors.

Connect to each replica in your AlwaysOn Availability Group and run the below script to get the read_only_routing_url for that replica.
Then copy the URL from the result set and use it when setting up read_only_routing_url. Find more details on that here

PRINT 'Read-only-routing url script v.2012.1.24.1'
 
PRINT 'This SQL Server instance version is [' + cast(serverproperty('ProductVersion') as varchar(256)) + ']'
 
IF (ServerProperty('IsClustered') = 1) 
BEGIN
    PRINT 'This SQL Server instance is a clustered SQL Server instance.'
END
ELSE
BEGIN
    PRINT 'This SQL Server instance is a stANDard (not clustered) SQL Server instance.'   
END
 
IF (ServerProperty('IsHadrEnabled') = 1) 
BEGIN
    PRINT 'This SQL Server instance is enabled for AlwaysOn.'
END
ELSE
BEGIN
    PRINT 'This SQL Server instance is NOT enabled for AlwaysOn.'
END
 
-- Detect SQL Azure instance. 
DECLARE @is_sql_azure bit
SET @is_sql_azure = 0
 
BEGIN try 
    SET @is_sql_azure = 1 
    EXEC('DECLARE @i int SET @i = sql_connection_mode()') 
    PRINT 'This SQL Server instance is a Sql Azure instance.'
END try 
BEGIN catch 
    SET @is_sql_azure = 0 
    PRINT 'This SQL Server instance is NOT a Sql Azure instance.'
END catch
 
-- Check that this is SQL 11 or later, otherwise fail fast. 
IF (@@microsoftversion / 0x01000000 < 11 or @is_sql_azure > 0) 
BEGIN
    PRINT 'This SQL Server instance does not support read-only routing, exiting script.'
END
ELSE
BEGIN -- IF server supports read-only routing
 
    -- Fetch the dedicated admin connection (dac) port. 
    -- Normally it's always port 1434, but to be safe here we fetch it FROM the instance. 
    -- We use this later to exclude the admin port FROM read_only_routing_url. 
    DECLARE @dac_port int
    DECLARE @reg_value varchar(255) 
    EXEC xp_instance_regread 
        N'HKEY_LOCAL_MACHINE', 
        N'SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp', 
        N'TcpDynamicPorts', 
        @reg_value output
 
    SET @dac_port = cast(@reg_value as int)
 
    PRINT 'This SQL Server instance DAC (dedicated admin) port is ' + cast(@dac_port as varchar(255)) 
    IF (@dac_port = 0) 
    BEGIN 
        PRINT 'Note a DAC port of zero means the dedicated admin port is not enabled.' 
    END
 
    -- Fetch ListenOnAllIPs value. 
    -- IF SET to 1, this means the instance is listening to all IP addresses. 
    -- IF SET to 0, this means the instance is listening to specIFic IP addresses. 
    DECLARE @listen_all int 
    EXEC xp_instance_regread 
        N'HKEY_LOCAL_MACHINE', 
        N'SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer\SuperSocketNetLib\Tcp', 
        N'ListenOnAllIPs', 
        @listen_all output
 
    IF (@listen_all = 1) 
    BEGIN 
        PRINT 'This SQL Server instance is listening to all IP addresses (default mode).' 
    END 
    ELSE 
    BEGIN 
        PRINT 'This SQL Server instance is listening to specIFic IP addresses (ListenOnAllIPs is disabled).' 
    END
 
    -- Check for dynamic port configuration, not recommended with read-only routing. 
    DECLARE @tcp_dynamic_ports varchar(255) 
    EXEC xp_instance_regread 
        N'HKEY_LOCAL_MACHINE', 
        N'SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer\SuperSocketNetLib\Tcp\IPAll', 
        N'TcpDynamicPorts', 
        @tcp_dynamic_ports output
 
    IF (@tcp_dynamic_ports = '0') 
    BEGIN 
        PRINT 'This SQL Server instance is listening on a dynamic tcp port, this is NOT A RECOMMENDED CONFIGURATION when using read-only routing, because the instance port can change each time the instance is restarted.' 
    END 
 ELSE 
    BEGIN 
        PRINT 'This SQL Server instance is listening on fixed tcp port(s) (it is not configured for dynamic ports), this is a recommended configuration when using read-only routing.' 
    END
 
    -- Calculate the server domain AND instance FQDN. 
    -- We use @server_domain later to build the FQDN to the clustered instance. 
    DECLARE @instance_fqdn varchar(255) 
    DECLARE @server_domain varchar(255)
 
    -- Get the instance FQDN using the xp_getnetname API 
    -- Note all cluster nodes must be in same domain, so this works for calculating cluster FQDN. 
    SET @instance_fqdn = '' 
		EXEC xp_getnetname @instance_fqdn output, 1 
 
    -- Remove embedded null character at END IF found. 
    DECLARE @terminator int 
		SET @terminator = charindex(char(0), @instance_fqdn) - 1 
			 IF (@terminator > 0) 
		BEGIN 
			SET @instance_fqdn = substring(@instance_fqdn, 1, @terminator) 
		END
     -- Build @server_domain using @instance_fqdn. 
		 SET @server_domain = @instance_fqdn
     -- Remove trailing portion to extract domain name. 
		 SET @terminator = charindex('.', @server_domain) 
    IF (@terminator > 0) 
		 BEGIN 
			SET @server_domain = substring(@server_domain, @terminator+1, datalength(@server_domain)) 
		 END 
    PRINT 'This SQL Server instance resides in domain ''' +  @server_domain + ''''
 
    IF (ServerProperty('IsClustered') = 1) 
    BEGIN 
        -- Fetch machine name, which for a clustered SQL instance returns the network name of the virtual server. 
        -- AppEND @server_domain to build the FQDN. 
        SET @instance_fqdn = cast(serverproperty('MachineName') as varchar(255)) + '.' + @server_domain 
    END
 
    DECLARE @ror_url varchar(255) 
    DECLARE @instance_port int
 
    SET @ror_url = '' 
    -- Get first available port for instance. 
    SELECT TOP 1    -- SELECT first matching port 
    @instance_port = port 
    FROM sys.dm_tcp_listener_states 
    WHERE type=0 -- Type 0 = TSQL (to avoid mirroring ENDpoint) 
    AND state=0    --  State 0 is online    
    AND port <> @dac_port -- Avoid DAC port (admin port) 
    AND 
    -- Avoid availability GROUP listeners 
    ip_address not in (SELECT ip_address FROM sys.availability_GROUP_listener_ip_addresses agls) 
    GROUP by port        
    ORDER BY port asc  -- Pick first port in ascENDing ORDER
 
    -- Check IF there are multiple ports AND warn IF this is the case. 
    DECLARE @list_of_ports varchar(max) 
    SET @list_of_ports = ''
 
    SELECT 
    @list_of_ports = @list_of_ports + 
        case datalength(@list_of_ports) 
        when 0 then cast(port as varchar(max)) 
        ELSE ',' +  cast(port as varchar(max)) 
        END 
    FROM sys.dm_tcp_listener_states 
    WHERE type=0    --     Type 0 = TSQL (to avoid mirroring ENDpoint) 
    AND  state=0    --  State 0 is online    
    AND  port <> @dac_port -- Avoid DAC port (admin port) 
    AND 
    -- Avoid availability GROUP listeners 
    ip_address not in (SELECT ip_address FROM sys.availability_GROUP_listener_ip_addresses agls) 
    GROUP BY port        
    ORDER BY port asc
 
    PRINT 'This SQL Server instance FQDN (Fully QualIFied Domain Name) is ''' + @instance_fqdn + '''' 
    PRINT 'This SQL Server instance port is ' + cast(@instance_port as varchar(10))
 
    SET @ror_url = 'tcp://' + @instance_fqdn + ':' + cast(@instance_port as varchar(10))
 
    PRINT '****************************************************************************************************************' 
    PRINT 'The read_only_routing_url for this SQL Server instance is ''' + @ror_url + '''' 
    PRINT '****************************************************************************************************************'
 
    -- IF there is more than one instance port (unusual) list them out just in case. 
    IF (charindex(',', @list_of_ports) > 0) 
    BEGIN 
        PRINT 'Note there is more than one instance port, the list of available instance ports for read_only_routing_url is (' + @list_of_ports + ')' 
        PRINT 'The above URL just uses the first port in the list, but you can use any of these available ports.' 
    END
	END

Read Full Post »

SQL Server – New Database Backup Encryption with SQL Server 2014

Posted in Backup, Encryption, tagged SQL Server 2014 on July 21, 2015| 4 Comments »

With the release of Microsoft SQL Server 2014, we have the first version of SQL Server that supports encrypting database backups directly from the database engine without any third party software being installed on the SQL Server.

Preparing the Instance for Encrypted Backups

Before you can have the SQL Server database engine encrypt your backups, you have some basic setup which needs to be done. The first thing to check is that the master database has a master key in the database. Odds are that there is already a master key within the master database, as SQL Server will put one there by default when SQL Server is installed. Whether a master key has been installed can be verified by querying the master.sys.symmetric_keys catalog view and looking for a key named ##MS_DatabaseMasterKey##. If the row exists, then there is nothing else which needs to be done. If the row does not exist, then create a master key within the master database by using the CREATE MASTER KEY command as shown in Listing 1.

CREATE MASTER KEY ENCRYPTION 
BY PASSWORD='MyPa$$w0rd'

The nest preparation item which needs to be done is that a certificate or an asymmetric key must be created within the master database. If an asymmetric key is going to be used (not shown in this article), then the asymmetric key must be protected through an EKM (Extended Key Management).

If there is already a certificate within the master database, this certificate can be used, provided that it has not expired. You can see the list of certificates which exist within the master database by querying the sys.certificates catalog view, with the expiration date shown in the expiry_date column.

If there is no certificate which is usable, then a certificate can be created using the CREATE CERTIFICATE command as shown in Listing 2.

CREATE CERTIFICATE MyBackupCert 
WITH SUBJECT='Backup Encryption Certificate'

If using a certificate, the certificate must be backed up and the backup placed in a safe location. This backup will be needed if the server needs to be rebuild and the backup restored, or if you wish to restore the database to another server. Backing up the certificate is done via the BACKUP CERTIFICATE statement as shown in Listing 3.

BACKUP CERTIFICATE MyBackupCert 
TO FILE='c:\backup\MyBackupCert.crt'

Backing up the database using SQL Server Management Studio (SSMS) setup requirements, are the same as when using T-SQL. Using encryption with the GUI requires that you set the database to backup to a new media set on the “Media Options” page as shown in Figure 1.

Figure 1: Set the database to backup to a new media set under “Media Options”

The encryption options are available on the “Backup Options” page of the database backup screen. You can enable the backup encryption by checking the “Encrypt backup” check box as shown in Figure 2. You can then set the Algorithm and the certificate or asymmetric key that will be used from the two dropdowns which are shown in Figure 2.

Figure 2: Enable the backup encryption by checking “Encrypt backup”

Backing up the Database

Once the certificate or asymmetric key has been created, the database can be backed up using the certificate or asymmetric key, to secure the backup. When backing up the database you can select from four different encryption keys. These are shown below, in order of strength:

DES
AES 128
AES 192
AES 256

The stronger the encryption that is used, the more CPU power required to encrypt the data, and the more CPU power required for someone to attempt to break the encryption. With the CPU power available in modern SQL Servers and the amount of CPU power available to people who may attempt to break the encryption, selecting AES 256 is strongly encouraged.

When using backup encryption with SQL Server backups, each backup must be written to a new media set. Essentially, this means that the backup must be written to a new backup file each time a backup is taken.

Encrypting a database when it is being backed up is very similar to a traditional SQL Server backup. The normal BACKUP DATABASE (or BACKUP LOG) command is used, simply supplying the WITH ENCRYPTION parameter as shown in Listing 4.

BACKUP DATABASE [AdventureWorks2008R2] 
TO DISK = N'C:\Backup\AdventureWorks2008R2.bak'
WITH FORMAT, INIT, NAME = N'AdventureWorks2008R2-Full DB Backup',
ENCRYPTION(ALGORITHM = AES_128, SERVER CERTIFICATE = [NewCert])

Restoring an Encrypted Database

Restoring an encrypted database is just as simple as restoring an unencrypted database. The same RESTORE DATABASE command is used, as normal. The only requirement is that the certificate, or asymmetric key, that was used to encrypt the backup, must exist on the instance before attempting to restore the database. If the certificate or asymmetric key does not exist on the server, then the database will not be restored. If the certificate or asymmetric key has been lost, then there is no way to restore the database from its backup.

If the certificate or asymmetric key is not already on the server, you’ll not be able to use the RESTORE FILELISTONLY or RESTORE HEADERONLY commands to view the metadata about the backup, either, as the entire backup file is encrypted.

Restoring the database from an expired certificate is done just like normal, provided that the certificate already exists on the server. If the certificate has expired, but does not exist on the SQL Server instance, the certificate can still be created from the backup file. The creation of the certificate from the backup file where the certificate is expired returns a warning, but it will import correctly.

Instance Limitations

Database backup encryption is a Standard Edition feature, meaning that it’s available on the Standard and Enterprise editions of Microsoft SQL Server 2014. While you can only backup with Standard or Enterprise Edition, an encrypted backup can be restored to any edition of Microsoft SQL Server 2014, including Express and Web editions. The only limitation is that the database must be under 10 GB in size in order to be restored to an Express Edition instance.

Summary

When used properly, database backup encryption is a great way to protect database backups from being viewed by unauthorized people. SQL Server database backup encryption is easy to setup and simple to use requiring only a master key within the master database and either a certificate or asymmetric key. However, if the certificate is lost, then the database backup becomes useless to you as you’ll have no way to restore the database from the database backup file.

Read Full Post »

SQL Server – Capture Execution Plan of a Currently Running Query/Process SPID

Posted in Performance, Queries, T-SQL, tagged SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 on June 4, 2015| 4 Comments »

DBAs most often face scenarios where they need to capture graphical execution plan of a query currently running on a live production instance because of multiple reasons like:

why a particular SPID is causing blocking
why is my query running slow
why isn’t the index getting used
which operator is costing more and why

While there are multiple ways to retrieve the execution plan, below is the query I always keep handy as I can run this safely on a live production server with minimal effort.

SELECT CONVERT(XML, c.query_plan) AS ExecutionPlan
FROM sys.dm_exec_requests a with (nolock)
OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b
OUTER APPLY sys.dm_exec_text_query_plan (a.plan_handle, a.statement_start_offset, a.statement_end_offset) c
LEFT JOIN sys.dm_exec_query_memory_grants m (nolock)
ON m.session_id = a.session_id
AND m.request_id = a.request_id
JOIN sys.databases d
ON d.database_id = a.database_id
WHERE  a.session_id = @@SPID --replace @@SPID with the SPID number for which you want to capture query plan
ORDER BY a.Start_Time

Hope this will be a good addition to your query bank.

Read Full Post »

SQL Server – How to Calculate Length and Size of any field in a SQL Table

Posted in LOB, Performance, Queries, T-SQL, tagged SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 on June 4, 2015| 7 Comments »

Recently I came across a situation where queries are loading extremely slow from a table. After careful analysis we found the root cause being, a column with ntext datatype was getting inserted with huge amounts of text content/data. In our case DATALENGTH T-SQL function came real handy to know the actual size of the data in this column.

According to books online, DATALENGTH (expression) returns the length of the expression in bytes (or) the number of bytes SQL needed to store the expression which can be of any data type. From my experience this comes very handy to calculate length and size especially for LOB data type columns (varchar, varbinary, text, image, nvarchar, and ntext) as they can store variable length data. So, unlike LEN function which only returns the number of characters, the DATALENGTH function returns the actual bytes needed for the expression.

Here is a small example:

Use AdventureWorksLT2012
GO
Select ProductID, DATALENGTH(Name) AS SizeInBytes, LEN(Name) AS NumberOfCharacters
FROM [SalesLT].[Product]

–Results

If your column/expression size is too large like in my case, you can replace DATALENGTH(Name) with DATALENGTH(Name)/1024 to convert to KB or with DATALENGTH(Name)/1048576 to get the size in MB.

Read Full Post »

SQL Server – Changing Ownership for All the User Databases to “sa” Account

Posted in Security, tagged SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 on May 5, 2015| 13 Comments »

Most people prefer to have “sa” account as the database owner, primary reason being sa login cannot be removed/deleted unlike any user account or service account and so the databases will never end-up in an orphaned stage.

I came-up with the below method to change the ownership to sa on all the 40 databases in our environment recently.

Step 1: Check the databases that does not have sa account as the owner

SELECT name AS DBName, suser_sname(owner_sid) AS DBOwner  
FROM sys.databases
WHERE suser_sname(owner_sid) <> 'sa'

Step 2: Generate the scripts to make sa account as owner for all the databases

SELECT 'ALTER AUTHORIZATION ON DATABASE::' + QUOTENAME(name) + ' TO [sa];'
from sys.databases
where name not in ('master', 'model', 'tempdb', 'msdb')
AND suser_sname(owner_sid) <> 'sa'

Step 3: Execute the result set from step 2 above to change the ownership to sa

--Sample result set from step2 above
ALTER AUTHORIZATION ON DATABASE::[AdventureWorksLT2012] TO [sa];
ALTER AUTHORIZATION ON DATABASE::[Northwind] TO [sa];
ALTER AUTHORIZATION ON DATABASE::[Pubs] TO [sa];

For more information on sa account you can check my previous blog post HERE

Read Full Post »

SQL Server – Ports used by Database Engine and other SQL Features

Posted in Internals, Security, tagged SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 on December 7, 2014| 1 Comment »

Most of us know the default port for SQL Server is 1433, but there are various ports being used by SQL Server for other database services and SQL features.

In the below table I tried to list the ports that are frequently used by the Database Engine

Item	Port
Default instance	TCP port 1433
Named instance in default configuration	Dynamic port. You can configure named instances to use fixed TCP ports
Dedicated Admin Connection (DAC)	TCP port 1434
SQL Server Browser service	UDP port 1434
SQL Server instance running over an HTTP end-point	TCP port 80 for CLEAR_PORT traffic TCP port 443 for SSL_PORT traffic
Service Broker	TCP port 4022
Replication	TCP port 1433 for default instance
Transact-SQL Debugger	TCP port 135
Analysis Services	TCP port 2383 for default instance
Reporting Services Web Services	TCP port 80
Reporting Services configured for use through HTTPS	TCP port 443
Integration Services: Microsoft remote procedure calls	TCP port 135
Integration services run time	TCP port 135
Microsoft Distributed Transaction Coordinator (MS DTC)	TCP port 135
SQL Server Management Studio browse connection to browser service	UDP port 1434

You can find more information about the TCP and UDP ports SQL Server uses from books online here: Configure the Windows Firewall to Allow SQL Server Access

Read Full Post »

SQL Server – Aborted Online Index Operation & Observed Elevated Values for PWAIT_MD_RELATION_CACHE and MD_LAZYCACHE_RWLOCK Wait Types

Posted in Indexes, Performance, TempDB, tagged SQL Server 2012, SQL Server 2014 on August 12, 2014| Leave a Comment »

Last week on one of our production servers (version 2011.110.3373.0), we’ve encountered a strange issue which is relatively new (only applicable for SQL Server 2012 and 2014 versions). Based on the symptoms observed and doing a quick Bing search found we encountered a product bug!

I observed multiple SPIDs, around 300+ running the same stored procedure which is doing an INSERT operation and are in a suspended status, they seemed to be hanging there for atleast like 45 minutes (usually they finish in few seconds). There is no blocking and I found nothing wrong with the SQL Server except for started seeing elevated values for PWAIT_MD_RELATION_CACHE and MD_LAZYCACHE_RWLOCK wait types. And, oh! I also noticed that tempdb data was at 99% full.

So I thought I found the root cause and tried mitigating the problem by adding extra tempdb space but still wasn’t any help. As the last resort I had to failover the services to another node, basically restarted SQL Server, to bring the server fully functional.

On further investigation we found the root cause as someone tried to create an index (online) to improve performance, but later he cancelled the create index and we hit this bug. However, this bug only effected the table on which index operation was attempted and so, everything else was operational except for that Stored Procedure running multiple SPIDs trying to INSERT to the table on which the user attempted CREATE INDEX command was blocked, impairing that part of functionality of the application.

Resolution: Cumulative Update 9 for SQL Server 2012 SP1; Cumulative Update 1 for SQL Server 2014

Here are more details about this problem: http://support.microsoft.com/kb/2926712

Read Full Post »