Automating Patching Activity

Imagine that you manage an enterprise that consists of 100 SQL Server instances. Fifty of these instances are running SQL Server 2014 RTM. You have a requirement to upgrade those 50 instances to SP1. Your company’s maintenance window runs from 6 am Saturday morning through 6 pm Sunday evening. You have two choices. You can either spend your entire weekend installing service packs, or you can run an automated process to patch the servers.

This section will assume that performing a dull, repetitive, time-consuming task is not the way that you enjoy spending your weekends and demonstrate how to create an appropriate automation routine.

We could choose either PowerShell or SQL Server Integration Services, as a tool for orchestrating our workflow. We will choose PowerShell, to keep our patching code consistent with our automated build, which we created in Chapter 7.

The first thing that our script will have to do is decipher which instances in our enterprise have to be upgraded. We will do this with the help of our inventory database, which we created in Chapter 6. To follow the demonstrations in this section, you can use the script in Listing 9-1 to insert some sample data into the Inventory database, which our process will use to determine which servers to patch.

Listing 9-1. Insert Sample Data into Inventory Database

USE Inventory
GO

DECLARE @ServerId       INT

INSERT INTO dbo.Server (ServerName, ClusterFlag, WindowsVersion, SQLVersion, ServerCores, ServerRAM, VirtualFlag, Hypervisor, ApplicationOwner, ApplicationOwnerEMail)
VALUES ('ESPRODSQL14_001', 0, 'Windows Server 2012 SP1', 'SQL Server 2014 RTM', 4, 128, 0, NULL, NULL, NULL) ;

SET @ServerID = @@IDENTITY

INSERT INTO dbo.Instance (InstanceName, ServerID, Port, IPAddress, SQLServiceAccountID, AuthenticationMode, saAccountName, saAccountPassword, InstanceClassification, InstanceCores, InstanceRAM, SQLServerAgentAccountID)
VALUES ('SQL14INSTANCE1', @ServerId, 50001, '10.2.2.5', 1, 1, 'sa', 'Pa$$w0rd', 1, 2, 64, 1),
       ('SQL14INSTANCE2', @ServerId, 50002, '10.2.2.6', 1, 1, 'sa', 'Pa$$w0rd', 1, 2, 64, 1)

We must also ensure that SQL Server 2014 SP1 is available on a shared location. Therefore, we will create a shared folder on ESASSMGMT1 called SQL 2014 SP1, based on the folder c:Software UpdatesSQL 2014 SP1 and place the SP1 binaries in this location.

Now that our management server has been prepared, we will begin to design our data flow. Spending time in drawing the data flow helps when you are developing the process. The data flow can also be used as documentation of the process, which assists new starters joining your team. It also provides a helpful reference, if you must alter or enhance the process in the future. Figure 9-1 depicts the workflow that our PowerShell script will use.

Figure 9-1. Automated patching workflow

More complex workflows may be modular. For example, you may have an orchestration script, written in PowerShell, which calls a series of other PowerShell script, with these child scripts actually performing the work. In this case, it is often useful for the process diagram to be supported by a spreadsheet, which registers the order of the scripts to be orchestrated, along with file names and parameters. An example of this can be found in Table 9-1.

Import-Module sqlps

#Get a list of servers to patch

[array]$ServerInstances = invoke-sqlcmd -ServerInstance "ESASSMGMT1"  -Database "Inventory" -Query "SELECT S.ServerName, I.InstanceName
FROM dbo.Server S
INNER JOIN dbo.Instance I
         ON S.ServerID = I.ServerID
WHERE S.SQLVersion = 'SQL Server 2014 RTM'"

foreach($ServerInstance in $ServerInstances)
{
   #Copy binaries to local server

   Copy-Item -Path "C:Software UpdatesSQLServer2014SP1-KB3058865-x64-ENU.exe" -Destination "\$ServerInstance.ServerNamec$" -Force

   #Establish a session to target server

   $session = new-pssession -computername $ServerInstance.InstanceName

   #Run setup

   invoke-command -session $session {C:SQLServer2014SP1-KB3058865-x64-ENU.exe /iacceptsqlserverlicenseterms /instancename=$ServerInstance.InstanceName /UpdateEnabled=False /quiet}

   #Update inventory database

   invoke-sqlcmd -ServerInstance "ESASSMGMT1" -Database "Inventory" -Query "UPDATE S
   SET SQLVersion = 'SQL Server 2014 SP1'
   FROM dbo.Server S
   INNER JOIN dbo.instance I
       ON S.ServerID = I.ServerID
   WHERE I.InstanceName = '$ServerInstance.InstanceName'"
}

