January 22, 2014 at 3:18 pm
i am planning to do an instance sql 2008 with 20 databases around 2-10 gbs each migration to a sql 2008 r2 sp2 instance (new storage), does anyonethink of any major setbacks i might have? Would full 2008 db backups work in a sql 2008 r2 instance?
January 22, 2014 at 3:29 pm
There are not major differences between these two versions, but they are different, so you would have to test that all your apps work the same, and are compatible with 2008R2. So do this in a test environment first.
Your major problem is going to be getting the testing done and coordinating the upgrade of the various apps on what sounds like a consolidated server. I would stand up a new server and do the apps piecemeal.
A 2008 backup can be restored onto a 2008R2 instance.
---------------------------------------------------------------------
January 23, 2014 at 6:22 am
I assume you are moving from one server to another, current one running SQL2008 and the new one running SQL2008R2...
Yes, db backups are easily restored from SQL2008 to 2008R2 without issue. Typically on any version upgrade you should rebuild the indexes on all tables but since this is SQL2008 to SQL2008R2 I don't know if that is necessary.... At minimum update the stats on the databases once you restore them into SQL2008R2. This will make sure that the optimizer is using good stats for indexes.
At minimum run the DBCC CHECKDB on the databases before you back them up and restore into the new one. The last thing you need to do is carry any corruption over. Also, not sure how you plan on moving over logins and any jobs or SSIS packages but those need to be taken into consideration as well. And yes..... do this is a test environment first and have apps folks test, test test
January 23, 2014 at 7:52 am
Thank you guys, this is very helpful!
January 23, 2014 at 9:20 am
script enclosed I used when migrating databases from 2000 to 2008 by backups
/*****************************************************************************************/
/* this generic script restores a SQL 2000 database backup into a SQL 2008 instance, */
/* thereby upgrading it. Replace dbname with your database */ */
/* includes all post upgrade steps to optimise the upgrade process */
/* run them one step at a time. */
/*****************************************************************************************/
restore filelistonly from disk = 'path\dbname_etc.BAK'
restore database dbname from disk = 'path\dbname_etc.BAK'
with move 'dbname_Data' to data location\dbname_data.mdf',
move 'dbname_Log' to log location\dbname\dbname_log.ldf'
-- set compatibility level to 100, allows use of all SQL 2008 features. Code needs to be compatible with SQL2008 restrictions
exec sp_dbcmptlevel 'dbname','100'
use dbname
go
-- change dbowner to same as it was under SQL 2000, it is usually 'sa', but check
exec sp_changedbowner 'sa'
-- update all statistics, needed for performance, SQL 2008 interprets these differently
exec sp_updatestats
-- update usage stats (correct sp_spaceused values) can be incorrect after SQL 2008 upgrade
dbcc updateusage(0)
-- ensure torn page detaction set. Use this rather than checksum on 03 due to less overhead, use checksum on other servers
alter database dbname set page_verify checksum -- page_verify torn_page_detection
-- upgrade creates a schema for all users and sets this as default, use following to create SQL to
-- modify users default schema to dbo and then run in a new window
-- if SQL 2000 objects were not owned by dbo modify script accordingly
-- SQL2000 always checks objects by username.object first, then checks dbo.object if not found,
-- so this check can be avoided and improve performance by setting default_schema to dbo so it is checked first,
-- however if all objects are owned by the user or all or some are owned by user and object not qualified with
-- owner in SQL then default_schema should be left at user. Only testing may prove this.
set nocount on
select 'alter user '+ name+ ' with default_schema = dbo' from sys.sysusers
where uid > 4 and isntgroup = 0 and issqlrole = 0 and isapprole = 0
order by name
-- now drop all the user schemas created
select 'drop schema ['+ name+ '] ' from sys.sysusers
where uid > 4 and issqlrole = 0 and isapprole = 0
order by name
-- if not done before backup used in migration, check database integrity, if comes up clean, dat_purity will be enabled for all future checkdbs
dbcc checkdb with data_purity
-- if database reasonable size, take this opportunity to reindex it (run dbcc dbreindex maint plan job)
-- if a large no of ad-hoc queries run on this database, consider forced parameterization option
-- could reduce cpu usage significantly
--alter database dbname set parameterization forced
-- now back the database up (optional at this point - but will need including in backup strategy)
Alter database dbname set recovery simple --optional, use on test environment
backup database dbname to disk = 'h:\server\data\mssql\backup\dbname_post_upgrade.bak' with init
---------------------------------------------------------------------
January 24, 2014 at 3:37 pm
thank you so much for that script! , one more question regarding this migration, these are fairly large dbs (1 terabyte) and the day we go live we would like to have app down only a few hours. Would it be possible to do a full backup for the DBs (sql 2008) and restores in the new instance (sql 2008 r2) a few hours before going "live" and then when we go live, turn off app , do a differential backup for the dbs on sql 2008 instance and do the differential restore to sql 2008 r2 sp2 instance. Would that work?
January 25, 2014 at 10:11 am
yes, thats a fairly common method used to do migrations or any move of server. Restore the full with norecovery and when ready for cutover, do a diff or log backup which will be much smaller, copy over and restore.
Get all your server level objects copied over as well beforehand (logins jobs etc). If you have user defined error messages, don't forget those!
---------------------------------------------------------------------
January 27, 2014 at 11:43 am
yes thank you, we have done this in the past but with the same versions of sql server (sql server 2008 r2 sp2), my question was if this can be done with different versions since the migration will be from sql 2008 to a sql 2008 r2 sp2 instance. Can a differential or log backup from a sql 2008 db be restored to sql 2008 r2 sp2 database ?
January 27, 2014 at 12:00 pm
DBA-640728 (1/27/2014)
yes thank you, we have done this in the past but with the same versions of sql server (sql server 2008 r2 sp2), my question was if this can be done with different versions since the migration will be from sql 2008 to a sql 2008 r2 sp2 instance. Can a differential or log backup from a sql 2008 db be restored to sql 2008 r2 sp2 database ?
yes
---------------------------------------------------------------------
January 27, 2014 at 12:07 pm
thanks again 🙂
January 28, 2014 at 3:42 am
Why are you planning to upgrade only to SQL 2008 R2?
Apart from SSIS, there are very few changes between SQL 2008 and SQL 2012 or even SQL 2014. The amount of testing you need to do is much the same regardless of release.
With SSIS, there are a number of changes when you get to SQL 2012, but no extra changes between SQL 2012 and SQL 2014. If you have got a justification to upgrade, then it is worth looking at if the extra time needed to upgrade SSIS can be justified now.
Every upgrade you do takes time. With your current plans you are imposing a few man-weeks of time on your organisation at some point in the future when upgrades to SQL 2012 and SQL 2014 are needed.
My advice is to plan now for an upgrade from SQL 2008 to SQL 2014. This may add 50% more effort to the current upgrade process, but will save much more time over the longer term. Even though SQL 2014 is currently at CTP2 level, by the time you are ready to go live (March/April?) SQL 2014 will be RTM. In any case, Microsoft support an upgrade from CTP2 to RTM for a production system.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 28, 2014 at 3:50 am
ok, now I am wondering why I posted mu upgrade from SQL2000 to 2008 script, must have had a rush of blood to the head!
it is still relevant up to a point but the schema related stuff is irrelevant.
---------------------------------------------------------------------
January 28, 2014 at 12:44 pm
DBA-640728 (1/22/2014)
i am planning to do an instance sql 2008 with 20 databases around 2-10 gbs each migration to a sql 2008 r2 sp2 instance (new storage), does anyonethink of any major setbacks i might have? Would full 2008 db backups work in a sql 2008 r2 instance?
Since 2008R2 is actually going to go out of support soon, consider upgrading to 2012, instead.
--Jeff Moden
February 13, 2014 at 7:10 am
thank you guys, the reason we are migrationg to only sql 2008r2 sp2 is that we have a very tight deadline to move over 10 production instances (4 months) out of their current hardware. I will definetely take into account when we have new installs to do it sql 2012 or better yet sql 2014. Thank you!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply