Do you need access to Production?

During a recent client meeting about a database migration, I realised that I have never logged into a SQL Server on their production environment. My involvement has been strictly dealing with setting up the new environment and log shipping the backups.

I get that I’m not a full-service DBA for this client, but it got me wondering about the many security discussions I’ve seen and participated in, in the past: that not even a junior DBA might need access to production database systems, if it’s not within the scope of his or her work.

From this experience, I can honestly say that my part of the job has not been impacted by not having full access to the production environment.

Have a look at your own network, take the time to think about who has access and who actually needs it. Chances are, developers really don’t need sa on your production database server, and you will be able to reduce the attack surface of your systems.

Remember that many incidents of data exfiltration are internal.

The Transaction Log Is Very Resilient

Last week I wondered whether a log backup chain would survive a database migration. Unsurprisingly, the answer is “yes”.

I also asked if a log backup taken on the new instance would be usable on the old instance, if the need arose to fail back to the original environment. Unsurprisingly, the answer is “it depends”.

Setting up the Test

Using our test case from last week, we start with a database on a SQL Server 2008 R2 instance.

CREATE DATABASE [TransactionLogChain];
GO

Make sure the database is in Full Recovery Mode, and then make sure we have a full backup, otherwise the database remains in pseudo-simple mode until it is backed up.

-- Full Recovery Mode, Right Now!
ALTER DATABASE [TransactionLogChain]
SET RECOVERY FULL WITH NO_WAIT;
GO

-- Full database backup
BACKUP DATABASE [TransactionLogChain]
TO DISK = N'D:\SQLBackup\TLC.bak';
GO

We now create a table to write to, taking a transaction log backup afterwards.

-- Identity column only
CREATE TABLE [dbo].[TransactionLogChain]
([ID] INT IDENTITY(1,1) NOT NULL);
GO

Insert some rows to have a database worth having:

-- Use the GO <n> batch processor to run 50 times
INSERT INTO [dbo].[TransactionLogChain] DEFAULT VALUES;
GO 50

Take a transaction log backup of this SQL Server 2008 R2 database.

-- Transaction log backup containing table creation
-- and first 50 row inserts
BACKUP LOG [TransactionLogChain]
TO DISK = N'D:\SQLBackup\TLC_01.trn';
GO

That’s enough to begin testing our first scenario because we have to restore this database to our SQL Server 2016 instance. Remember to use NORECOVERY because we have to apply the log afterwards.

-- Full backup is restored first
USE [master];
GO
RESTORE DATABASE [TransactionLogChain]
FROM DISK = N'D:\SQLBackup\TLC.bak'
WITH FILE = 1,
MOVE N'TransactionLogChain' TO N'D:\SQL2016\TLC.mdf',
MOVE N'TransactionLogChain_log' TO N'D:\SQL2016\TLC_log.ldf',
WITH NORECOVERY;
GO

Apply the log backup. A good habit is to restore all log files with the NORECOVERY option as well, just in case. When we are sure we want to bring the database online, then we can simply issue a WITH RECOVERY command.

-- Log backup is restored next
USE [master];
GO
RESTORE LOG [TransactionLogChain]
FROM DISK = N'D:\SQLBackup\TLC_01.trn'
WITH NORECOVERY;
GO

Bring the database online:

RESTORE DATABASE [TransactionLogChain] WITH RECOVERY;
GO

We now have an online version of our database on the SQL Server 2016 instance.

Let’s run the insert script again to add another 50 rows to the table.

-- Use the GO <n> batch processor to add another 50 rows
INSERT INTO [dbo].[TransactionLogChain] DEFAULT VALUES;
GO 50

A quick SELECT tells us that the table has 100 row(s) affected.

Test 1: Using a log from a new instance

Theoretically, nothing is wrong with this process so far. SQL Server considers the log backup chain unbroken. We can take a log backup on the new instance and use that in connection with the existing log backup chain to restore our database to its current state on SQL Server 2016.

-- Transaction log backup containing extra 50 rows
BACKUP LOG [TransactionLogChain]
TO DISK = N'D:\SQLBackup\TLC_02.trn';
GO

Let’s test this. First we must drop the [TransactionLogChain] database from the 2016 instance.

USE [master];
GO
DROP DATABASE [TransactionLogChain];
GO

Restore all three files now: the full backup and transaction log backup from 2008 R2, and the 2016 transaction log. Finally, bring the database online.