Automating Environment Refreshes

Environment refreshes are often required by development teams, to refresh data in a lower environment from production data. This data can then be used for purposes such as performance testing or to synchronize data structures between Production and UAT before testing a code deployment.

In some scenarios, this is a fairly straightforward process, which can be achieved through techniques such as a backup restore or DETACH…copy…ATTACH. In other environments, however, especially where there is a heightened need for security, the process can be a lot more complex.

For example, I once worked with a FTSE 100 company that was tightly controlled by regulators, and when developers required an environment refresh, there was no way that they could be given access to live data. Therefore, the process of refreshing the environment consisted of the following steps:

1. Backup the production database

2. Put the development server into SINGLE USER mode, so that developers could not access it

3. Restore the database onto the development server

4. Remove database users without a login

5. Obfuscate the data

6. Put instance back into MULTI USER mode

In this section, we will re-create this process for the AdventureWorks2016 database, which we will copy from ESPROD1 to ESPROD2. This process is depicted in the workflow that can be found in Figure 9-2.

Figure 9-2. Environment refresh workflow

We will parameterize the process, so that it can be easily modified for any database. Again, we have the choice of orchestrating this workflow using either PowerShell or SSIS. This time, we will use SSIS.

Creating the SSIS Package

We will now use SSDT to create an SQL Server Integration Services Project , which we will name EnvironmentRefresh. We will give the same name to the package that is automatically created within the project.

Our first task will be to create three project parameters that will accept the name of the Production ServerInstance, the name of the Development ServerInstance, and the name of the database to be refreshed. This is illustrated in Figure 9-3.

Figure 9-3. Creating project parameters

The next step will be to create two OLEDB Connection Managers, by right-clicking in the Connection Managers window and using the New OLEDB Connection dialog box. We should name one Connection Manager ProductionServer and the other DevelopmentServer.

Once the Connection Managers have been created, we can use the Expression Builder, to configure the ServerName property of each Connection Manager to be dynamic, based on the ProductionServer and DevelopmentServer project parameters that we created. Figure 9-4 illustrates using Expression Builder to configure the ServerName property of the ProductionServer Connection Manager.

Figure 9-4. Expression Builder

Tip

A full walk-through of using Expression Builder to create an expression of the ServerName property of a Connection Manager can be found in Chapter 8.

Tip

Remember to configure the DelayValidation of the Connection Manager to true. Otherwise, the package will fail validation.

We must now create three package variables , which will hold the SQL statements that will be run to back up the database, restore the database, and obfuscate the database. We will enter the appropriate expressions to build the SQL statements within the variables, as illustrated in Figure 9-5. It is important to set the EvaluateAsExpression property of the variables to true. This can be configured in the Properties window, when the variable is in scope.

Figure 9-5. Creating package variables

Tip

In the expression scripts below, ensure that you change file paths to match your own configuration.

The expression stored in the BackupSQLStatement variable is detailed in Listing 9-3.

Listing 9-3. BackupSQLStatement Expression

"BACKUP DATABASE " +  @[$Project::DatabaseName] + " TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\" + @[$Project::DatabaseName] + ".bak'"

The expression stored in the RestoreSQLStatement variable can be found in Listing 9-4.

Listing 9-4. RestoreSQLStatement Expression

"RESTORE DATABASE " +  @[$Project::DatabaseName] + " FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\" + @[$Project::DatabaseName] + ".bak' WITH REPLACE; GO ALTER DATABASE " + @[$Project::DatabaseName] + "SET SINGLE_USER WITH ROLLBACK IMMEDIATE;"

The expression stored in the ObfuscateDataStatement variable can be found in Listing 9-5.

Listing 9-5. ObfuscateDataStatement Expression

"DECLARE @SQL NVARCHAR(MAX) ;

