Tag: Log Shipping

AlwaysOn, SQL, SQL 2012

Querying Change Tracking Tables Against a Secondary AG Replica

If you aren’t familiar with Change Tracking I would recommend heading out and reading Kendra Little’s Change Tracking master post which contains a wealth of information. 

I’ve been using CT for a while now and it does what it says on the box. The performance can be painful when querying tables that have changed a lot (the changetable function performs a huge amount of aggregation and seems to be optimized for 1000 rows of data). One of the things that I’ve always wanted to do is perform loads into an ODS from a DR site. 

I use AvailabilityGroups to ensure that a near real-time copy of the data is kept in another data center in another part of the country. I’ve tried a couple of times to query the change information from one of the secondary replicas, but sadly it’s not supported and so I would get the error

Msg 22117, Level 16, State 1, Line 1
For databases that are members of a secondary availability replica, change tracking is not supported. Run change tracking queries on the databases in the primary availability replica.

 

Yesterday I was messing around with database snapshots and was really happy to discover that it is possible to use the changetable function against a snapshot and not receive any errors. This will only work against readable secondary replicas (as the database needs to be online in order to be able to take the snapshot).

This is also the case with log shipped copies of databases. If the database is in standby then you can access the changetable function directly, or do so off a snapshot.

 

It doesn’t seem like this is a big deal, but if you like to load data into an ODS or Data Warehouse server and it’s not located in the same location as your AG primary, then this is huge as you can asynchronously write data over a WAN and then do all your data loads local to the ODS. This is far more efficient (and your network admin will like you a lot more) than pulling the data over in a large chunk nightly and saturating a part of the network.

Just another way that you can make your DR system work for you.

SQL

Speedy Log Shipping Setup

As a follow up to last weeks post on quickly restoring multiple databases to default locations I’ve another script which might help you with quick deployments.

Last week I covered restoring the databases, I also needed to get log shipping setup for multiple databases. If you’ve ever had to configure log shipping you know that there’s lots of clicking and typing of things and entering information. It’s a bit of a pain for a single database, when you have to set it up for multiple databases it’s a long, slow, time consuming nightmare.

I’d had enough of dealing with this so I put together a script that will do the complete configuration of the primary and secondary servers with a single execution. The key to doing this is using SQLCMD mode from within SSMS.

Never used SQLCMD mode before? I’m not surprised, it’s not that well known. To enter SQLCMD mode you either choose the option from the Query menu item or add the button to one of your tool bars

image

This allows you to do some special things, like parameter substitution and, key to what I’m doing, change connections as a part of the script.

Try the following, replacing PrimaryServer and SecondaryServer with something relevant to your environment.

image

As you can see from the script it connected to each server in turn and grabbed the @@SERVERNAME value. That’s pretty special.

My log shipping script does very much the same thing. You just provide the primary and secondary servers, the file locations for the transaction log backups and the name of the database. The script takes care of the rest.

It will perform a very basic check to ensure that the local backup directory exists and error if not. The script also does not encompass a log shipping monitor server and has a default of 15 minutes for the backup/copy/restore processes. All of that could be quickly adjusted and made additional parameters.

View the script below or download SetupLogShipping.sql

Of course this doesn’t answer the question of when or why you would use log shipping, that’s for another post.

:ON ERROR exit

 

:setvar PrimaryServer "ServerA"

:setvar PrimaryDatabase "MyDB"

:setvar PrimaryBackupDirectory "E:BackupDBTrxLogs"

:setvar PrimaryBackupShare "ServerAtrxlogs$"

:setvar SecondaryServer "ServerB"

:setvar SecondaryDatabase "MyDB"

:setvar SecondaryBackupDirectory "ServerBTrxlogs$TrxForRestore"

 

SET NOCOUNT ON

/* Begin: Script to be run at Primary */

 

 

:CONNECT $(PrimaryServer)

 

BEGIN

USE msdb;

 

/* Check to see if the backup folder exists */

RAISERROR('Checking to see if $(PrimaryBackupDirectory) exists...',0,1) WITH NOWAIT

DECLARE @CheckDir NVARCHAR(2000)

DECLARE @ErrorMsg NVARCHAR(200)

