Disaster Recovery - Part 1 - Possibly Almost Everything But the Kit

Over the years I have been fortunate or unfortunate enough, depending on who you are to have experienced several disasters along with a couple of very near misses.  In this post I am going to explore some of the areas of DR that are not related to the physical kit.  Let me just start by defining what I mean by a disaster, a disaster is not a cluster fail-over, it is not a drive failure in a RAID array and it is not a UPS failure.  Disasters can be classified in two categories.  The first are natural disasters such as floods, fires or earthquakes.  The second are man made disasters, for example, infrastructure failure, or even terrorism.

Disaster strikes when you least expect it!  Disaster is unforgiving!  Disaster doesn't care!  One day it will come and it will bite you, of that you can be sure!

So let us take a look at the things you might not have considered.

Plan
Do you even have a plan?  If not then you better start praying!  If you do you are in with a fighting chance.  Everyone involved should have visibility of the plan, the actions required and the order in which they need to be followed.  It is pointless having the plan just on a file server in the building that is now ablaze!  A physical copy is a great idea, or if you have a laptop, tablet or even a phone, a copy there would also suffice.  The more locations the better, but always remember, everyone MUST have the same plan, when it is updated, circulate it!

Who
Who is required? Who is available?  Not every member of the team will be required to help with the recovery process.  Not every member of the team will be available, as I have mentioned earlier, disaster is unforgiving, disaster doesn't care.  Chances are you will have team members who would have been involved in the recovery process, that are on annual leave, incapacitated or otherwise engaged.  Personally I don't think it is overkill to have a weekly list of personnel that are available with contact details, this will make a massive difference.

Where
If your lucky your office will still be in one piece and you may be able to conduct all your work from there.  If your office is still standing, can the required personnel gain access?  do you have keys, security fobs and access codes available?  In the event that your office is inaccessible, what do you do?  Is there another office or remote site that can be used? Again, can the required personnel gain access?  do you have keys, security fobs and access codes available? Is the VPN even available to be an option?  Things can deteriorate rapidly if there isn't somewhere from which the required personnel can work from.

Hours
Depending on the extent of the disaster and or your environment you may be looking at a very lengthy recovery process.  No one person can function at 100% for 24 or 48 hours, what I find works here is to have as many bodies available, working to start with then to split into multiple teams and work in shifts.  This way a lot of the initial work can be started by as many people as possible, which is then continued by one team giving other teams chance to get some much needed rest in anticipation for taking over later in the recovery process.  It is pointless having a team of 10 people working flat out for 24 hours as mistakes will be made as fatigue sets in, it is much safer to have 2 teams of 5 working 6-8 hour shifts.

Communication
Who is going to be responsible for communications?  You don't want 10 engineers sending out potentially conflicting messages to the business.  You also don't want someone stood over your shoulder asking you how long is left, where on the plan are we up to.  Someone should be responsible for collating, and communicating this information, this also helps the other way by the business having a single point of communication, having only one person to contact for updates.

Other Commitments
No one person can be available 24/7.  There may be team members with children that need to be cared for, chances are they will need to be looked after by friends or family but may have to be brought into the office, or remote site and "entertained".  DR is not much fun for a 5 year old on a sunny Saturday afternoon mid-summer.

Food & Drink
That is right folks, fuel!  We all need watering and feeding, without this after about 12 hours you will seriously start to flag, concentration levels will fall dramatically and again mistakes will be made.  Someone should responsible for keeping everyone fed and watered and the relevant expenses agreed beforehand.

Testing

So you have a plan, know exactly who, where, when and how to recover, great job, but that is only half the battle.  Have you tested the recovery process?  If the answer is no then I would be inclined to say your plan will fail.  Why?  Well in my experience, even with the best minds in the world something will have been missed.  Testing the recovery plan will highlight this, some config file, DLL or permission will not have been taken into consideration.  The proof really is in the pudding, get testing!