SET @SQL = (SELECT CASE t.name WHEN 'int' THEN 'UPDATE ' + SCHEMA_NAME(o.schema_id) + '.' + QUOTENAME(OBJECT_NAME(nkc.object_id)) + ' SET ' +  QUOTENAME(c.name) + ' = CHECKSUM(' + c.name + '); '
WHEN 'money' THEN 'UPDATE ' + QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(nkc.object_id)) + ' SET ' +  QUOTENAME(c.name) + ' = CHECKSUM(' + c.name + '); '
WHEN 'nvarchar' THEN 'UPDATE ' + QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(nkc.object_id)) + ' SET ' +  QUOTENAME(c.name) + ' = LEFT(RTRIM(CONVERT(nvarchar(255), NEWID())), ' + CAST(c.max_length / 2 AS NVARCHAR(10)) + '); '
WHEN 'varchar' THEN 'UPDATE ' + QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(nkc.object_id)) + ' SET ' +  QUOTENAME(c.name) + ' = LEFT(RTRIM(CONVERT(nvarchar(255), NEWID())), ' + CAST(c.max_length AS NVARCHAR(10)) + '); '
WHEN 'text' THEN 'UPDATE ' + QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(nkc.object_id)) + ' SET ' +  QUOTENAME(c.name) + ' = LEFT(RTRIM(CONVERT(nvarchar(255), NEWID())), ' + CAST(c.max_length AS NVARCHAR(10)) + '); '
WHEN 'ntext' THEN 'UPDATE ' + QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(nkc.object_id)) + ' SET ' +  QUOTENAME(c.name) + ' = LEFT(RTRIM(CONVERT(nvarchar(255), NEWID())), ' + CAST(c.max_length AS NVARCHAR(10)) + '); '  END
FROM
(
        SELECT object_id, column_id
        FROM sys.columns
        EXCEPT --Exclude foreign key columns
        SELECT parent_object_id, parent_column_id
        FROM sys.foreign_key_columns
        EXCEPT --Exclude check constraints
        SELECT parent_object_id, parent_column_id
        FROM sys.check_constraints
) nkc
INNER JOIN sys.columns c
        ON nkc.object_id = c.object_id
        AND nkc.column_id = c.column_id
INNER JOIN sys.objects o
        ON nkc.object_id = o.object_id
INNER JOIN sys.types t
        ON c.user_type_id = t.user_type_id
        AND c.system_type_id = t.system_type_id
INNER JOIN sys.tables tab
        ON o.object_id = tab.object_id
WHERE is_computed = 0  --Exclude computed columns
        AND c.is_filestream = 0 --Exclude filestream columns
        AND c.is_identity = 0 --Exclude identity columns
        AND c.is_xml:document = 0 --Exclude XML columns
        AND c.default_object_id = 0 --Exclude columns with default constraints
        AND c.rule_object_id = 0 --Exclude columns associated with rules
        AND c.encryption_type IS NULL --Exclude columns with encryption
        AND o.type = 'U' --Filter on user tables
        AND t.is_user_defined = 0 --Exclude columns with custom data types
        AND tab.temporal_type = 0 --Exclude temporal history tables
        FOR XML PATH('')
) ;

EXEC(@SQL) ;

ALTER DATABASE " +  @[$Project::DatabaseName] + " SET MULTI_USER ;"

We will now create an Execute SQL task, which will be used to back up the production database. As you can see in Figure 9-6, we will use the Execute SQL Task Editor dialog box to configure the task to run against the production server. We will configure the SQL source type as a variable and then point the task to our BackupSQLStatement variable .

Figure 9-6. Configuring backup task

We will now add a second Execute SQL Task, which we will configure to restore the database onto the Development Server and place it into Single User mode. The Execute SQL Task Editor for this task is illustrated in Figure 9-7. Here, you will notice that we have configured the task to run against our Development Server. We have configured the SQL source as a variable and pointed the task to our RestoreSQLStatement variable .

Figure 9-7. Configuring restore task

The next Execute SQL Task will be used to remove any existing database users without a login. Since SQL Server 2012, it is possible to create logins directly at the database level, as opposed to database users, which must map to logins at the Instance level. This feature is part of contained database functionality, which simplifies administration when using technologies such as AlwaysOn availability groups. In our scenario, however, database users without a login can cause an issue, because instead of being orphaned on the instance of the lower environment, unauthorized users could connect, if they have a database login and know the password. Therefore, we will remove all such logins.

Figure 9-8 illustrates how we will use the Execute SQL Task Editor dialog box to configure this process. You will note that we have configured the Connection property to run the query against the Development Server; we have configured the SQL source as direct input; and we have typed the query directly into the SQLStatement property . We have not used a variable, as this time, we will explore an alternative way of configuring the task to be dynamic.