DECLARE @DirInfo TABLE

    (

      FileExists BIT ,

      FileIsADirectory BIT ,

      ParentDirectoryExists BIT

    )

                                

SET @CheckDir = '$(PrimaryBackupDirectory)'

 

INSERT  INTO @DirInfo

        ( FileExists ,

          FileIsADirectory ,

          ParentDirectoryExists

        )

        EXECUTE [master].dbo.xp_fileexist @CheckDir

 

 

 

IF NOT EXISTS ( SELECT  *

                FROM    @DirInfo

                WHERE   FileExists = 0

                        AND FileIsADirectory = 1

                        AND ParentDirectoryExists = 1 ) 

    BEGIN

        SET @ErrorMsg = 'The primary backup directory $(PrimaryBackupDirectory) does not exist...quitting' 

        RAISERROR(@ErrorMsg,16,1) WITH NOWAIT

        RETURN

    END

RAISERROR('$(PrimaryBackupDirectory) exists, continuing...',0,1) WITH NOWAIT  

 

DECLARE @LS_BackupJobId    AS uniqueidentifier 

DECLARE @LS_PrimaryId    AS uniqueidentifier 

DECLARE @SP_Add_RetCode    As int 

 

RAISERROR('Enabling log shipping for $(PrimaryDatabase) on $(PrimaryServer)...',0,1) WITH NOWAIT

 

EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database 

        @database = N'$(PrimaryDatabase)' 

        ,@backup_directory = N'$(PrimaryBackupDirectory)' 

        ,@backup_share = N'$(PrimaryBackupShare)' 

        ,@backup_job_name = N'LSBackup_$(PrimaryDatabase)' 

        ,@backup_retention_period = 4320

        ,@backup_compression = 1

        ,@backup_threshold = 60 

        ,@threshold_alert_enabled = 1

        ,@history_retention_period = 5760 

        ,@backup_job_id = @LS_BackupJobId OUTPUT 

        ,@primary_id = @LS_PrimaryId OUTPUT 

        ,@overwrite = 1 

 

 

IF (@@ERROR = 0 AND @SP_Add_RetCode = 0) 

BEGIN 

 

DECLARE @LS_BackUpScheduleUID    As uniqueidentifier 

DECLARE @LS_BackUpScheduleID    AS int 

 

 

EXEC msdb.dbo.sp_add_schedule 

        @schedule_name =N'LSBackupSchedule_$(PrimaryServer)' 

        ,@enabled = 1 

        ,@freq_type = 4 

        ,@freq_interval = 1 

        ,@freq_subday_type = 4 

        ,@freq_subday_interval = 15 

        ,@freq_recurrence_factor = 0 

        ,@active_start_date = 20120607 

        ,@active_end_date = 99991231 

        ,@active_start_time = 0 

        ,@active_end_time = 235900 

        ,@schedule_uid = @LS_BackUpScheduleUID OUTPUT 

        ,@schedule_id = @LS_BackUpScheduleID OUTPUT 

 

EXEC msdb.dbo.sp_attach_schedule 

        @job_id = @LS_BackupJobId 

        ,@schedule_id = @LS_BackUpScheduleID  

 

EXEC msdb.dbo.sp_update_job 

        @job_id = @LS_BackupJobId 

        ,@enabled = 1 

 

 

END 

 

 

EXEC master.dbo.sp_add_log_shipping_alert_job 

 

EXEC master.dbo.sp_add_log_shipping_primary_secondary 

        @primary_database = N'$(PrimaryDatabase)' 

        ,@secondary_server = N'$(PrimaryServer)' 

        ,@secondary_database = N'$(SecondaryDatabase)' 

        ,@overwrite = 1 

 

 

RAISERROR('Log shipping enabled for $(PrimaryDatabase) on $(PrimaryServer)...',0,1) WITH NOWAIT

END

GO

/* ENDS THE PRIMARY SCRIPT */

 

 

 

/* Script to be run on secondary */

:CONNECT $(SecondaryServer)

USE msdb;

 

DECLARE @LS_Secondary__CopyJobId    AS uniqueidentifier 

DECLARE @LS_Secondary__RestoreJobId    AS uniqueidentifier 

DECLARE @LS_Secondary__SecondaryId    AS uniqueidentifier 

DECLARE @LS_Add_RetCode    As int 

 