Summary
If all of the above are covered, then there is a good chance your business and infrastructure are in good hands.  There will always be something that is forgotten, always some spanner thrown into the mix, it is up to us as professionals to make sure we have catered for as much as is conceivably possible to make sure that when disaster does strike we are armed to the back teeth with the tools to help us succeed.

Enjoy!

Chris

Database Mirroring Error

I have been setting up multi instance database mirroring for the last couple of days along with some other DR related processes.  I will do a detailed post about these at a later date.  I came across this particular error again (for about the sixth or seventh time) Error Message below;

Msg 1416, Level 16, State 31, Line “LineNumberHere” Database "DatabaseNameHere" is not configured for database mirroring

The reason for me posting this is because each time I have seen this error it has been at the same point in the process of setting up database mirroring.  As always I prefer to avoid using the GUI and Wizard and instead have a set of scripts to setup mirroring.  I had created my endpoints, granted permissions, taken full and log backups of the database(s) in question on the PRINCIPAL Server and restored them to the MIRROR server WITH NORECOVERY.  The next step in my process is to run the below to enable the mirroring partnership;

ALTER DATABASE "DatabaseNameHere" SET PARTNER = 'TCP://FullyQualifiedNameHere:PortNumberHere';

GO

The reason for the failure is that I ran the script on the PRINCIPAL and it should be run on the MIRROR server first, Doh!

I remember the first time I saw this error I followed what a lot of people in various forums advised and amongst other solutions (which also didn’t work) recreated the backups and restored them.  This can be a time consuming process if you are working with large databases so I would advise you to first check that you are running this step on the MIRROR server first as it could save you a considerable amount of time and effort.

Enjoy!

Chris

High Availability Lingo

You have got to love the way the SQL Server team change the lingo in all of their high availability (HA) / disaster recovery (DR) features.  To a novice and even to more seasoned DBA’s it can be confusing, this post will not go into the nitty gritty of how each of these features work but just simply explain the terms commonly used.  I will cover the most common terms used with;

• Log Shipping
• Replication
• Database Mirroring
• AlwaysOn Availability Groups

  

Log Shipping

Primary Server

The primary server in a log shipping configuration is the instance of the SQL Server Database Engine that is your production server.

Primary Database

                The primary database is the database on the primary server that you want to back up to another server.

Secondary Server

                The secondary server in a log shipping configuration is the server where you want to keep a warm standby copy of your primary database.

Secondary Database

                The secondary database is the database on the secondary server that exists as a warm standby of the primary database.

Monitor Server

                The optional monitor server tracks all of the details of log shipping.

Backup Job

                A backup job is created on the primary server instance for each primary database. It performs the backup operation, logs history to the local server and the monitor server, and deletes old backup files and history information.

Copy Job

A copy job is created on each secondary server instance in a log shipping configuration. This job copies the backup files from the primary server to a configurable destination on the secondary server and logs history on the secondary server and the monitor server.

Restore Job

A restore job is created on the secondary server instance for each log shipping configuration. This job restores the copied backup files to the secondary databases.

Alert Job

                If a monitor server is used, an alert job is created on the monitor server instance. This alert job is shared by the primary and secondary databases of all log shipping configurations using this monitor server instance.

Replication

Publisher

The Publisher is a database instance that makes data available to other locations through replication.

Publication

                A publication is a collection of one or more articles from one database.

Article

                An article identifies a database object that is included in a publication.

Distributor

                The Distributor is a database instance that acts as a store for replication specific data associated with one or more Publishers. Each Publisher is associated with a single database (known as a distribution database) at the Distributor.

Subscriber

                A Subscriber is a database instance that receives replicated data.

Subscription

                A subscription is a request for a copy of a publication to be delivered to a Subscriber.

Transactional Replication

                Transactional replication typically starts with a snapshot of the publication database objects and data. As soon as the initial snapshot is taken, subsequent data changes and schema modifications made at the Publisher are usually delivered to the Subscriber as they occur (in near real time). The data changes are applied to the Subscriber in the same order and within the same transaction boundaries as they occurred at the Publisher; therefore, within a publication, transactional consistency is guaranteed.

