Automated Rapid Recovery during Database Code Releases using Snapshots

Question

Automated Rapid Recovery during Database Code Releases using Snapshots

NJ-DBA

SSChampion

Points: 13834
More actions
December 8, 2015 at 12:15 am

#303144

Comments posted to this topic are about the item Automated Rapid Recovery during Database Code Releases using Snapshots

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Robert_sqlapprenticeDOTnet SSC-Addicted Points: 433 More actions · Answer 1

Hi,

first of all, thanks for you work. I think this article is very useful for me. I think I will consider to use it in my Solution.

Just one remark for the process flowchart.

It looks like this now:

deploy code... -> successful?(no) -> revert to snapshots -> drop snapshot -> rework code -> deploy code...

But I think it should be look like:

deploy code... -> successful?(no) -> revert to snapshots -> drop snapshot -> rework code -> take snapshot -> deploy code...

OR

deploy code... -> successful?(no) -> revert to snapshots -> rework code -> deploy code...

Robert

NJ-DBA SSChampion Points: 13834 More actions · Answer 2

NJ-DBA

SSChampion

Points: 13834

December 8, 2015 at 5:14 am

#1844620

Yes, you're right about the flowchart.

denist80 SSC Rookie Points: 42 More actions · Answer 3

Hello,

Thank you for the article!

How would this play if a VLDB is a primary replica in AG? Say, someone had a bad release and now needs to revert to the snapshot. It will break the secondary replica, correct?

Thanks,

Denis

NJ-DBA SSChampion Points: 13834 More actions · Answer 4

denist80 (12/8/2015)
Hello,
Thank you for the article!
How would this play if a VLDB is a primary replica in AG? Say, someone had a bad release and now needs to revert to the snapshot. It will break the secondary replica, correct?
Thanks,
Denis

Not necessarily. You can revert a primary in Availability Groups if you first disconnect the secondaries, then revert, then re-establish the secondaries. I've not tested that, but I think that's the textbook method for using snapshots in conjunction with AG or mirroring.

penglin 8198 SSC Enthusiast Points: 188 More actions · Answer 5

Love the chart.

I don't have Enterprise Edison in our lab, can't test. Tried SP_SNAPSHOT_ALL_DATABASES on a db with 4 files, I got 4 'create' statements. Is this intended?

NJ-DBA SSChampion Points: 13834 More actions · Answer 6

Yep, the code does a select of the sql statement before executing it.

penglin 8198 SSC Enthusiast Points: 188 More actions · Answer 7

I found a developer's edition in the lab. Below are the @stmt:

create database TSQL2012_Snapshot on (name=TSQL2012,filename='M:\MSSQL\DATA\TSQL2012.mdf.ss') as snapshot of TSQL2012

create database TSQL2012_Snapshot on (name=TSQL2012_2,filename='M:\MSSQL\DATA\TSQL2012_2.ndf.ss') as snapshot of TSQL2012

errors:

Msg 5127, Level 16, State 1, Line 8

All files must be specified for database snapshot creation. Missing the file "TSQL2012_2".

Msg 5127, Level 16, State 1, Line 9

All files must be specified for database snapshot creation. Missing the file "TSQL2012".

NJ-DBA SSChampion Points: 13834 More actions · Answer 8

As written, the proc does not account for databases with multiple data files. It would be a pretty straightforward change to make though.

Ash SSC Eights! Points: 947 More actions · Answer 9

Thanks for the share.

@penglin,

For multiple database files, you can use something like below. Add it to the original code or write up something

---this script generates create snapshot code. run in text mode.

---set [query option] --> [result] --> [text] --> [Maximum number of chracters displayed in each column to 8000], before running in text mode.

USE [dbname]

GO

SET NOCOUNT ON;

WITH preamble(c) AS (SELECT 'IF EXISTS (SELECT name FROM sys.databases WHERE name = '''+ db_name() + '' + '_Snapshot'')'+ CHAR(10)+

'DROP DATABASE ['+ db_name() + '' + '_Snapshot]'+CHAR(10)+CHAR(10)+

'GO'+ CHAR(10)+CHAR(10)+

'CREATE DATABASE [' + db_name() + '_Snapshot] ON'),

files(c) AS (SELECT '(name=''' + name + ''', filename=''' + physical_name + '.snap'')' + CHAR(10) FROM sys.database_files WHERE type = 0),

filescoalesce (c) AS (SELECT c + ',' FROM files FOR XML PATH('')),

lastline(c) AS (SELECT 'AS SNAPSHOT OF [' + db_name() + ']' + CHAR(10) + CHAR(10) + 'GO' + CHAR(10))

SELECT c [--] from preamble

UNION all

SELECT left(c, len(c) -2) from filescoalesce

UNION all

SELECT c FROM lastline

jeffrey yao SSCarpal Tunnel Points: 4365 More actions · Answer 10

Always be cautious about using snapshot for VLDB dbs when your deployment code involves some big data change, such as dropping/recreating indexes, deleting/inserting big chunk of data. Other than the size limit of the snapshot itself (I believe it has a limit under 200GB? or even lower like 120GB? at least that what I encountered in sql 2005 on my 1.5+TB db).

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 11

akljfhnlaflkj

SSC Guru

Points: 76202

December 9, 2015 at 6:26 am

#1844877

Thanks for the good article and code.

NJ-DBA SSChampion Points: 13834 More actions · Answer 12

Iwas Bornready (12/9/2015)
Thanks for the good article and code.

Appreciate the feedback!