Subversion Repositories Actemium.ccr-dmv

Rev

Blame | Last modification | View Log | Download 

createdAt: "2019-06-16T11:06:38.620Z"
updatedAt: "2019-06-16T17:45:16.176Z"
type: "MARKDOWN_NOTE"
folder: "ba82be21b49821ef92ba"
title: "SQL Server 2008R2"
tags: []
content: '''
  # SQL Server 2008R2
  
  <!-- toc -->
  
  - [SQL Server Support Matrix](#SQL-Server-Support-Matrix)
  - [Upgrade SQL Server 2000 database to 2008 R2 and enable new features](#Upgrade-SQL-Server-2000-database-to-2008-R2-and-enable-new-features)
  
  <!-- tocstop -->
  
  ## SQL Server Support Matrix
  ![5afc25d5.png](:storage\\dd1174b1-002f-43e2-ae02-8d73a79cb541\\5afc25d5.png)
  
  ## Upgrade SQL Server 2000 database to 2008 R2 and enable new features
  The foremost step to do is to run the Upgrade Advisor on SQL Server 2000 database and address all the issues reported by it.
  
  As a best practice, use the Upgrade Advisor tool on your SQL Server 2000 legacy database and import a trace file to the Upgrade Advisor tool for analysis. The trace file lets the Upgrade Advisor detect issues that might not show up in a simple scan of the database, such as TSQL embedded in applications. You can capture traces of TSQL using SQL Profiler on your SQL Server 2000 server during typical hours and analyze these traces using the Upgrade Advisor.
  
  So the rest of the steps would be :
  
  On the day of migration :
  1. script our logins on 2000 server using sp_help_revlogin.
  1. Script out jobs and linked servers from sql 2000 server.
  1. stop webservers connecting to 2000 server. Make sure that no applications are connecting to the 2000 server.
  1. backup your databases and restore on destination sql 2008 R2 server.(note: Do not detach/attach as things could go wrong and you will end up with detached database and no backups !)
  1. Once your backups are restored on the 2008 R2 server, run the output from sp_help_revlogin on 2008 R2 server to recreate logins.
  1. Sync up orphan users (If any) and recreate sql agent jobs and linked servers on the new server.
  1. change compatibility level on the restored databases to 100.
  Dbcc checkdb with all_errormsgs and data_purity options turned on : `DBCC CHECKDB ('<db_name_goes_here>' ) WITH ALL_ERRORMSGS,NO_INFOMSGS, DATA_PURITY`
  1. run DBCC UPDATEUSAGE on the restored databases `DBCC UPDATEUSAGE('database_name') WITH COUNT_ROWS`
  1. Update statistics on all tables with full scan: `Update Statistics table_name with FULLSCAN`
  1. Optional : Check the fragmentation levels and depending on the fragmentation level, run a reorg/rebuild of all Indexes. You can use Ola's scripts.
  1. Recompile all SP's using `sp_recompile 'procedureName'`
  1. Refresh your views `SP_REFRESHVIEW view_name`
  1. make sure to change the database option : page verify to CHECKSUM.
  1. Change the recovery model (if different from sql 2000) to FULL. If you change to FULL recovery model, then MAKE SURE you do Transaction Log backups frequently. This will help you to recover point-in-time as well as not bloat your T-Log.
  1. In SQL Server 2005 and up, Database Mail was introduced. So you have to migrate from SQLMail to Database Mail.
  
  
  ```SQL
  USE [master]
  GO
  sp_configure 'show advanced options',1
  GO
  RECONFIGURE WITH OVERRIDE
  GO
  sp_configure 'Database Mail XPs',1
  GO
  RECONFIGURE 
  GO
  ```
  
  Also, if you have any replication, then you have to reset it up. If any DR like logshipping or Mirroring (new in 2005 and up, but depreciated in 2012) then you have to reset it up as well.
  
  Old DTS packages needs to be migrated to SSIS using C:\\Program Files\\Microsoft SQL Server\\100\\DTS\\Binn\\DTSMigrationWizard.exe (command line) or using Package Migration Wizard.
  
  Also, you can use my script found at https://dba.stackexchange.com/a/36701/8783. Though, it uses detach/attach method, I highly recommend you to use BACKUP/RESTORE method. Change the script accordingly.
  
'''
linesHighlighted: []
isStarred: false
isTrashed: false