Merge Replication

                Merge replication, like transactional replication, typically starts with a snapshot of the publication database objects and data. Subsequent data changes and schema modifications made at the Publisher and Subscribers are tracked with triggers. The Subscriber synchronizes with the Publisher when connected to the network and exchanges all rows that have changed between the Publisher and Subscriber since the last time synchronization occurred.

Snapshot Replication

                Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.

Peer to Peer Replication

                Peer-to-peer transactional replication lets you insert, update, or delete data at any node in a topology and have data changes propagated to the other nodes. Because you can change data at any node, data changes at different nodes could conflict with each other. If a row is modified at more than one node, it can cause a conflict or even a lost update when the row is propagated to other nodes.

Snapshot Agent

                The Snapshot Agent is typically used with all types of replication. It prepares schema and initial data files of published tables and other objects, stores the snapshot files, and records information about synchronization in the distribution database. The Snapshot Agent runs at the Distributor.

Log Reader Agent

                The Log Reader Agent is used with transactional replication. It moves transactions marked for replication from the transaction log on the Publisher to the distribution database. Each database published using transactional replication has its own Log Reader Agent that runs on the Distributor and connects to the Publisher.

Distribution Agent

                The Distribution Agent is used with snapshot replication and transactional replication. It applies the initial snapshot to the Subscriber and moves transactions held in the distribution database to Subscribers.

Merge Agent

                The Merge Agent is used with merge replication. It applies the initial snapshot to the Subscriber and moves and reconciles incremental data changes that occur. Each merge subscription has its own Merge Agent that connects to both the Publisher and the Subscriber and updates both.

Queue Reader Agent

                The Queue Reader Agent is used with transactional replication with the queued updating option. The agent runs at the Distributor and moves changes made at the Subscriber back to the Publisher. Unlike the Distribution Agent and the Merge Agent, only one instance of the Queue Reader Agent exists to service all Publishers and publications for a given distribution database.

Database Mirroring

Principal Server

The Principal Server instance serves the database to clients.

Mirror Server

                The Mirror Server instance acts as a hot or warm standby server.

Witness

                High-safety mode with automatic failover requires a third server instance, known as a witness. Unlike the two partners, the witness does not serve the database. The witness supports automatic failover by verifying whether the principal server is up and functioning.

Hot Standby

                Hot Standby is the term used when a database mirroring session is synchronised database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions.

Warm Standby

                Warm Standby is the term used when a database mirroring session is not synchronized; the mirror server is typically available as a warm standby server (with possible data loss).

Operating Modes

There are two mirroring operating modes. One of them, high-safety mode supports synchronous operation.  The second operating mode, high-performance mode, runs asynchronously.

High-Safety

                Under high-safety mode, when a session starts, the mirror server synchronizes the mirror database together with the principal database as quickly as possible.

High-Performance

                Under high-performance mode, the mirror server tries to keep up with the log records sent by the principal server. The mirror database might lag somewhat behind the principal database. However, typically, the gap between the databases is small.

Synchronous

                Under synchronous operation, a transaction is committed on both partners, but at the cost of increased transaction latency.

Asynchronous

Under asynchronous operation, the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance.

Transaction Safety

                If the SAFETY option is set to FULL, database mirroring operation is synchronous, after the initial synchronizing phase. If a witness is set in high-safety mode, the session supports automatic failover.  If the SAFETY option is set to OFF, database mirroring operation is asynchronous. The session runs in high-performance mode, and the WITNESS option should also be OFF.

Role Switching

                Within the context of a database mirroring session, the principal and mirror roles are typically interchangeable in a process known as role switching. Role switching involves transferring the principal role to the mirror server.

AlwaysOn Availability Groups

Availability Group

A container for a set of databases, availability databases, that fail over together.

Availability Database

A database that belongs to an availability group. For each availability database, the availability group maintains a single read-write copy (the primary database) and one to four read-only copies (secondary databases).

