Automated Rapid Recovery during Database Code Releases using Snapshots

  • NJ-DBA

    SSChampion

    Points: 13832

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

  • Robert_sqlapprenticeDOTnet

    SSC-Addicted

    Points: 433

    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: 13832

    Yes, you're right about the flowchart.

  • denist80

    SSC Rookie

    Points: 42

    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: 13832

    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

    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: 13832

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

  • penglin 8198

    SSC Enthusiast

    Points: 188

    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: 13832

    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: 826

    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: 4352

    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

    Thanks for the good article and code.

  • NJ-DBA

    SSChampion

    Points: 13832

    Iwas Bornready (12/9/2015)


    Thanks for the good article and code.

    Appreciate the feedback!

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

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