Figure 9-8. Configuring remove database users without a login task

The expression entered into the SQLSourceType property can be found in Listing 9-6.

Listing 9-6. Remove Database Users Without a Login

"USE " +  @[$Project::DatabaseName] + " GO
DECLARE @SQL NVARCHAR(MAX)

SET @SQL = (
SELECT 'DROP USER ' + QUOTENAME(name) + ' ; '
FROM sys.database_principals
WHERE type = 'S'
       AND authentication_type = 0
       AND principal_id > 4
FOR XML PATH('')
) ;

EXEC(@SQL)"

We will make this task dynamic by configuring an expression on the SQLStatement property of the task, as illustrated in Figure 9-9.

Figure 9-9. Configuring expression on SQLStatement property

Tip

While the two approaches that we have discussed are functionally equivalent, I recommend using the variable approach. This is because your code will be much more transparent, easier to follow, and less prone to bugs.

The final Execute SQL task will be used to obfuscate the data and place it back in Multi User mode. As illustrated in Figure 9-10, we will use the Execute SQL Task Editor dialog box to configure the connection to be made to the Development Server and configure the SQL source as a variable. We will then point the task to the ObfuscateDataSQLStatement variable . The dynamic techniques that we have used to create the package mean that when we run the package (probably as a manual execution of an SQL Server Agent job), we can pass in any Production Server, Development Server, and database. The metadata-driven process will obfuscate textual data, integers, and money data in any database. If the need arises, you can also easily amend the script to obfuscate only certain data, such as a specific schema, within the database.

Figure 9-10. Configuring obfuscation task

All of the required tasks have now been created and configured. To clean up the package, join each task in sequence, using success precedence constraints. Because there are four Execute SQL Tasks, I also strongly recommend renaming them, to give them intuitive names. This is always a best practice, but even more important, when there are so many tasks of the same type, as by default, they will be named with a meaningless, sequential number. The final control flow is depicted in Figure 9-11.

Figure 9-11. Completed control flow

Designing a Response to 9002 Errors

As we did when I discussed automating routine maintenance, before creating a process to respond to 9002 errors , we will first create a process flow. This will assist with the coding effort and also act as ongoing documentation of the process.

Figure 9-12 displays the process flow that we will follow when writing our code.

Figure 9-12. Responding to 9002 errors process flow

Automating a Response to 9002 Errors

We will use an SQL Server Agent alert to trigger our process, if a 9002 error occurs. To create a new alert, drill through SQL Server Agent in SQL Server Management Studio and select New Alert from the context menu of Alerts. This will cause the New Alert dialog box to be displayed. On the General page of the dialog box, illustrated in Figure 9-13, we will give the alert a name and configure it to be triggered by SQL Server error number 9002.

Figure 9-13. New Alert—General page

On the Response page of the dialog box, we will check the option to Execute Job and use the New Job button to invoke the New Job dialog box. On the General page of the New Job dialog box, we will define a name for our new Job, as illustrated in Figure 9-14.

Figure 9-14. New Job—General page

On the Steps page of the New Job dialog box, we will use the New button to invoke the New Job Step dialog box. On the General page of the New Job Step dialog box, we will give our job step a name and configure it to execute the script in Listing 9-7.

Listing 9-7. Respond to 9002 Errors

--Create a table to store log entries

CREATE TABLE #ErrorLog
(
LogDate          DATETIME,
ProcessInfo      NVARCHAR(128),
Text             NVARCHAR(MAX)
) ;

--Populate table with log entries

INSERT INTO #ErrorLog
EXEC('xp_readerrorlog') ;

--Declare variables

DECLARE @SQL NVARCHAR(MAX) ;
DECLARE @DBName NVARCHAR(128) ;
DECLARE @LogName NVARCHAR(128) ;
DECLARE @Subject        NVARCHAR(MAX) ;

--Find database name where error occured

