Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have been working with SQL server on and off since SQL Server 6.5, the old advice that still rings in my head was never to do an in-place upgrade.

I'm currently upgrading my 2008 R2 DEV and TEST systems to SQL Server 2012 and need to use the same hardware. The thought of not having to restore my Reporting services configuration is very attractive and i'm really up against the wall time wise. There is no analysis services involved or anything unusual or non-standard - only the database engine and reporting services are installed.

Has anyone experienced serious issues with in-place upgrades? Or should I reevaluate my position about in-place upgrades?

share|improve this question
    
I opted to do an inplace upgrade on 1 server with reporting services installation. The only issue I came across was attempting to use import export tool in SSMS with native client 11. Attempting the transformation failed with an error about unrecognized datatypes. the workaround I used was to save the package and run it in SQL Data Tools(BIDS replacement) which worked fine. I think this must be to do with the config file for SSIS 2008 not being overwritten. It occured to me later, you may simply be able to change the native client back to 10. – DamagedGoods Jan 10 '13 at 9:37
up vote 78 down vote accepted

Really Short Answer - In Place is okay. You can review your configuration afterwards and implement the best practices for SQL Server 2012.

A Longer Answer on SQL Server Upgrades/Migrations

So this is an opinion thing and there isn't a necessarily wrong or right answer but I prefer migration style upgrades over in-place for a lot of reasons. That being said - some of my clients for various reasons have had no choice but to do an in-place and really since SQL Server 2005, in-place upgrades haven't been as bad as they used to be.

Why I Prefer A Migration to an In-Place Upgrade

  • Easier Rollback - If something goes wrong you can rollback by simple saying "we aborted the upgrade.. Please change connection strings to old server while we resolve this". With an in-place you are fixing it or you are down.
  • Refresh Hardware - Hardware changes rapidly. You can easily get stuck on hardware that was right for your company 4 years ago but not right for today and the next four years with an in-place upgrade. You will likely have to do a migration at some point anyway for the new hardware.
  • Feel Better - Sure... This one is subjective, but it feels good knowing you are starting with a new OS installation, a new SQL installation with no cobwebs from the person on the job before you (or you before you knew what you knew today) that could possibly cause you headaches in the future.
  • New OS - A migration gives you a chance to start with a new OS version if you aren't on the latest and greatest today.
  • You Can Test It - Ever want to get a set of baselines on a new machine before you install SQL and cloud it up with databases and usage? You can do that now.
  • Sometimes it is easier to sneak in best practices - Maybe the SQL Server service account was a local admin. Maybe Builtin Administrators is in the SA server role. Maybe things have been sort of hacked together to make it work before. You can fix that all and start fresh.
  • Free test environment and extra sleep - It is a great benefit to have an environment you can work in ahead of the actual cutover day when you make this new environment live. Doing a migration to a new environment means you can build it during business hours, well ahead of your actual cutover day and test it in many ways ahead of time. You can run full regression testing on all applications and systems for days and have a great peace of mind before you actually do the final set of restores/attaches and cutover all applications and access to the new environment.
  • You don't have to do it all at once - A very common situation I bump into is an environment that is trying to consolidate to just a few instances. Perhaps one per version, perhaps one per "tier" and version. A lot of these projects have different timelines for various applications and databases based on testing, project plans and vendor certification timeliness. Doing the migration means you can move those databases that are ready, when they are ready and still handle requests for those databases that can't move for one reason or another.

Mind you I am not saying you have to do this as a migration. In-Place works and it works well if you are not planning on the buying new hardware in your budget and can't do that for this upgrade. The support in the upgrade process is so much better than it was in the 6.5 days so you are not putting yourself in a bad position doing this.

If you do plan on doing in-place for dev/test but want to do a migration for production you might consider doing at least one migration before production. This way you can work out your checklist ahead of time and deal with any potential issues that you weren't thinking of.

Attach/Detach vs. Backup/Restore for Migrations

If you decide to go with the migration approach, there is still one more decision you may still have a debate over and that is how you move your database to the new environment. You can either detach your database from the old server and attach it to the new or back it up and restore it there.

I prefer backup/restore. The biggest advantage I hear about detach/attach is that it saves some time. For me backup/restore wins for a few reasons:

  • Keep the old accessible - This allows you to still have an accessible database on the source server. detach/attach should do the same, but it will require a few steps and there is room for human error with detach/attach that could complicate this.
  • You are guaranteeing that you have a backup - Instead of just taking a database from a detach and potentially forgetting a backup step, you've made sure that you've taken that backup.
  • Human error - If you delete the wrong file, forget where you are sending something or otherwise mess up your steps, you risk much by moving the data and log files around for your database. Now you can mitigate this by copying instead of cutting (and if you do detach, you should get out of the cut and paste habit) but you could stil mess up. SQL Server is no longer locking those files and it's just too easy to delete a file accidentally for me to risk it.
  • It isn't really that slower - Taking a backup and copying it is a bit more time, but it isn't that much that I am willing to pay the extra risk for it. In fact - using full recovery model and log backups, you can take the downtime down even lower for cutovers as described below in "How to Make the Migration Approach Faster"

