Log Shipping Article

List of Tables & Stored Procedures

Primary server tables

Table	Description
log_shipping_monitor_alert	Stores alert job ID. This table is only used on the primary server if a remote monitor server has not been configured.
log_shipping_monitor_error_detail	Stores error detail for log shipping jobs associated with this primary server.
log_shipping_monitor_history_detail	Stores history detail for log shipping jobs associated with this primary server.
log_shipping_monitor_primary	Stores one monitor record for this primary database.
log_shipping_primary_databases	Contains configuration information for primary databases on a given server. Stores one row per primary database.
log_shipping_primary_secondaries	Maps primary databases to secondary databases.

Primary Server Stored Procedures

Stored Procedure	Description
sp_add_log_shipping_primary_database	Sets up the primary database for a log shipping configuration, including the backup job, local monitor record, and remote monitor record.
sp_add_log_shipping_primary_secondary	Adds a secondary database name to an existing primary database.
sp_change_log_shipping_primary_database	Changes primary database settings including local and remote monitor record.
sp_cleanup_log_shipping_history	Cleans up history locally and on the monitor based on retention period.
sp_delete_log_shipping_primary_database	Removes log shipping of primary database including backup job as well as local and remote history.
sp_delete_log_shipping_primary_secondary	Removes a secondary database name from a primary database.
sp_help_log_shipping_primary_database	Retrieves primary database settings and displays the values from the log_shipping_primary_databases and log_shipping_monitor_primary tables.
sp_help_log_shipping_primary_secondary	Retrieves secondary database names for a primary database.
sp_help_log_shipping_primary_secondary	Retrieves secondary database names for a primary database.
sp_refresh_log_shipping_monitor	Refreshes the monitor with the latest information for the specified log shipping agent.

Secondary Server Tables

Table	Description
log_shipping_monitor_alert	Stores alert job ID. This table is only used on the secondary server if a remote monitor server has not been configured.
log_shipping_monitor_error_detail	Stores error detail for log shipping jobs associated with this secondary server.
log_shipping_monitor_history_detail	Stores history detail for log shipping jobs associated with this secondary server.
log_shipping_monitor_secondary	Stores one monitor record per secondary database associated with this secondary server.
log_shipping_secondary	Contains configuration information for the secondary databases on a given server. Stores one row per secondary ID.
log_shipping_secondary_databases	Stores configuration information for a given secondary database. Stores one row per secondary database.

Note: Secondary databases on the same secondary server for a given primary database share the settings in the log_shipping_secondary table. If a shared setting is altered for one secondary database, the setting is altered for all of them.

Secondary Server Stored Procedures

Stored Procedures	Description
sp_add_log_shipping_secondary_database	Sets up a secondary database for log shipping.
sp_add_log_shipping_secondary_primary	Sets up the primary information, adds local and remote monitor links, and creates copy and restore jobs on the secondary server for the specified primary database.
sp_change_log_shipping_secondary_database	Changes secondary database settings including local and remote monitor records.
sp_change_log_shipping_secondary_primary	Changes secondary database settings such as source and destination directory, and file retention period.
sp_cleanup_log_shipping_history	Cleans up history locally and on the monitor based on retention period.
sp_delete_log_shipping_secondary_database	Removes a secondary database and the local history and remote history.
sp_delete_log_shipping_secondary_primary	Removes the information about the specified primary server from the secondary server.
sp_help_log_shipping_secondary_database	Retrieves secondary database settings from the log_shipping_secondary,log_shipping_secondary_databases, and log_shipping_monitor_secondary tables.
sp_help_log_shipping_secondary_primary	This stored procedure retrieves the settings for a given primary database on the secondary server.
sp_refresh_log_shipping_monitor	Refreshes the monitor with the latest information for the specified log shipping agent.

Monitor Server Tables

Table	Description
log_shipping_monitor_alert	Stores alert job ID.
log_shipping_monitor_error_detail	Stores error detail for log shipping jobs.
log_shipping_monitor_history_detail	Stores history detail for log shipping jobs.
log_shipping_monitor_primary	Stores one monitor record per primary database associated with this monitor server.
log_shipping_monitor_secondary	Stores one monitor record per secondary database associated with this monitor server.

Monitor Server Stored Procedures

Stored Procedures	Description
sp_add_log_shipping_alert_job	Creates a log shipping alert job if one has not already been created.
sp_delete_log_shipping_alert_job	Removes a log shipping alert job if there are no associated primary databases.
sp_help_log_shipping_alert_job	Returns the job ID of the alert job.
sp_help_log_shipping_monitor_primary	Returns monitor records for the specified primary database from the log_shipping_monitor_primary   table.
sp_help_log_shipping_monitor_secondary	Returns monitor records for the specified secondary database from the log_shipping_monitor_secondary table.