USE [master];
GO
RESTORE DATABASE [TransactionLogChain]
FROM DISK = N'D:\SQLBackup\TLC.bak'
WITH FILE = 1,
MOVE N'TransactionLogChain' TO N'D:\SQL2016\TLC.mdf',
MOVE N'TransactionLogChain_log' TO N'D:\SQL2016\TLC_log.ldf',
WITH NORECOVERY;
GO

RESTORE LOG [TransactionLogChain]
FROM DISK = N'D:\SQLBackup\TLC_01.trn'
WITH NORECOVERY;
GO

RESTORE LOG [TransactionLogChain]
FROM DISK = N'D:\SQLBackup\TLC_02.trn'
WITH NORECOVERY;
GO

RESTORE DATABASE [TransactionLogChain] WITH RECOVERY;
GO

USE [TransactionLogChain];
GO
SELECT * FROM [dbo].[TransactionLogChain];
GO

And our result is the same:

(100 row(s) affected).

Second Test: Applying a new log to an old instance

Now let’s take a look at applying a log backup chain to the original instance.

The full backup and first log file restore just fine, but on the log backup taken from 2016, we get the following error:

Msg 3241, Level 16, State 0, Line 17
The media family on device 'D:\SQLBackup\TLC_02.trn' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 17
RESTORE LOG is terminating abnormally.

As I originally suspected, the version of SQL Server on which the newer log backup is taken matters.

I was able to take a SQL Server 2008 R2 backup, restore it to SQL Server 2016, run a log backup, and restore the entire chain on SQL Server 2016.

I was not able to restore the 2016 log to the 2008 R2, 2012 or a 2014 instance, however.

If for whatever reason we need to fail back to the original environment, using logs taken in the new environment, we must be running the same version of SQL Server.

The build does not seem to matter as much, but honestly, don’t take my word for it.

How does this help me?

Let’s say we are migrating a massive database and have used log shipping to keep the old and new environments in sync. As soon as we cut over to the new environment, we can continue taking log backups and be confident that the existing log shipping files and original full backup can be retained, until our next full backup.

Please feel free to leave a comment here, or find me on Twitter at @bornsql. I would love to hear your thoughts.

The Resilience of the Log Backup Chain

Much has been written about the log backup chain in SQL Server, where we are able to restore a database to a point in time using a combination of full, differential and transaction log backups.

This requires a database being in Full Recovery Mode and transaction log backups being taken at regular intervals.

If the database is put into simple recovery mode, and then switched back to full recovery mode, the log backup chain is broken, and a full or differential backup must be taken to fix it.

I’ve been working on a long-term project to migrate over fifty databases running on SQL Server 2008 R2 to Azure virtual machines running SQL Server 2014.

We use a custom form of Log Shipping to keep the Azure environment in sync with production. The log chain is stored on Azure Blob Storage, and each log file is restored every half hour to the new environment using a custom PowerShell script.

This allows us to have a very small maintenance window for the final cutover, requiring just the tail of the log backup to be restored.

Last week, I wondered aloud what would happen if we had to fail back to the original system, after processing transactions on the new environment. Would a log backup taken on the Azure environment work on the older servers? Technically speaking, the log backup chain would not have been broken. Perhaps a version conflict?

Let’s look at a test case.

  1. Create a database in SQL Server 2008 R2 with Full Recovery, and perform a full backup, plus several transaction log backups.
  2. Restore the full backup and log chain to a new SQL Server 2016 instance, perform some actions here, and then perform another log backup from the new instance.
  3. Test if the newest log backup can be applied, along with the older ones, in a recovery on SQL Server 2016. I say “Yes”.
  4. Test if the newest log backup can be applied, along with the older ones, in a recovery on SQL Server 2008 R2. I say “No”.

Tune in next week for the results. You’re always welcome to comment here or discuss it on Twitter at @bornsql.

Career Limiting Moves – Reply All

In a recent episode of a Netflix show called Grace & Frankie, a vitriolic and profanity-ridden email with very damaging statements was accidentally sent to a mailing list.

Fortunately for the characters involved, Siri auto-corrected most of the really bad stuff, and Vin Diesel became an unwitting participant in the storyline.

Unfortunately, there’s real life, and in real life I’m sure we all have stories where we hit Reply All in our email unintentionally, with detrimental effects.

Instead of telling you my own story here, I’d like to hear from you, either in the comments or on Twitter (@bornsql). What was your worst Reply All incident?