asked
Things to mind before going for patching ,sql server upgradationDB and DB migration
Hi, what are the things to keep in mind before going for patching (service packs),Database migration and Sql server upgrading process other than having backups of the databases and disk space. Thanks in advance
This web site works by voting. For all helpful answers below, indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer.
It probably varies by environment, but I'd make sure that I had good, tested, backups in place. I'd make sure that I had a recovery plan ready. I'd also make sure I tested the service pack or the upgrade on an environment other than production. The first time you deploy something should never be the production environment.
could you please elaborate why should we have a specific recovery plan, Do you mean its good to have a note of databases and the type of recovery model they are switched to before going for patching.
No, I mean a full understanding of how you're taking your backups and a documented and tested mechanism of getting those databases back online from the backups or from secondary locations.
A couple of words on migration: **Database migration** has a number of steps involved that you need to go through for it to be successful. Here is a short example: **Preparation** - Make sure that database/system is supports the destinaton SQL server version. Devs or vendors will provide you with an answer. - Set the migration date together with the system/database owner. Communicate this to the users. (Good to do when you have tested the migration process) - Make sure that you identify all dependencies that need to be moved. SQL Agent Jobs, Linked Servers, SSIS Packages. Also you might have a number of other integrations pulling data från the databas, (talk to devs, sysowner). - Identify the application server or clients that connect to the database, make sure that they can connect to your destination server. Odbc is a nice tool for this. **Take care of all task pre migration** - Upgrade/Update SSIS packages - Create linked server on destination server or on the other servers that had one. - Script all jobs that need to be moved and update them if necessary with other drive paths and so on. - Make sure that there is enough diskspace for data/translog and backup file on destination server. - Script all logins/users in the database **Test migration day** - Test to migrate your database and everything you prepared for - Create an excelsheet with all the steps you need to execute on migration day. Add the scripts as comments if you want. **Migration day** - Shut down applications accessing the database. - Take a backup of the database on source and set it offline immediately (do not set it offline if it is needed in the migration process). - Restore database and change settings that vendor/dev asked you to do. (Compatibility mode, recovery model, Page verify, auto_close, Auto Shrink are common settings to change) - Run your scripts to add users and other dependencies. - Run post upgrade scripts (DBCC UPDATEUSAGE, WITH DATA_PURITY) - If transaction log backups are running, take a full backup of the database so you do not get error messages about log backups failing. - You might want to rebuild indexes to update stats, if there is enough time for that. - Redirect application server and clients to new database. - Check the error log on source server, are there any clients/servers still connecting to the old database thats offline? Could be something you missed in your preparation. These are some basic steps, some databases are super easy to migrate others have a billion integrations and you have to gather a big team to be able to move the database.
16 People are following this question.