RAISERROR('Adding log shipping secondary to $(SecondaryServer) with database name $(SecondaryDatabase)...',0,1) WITH NOWAIT

 

EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary 

        @primary_server = N'$(PrimaryServer)' 

        ,@primary_database = N'$(PrimaryDatabase)' 

        ,@backup_source_directory = N'$(PrimaryBackupShare)' 

        ,@backup_destination_directory = N'$(SecondaryBackupDirectory)' 

        ,@copy_job_name = N'LSCopy_$(PrimaryServer)_$(PrimaryDatabase)' 

        ,@restore_job_name = N'LSRestore_$(PrimaryServer)_$(PrimaryDatabase)' 

        ,@file_retention_period = 4320 

        ,@overwrite = 1 

        ,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT 

        ,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT 

        ,@secondary_id = @LS_Secondary__SecondaryId OUTPUT 

 

IF (@@ERROR = 0 AND @LS_Add_RetCode = 0) 

BEGIN 

 

DECLARE @LS_SecondaryCopyJobScheduleUID    As uniqueidentifier 

DECLARE @LS_SecondaryCopyJobScheduleID    AS int 

 

RAISERROR('Creating log shipping secondary jobs...',0,1) WITH NOWAIT

 

EXEC msdb.dbo.sp_add_schedule 

        @schedule_name =N'DefaultCopyJobSchedule' 

        ,@enabled = 1 

        ,@freq_type = 4 

        ,@freq_interval = 1 

        ,@freq_subday_type = 4 

        ,@freq_subday_interval = 15 

        ,@freq_recurrence_factor = 0 

        ,@active_start_date = 20120607 

        ,@active_end_date = 99991231 

        ,@active_start_time = 0 

        ,@active_end_time = 235900 

        ,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT 

        ,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT 

 

EXEC msdb.dbo.sp_attach_schedule 

        @job_id = @LS_Secondary__CopyJobId 

        ,@schedule_id = @LS_SecondaryCopyJobScheduleID  

 

DECLARE @LS_SecondaryRestoreJobScheduleUID    As uniqueidentifier 

DECLARE @LS_SecondaryRestoreJobScheduleID    AS int 

 

 

EXEC msdb.dbo.sp_add_schedule 

        @schedule_name =N'DefaultRestoreJobSchedule' 

        ,@enabled = 1 

        ,@freq_type = 4 

        ,@freq_interval = 1 

        ,@freq_subday_type = 4 

        ,@freq_subday_interval = 15 

        ,@freq_recurrence_factor = 0 

        ,@active_start_date = 20120607 

        ,@active_end_date = 99991231 

        ,@active_start_time = 0 

        ,@active_end_time = 235900 

        ,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT 

        ,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT 

 

EXEC msdb.dbo.sp_attach_schedule 

        @job_id = @LS_Secondary__RestoreJobId 

        ,@schedule_id = @LS_SecondaryRestoreJobScheduleID  

 

 

END 

 

 

DECLARE @LS_Add_RetCode2    As int 

 

 

IF (@@ERROR = 0 AND @LS_Add_RetCode = 0) 

BEGIN 

 

EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database 

        @secondary_database = N'$(SecondaryDatabase)' 

        ,@primary_server = N'$(PrimaryServer)' 

        ,@primary_database = N'$(PrimaryDatabase)' 

        ,@restore_delay = 0 

        ,@restore_mode = 0 

        ,@disconnect_users    = 0 

        ,@restore_threshold = 45   

        ,@threshold_alert_enabled = 1 

        ,@history_retention_period    = 5760 

        ,@overwrite = 1 

 

END 

 

 

IF (@@error = 0 AND @LS_Add_RetCode = 0) 

BEGIN 

 

EXEC msdb.dbo.sp_update_job 

        @job_id = @LS_Secondary__CopyJobId 

        ,@enabled = 1 

 

EXEC msdb.dbo.sp_update_job 

        @job_id = @LS_Secondary__RestoreJobId 

        ,@enabled = 1 

 

END 

 

RAISERROR('Setup of log shipping from database $(PrimaryDatabase) on server $(PrimaryServer) to database $(SecondaryDatabase) on $(SecondaryServer) complete!',0,1) WITH NOWAIT