If you do decide to do the backup/restore - that means your old source database will still be online. I like to bring that database offline after taking the backup. I sometimes go a step further and take the whole SQL instance offline after I've scripted out security, jobs, linked server, certificates, database mail settings, and other instance wide information. This avoids an issue during testing where someone says "Everything looks great!" only to realize a day or two later that they have been talking to the old database on the old server. Taking those databases offline or the whole instance offline allows you to prevent those false positives and the mess they make.

How to Make the Migration Approach Faster

You can minimize the downtime required for the cutover from an old to new environment for a busy production environment with little downtime by utilizing the full recovery model. Basically - stage the environment you are migrating to by restoring the latest full backup, any differential backups and any already taken log backups specifying NORECOVERY - then all you will have to do for the final cut over is restore the log backups that were not yet restored and the final log backup you wish to restore specifying WITH RECOVERY. This way for a large database, the actual cutover downtime window can be drastically minimized by paying for the cost of the full, differential and most log restores ahead of the downtime window. Thanks to Tao for pointing this out in the comments!

How to Make the In-Place Upgrade Safer

A few things you can do to improve your experience and results when choosing the in-place approach.

  • Backup - Take appropriate backups of all user and system databases of your environment ahead of time and ensure they are good (I'm paranoid.. I actually would restore them someplace first to really know they are good.. May be wasting your time.. But you may thank yourself in the event of a disaster).. Script out any configuration information about the SQL and OS installation at that environment.
  • Test things well before you start - Verify that you have a good environment and good databases. You should be doing things like looking at the error logs and running DBCC CHECKDB on a regular basis, but before doing an in-place upgrade is a great time to start. Fix any issues ahead of time.
  • Ensure OS health - Don't just make sure that SQL is healthy, make sure your server is healthy. Any gnarly errors in your system or application error event logs? How is your free space?
  • Prepare for the worst - I had a blog post series awhile ago that went on the premise that if you aren't preparing for failure - you are actually preparing to fail.. I still believe that. So think through the issues you may have and deal with them accordingly ahead of time. Get yourself in the "failure" mindset and you will think of things that you wouldn't have otherwise.

The Importance of Upgrade or Migration Checklists

If you do decide to do an upgrade (whether in place or migration) you should seriously consider creating a checklist and using this checklist in each environment. You should include a bunch of things in this checklist, not the least of which:

  1. At the Start - Do some things like perform a test upgrade, test your applications on the latest Database Compatibility level and consider running a tool like the SQL Server Upgrade Advisor ahead of time to see what sort of tasks you need to complete before doing the SQL Server upgrade or migration.
  2. Pre Steps - Cleanup, OS tasks, patching ahead of time, preparing applications for the upgrade (clean shutdowns, connection string work), backups, etc.
  3. Upgrade/Migration Steps - Everything you have to do for the upgrade or migration to succeed and in the right order. Installation, changing (or not changing depending on your testing and approach) compatibility mode changes to databases, etc.
  4. Post Migration/Upgrade Steps - Various tests, post new version or new server configuration options, best practice implementation, security changes, etc.
  5. Rollback Steps - All along the way you should have rollback steps and milestones. If you get this far and this happens, what will you do? What are "do a full rollback" criteria? And how do you do that rollback (reverse connection string changes, change back settings, go back to the old version, reinstall if an in place, point back to the old server if a migration, etc.)

And then have the person who will be doing the production upgrade follow the checklist in some environment other than production - especially one that closes resembles production if possible ("South of prod", as I say...) and note any issues or points where they had to divert from the checklist or improvise because of a lack in the checklist. Then get the changes merged in and have fun with your production change.

I can not over stress the importance of testing thoroughly post migration or upgrade and ahead of your migration enough. Making a rollback decision in the midst of an upgrade should be an easy one - especially during a migration. If there is something uncomfortable, rollback and figure it out if you can't troubleshoot it effectively and reliably in the heat of the migration. Once you are live on this new environment and users connect - rollback becomes a difficult task. You can't restore a SQL Server database to an earlier version. That means manual work and data migrations. I always wait a couple weeks to kill the old environment but you should do all you can to avoid needing that old environment by finding all of your issues before your live users ever touch the new environment. Preferably before you even kick off the upgrade/migration.

Quick note about SQL Server Reporting Services migration/upgrade Migrating an SSRS installation isn't quite a herculean task that many think it is. This technet/books online article is actually quite handy. One of the most important admonitions in that article is "Back up the encryption keys" especially if you have a lot of saved sensitive information like scheduled report e-mail recipient e-mail addresses, connection information for a multitude of connections, etc. You can ask one of my clients from awhile back how important that is. They know because I messed that step up and spent quite a lot of time modifying report schedules and connection string permissions.

share|improve this answer
4  
Mike's answers hit the points very well. I'd read his 'a bit more' section and use the risk analysis I mentioned to come up with a decision. – Ali Razeghi Nov 27 '12 at 1:21
11  
+1 fantastic, thorough answer that most of us are way too lazy for... :-) – Aaron Bertrand Nov 27 '12 at 1:50