Primary Database

The read-write copy of an availability database.

Secondary Database

A read-only copy of an availability database.

Availability Replica

An instantiation of an availability group that is hosted by a specific instance of SQL Server and maintains a local copy of each availability database that belongs to the availability group. Two types of availability replicas exist: a single primary replica and one to four secondary replicas.

Primary Replica

The availability replica that makes the primary databases available for read-write connections from clients and, also, sends transaction log records for each primary database to every secondary replica.

Secondary Replica

An availability replica that maintains a secondary copy of each availability database, and serves as a potential failover targets for the availability group. Optionally, a secondary replica can support read-only access to secondary databases can support creating backups on secondary databases.

Availability Group Listener

A server name to which clients can connect in order to access a database in a primary or secondary replica of an AlwaysOn availability group. Availability group listeners direct incoming connections to the primary replica or to a read-only secondary replica.

Availability Modes

                In AlwaysOn Availability Groups, the availability mode is a replica property that determines whether a given availability replica can run in synchronous-commit mode. For each availability replica, the availability mode must be configured for either synchronous-commit mode or asynchronous-commit mode.

Asynchronous-commit

Is a disaster-recovery solution that works well when the availability replicas are distributed over considerable distances.

Synchronous-commit

Emphasizes high availability over performance, at the cost of increased transaction latency.

Automatic failover

A failover that occurs automatically on the loss of the primary replica. Automatic failover is supported only when the current primary and one secondary replica are both configured with failover mode set to AUTOMATIC and the secondary replica currently synchronized. If the failover mode of either the primary or secondary replica is MANUAL, automatic failover cannot occur.

Planned manual failover (without data loss)

Planned manual failover, or manual failover, is a failover that is initiated by a database administrator, typically, for administrative purposes. A planned manual failover is supported only if both the primary replica and secondary replica are configured for synchronous-commit mode and the secondary replica is currently synchronized (in the SYNCHRONIZED state). When the target secondary replica is synchronized, manual failover (without data loss) is possible even if the primary replica has crashed because the secondary databases are ready for failover. A database administrator manually initiates a manual failover.

Forced manual failover (with possible data loss)

A failover that can be initiated by a database administrator when a planned manual failover is not possible, because either no secondary replica is SYNCHRONIZED with the primary replica (that is, no secondary replica is ready for failover) or the primary replica is not running. Forced manual failover, or forced failover, risks possible data loss and is recommended strictly for disaster recovery. This is the only form of failover supported by in asynchronous-commit availability mode.

Automatic failover set

Within a given availability group, a pair of availability replicas (including the current primary replica) that are configured for synchronous-commit mode with automatic failover, if any. An automatic failover set takes effect only if the secondary replica is currently SYNCHRONIZED with the primary replica.

Synchronous-commit failover set

Within a given availability group, a set of two or three availability replicas (including the current primary replica) that are configured for synchronous-commit mode, if any. A synchronous-commit failover set takes effect only if the secondary replicas are configured for manual failover mode and at least one secondary replica is currently SYNCHRONIZED with the primary replica.

Entire failover set

Within a given availability group, the set of all availability replicas whose operational state is currently ONLINE, regardless of availability mode and of failover mode. The entire failover set becomes relevant when no secondary replica is currently SYNCHRONIZED with the primary replica.

Enjoy!

Chris

How Resilient Is Your Recovery Plan?

In this modern age of technology DBA’s face an ever increasing demand from businesses; our databases must perform, be secure, highly available and scalable and equally as important recoverable.  There is an obsession with the 5 9’s within the industry, and rightly so, but just how resilient is your recovery plan?  Can you honestly say hand on heart you have covered everything?  I can’t, but it isn’t through want of trying.

Performance and Security

Let’s say then that you are happy with the performance of your databases, you have normalised to 3^rd normal form in the vast majority of cases without compromising performance.  All the right security measures are in place, Active Directory Groups with access available only for production DBA’s, you also have a controlled staged release procedure through Development, UAT and Live.   You monitor a wide variety of metrics and analyse them on a weekly basis for auditing and capacity planning.   You have a solid index strategy to squeeze out every possible ounce of performance.  Performance and Security you have covered pretty well but being a DBA you are always looking for ways to make things better.

