1 / 33

Migrating to SQL Server 2005

? David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp. Overview. Which Method to useIn Place upgrade or Side By SideUpgrade AdvisorSpecial CasesClusteringLog ShippingReplicationLessons learnedMore Info. ? David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Co

palti
Download Presentation

Migrating to SQL Server 2005

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

    1. Migrating to SQL Server 2005 David Lundell, MBA MCITP: Database Administrator, Database Developer, MCDBA MCT MCSE MCSD Mutually Beneficial Inc David@MutuallyBeneficial.com www.MutuallyBeneficial.com

    2. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Overview Which Method to use In Place upgrade or Side By Side Upgrade Advisor Special Cases Clustering Log Shipping Replication Lessons learned More Info

    3. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp In Place or Side by Side In Place upgrade Upgrade existing instance of 7.0 or SQL 2000 Side by Side Install a new instance of SQL 2005 without touching the existing instance Can be named instance on Same box OR can be an instance on a new box In place upgrade can always be migrated later – use same build #, same code page and sort order, restore master, msdb, model and then all user databases Side by side on same box then later deactivate default instance or upgrade it to 2005 and migrate back to default instance Side by side on new box can make use of dns aliases to quickly transition or could rename the server In place upgrade can always be migrated later – use same build #, same code page and sort order, restore master, msdb, model and then all user databases Side by side on same box then later deactivate default instance or upgrade it to 2005 and migrate back to default instance Side by side on new box can make use of dns aliases to quickly transition or could rename the server

    4. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Data Migration for Side by Side Database copy wizard Can move logins Detach/Reattach Full/FileGroup Backup and restore Manual T-Log Shipping Transactional Replication Can also be used after cutover to keep 2000 instance up to date in case a rollback is needed

    5. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Downtime considerations If all goes well with inplace it will have lowest downtime and lowest resource cost Manual T-Log Shipping and T-Replication can have lowest downtime For VLDB’s If enterprise edition Consider FileGroup backups and partial restores Backups better than detach since empty space not in backup, also consider zipping before copying over network

    6. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Pros and Cons of In Place Upgrade PROS Can be easier Can be quicker Same Server Name Same Instance Name Mostly automated CONS Rollback is challenging Must follow supported upgrade path All or nothing – can’t upgrade single db Need whole instance to be 2005 ready Notification Services must be migrated Reporting services must be in certain default config DTS packages remain DTS packages Analysis Services has lots of changes Reporting Services can’t have inplace upgrade if IIS 6.0 RS application pool is not using Network Service and virtual directories must have default config settings – security must be windows. ASP.NET account encrypted in registry will also prevent. Upgrade paths ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/702359c4-6ca9-42a8-860c-a95a802898a1.htmReporting Services can’t have inplace upgrade if IIS 6.0 RS application pool is not using Network Service and virtual directories must have default config settings – security must be windows. ASP.NET account encrypted in registry will also prevent. Upgrade paths ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/702359c4-6ca9-42a8-860c-a95a802898a1.htm

    7. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Pros and Cons of Side by Side PROS More control Migrate one db at a time if so desired Easier to rollback Longer test period CONS Redirect clients If old and new instance on same box – resource issue Transfer security, config, dts, jobs by hand Copying of data could mean more downtime Reporting Services can’t have inplace upgrade if IIS 6.0 RS application pool is not using Network Service and virtual directories must have default config settings – security must be windows. ASP.NET account encrypted in registry will also prevent.Reporting Services can’t have inplace upgrade if IIS 6.0 RS application pool is not using Network Service and virtual directories must have default config settings – security must be windows. ASP.NET account encrypted in registry will also prevent.

    8. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Which way to go? – It Depends! Freedom Factor Is this a homegrown app or vendor supplied If Vendor then at least read their recommendations Future Performance Factor If current box can easily handle current performance and expected future performance Then in place is a consideration If it can handle two instances for short time then side by side on same box a consideration If not then must do Side By Side with new box Budget Factor Money available for new server than Side by side with new box or in place upgrade and then migrate Complexity – lots of databases Side by side to migrate databases independently

    9. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Upgrade advisor No matter which way – run the Upgrade advisor For Side by Side we get to ignore a lot of issues First Use Profiler to capture a representative sample of sql activity Upgrade advisor can consume this Run remote Unless trying to analyze Reporting Services

    10. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Upgrade Advisor

    11. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Upgrade Advisor– Database Select In Place upgrade analyze every database you want to keep Including system Can ignore some of the warnings about views in msdb Side By Side – analyze only databases you want to migrate

    12. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Upgrade Advisor – Feed it a trace

    13. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Upgrade Advisor DTS packages Can only choose to analyze DTS packages on Server or in files So may need to run Upgrade advisor again

    14. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp While it runs do some reading SQL 2005 BOL -- Discontinued Database Engine Functionality in SQL Server 2005 Upgrade Advisor BOL -- Other Database Engine Upgrade Issues – look at undetectable issues

    15. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp

    16. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp

    17. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Upgrade Advisor – DTS Report

    18. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Cluster Most likely try as In place Minimize downtime Side by side on same cluster Need another resource group and install new virtual server In any event need to create domain groups in place of local groups (service account permissions) SQLServer2005MSSQLUser$<machine name>$<instance name>, SQLServer2005SQLAgentUser$<machine name>$<instance name>, and SQLServer2005MSFTEUser$<machine name>$<instance name>.

    19. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Log Shipping Will need to reset up log shipping after upgrade Migrate with Failover Upgrade a secondary, and then failover SQL Server 2005 will upgrade the database when this happens Upgrade the old primary Reset up log shipping Switch roles (if desired) SQL BOL -- Upgrading a SQL Server 2000 Log Shipping Configuration Section on Migrating with Failover

    20. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Log Shipping Cont. Migrating without Failover Upgrade the secondary Upgrade the primary (causes downtime) Reset up log shipping Redeploy Turn off log shipping Upgrade Primary and secondary Reset up log shipping

    21. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Replication Double check all changes have been applied everywhere Upgrade Distributor first then Publisher and then subscribers Note To be part of SQL 2005 replication existing servers must be at least: SQL Server 7.0 SP4 or SQL Server 2000 SP3

    22. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Lessons Learned Don’t let someone change firewall rules when do a migration that passes through firewall Ditto for in place upgrade on a Cluster Practice Have a plan and understand it DTS Packages will run on SQL 2005 But don’t use the legacy components to modify them Instead keep them on a SQL 2000 box Make them portable As time permits migrate them to SSIS.

    23. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Lessons Learned – cont. Run DBCC CheckDB before migrating Test your databases in original compatibility mode 70 or 80 as well as in 90 Test through your apps But also Unit test each of your objects Can use the new database snapshot technology to help with that For Cluster in place make certain nothing is wrong with resource group

    24. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Database Snapshots Have to do in T-SQL no GUI way Have to specify a file for every datafile in original database and must be on same server and both database must be on ntfs CREATE DATABASE Adventureworks_UnitTestSnap ON (NAME = AdventureWorks_Data -- must match orig ,FILENAME='c:\data\AW_Snap.mdf' -- must be different from orig ) AS SNAPSHOT OF Adventureworks

    25. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Snapback from Snapshot RESTORE DATABASE Adventureworks FROM DATABASE_SNAPSHOT = Adventureworks_UnitTestSnap

    26. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Case Study – Dept of Sanitation – large city Chose Side By Side Migrating from one server on one network to new server on another network Network cutover to take place same time Directory Services cutover same time Complicated security for SQL

    27. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Case Study Security Used Meta Data to script out assignment of permissions to new roles Examined roles

    28. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Examine roles WITH roleMembers AS ( SELECT -1 AS Role_principal_id,CAST('' AS SysName) RoleName, Rol.principal_id AS MemberID, Rol.Name as MemberName, 0 AS RLevel, CAST('' as varchar(8000)) AS Geneology FROM sys.database_principals Rol WHERE Rol.Type = 'R' AND NOT EXISTS (SELECT 'x' FROM sys.database_role_members RM WHERE Rol.Principal_ID = RM.member_principal_id) UNION ALL SELECT Rol.principal_id, Rol.Name ,Mem.principal_id, Mem.Name, RMB.RLevel + 1 , CAST(RMB.Geneology + ' ' + Rol.Name as varchar(8000)) FROM sys.database_principals Rol JOIN sys.database_role_members RM ON RM.role_principal_id = Rol.principal_id JOIN sys.database_principals Mem ON RM.member_principal_id = Mem.principal_id JOIN roleMembers RMB ON RMB.MemberID = Rol.Principal_ID ) SELECT * FROM roleMembers WHERE Role_Principal_id>-1 Order by RLevel, RoleName

    29. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Examine roles -- Results

    30. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp More Information SQL BOL – Upgrading to SQL Server 2005 ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/f7e79c63-875a-446c-9860-439486928ba1.htm Microsoft SQL 2005 Upgrade Technical Reference Guide http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en SQL Server 2005 Upgrade Handbook http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx Mindworks -- MS Gold Partner Learning Solutions -- Scottsdale MOC 2788 – High Availability Solutions with SQL 2005 Upgrades for Clustering, Log shipping and replication MOC 2780 – Administering SQL 2005 Databases MOC 2779 – Implementing a Database in SQL 2005

    31. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Questions David@MutuallyBeneficial.com

    32. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Other Appearances LOPSA Phoenix Sys Admin Days Nov 6-7th Glendale

    33. © David Lundell David@MutuallyBeneficial.com Oct 2006 Desert Code Camp Giveaways Book – SQL Server Best Practices by David Lundell Your choice of 20% off cost when it comes out A Drawing for a free book (1/20 chance of winning) For every 20 people one book (so 1-20 1 book, 21-40 2 books etc) Just Email David@MutuallyBeneficial.com Tell me whether you want a discount or a shot at a free copy

More Related