In my experience, the same decision making process should be made as prior. AFAIK there hasn't been any 'world changers' with SQL Server installation, within the MS SQL Server product in itself, and the potential problems you have when rolling out software with millions of lines of code. Something bad could happen and now you're stuck with no 'ROLLBACK' option.

You do however have other alternatives in place. You could consider making a snapshot of the system, restore elsewhere, perform the upgrade and see what happens. This test should give you a lot of comfort, but it doesn't absolutely guarantee no problems will arise on the prod box. However, this is an option that was not available back in SQL 6.5 days.

I would just assume the worst case scenario. You do a in place upgrade and it fails miserably. You have to then recover from this within your RTO and RCO. Does the business understand the risks and do you have plans in place to mitigate it?

If the business is not OK with this, then don't do it would be my advice.

share|improve this answer

If you have your servers running in a virtual environment, you can perform a snapshot on a clone and then apply the in-place upgrade and test the instance to verify the upgrade was successful. If it works, you can apply the snapshot and make the clone the production server. If it goes poorly, you can delete the snapshot and go back to the pre-upgrade image to try again, or delete the clone and to a full migration.

share|improve this answer
    
Only if the storage is also virtualized and part of the snapshot. If the storage is directly attached to the VM it won't be 'rolled back' when the snapshot is restored... – Remus Rusanu Dec 29 '13 at 17:08

Due to a large hardware investment, we were required to upgrade just the OS while keeping the current SQL Server version (2012, 3 servers, 22 instances, ~300 databases). No complex setups like mirroring, etc.

This example doesn't match the question exactly since SQL Server isn't being upgraded. I think this is still a good answer because the steps shown would actually be simpler than a true in place migration.

Overview: An external drive was attached to take full backups mainly as a precaution. Only the model and msdb will actually restored from the external drive. The ldf/mdf were left in place for detach/attach. Some local accounts were referenced within the DBs. After they were re-created in the OS, the references within the DB were re-created (since SIDs may change).

Then here were the steps that worked for us:

1) Take note of server level settings which will be restored in steps 12 (Server Roles) and 18 through 23.

2) Patch SQL Server 2012 to SP3 (consistency required if we want to restore any system dbs).

3) Verify versions match on each instance. "Select @@version"