Highly Available

You have a 2 node active passive Windows Failover Cluster, have tested all the possible failover scenarios and are happy that failovers and failbacks occur successfully.  Again you are happy with the high availability of your environment.

Scalability

You have plenty of room for “growth” both in size and load on the environment, there is also a contingency for additional resources (for ITIL people I mean storage, CPU and memory ;) if required.  Performance and utilisation is monitored and reviewed weekly as part of your standards.  You are comfortable that the environment scales.

Recoverable

You have configured Database Mirroring in high safety with manual failover to prevent false failovers as the DR site in a different country.  Failover has been tested and the databases are served to the applications successfully failback also works a treat.  You take full backups on a Sunday differentials every night and transaction log backups every 15 minutes.  The backups are then backed up to tape and sent to an offsite storage facility.

Good Times (GT’s), so far so good you have ticked all the required boxes up to this point.  Now let’s throw in some scenarios and see how this environment would work.

Performance problems reported

                Your solid release process highlighted a potential risk and the affected processes are rolled back to guarantee performance while the issues are being addressed.

Potential Security Breach

                This was found proactively as during your weekly review you noticed login failures and raised the issue with your security team to resolve.

Running low on disk space

Again this is proactive, based on current growth rates you predict that a volume will run out of space in 6 months, a change request is made and the volume expanded.

A Server in the cluster fails

                No problem here, failover is automatic you resolve the error with the problematic node and add it back into the cluster.

Entire cluster / primary site failure

                Again no problem you manually failover from the principal to the failover partner, the application and operation teams follow their DR plans and hey presto with little downtime your environment is up and serving customers again.

Data Loss

                At 12:00 reports of errors in the application reach your team, Investigation finds that a disgruntled DBA who was working their notice period deleted 100,000 records from the order table at 11:05 on a Friday morning.  A decision is made to recover from backup, the backups are kept on disk for one week so you look at restoring the affected database.  This is where the problem arises.

Your backup script consists of restoring the full backup from Sunday The differential from Thursday night and then all transaction logs from Friday with a STOP AT command at the last transaction before the delete.  The full backup restores fine but the differential backup from Thursday night fails as it is incomplete.  You investigate further and have to result to restoring the full backup from Sunday the differential backup from Wednesday and all transaction logs from Thursday up until 23:45 which is the last transaction log before the incomplete differential.

Yes that’s right you have lost just over 12 hours’ worth of data!  Things didn’t have to be this way though.  The solution is to verify your backups; if you would have verified the consistency of your backups this could have been avoided.

Summary

I know from experience trying to justify the cost of a server can be a painstaking task but justifying one that does not serve clients is even more difficult.  The spin I always use is;

“How much would it cost the company if we lost a day’s worth of data?” 

As a techie it is a no brainer, but persuading someone to part with their money that they see no day to day benefit from having is a different matter.  From their standpoint it is not revenue generating and the risk is worth taking.

In an ideal world all backups would be verified by restoring them but this is simply not practical for everyone as the budget may simply not available.  Take into consideration also that most environments I have worked in have had 40+ SQL Servers running different versions; this would potentially require more than one server to verify the backups.

Recommendations

What I would recommend is;

1 – Get buy in from other team members and line managers, the more people you have that agree with you on this the stronger the case will be when it is presented to the person with the cheque book!

2 - Prioritise your SQL Servers and verify the backups of the most critical databases as often as possible.  If you lose data in some databases you can get by, for example IT Internal databases (SQLSentry, Solarwinds).

Believe me it may one day save you and quite possibly your job!

A Script A Day - Day 19 - Remove Virtual Subscriptions

Today’s script is to help replication performance.  It was something I learned from my resident replication expert Paul Anderton.  Below is a description of virtual subscriptions.

If Immediate_Sync is set when a publication is created then virtual subscriptions can occur.  These can affect the "Distribution Clean Up: distribution" SQL job and the "msrepl_commands" table.  By Default the job runs every 10 mins and removes replicated commands from the "msrepl_commands" table dependant on the @min_distretention value (0 hrs is default).  If virtual subscriptions are present then the @min_distretention value is ignored and all replicated commands will only be removed after the @max_distretention is reached (72 hrs is default).

This script is one I have run on all my servers serving as a distributor to remove the virtual subscribers.  The performance gain in all instances is fantastic the distribution clean up job runs a lot faster and replication latency (number of undistributed commands) is dramatically reduced.

/*

      -----------------------------------------------------------------

      Remove Virtual Subscriptions

      -----------------------------------------------------------------

     

      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

 

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

     

      -----------------------------------------------------------------

*/

-- Set database context

USE distribution;

GO

-- Get undelivered commands

SELECT

      *

FROM

      MSdistribution_status

ORDER BY

      UndelivCmdsInDistDB DESC

-- Check for virtual subscriptions

SELECT

      msp.publication,

      mss.publisher_db,

      mss.publication_id,

      mss.subscriber_id,

      mss.subscriber_db,

      mss.subscription_type,

      mss.[status]

FROM

      distribution.dbo.MSsubscriptions mss

      left join distribution.dbo.MSpublications msp ON mss.publication_id = msp.publication_id

GROUP BY

      msp.publication,

      mss.publisher_db,

      mss.publication_id,

      mss.subscriber_id,

      mss.subscriber_db,

      mss.subscription_type,

      mss.[status];

GO

-- Remove virtual subscriptions

DECLARE @minid INT

DECLARE     @maxid INT

DECLARE @pubname VARCHAR(100)

DECLARE     @pubdb VARCHAR(100)

SELECT

      @minid = MIN(mss.publication_id),

      @maxid =  MAX(mss.publication_id)

FROM

      distribution.dbo.MSsubscriptions mss

      INNER JOIN distribution.dbo.MSpublications msp ON mss.publication_id = msp.publication_id

WHERE

      mss.subscriber_db = 'virtual'

WHILE @minid <= @maxid

BEGIN

      SELECT

            @pubname = msp.publication,

            @pubdb = mss.publisher_db

      FROM

            distribution.dbo.MSsubscriptions mss

      INNER JOIN distribution.dbo.MSpublications msp ON mss.publication_id = msp.publication_id

      WHERE

            mss.subscriber_db = 'virtual'

            AND mss.publication_id = @minid

           

                        EXEC ('

                        EXEC ' + @pubdb + '.dbo.sp_changepublication

                        @publication = ' + @pubname + ',

                        @property = ''allow_anonymous'',

                        @value = ''false'' ;

                        ')

                        EXEC ('

                        EXEC ' + @pubdb + '.dbo.sp_changepublication

                        @publication = ' + @pubname + ',

                        @property = ''immediate_sync'',

                        @value = ''false'' ;

                        ')

SELECT

      @minid = MIN(mss.publication_id)

FROM

      distribution.dbo.MSsubscriptions mss

      inner join distribution.dbo.MSpublications msp ON mss.publication_id = msp.publication_id

WHERE

      mss.subscriber_db = 'virtual'

      AND mss.publication_id > @minid

END;

GO

-- Check for virtual subscriptions

SELECT

      msp.publication,

      mss.publisher_db,

      mss.publication_id,

      mss.subscriber_id,

      mss.subscriber_db,

      mss.subscription_type,

      mss.[status]

FROM

      distribution.dbo.MSsubscriptions mss

      left join distribution.dbo.MSpublications msp ON mss.publication_id = msp.publication_id

GROUP BY

      msp.publication,

      mss.publisher_db,

      mss.publication_id,

      mss.subscriber_id,

      mss.subscriber_db,

      mss.subscription_type,

      mss.[status];

GO

Enjoy!

Chris