SET @DBName = (
                                SELECT TOP 1
                                                SUBSTRING(
                                                        SUBSTRING(
                                                                Text,
                                                                35,
                                                                LEN(Text)
                                                        ),
                                                        1,
                                                                CHARINDEX(
                                                                        '''',
                                                                        SUBSTRING(Text,
                                                                                35,
                                                                                LEN(Text)
                                                                        )
                                                                )-1
                                                 )
                                        FROM #ErrorLog
                                        WHERE Text LIKE 'The transaction log for database%'
                                        ORDER BY LogDate DESC
                                        FOR XML PATH('')
) ;

--Find name of log file that is full

SET @LogName = (
                                SELECT name
                                FROM sys.master_files
                                WHERE type = 1
                                        AND database_id = DB_ID(@DBName)
) ;

--Kill any active queries, to allow clean up to take place

SET @SQL = (
                                SELECT 'USE Master; KILL ' + CAST(s.session_id AS NVARCHAR(4)) + ' ; '
        FROM sys.dm_exec_requests r
                INNER JOIN sys.dm_exec_sessions s
                        ON r.session_id = s.session_id
                INNER JOIN sys.dm_tran_active_transactions at
                        ON r.transaction_id = at.transaction_id
        WHERE r.database_id = DB_ID(@DBName)
        ) ;

        EXEC(@SQL) ;

--IF recovery model is SIMPLE

IF (SELECT recovery_model FROM sys.databases WHERE name = @DBName) = 3
BEGIN
                --Issue a CHECKPOINT
                SET @SQL = (
                SELECT 'USE ' + @DBName + ' ; CHECKPOINT'
        ) ;

        EXEC(@SQL) ;

        --Shrink the transaction log

        SET @SQL = (
                                SELECT 'USE ' + @DBName + ' ; DBCC SHRINKFILE (' + @LogName + ' , 1)'
        ) ;

        EXEC(@SQL) ;

        --e-mail the DBA Team

        SET @Subject = (SELECT '9002 Errors on ' + @DBName + ' on Server ' + @@SERVERNAME)

        EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'ESASS Administrator',
    @recipients = '[email protected]',
    @body = 'A CHECKPOINT has been issued and the Log has been shrunk',
    @subject = @Subject ;
END

--If database in full recovery model

IF (SELECT recovery_model FROM sys.databases WHERE name = @DBName) = 1
BEGIN
        --If reuse delay is not because of replication or mirroring/availability groups

        IF (SELECT log_reuse_wait FROM sys.databases WHERE name = @DBName) NOT IN (5,6)
        BEGIN
                --Backup transaction log

                SET @SQL = (
                                        SELECT 'BACKUP LOG '
                                        + @DBName
                                        + ' TO  DISK = ''C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLBackup'
                                        + @DBName
                                        + '.bak'' WITH NOFORMAT, NOINIT,  NAME = '''
                                        + @DBName
                                        + '-Full Database Backup'', SKIP ;'
                ) ;

                EXEC(@SQL) ;

                --Shrink the transaction log

                SET @SQL =  (
                                        SELECT 'USE ' + @DBName + ' ; DBCC SHRINKFILE (' + @LogName + ' , 1)'
                ) ;

                EXEC(@SQL) ;

                --e-mail the DBA Team

                SET @Subject = (SELECT '9002 Errors on ' + @DBName + ' on Server ' + @@SERVERNAME) ;

                EXEC msdb.dbo.sp_send_dbmail
                @profile_name = 'ESASS Administrator',
                @recipients = '[email protected]',
                @body = 'A Log Backup has been issued and the Log has been shrunk',
                @subject = @Subject ;
        END
        --If reuse delay is because of replication or mirroring/availability groups

        ELSE
        BEGIN
                --e-mail DBA Team
                SET @Subject = (SELECT '9002 Errors on ' + @DBName + ' on Server ' + @@SERVERNAME) ;

                EXEC msdb.dbo.sp_send_dbmail
                @profile_name = 'ESASS Administrator',
                @recipients = '[email protected]',
                @body = 'DBA intervention required - 9002 errors due to HA/DR issues',
                @subject = @Subject ;
        END
END

This is illustrated in Figure 9-15.

Figure 9-15. New Job Step—General page

After exiting the New Job Step dialog box and the New Job dialog box, our new job will automatically be selected in the Execute job drop-down list on the Response page of the New Alert dialog box, as shown in Figure 9-16.

Figure 9-16. Response page

Figure 9-17 displays the Options page of the New Alert dialog box. While we do not have to configure anything on this page, in our scenario, you should be aware of the options that can be configured on this page.

Figure 9-17. Options page

For me, the most interesting option is the ability to provide a delay between responses. Configuring this option can help you avoid the possibility of “false” responses being fired while a previous response is still in the process of resolving an issue. You are also able to use the Options page to specify if you want the error test that caused the Alert to fire to be included in any e-mail, pager, or NETSEND notifications that are sent. You can also add additional message text.