4) Generate these 6 scripts by running this script. Redgate SQL Multiscript is a huge timesaver if there are many instances (Adjust the Tools -> Options => Line Length to the max (8192) and then use the Text Output).

  • Backup
  • Restore
  • Detach
  • Attach
  • Recreate Logins
  • Relink Users to Logins

    -- (1) BACKUP / (2) RESTORE
    --    
    --*** SET THESE to external drive location
    --*** and create the Destination Directories
    declare 
        @backupInstanceDir  varchar(300) = 'F:\ExternalDriveBackups\' + replace(@@servername, '\', '_'),
        @dateSuffix         varchar(100) = '2015-12-14'; 
    
    if (object_id('tempdb..DatabaseStatus') is not null)
    drop table #DAtabseSTatus;
    
    select 
        d.name DbName, 
        d.state_desc DbState,
        d.user_access_desc UserMode,
        convert(bit, (d.is_read_only * -1 + 1)) as IsWritable,
        d.is_trustworthy_on as IsTrustWorthy,
        d.is_in_standby IsInStandby,
        d.recovery_model_desc RecoveryModel,
        suser_sname(d.owner_sid) as Owner,
        convert(bit, 
            case when d.database_id <= 4 or d.is_distributor = 1
                then 1
                else 0
            end) as IsSystemDb,
        mf.type_desc as FileType,
        mf.name FileName,
        mf.state FileState,
        mf.state_desc FileStatDesc,
        mf.physical_name PhysicalName,
        mf.type as FileTypeId    
    into #DatabaseStatus
    from
        sys.master_files AS mf
    join sys.databases AS d
    ON  mf.database_id = d.database_id
    where
        1=1
    order by
        d.name,
        mf.physical_name;
    
    if object_id('tempdb..#sqlOut') is not null
        drop table #sqlOutBU
    
    if object_id('tempdb..#sqlOut') is not null
        drop table #sqlOutRE
    
    create table #sqlOutBU
    (
        Command nvarchar(max) not null,
        Row int identity(1,1) not null primary key
    );
    
    create table #sqlOutRE
    (
        Command nvarchar(max) not null,
        Row int identity(1,1) not null primary key
    );
    
    insert into #sqlOutBU select char(10) + '-- BACKUP SCRIPT' + char(10);
    insert into #sqlOutRE select char(10) + '-- RESTORE SCRIPT' + char(10);
    
    
    insert into #sqlOutBU select char(10) + char(10) + '/* ---------------------------------------------------------------------------------------------' + char(10) + 
    'ServerName: ' + @@servername + char(10) + 'ServiceName: ' + @@servicename + char(10) + 'Version: ' + @@version + 
    '--------------------------------------------------------------------------------------------- */';
    
    insert into #sqlOutRE select char(10) + char(10) + '/* ---------------------------------------------------------------------------------------------' + char(10) + 
    'ServerName: ' + @@servername + char(10) + 'ServiceName: ' + @@servicename + char(10) + 'Version: ' + @@version + 
    '--------------------------------------------------------------------------------------------- */';        
    
    PRINT '--Script for Backing up all DBs in a SQL Server Instance to a specific location' 
    
    SET nocount ON 
    
    insert into #sqlOutBU select char(10) + 
    '--' + char(10) + '-- BACKUP ' + @@servername + '--' + char(10) + 
    'use [Master]; set deadlock_priority high;' + char(10);
    
    insert into #sqlOutRE select '
    -- RESTORE
    --
    -- BE SURE TO BACKUP SYSTEM DBS TO AN ALTERNATE LOCATION JUST BEFORE RESTORING!
    --
    use [Master]; set deadlock_priority high;' + char(10);
    
    DECLARE @dbname nvarchar(128) 
    declare dblist_cursor cursor fast_forward for 
    select [name] from master.sys.databases where [name] != 'tempdb'
    order by iif(database_id <= 4, '0', '1') + [name]
    
    open dblist_cursor 
    fetch next from dblist_cursor into @dbname 
    
    while @@fetch_status = 0 
    begin 
    
        declare @bak nvarchar(300) = @backupInstanceDir + '\' + @dbname + '_' + @dateSuffix + '.bak';
    
        insert into #sqlOutBU select char(10) + 'backup database [' + @dbname + '] to disk = ''' + @bak + ''' WITH COPY_ONLY, NOFORMAT, NOINIT, ' + char(10) + 
            'NAME = N''' + @dbName + '-Full'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 25;';
    
        insert into #sqlOutRE select 'restore database [' + @dbName + '] from disk = ''' + @bak + ''' WITH FILE = 1,' + char(10) +
        (
            select '    move ''' + FileName + ''' to ''' + PhysicalName + '''' From #DatabaseStatus
            where FileType = 'Rows' and DbName = @dbName
        ) + ',' + char(10) +
        (
            select '    move ''' + FileName + ''' to ''' + PhysicalName + '''' From #DatabaseStatus
            where FileType = 'Log' and DbName = @dbName
        ) + ',' + char(10) +
        '    NOUNLOAD, REPLACE, STATS = 25;' + char(10);               
    
        fetch next from dblist_cursor into @dbname 
    end 
    
    close dblist_cursor 
    deallocate dblist_cursor 
    
    insert into #sqlOutBU select char(10) + 'go' + char(10);
    insert into #sqlOutRE select char(10) + 'go' + char(10);
    
    select Command from #sqlOutBU order by Row; -- BACKUP SCRIPT
    select Command from #sqlOutRE order by Row; -- RESTORE SCRIPT
    
    go
    
    
    
    --
    -- (3) DETACH  -  Org Author: Artemakis Artemiou
    --      
    
    if object_id('tempdb..#sqlOutDT') is not null
        drop table #sqlOutDT
    
    create table #sqlOutDT
    (
        Command nvarchar(max) not null,
        Row int identity(1,1) not null primary key
    );
    
    insert into #sqlOutDT select char(10) + '-- DETACH all DBs from a SQL Server Instance' + char(10);      
    
    insert into #sqlOutDT select char(10) + char(10) + '/* ---------------------------------------------------------------------------------------------' + char(10) + 
    'ServerName: ' + @@servername + char(10) + 'ServiceName: ' + @@servicename + char(10) + 'Version: ' + @@version + 
    '--------------------------------------------------------------------------------------------- */';
    
    SET nocount ON 
    
    insert into #sqlOutDT select char(10) + '--' + char(10) + '-- DETACH ' + @@servername + char(10) + '--' + char(10) + '
    use MAster; set deadlock_priority high;' + char(10) + char(10);
    
    DECLARE @dbname nvarchar(128) 
    DECLARE dblist_cursor CURSOR fast_forward FOR 
    SELECT [name] 
    FROM   master.sys.databases 
    WHERE  database_id > 4 
    
    OPEN dblist_cursor 
    FETCH next FROM dblist_cursor INTO @dbname 
    
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
        insert into #sqlOutDT select
        'alter database ' + @dbname + ' set single_user with rollback immediate;' + char(10) +
        'EXEC sp_detach_db ''' + @dbname + ''', ''true'';' + char(10);
        FETCH next FROM dblist_cursor INTO @dbname 
    END 
    
    CLOSE dblist_cursor 
    DEALLOCATE dblist_cursor 
    
    insert into #sqlOutDT select char(10) + 'go' + char(10);
    select Command from #sqlOutDT order by Row;
    
    go
    
    
    
    --
    -- (4) ATTACH  -  Org Author: Artemakis Artemiou
    --    
    
    if object_id('tempdb..#sqlOut') is not null
        drop table #sqlOutAT
    
    create table #sqlOutAT
    (
        Command nvarchar(max) not null,
        Row int identity(1,1) not null primary key
    );
    
    insert into #sqlOutAT select char(10) + '-- ATTACH ALL DBs to a SQL Server Instance' + char(10);
    
    insert into #sqlOutAT select char(10) + char(10) + '/* ---------------------------------------------------------------------------------------------' + char(10) + 
    'ServerName: ' + @@servername + char(10) + 'ServiceName: ' + @@servicename + char(10) + 'Version: ' + @@version + 
    '--------------------------------------------------------------------------------------------- */';
    
    SET NOCOUNT ON
    
    insert into #sqlOutAT select char(10) + '--' + char(10) + '-- ATTACH ' + @@servername + char(10) + '--' + char(10) + 
    'use MAster;' + char(10) + char(10);
    
    DECLARE @dbname nvarchar(128);
    
    DECLARE DBList_cursor CURSOR fast_forward FOR 
    select [name] from master.sys.databases where database_id > 4
    order by name;
    
    OPEN DBList_cursor
    
    FETCH NEXT FROM DBList_cursor 
    INTO @dbname
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
    declare @attach_TSQL_script varchar(max)
    set @attach_TSQL_script=''
    set @attach_TSQL_script=@attach_TSQL_script+'CREATE DATABASE ' + @dbname +' ON ' 
    
    declare @tsql varchar(max),@filename varchar(max)
    set @tsql='DECLARE DBFiles_cursor CURSOR FOR select [filename] from '+ @dbname + '.sys.sysfiles'
    
    execute (@tsql) 
    
    PRINT '--'+@dbname 
    
    OPEN DBFiles_cursor
    FETCH NEXT FROM DBFiles_cursor INTO @filename
    
    WHILE @@FETCH_STATUS = 0
    BEGIN   
    set @attach_TSQL_script=@attach_TSQL_script+ char(10)+'    (FILENAME = '''+ @filename +'''),' 
    FETCH NEXT FROM DBFiles_cursor INTO @filename
    END
    
    set @attach_TSQL_script=SUBSTRING(@attach_TSQL_script,0,len(@attach_TSQL_script))
    set @attach_TSQL_script=@attach_TSQL_script+ char(10) +'    FOR ATTACH;';
    
    insert into #sqlOutAT select @attach_TSQL_script + char(10);
    
    PRINT @attach_TSQL_script 
    PRINT ''
    
    CLOSE DBFiles_cursor
    DEALLOCATE DBFiles_cursor
    
    FETCH NEXT FROM DBList_cursor 
    INTO @dbname
    
    END 
    
    CLOSE DBList_cursor
    DEALLOCATE DBList_cursor
    
    insert into #sqlOutAT select char(10) + 'go' + char(10);
    select Command from #sqlOutAT order by Row;
    go
    
    
    
    --
    -- (5) GENERATE A 'RE-CREATE LOGINS' SCRIPT
    --
    -- This script was modified from a version that was designed to copy from one server to another:
    --      http://stackoverflow.com/a/5983773/538763
    --
    
    
    USE [master]
    
    if object_id('tempdb..#sqlOut') is not null
    drop table #sqlOut;
    
    create table #sqlOut
    (
    Command nvarchar(max) not null,
    Row int identity(1,1) not null primary key
    );
    
    insert into #sqlOut select char(10) + '-- RECREATE LOGINS' + char(10);
    
    
    insert into #sqlOut select char(10) + char(10) + '/* ---------------------------------------------------------------------------------------------' + char(10) + 
    'ServerName: ' + @@servername + char(10) + 'ServiceName: ' + @@servicename + char(10) + 'Version: ' + @@version + 
    '--------------------------------------------------------------------------------------------- */';
    
    insert into #sqlOut select 'use Master;' + char(10);
    go
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    declare @Debug bit = 0;
    declare @PartnerServer varchar(100) = @@SERVICENAME;  -- use current server before it is shutdown (disabled below)
    
    declare
        @MaxID int,
        @CurrID int,
        @SQL nvarchar(max),
        @LoginName sysname,
        @IsDisabled int,
        @Type char(1),
        @SID varbinary(85),
        @SIDString nvarchar(100),
        @PasswordHash varbinary(256),
        @PasswordHashString nvarchar(300),
        @RoleName sysname,
        @Machine sysname,
        @PermState nvarchar(60),
        @PermName sysname,
        @Class tinyint,
        @MajorID int,
        @ErrNumber int,
        @ErrSeverity int,
        @ErrState int,
        @ErrProcedure sysname,
        @ErrLine int,
        @ErrMsg nvarchar(2048);
    
    declare @Logins Table (LoginID int identity(1, 1) not null primary key,
                        [Name] sysname not null,
                        [SID] varbinary(85) not null,
                        IsDisabled int not null,
                        [Type] char(1) not null,
                        PasswordHash varbinary(256) null)
    declare @Roles Table (RoleID int identity(1, 1) not null primary key,
                    RoleName sysname not null,
                    LoginName sysname not null)
    declare @Perms Table (PermID int identity(1, 1) not null primary key,
                    LoginName sysname not null,
                    PermState nvarchar(60) not null,
                    PermName sysname not null,
                    Class tinyint not null,
                    ClassDesc nvarchar(60) not null,
                    MajorID int not null,
                    SubLoginName sysname null,
                    SubEndPointName sysname null)
    
    Set NoCount On;
    
    If CharIndex('\', @PartnerServer) > 0
    Begin
    Set @Machine = LEFT(@PartnerServer, CharIndex('\', @PartnerServer) - 1);
    End
    Else
    Begin
    Set @Machine = @PartnerServer;
    End
    
    -- Get all Windows logins from principal server
    Set @SQL = 'Select P.name, P.sid, P.is_disabled, P.type, L.password_hash' + CHAR(10) +
        'From ' /*+ QUOTENAME(@PartnerServer) + '.*/ + 'master.sys.server_principals P' + CHAR(10) +
        'Left Join '/* + QUOTENAME(@PartnerServer) + '.*/ + 'master.sys.sql_logins L On L.principal_id = P.principal_id' + CHAR(10) +
        'Where P.type In (''U'', ''G'', ''S'')' + CHAR(10) +
        'And P.name <> ''sa''' + CHAR(10) +
        'And P.name Not Like ''##%''' + CHAR(10) +
        'and P.Name Not like ''NT SERVICE%''' + CHAR(10) +
        'And CharIndex(''' + @Machine + '\'', P.name) = 0;';
    
    Insert Into @Logins (Name, SID, IsDisabled, Type, PasswordHash)
    Exec sp_executesql @SQL;
    
    -- Get all roles from principal server
    Set @SQL = 'Select RoleP.name, LoginP.name' + CHAR(10) +
        'From '/* + QUOTENAME(@PartnerServer) + '.*/ + 'master.sys.server_role_members RM' + CHAR(10) +
        'Inner Join '/* + QUOTENAME(@PartnerServer) + .*/ +'master.sys.server_principals RoleP' +
        CHAR(10) + char(9) + 'On RoleP.principal_id = RM.role_principal_id' + CHAR(10) +
        'Inner Join '/* + QUOTENAME(@PartnerServer) + '.*/ + 'master.sys.server_principals LoginP' +
        CHAR(10) + char(9) + 'On LoginP.principal_id = RM.member_principal_id' + CHAR(10) +
        'Where LoginP.type In (''U'', ''G'', ''S'')' + CHAR(10) +
        'And LoginP.name <> ''sa''' + CHAR(10) +
        'And LoginP.name Not Like ''##%''' + CHAR(10) +
        'And LoginP.name Not Like ''NT SERVICE%''' + CHAR(10) +
        'And RoleP.type = ''R''' + CHAR(10) +
        'And CharIndex(''' + @Machine + '\'', LoginP.name) = 0;';
    
    Insert Into @Roles (RoleName, LoginName)
    Exec sp_executesql @SQL;
    
    -- Get all explicitly granted permissions
    Set @SQL = 'Select P.name Collate database_default,' + CHAR(10) +
        '   SP.state_desc, SP.permission_name, SP.class, SP.class_desc, SP.major_id,' + CHAR(10) +
        '   SubP.name Collate database_default,' + CHAR(10) +
        '   SubEP.name Collate database_default' + CHAR(10) +
        'From '/* + QUOTENAME(@PartnerServer) + '.*/ + ' master.sys.server_principals P' + CHAR(10) +
        'Inner Join '/* + QUOTENAME(@PartnerServer) + '.*/ + ' master.sys.server_permissions SP' + CHAR(10) +
        CHAR(9) + 'On SP.grantee_principal_id = P.principal_id' + CHAR(10) +
        'Left Join '/* + QUOTENAME(@PartnerServer) + '.*/ + ' master.sys.server_principals SubP' + CHAR(10) +
        CHAR(9) + 'On SubP.principal_id = SP.major_id And SP.class = 101' + CHAR(10) +
        'Left Join '/* + QUOTENAME(@PartnerServer) + '.*/ + ' master.sys.endpoints SubEP' + CHAR(10) +
        CHAR(9) + 'On SubEP.endpoint_id = SP.major_id And SP.class = 105' + CHAR(10) +
        'Where P.type In (''U'', ''G'', ''S'')' + CHAR(10) +
        'And P.name <> ''sa''' + CHAR(10) +
        'And P.name Not Like ''##%''' + CHAR(10) +
        'And P.name Not Like ''NT SERVICE%''' + CHAR(10) +
        'And CharIndex(''' + @Machine + '\'', P.name) = 0;'
    
    Insert Into @Perms (LoginName, PermState, PermName, Class, ClassDesc, MajorID, SubLoginName, SubEndPointName)
    Exec sp_executesql @SQL;
    
    --select * from @Logins;
    --select * from @Roles;
    --select * from @perms;
    
    
    Select @MaxID = Max(LoginID), @CurrID = 1
    From @Logins;
    
    While @CurrID <= @MaxID
    Begin
    Select @LoginName = Name,
        @IsDisabled = IsDisabled,
        @Type = [Type],
        @SID = [SID],
        @PasswordHash = PasswordHash
    From @Logins
    Where LoginID = @CurrID;
    
    --    If Not Exists (Select 1 From sys.server_principals
    --              Where name = @LoginName)
    Begin
    
        set @sql = char(10);
        set @sql += 'If Not Exists (Select 1 From sys.server_principals Where name = ''' + @LoginName + ''')' + char(10);
        set @sql += 'begin' + char(10) + '    ';
    
        Set @SQL += 'Create Login ' + quotename(@LoginName)
        If @Type In ('U', 'G')
        Begin
            Set @SQL = @SQL + ' From Windows;'
        End
        Else
        Begin
            Set @PasswordHashString = '0x' +
                Cast('' As XML).value('xs:hexBinary(sql:variable("@PasswordHash"))', 'nvarchar(300)');
    
            Set @SQL = @SQL + ' With Password = ' + @PasswordHashString + ' HASHED;  --, ';
    
            Set @SIDString = '0x' +
                Cast('' As XML).value('xs:hexBinary(sql:variable("@SID"))', 'nvarchar(100)');
            Set @SQL = @SQL + 'SID = ' + @SIDString + ';' + char(10);
        End
    
        set @sql += char(10) +
            '    print ''Created Login ' + @loginName  + ''';' + char(10) +
            'end' + char(10) +
            'else' + char(10) +
            convert(nvarchar(max), '    print ''Login ' + @loginName + ' already existed. '';') + char(10);
    
        If @Debug = 0
        insert into #sqlOut select @SQL;                      
        Else
        Print @SQL;
    
        If @IsDisabled = 1
        Begin
            Set @SQL = 'Alter Login ' + quotename(@LoginName) + ' Disable;'
            If @Debug = 0
                insert into #sqlOut select @SQL;                              
            Else              
                Print @SQL;              
        End
        End
    Set @CurrID = @CurrID + 1;
    End
    
    
    insert into #sqlOut select char(10) + 'use Master;' + char(10);
    
    Select @MaxID = Max(RoleID), @CurrID = 1
    From @Roles;
    
    While @CurrID <= @MaxID
    Begin
    Select @LoginName = LoginName,
        @RoleName = RoleName
    From @Roles
    Where RoleID = @CurrID;
    
    /*  If Not Exists (Select 1 From sys.server_role_members RM
                Inner Join sys.server_principals RoleP
                    On RoleP.principal_id = RM.role_principal_id
                Inner Join sys.server_principals LoginP
                    On LoginP.principal_id = RM.member_principal_id
                Where LoginP.type In ('U', 'G', 'S')
                And RoleP.type = 'R'
                And RoleP.name = @RoleName
                And LoginP.name = @LoginName)*/
    Begin
        If @Debug = 0
        Begin          
            insert into #sqlOut select 'Exec sp_addsrvrolemember @rolename = ''' + @RoleName + ''', @loginame = ''' + @LoginName + ''';';
        End
        Else
        Begin
            Print 'Exec sp_addsrvrolemember @rolename = ''' + @RoleName + ''',';
            Print '     @loginame = ''' + @LoginName + ''';';
        End
    End
    
    Set @CurrID = @CurrID + 1;
    End
    
    
    insert into #sqlOut select char(10) + 'use Master;' + char(10);
    
    
    Select @MaxID = Max(PermID), @CurrID = 1
    From @Perms;
    
    While @CurrID <= @MaxID
    Begin
    Select @PermState = PermState,
        @PermName = PermName,
        @Class = Class,
        @LoginName = LoginName,
        @MajorID = MajorID,
        @SQL = PermState + space(1) + PermName + SPACE(1) +
            Case Class When 101 Then 'On Login::' + QUOTENAME(SubLoginName)
                    When 105 Then 'On ' + ClassDesc + '::' + QUOTENAME(SubEndPointName)
                    Else '' End +
            ' To ' + QUOTENAME(LoginName) + ';'
    From @Perms
    Where PermID = @CurrID;
    
    /*If Not Exists (Select 1 From sys.server_principals P
                Inner Join sys.server_permissions SP On SP.grantee_principal_id = P.principal_id
                Where SP.state_desc = @PermState
                And SP.permission_name = @PermName
                And SP.class = @Class
                And P.name = @LoginName
                And SP.major_id = @MajorID)*/
    Begin
        If @Debug = 0
                insert into #sqlOut select @sql;                      
        Else          
            Print @SQL;          
    End
    
    Set @CurrID = @CurrID + 1;
    End
    
    
    select Command from #sqlOut as SqlOut order by Row;
    go
    
    
    --
    -- (6) Generate a script to Re-link all users to logins based on current state (before shutdown)
    --
    
    use Master;
    
    if object_id('tempdb..#sqlOut') is not null
    drop table #sqlOut;
    
    create table #sqlOut
    (
        Command nvarchar(max) not null,
        Row int identity(1,1) not null primary key
    );
    
    insert into #sqlOut select char(10) + '-- RELINK USERS TO LOGINS' + char(10);
    
    insert into #sqlOut select char(10) + char(10) + '/* ---------------------------------------------------------------------------------------------' + char(10) + 
    'ServerName: ' + @@servername + char(10) + 'ServiceName: ' + @@servicename + char(10) + 'Version: ' + @@version + 
    '--------------------------------------------------------------------------------------------- */';
    
    declare @dbCmd varchar(8000) = '
    use ?;
    
    insert into #sqlOut select char(10) + ''use ?;'' + char(10);  
    
    with links as
    (
    select u.name as UserName,
        l.loginname as LoginName
        from sysusers u 
        join master..syslogins l
        on u.sid = l.sid        
    where u.name != ''dbo''
        and u.isSqlUser = 1 or l.isNtName = 1 or l.isNtGroup = 1
    )
    insert into #sqlOut 
    select ''alter user ['' + UserName + ''] with name = ['' + UserName + ''], login = ['' + LoginName + '']''
    from links
    ';    
    
    exec sp_MSforeachdb @dbCmd;
    
    select Command from #sqlOut order by Row;
    
    go
    

5) Run script to Backup all DBs including system (master, msdb, model) to external drive.

6) Run script to Detach all DBs

7) C Drive will be re-formatted. Preserve the LDF/MDF's if they were NOT on C.

8) Windows Server 2012 is installed on C

9) Move the LDF/MDF for original system files out of the way if they were not on the C Drive.

10) SQL Server 2012 will be re-installed and patched to SP3 a. Re-create system User/Group accounts

11) Backup System DBs to a NEW location or filename (careful to not overwrite the originals!).

12) Run recreate roles snippet. Something like:

USE [master]
CREATE SERVER ROLE [SomeServerRole]
--ALTER SERVER ROLE [dbcreator] ADD MEMBER [SomeServerRole]
--ALTER SERVER ROLE [bulkadmin] ADD MEMBER [SomeServerRole]
-- ALTER SERVER ROLE [SomeServerRole] ADD MEMBER [SomeMemberOrRole]

13) Run recreate login script (doesn't do anything if logins were restored)

14) Stop SQL AGENT.

(Could restore Master here, we chickened out).

15) Attach mdf/ldf using script from above. a. If fail manually restore from bak using script from above.

16) Attempt Restore of Model

17) Ensure SQL Agent is stopped. Restore MSDB (link) a. If fails, need to re-create jobs + maintenance plan + mail configuration + operators

18) Open User To Login script...

    a. If there are master users (rare?) then First Re-Create users for master since it was not restored:
        use master;       
        CREATE USER [ABC] FOR LOGIN [machine\ABC]

    b. Run the rest of the script

19) Enable service broker to match original value SELECT name, is_broker_enabled FROM sys.databases;

    alter database MSDB set single_user with rollback immediate;
    ALTER DATABASE [MSDB] SET ENABLE_BROKER;
    alter database MSDB set multi_user;

20) Start SQL Agent

21) Set Parallelism threshold to original value

22) Adjust any database settings to their original values:

 declare @dbCmd varchar(8000) = '
      use ?;
      if db_name() not in (''master'', ''model'', ''tempdb'', ''msdb'')
      begin
             print ''Adjusting [?]...'';    
            alter database [?] set single_user with rollback immediate;
             aLTER AUTHORIZATION ON DATABASE::[?] to [sa];
            -- alter database [?] set trustworthy on;
            ALTER DATABASE [?] SET AUTO_CLOSE OFF WITH NO_WAIT;     
            alter database [?] set multi_user;
      end     
      else
             print ''Skipping [?]...'';
    ';    

    exec sp_MSforeachdb @dbCmd;

23) Check job ownership:

select s.name as JobName, l.name as login, SUSER_SNAME(s.owner_sid) AS login2
from  msdb..sysjobs s 
left join master.sys.syslogins l on s.owner_sid = l.sid

If the SQL Server Version had also been upgraded I don't believe the model and msdb databases could have been restored so jobs would have been lost due to https://support.microsoft.com/en-us/kb/264474

What's missing:

  • Orignal users in master database (rare?)
  • Server Roles
  • ?
share|improve this answer

Nothing is wrong with either approach per se -- I've done both and both the outcomes are typically good.

If there is a problem with the migration approach, it's not technical: it's laziness. Too often I find that the reason a company hasn't gone fully to xxxx version yet is because they chose a swing migration and never got around to do the hard work to completely move. Now they have two or more sets of servers instead of one.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.