Feeds:
Posts
Comments

We are happy to announce the birth of DataGinger. Starting today we officially re-branded ourselves as DataGinger.com (previously SQLServerZest.com). The main motive for this change is to expand our blogging topics beyond SQL Server and widen our scope to include all data related technologies.

-Your Friends at DataGinger🙂

Disk space is one of those things that frequently runs out of space no matter how much you bump it up irrespective of the service you are running in the server. I know storage is cheap but who wouldn’t want to keep an eye on what’s cooking especially when it has the potential to bring things to halt?

I believe that if you run a Windows Service, or if anything Windows is your job, then implementing PowerShell will make your job a lot easier and lot more fun. This PowerShell script calculates free disk spaces in multiple servers (from a text file) and emails the report in a HTML format. The script can be scheduled using Windows Scheduler or SQL Agent Job to run at a certain time or interval. This is designed to report only servers with less than 20% free space but you can customize for your needs

Prerequisite:

If you have never used PowerShell on your system before, chances are that your PowerShell “Execution Policy” is set to restrict execution of scripts on your machine, and you’ll have trouble running this script. To allow your scripts to execute, you need to set your Execution Policy to RemoteSigned. Here is the procedure to, first of all, check what yours is set to, and then, if necessary, set it to RemoteSigned.

  • Run PowerShell as Administrator on your PC/Server
  • Enter in and run the Get-ExecutionPolicy cmdlet – this will output the current setting. If it is not alreadyRemoteSigned, or Unrestricted, then use the following cmdlet to set it to allow your scripts to run:Set-ExecutionPolicy RemoteSigned
  • You should now be asked to confirm whether you are sure. Cick Yes to confirm as shown below

1

Now that your environment is ready to run the cmdlets and scripts, lets take a look at the basic rundown of the script’s processes:

  1. Iterate through a list of servers you specify in a text file, checking disk space.
  2. Check each free disk space percentage figure against a pre-defined percent threshold figure.
  3. If the disk in question is below this threshold, then add the details to the report, if not, skip past it.
  4. Assemble an e-mail and send it off to the specified recipient(s) if any of the drives were below the free disk space threshold.

The Script


#########################################################
#
# SQLSERVERZEST: Server Disk Space monitoring Report
#
#########################################################
 
#### Provide Below email and SMTP details ####

$fromemail ="abc@email.com" 
$users="recipients@email.com"
$Server= "smptserver.DomainName.Com"

$computers = get-content -Path "//ServerName/../Servers.txt"  # Specify servers' list path


# Set free disk space threshold below in percent (default at 20%)
[decimal]$thresholdspace = 20
 

 #### Main Sctipt Block ####

$tableFragment= Get-CimInstance -ComputerName $computers cim_LogicalDisk -erroraction 'silentlycontinue' `
| select SystemName, DriveType, VolumeName, Name, @{n='Size (Gb)' ;e={"{0:n2}" -f ($_.size/1gb)}},@{n='FreeSpace (Gb)';e={"{0:n2}" -f ($_.freespace/1gb)}}, @{n='PercentFree';e={"{0:n2}" -f ($_.freespace/$_.size*100)}} `
| Where-Object {$_.DriveType -eq 3 -and [decimal]$_.PercentFree -lt [decimal]$thresholdspace} `
| ConvertTo-HTML -fragment 


#### HTML for our body of the email report ####

$HTMLmessage = @"
<font color=""Red"" face=""Segoe UI Light, Segoe UI Light"" size=""8"">
<u><b>Disk Space Storage Report</b></u>
<br>This report was generated because the drive(s) listed below have less than $thresholdspace% free space. Drives above this threshold will not be listed.
<br>
<style type=""text/css"">body{font: .8em ""Segoe UI Light"", Segoe UI Light, Segoe UI Light, Segoe UI Light, Segoe UI Light;}
ol{margin:0;padding: 0 1.5em;}
table{color:#FFF;background:#C00;border-collapse:collapse;width:647px;border:5px solid #900;}
thead{}
thead th{padding:1em 1em .5em;border-bottom:1px dotted #FFF;font-size:120%;text-align:left;}
thead tr{}
td{padding:.5em 1em;}
tfoot{}
tfoot td{padding-bottom:1.5em;}
tfoot tr{}
#middle{background-color:#900;}
</style>
<body BGCOLOR=""white"">
$tableFragment
</body>
"@
 
# Set up a regex search and match to look for any <td> tags in our body. These would only be present if the script above found disks below the threshold of free space.
# We use this regex matching method to determine whether or not we should send the email and report.
$regexsubject = $HTMLmessage
$regex = [regex] '(?im)<td>'
 
# if there was any row at all, send the email
if ($regex.IsMatch($regexsubject)) {
 send-mailmessage -from $fromemail -to $users -subject "Disk Space Monitoring Report" -BodyAsHTML -body $HTMLmessage -priority High -smtpServer $server
}
 
# End of Script

Here is the sample email report

2

 

This is just a quick report that I developed but as with any scripting language, PowerShell will give you plenty of customization to modify the look and feel of your report as desired.

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)

0

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

  1. Define a read-only routing URL
  2. Define a read-only routing List
  3. 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

1

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

4

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

4

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

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

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"

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"

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.

 

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.

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

DATALENGTH

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.

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

According to MSDN:

If the SQL Server service is running as a built-in account, such as Local System, Local Service, or Network Service, or a nondomain account, you must use certificates for endpoint authentication, and the Add New Replica wizard will be unable to create a database mirroring endpoint on the server instance. In this case, we recommend that you create the database mirroring endpoints manually before you launch the Add Replica to Availability Group Wizard.

The below message is displayed when we try to add replica with a nondomain account:

The Endpoints tab lists at least one endpoint that uses only Windows Authentication. However, the server instance might be running under a nondomain account. To use the listed endpoint, change the corresponding SQL Server service account to a domain account. To continue using the nondomain account, alter the endpoint to use a certificate. Do you want to use the listed endpoints?

One way to get around this issue without actually using a domain account is to grant access to the built-in account using the below script.


GRANT CONNECT ON endpoint::hadr_endpoint
TO [domain\servername$]

Note: If you do not have a end point, then you might have to create one for database mirroring as below.

 

CREATE ENDPOINT [Hadr_endpoint]
	STATE=STARTED
	AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
	FOR DATA_MIRRORING (ROLE = ALL
, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)

 

While I do not recommend you to use non domain account to configure AlwaysOn but this quick work around will get you through the blocking especially when you are configuring AlwaysOn for testing purposes.