Migrating To SQL Server 2005: Migrating To SQL Server 2005 Just Like A Trip To The Beach Michael Townshend & Dennis Murphy DNREC


Step 1 – Assess Your Readiness: Step 1 – Assess Your Readiness Swim Suit Still Fit? Time to upgrade hardware? Sun Screen Out of Date? Inventory User Accounts, Databases & Delete Unneeded Make a To Do List Run SQL Server Upgrade Advisor


Step 1 – Assess Your Readiness: Step 1 – Assess Your Readiness DNREC Hardware Pre-Upgrade 2 quad Processor Servers in Active/Passive Configuration with 1 TB SAN Post Upgrade 2 Dual Core Hyper-threaded 64 bit Quad Processor Servers in Active/Passive Configuration with 3 TB SAN


Make A Plan: Make A Plan Pick Vehicle Make Load Plan Set Date


Pick A Vehicle: Pick A Vehicle Bicycle (Express) Max CPU: 1 Max RAM: 1GB Max DB Size: 4GB Failover Clustering: No Report Server: No Management Studio: No SSIS: No Free


Pick A Vehicle: Pick A Vehicle Compact (Workgroup) Max CPU: 2 Max RAM: 3 GB Max DB Size: No Limit Failover Clustering: No Report Server: No Management Studio: Yes SSIS: No $


Pick A Vehicle: Pick A Vehicle Sedan (Standard) Max CPU: 4* Max RAM: No Limit Max DB Size: No Limit Failover Clustering: Yes Report Server: Yes Management Studio: Yes SSIS: Yes (Simple Transforms) $$


Pick A Vehicle: Pick A Vehicle Luxury Sedan (Enterprise) Max CPU: None Max RAM: No Limit Max DB Size: No Limit Failover Clustering: Yes Report Server: Yes Management Studio: Yes SSIS: Yes (Advanced Transforms) $$$$


Make Load Plan: Make Load Plan Coolers>Chairs>Towels Install SS 2005 on staging server with same name as new server Migrate DBs Individually to Staging Server Use Backup & Restore Point Apps to New Location Over Weekend Move DBs to new cluster Rename cluster to name of staging server


Fix Your Tools: Fix Your Tools Migrate DTS Packages To SSIS There are problems directly migrating DTS packages from the server to SSIS Online resources indicate problems lie in Package names. Upgrade advisor does not check this. We chose to export from 2K5 to Structured Text Files then upgraded each separately.


Fix Your Tools: Fix Your Tools Migrate DTS Packages To SSIS Upgrade wizard will not convert all tasks. Tasks not converted will remain DTS tasks within a SSIS rapper. This causes confusion with connection strings as they are changed in the DTS package, not the SSIS rapper.


Fix Your Tools: Fix Your Tools Migrate DTS Packages To SSIS Packages not upgraded will need to be redeveloped in SSIS.


Fix Your Tools: Fix Your Tools Migrate DTS Packages To SSIS SSIS Development Be careful of the 64 bit environment SSIS will not use 32 bit drivers event if installed on the server. This has implications when accessing data with Jet drivers as no 64 bit versions do not exist. Explicit data type conversions are expected especially between Unicode and non-Unicode.


Fix Your Tools: Fix Your Tools Migrate DTS Packages To SSIS SSIS development environment validates on the fly. While this is a nice feature it can take some time when validating large packages. SSIS will not retain passwords within the connection strings. Workaround is to use configuration files.


Meet Your Neighbors: Meet Your Neighbors Set Up Linked Servers & Distributed Queries


64-bit 2K5 to 32-bit 2k: 64-bit 2K5 to 32-bit 2k Install SP3 or SP4 on 2K boxes Manually run Instcal.sql Reference: http://support.microsoft.com/default.aspx?scid=kb;en-us;906954


Enjoy!: Enjoy! Sit Back Relax Chill Out