WAITING TYPES

 > When if any query waiting for resources then relevant wait type comes into picture. Which cause high performance impact.

How to find wait type:

Select * from sys.sysprocesses

Column “last wait type”

Types of wait types:

1. LCK_M_S: Occurs when a task is waiting to acquire a shared lock. [Occurs mostly in blockings]

2. ASYNC_IO_COMPLETION: Occurs when a task is waiting for I/Os to finish.

3. ASYNC_NETWORK_IO: Occurs on network writes when the task is blocked behind the network. Verify that the client is processing data from the server.

CDC (CHANGE DATA CAPTURE)

 >>What is Change Data capture? [CDC]

1. Microsoft SQL Server 2008 has introduced a very exciting feature for logging DML changes.

2. Change data capture provides information about DML changes on a table and a database.

3.Change data capture records insert, update, and delete activity , that’s applied to a SQL Server table and makes a record available of what changed, where, and when, in simple relational 'change tables’.

4. Also stores historical data and COLUMN level changes in SQL Server by using CDC feature.

5. Change data capture is available only on the Enterprise, Developer, and Evaluation editions of SQL Server

>>How it works>

1. The source of change data for change data capture is the SQL Server transaction log.

2. As inserts, updates, and deletes are applied to tracked source tables, entries that describe those changes are added to the log.

3. The log serves as input to the change data capture process. This reads the log and adds information about changes to the tracked table’s associated change table.

>>Permissions required to configure CDC:

EITHER DB_OWNER OR SYSADMIN permissions

>>CDC Configuration steps:

1. Enable CDC on database by using

EXEC sys.sp_cdc_enable_db

@5 system tables gets created automatically.

[cdc].[captured_columns]

[cdc].[change_tables]

[cdc].[ddl_history]

[cdc].[index_columns]

[cdc].[lsn_time_mapping]

[dbo].[systranschemas]

2. Enable CDC on table by using

EXEC sys.sp_cdc_enable_table

@source_schema = N'dbo',

@source_name = N'MyTable',

@role_name = NULL

Note: Few CDC system table and 2 CDC jobs create automatically inside of the SQL Server databases

CDC Default Tables:

cdc.captured_columns: This table returns result for list of captured column.

cdc.change_tables: This table returns list of all the tables which are enabled for capture.

cdc.ddl_history: This table contains history of all the DDL changes since capture data enabled.

cdc.index_columns: This table contains indexes associated with change table.

cdc.lsn_time_mapping: This table maps LSN number (for which we will learn later) and time.

dbo.systranschemas:

After enabling CDC on table one more addition tracking table

Ex: CDC.DBO_STAB_CT

List of automatic jobs:

cdc.DBNAME_capture

cdc.DBNAME_cleanup

Findings:

Select * from CDC.DBO_STAB_CT

If the operation column shows value

1: Delete operation

2: Insert operation

3: Before update

4: After update

Along with this data gets captured into CDC defined table.

Note: Enable CDC only with confirmation from apps team or client... If you enable it consumes more hardware resource and additional storage is required.

Check list for before Migration?

 Migration from SQL Server 2008 to SQL Server 2014 Check list

1. Identify databases you would like to migrate

2. Backup all user databases

3. Script out all the existing login

4. Script out all the Server roles if applicable

5. Script out all the Audit and Audit Specifications if Applicable

6. Script out backup devices if Applicable

7. Script out Server level triggers if Applicable

8. Script out Replication along with Configuration if Applicable

9. Script out Mirroring if Applicable

10. Script out Data Collection if Applicable

11. Script out Resource Governor’s objects if Applicable

12. Script out Linked Server if Applicable

13. Script out Logshipping if Applicable

14. Script out SQL Server Agent jobs

15. Script out all DB Mail objects such as Profile and its settings

16. Script out all Proxy accounts and credentials if Applicable

17. Script out all Operators if Applicable

18. Script out all alerts if Applicable

19. Save SQL Server, Server configuration in a file

20. Data Encryption keys

Destination SQL Server Checklist

1. Required SQL Server is installed

2. DBA SQL Server Check List is Completed

3. Enough Space for storing Backup and source scripts

4. Applications compatibility is signed off

How applications generally connect to database?

 1. When application try to connect, uses Config file (Configuration file--Resides in application server). Config file (.txt) should contains SQL Server instance name + user name [service account name--DBA team should add the account into SQL Server under security] + Password [Strong]

Application use all these details from Config file and point connection to SQL server database

Real time points:

> Any business 2 data centers maintains called PRODUCTION and DR data center.

> Always distance between data centers should not be more than 50 KM.

> Few people always work inside the data centers.