Befriending Dragons

Transform Tech with Anti-bullying Cultures

Leave a comment

New Releases – SQL Server Migration Assistant (SSMA)

We have shipped some updates to our migration tool – the SQL Server Migration Assistant (SSMA). It is now easier than ever to migrate to SQL Server 2005, 2008, and/or 2008 R2 from Access, Oracle, Sybase, and MySQL. You can even use SSMA to migrate from Access or MySQL to SQL Azure.


Customers and partners can provide SSMA feedback via


·         SSMA team blog

·         Microsoft announces SQL Server Migration Assistant for MySQL

·         Migrating to SQL Server 2008

Links to downloads:

Leave a comment

CTP of SP2 for SQL Server 2008 is now available

SQL Server 2008 will soon have a new service pack – SP2. SQL Server service packs go through a CTP (aka beta) cycle prior to release. The CTP of SP2 is now available for you to download and test. This is only the pre-release version, so only use it for testing.


·         SQL Server 2008 Service Pack 2 CTP

·         SQL Server 2008 Express Edition Service Pack 2 – CTP

·         Feedback via Connect

·         SQL Server 2008 Service Pack 2 Customer Technology Preview Available

“Today marks the release of the Customer Technology Preview (CTP) for Microsoft SQL Server 2008 Service Pack 2.  Microsoft SQL Server 2008 Service Pack 2 includes:

·         All hotfixes released up to Microsoft SQL Server 2008 SP1 CU 8.

·         Updates to the Reporting Services Add-in for Microsoft SharePoint Products 2007 allowing customer to connect to SQL Server 2008 R2 Report Servers.

·         Support for SQL Server 2008 R2 version of Management Studio to perform operations for data-tier applications (DAC) in SQL Server 2008 instances that have SP2 installed.  For more information please visit

·         Support for SQL Server 2008 R2 Utility Explorer and SQL Server Utility viewpoints in (SSMS) provide administrators a holistic view of resource health through an instance of SQL Server that serves as a utility control point (UCP).  For more information please visit

 We encourage customers to try Microsoft SQL Server 2008 SP2 CTP, and provide us your feedback.  We are actively reading all feedback and appreciate any thoughts that you might have to help us with this product update.  To provide feedback please visit the Microsoft Connect Feedback Center.”

·         List of the bugs that are fixed in SQL Server 2008 Service Pack 2


SQL Server Servicing plans


·         SQL Server 2008 SP2 is targeted for the third quarter of calendar year 2010.

·         SQL Server 2005 SP4 is targeted for the fourth quarter of calendar year 2010. This is planned to be the last SQL Server 2005 service pack.


SQL Server Service Pack support (mostly) ends 12 months after the next service pack releases. So 1 year after SQL Server 2008 SP2 releases you need to be have all SQL Server 2008 instances on SP2 (and optionally with SP2 based cumulative updates (CUs) applied). There is very limited support for older service packs under our new SP policy.


·         Service Pack support (general)

·         SQL Server lifecycle


Checklist for upgrading to a new version of SQL Server

Here is the basic process for a full version upgrade (such as 2000 to 2008) of the SQL Server engine. The steps will be different for RS or AS. I did not include all the details of variations such as replication, mirroring, log shipping, full-text search, or clustering. They are covered in the white paper in the first reference at the bottom.


Look for potential problems:

·         Run Upgrade Advisor from your new version of SQL Server and address any issues.

·         Run SQL Server 2008 Upgrade Assistant (I don’t have any details on this – it’s a 3rd party tool).

·         If upgrading to SQL Server 2008: Run the System Configuration Checker

·         Verify you are not using any discontinued features.

·         Read up on the “breaking changes” section of BOL.

·         Test not only the application but also all maintenance activities including disaster recovery.

·         Look for any hints/options or plan guides used in the system and test removing them.

·         On a development or test system – uncover and address issues caused by the upgrade and/or change in hardware

o   Install the new version and load copies of the databases.

o   Look for potential errors, changes in functionality, and performance changes.

o   Make sure you cover typical, peak, and periodic (such as month end) activity. Consider tracing high visibility activity as well, such as the functionality used by an executive or vocal user.

o   The more your test system looks like your production box (hardware, OS, configuration, SQL data) the more accurate your testing will be.

o   Do a profiler/readtrace replay: Capture at least one profiler trace using the replay template on production. Then use the replay functionality of either the RML tools or Profiler itself to replay the same traces on test. Consider that data modifications may fail depending on the state of the backups compared to the state of the database at the time the traces were taken.

o   Have testers hit the test system under load: typical and peak.

o   Put a full application load on the system and test all functionality. Tools like Profiler replay, Visual Studio Database Edition, RML, sample code such as what you might find on codeplex, and 3rd party products can all help with this process.

o   Consider using a tool such as Visual Studio Database Edition to add more test data to the system to simulate future usage.

o   If your IO setup will changed and the test system will be similar to production, consider running SQLIO (performance) and/or SQLIOSim and its new parser (correctness).

·         Test the upgrade process itself on a test system.

·         If doing an upgrade in place make sure the old system is at a minimum supported version level and edition for the upgrade (including the service pack). You cannot upgrade in-place from 32bit to 64bit. Check that your combination of old and new versions, editions, and platforms is supported.



·         Start a document/checklist with all the steps you plan to follow, who is responsible for each, and the expected time for the process.

·         Will you change the OS, hardware, firmware, disk configuration, and SQL Server version all at the same time? If so, you can reduce your overall downtime but you also make it harder to isolate problems that occur after the changes.

·         Decide on a physical implementation vs. a virtual machine. If you will use a virtual machine make sure it is fully supported by Microsoft and it is setup per virtualization best practices.

·         If you are moving to a new box:

      • Follow best practices for configuration – if your memory, number of CPUs, or number of instances on the box/cluster changed some configuration options will need to be adjusted.
      • Run SQLIO (performance) and SQLIOSim (IO integrity) and verify the system is “fast enough”
    • Usually you will want the drives laid out the same, if not plan for the steps to change the location: 314546 HOW TO: Move Databases Between Computers That Are Running SQL Server  

·         Do you have special functionality such as replication, mirroring, or clustering? If so, consider whether that changes any of the steps.

·         Will your upgrade be in-place, side-by-side on the same server, or side-by-side on a different server?

·         Will the new instance have exactly the same server name and instance name (i.e. Server1 or VirtualServer1InstanceA)? If not, how will you accommodate components such as replication and mirroring that assume the server name never changes?

·         Note that all instance-aware components within a given instance are upgraded together. Common components such as SSIS and the client tools are upgraded the first time a newer version is installed on a box.

·         Have a rollback/contingency plan that includes SLAs, how you will know when you must implement the contingency plan, who will make the decisions, etc.

·         What version will you upgrade to? What service pack/CU level will you be at?

·         Will you upgrade/move all user databases at once?

·         Will you move system databases and/or information from the system databases? This could include logins/passwords, linked server definitions, configuration settings, jobs, DTS/SSIS packages, etc. Note that system databases can only be restored to the EXACT same version. User databases can be restored/attached to a newer version but not back down to a lower major version.

·         Will you do a detach/attach or a backup/restore? Attach/detach is the simplest but generally has a longer user downtime. With backup/restore you can do a full (and possibly some incremental transaction log or differential) backup/restore before-hand and only have downtime for the latest transaction log backup/restore time period. This only works if the database is in full or bulk-logged recovery mode (and for bulk-logged you need to be sure you aren’t doing any bulk operations before the downtime).

·         How long do you expect the upgrade to take including time for potential troubleshooting, breaks, and interruptions? Add more time to that for the scheduled downtime.

·         Make sure you have enough disk space for the entire upgrade process including extra copies of your backups.


Pre-downtime steps

·         If upgrading to SQL Server 2008, consider applying .NET 3.5 SP1 and the latest Windows installer ahead of time as they require a reboot. You may also want to install the SQL Native Client ahead of time.

·         If you have a version of Visual Studio 2008 that is not patched to at least SP1, patch it to at least SP1 prior to installing SQL Server.

·         For SQL Server 2008 if you are not planning to install SP1, install RTM based CU4 (to fix known and resolved setup issues), then apply RTM, then reapply CU4. Or you can slipstream SP1+SP1 based CUx (or whatever the latest SP is at the time) and do a single install that includes RTM, SP1, CUx. For clusters, do each step across all nodes before going to the next step.

·         Take a baseline of the system and note the circumstances (busy vs. slow day, certain time of day/week/month/year, anything unusual).

·         If moving to a new server, install drivers, supporting applications/utilities, etc. on the new system.

·         Make sure OS level settings such as whether or not “Lock Pages in Memory” is enabled for the SQL Server account are the same on the old and new hardware.

·         If necessary, install the latest backward compatibility files on the SQL Server box and any client boxes (which may include other servers).

·         If moving from DTS to SSIS, consider upgrading the packages ahead of time. If you do this make sure no changes are made to the existing production DTS packages unless you also apply the changes to the converted SSIS packages.

·         Install the new version of the client tools (including service packs), samples, and sample databases on DBA and developer desktops.

·         Become familiar with the new interfaces, functionality, and how your features work in the new version.

·         Understand the upgrade process and how to troubleshoot failures in each step.

·         Test security and connectivity, such as whether linked servers work as expected.

·         Consider pre-growing system databases if doing an in-place upgrade.


Just prior to the upgrade:

·         Backup everything (system dbs, user dbs, registry, sys db settings, config options, encryption keys, etc) and save off the backups.

·         Run DBCC CHECKDB on all system and user databases – address any errors that are found.

·         If you are scripting out data from the system databases make sure you have everything you need.

·         If you are keeping the same server name on new hardware, make sure the old box is shut down and the new server and SQL Server (sp_dropserver + sp_addserver with the “local” parameter) have been changed to the old server name BEFORE you enable replication or anything else which relies on the server/instance name.

·         If doing a backup/restore on a full or bulk-logged recovery mode database, take the full backup and restore it to the new instance. Optionally you may have one or more tran and/or differential backups to restore as well.


At Downtime:

·         Document the process, such as actual time to complete each step, any changes you had to make, etc.

·         Make sure all users are out of the system and do whatever is necessary to keep them out until the upgrade is complete.

·         Disable startup processes, SQL Server trace flags, services that access SQL Server, etc.

·         Make sure all replicated data has hardened on all systems and stop replication.

·         Stop any non-essential services and applications, especially any that might have SQL Server related files open. Examples of common problem areas are IIS, backup software, virus/malware scanners, and applications that connect to SQL Server.

·         For an upgrade in place, start the upgrade.

·         For backup/restore, take the final transactional log backup, copy it to the new location (possibly using a SAN clone) and restore it to the new instance.

·         For attach/detach, detach the user databases from the original server, copy them to the new location (or use a SAN clone), and attach them.

·         Take new backups (after upgrade, before any changes) and save them off (where they will not be overwritten until you are ready for them to be).

·         If the system databases were not restored, copy their data and then sync the logins with the users.

·         If the system databases were restored for a pure migration as opposed to an upgrade, tweak configuration options as needed (for example, MAXDOP or the number of TempDB files may change if the number of cores has changed).

·         If you have DTS/SSIS packages or other supporting files stored in the file system, copy them to the new system and disable access to them on the old system.

·         Update statistics (essential for 2000 to 2005/8, nice for 2005 to 2008) on all user databases.

·         Set the compatibility mode to the current version for each user database (by default an attach or restore leaves it at the level of the source instance).

·         Take new backups (after changes) and save them off (where they will not be overwritten until you are ready for them to be).

·         If using Full-Text update your catalogs.

·         Perform another DBCC CHECKDB on each database – optional. If possible use the WITH DATA_PURITY option (this option is implied for databases created in 2005 or later and for any database that has once had that option explicitly stated).

·         Perform DBCC UPDATEUSAGE on each database.

·         Take new backups (after changes) and save them off (where they will not be overwritten until you are ready for them to be).

·         If the instance name changed, point the users/application(s) to the new instance name.

·         Test basic maintenance operations.

·         Test whatever application level items you can do yourself (perhaps with a replay of only SELECT statements) or test with a few trusted users.

·         Let the users back in.

·         Test the application(s) – have all users do at least basic operations.

·         Compare the current performance to your baseline, taking into account the circumstances such as the number of users on the system.

·         Evaluate the criteria to see if you need to apply your rollback/contingency plan.

·         Make sure your new backup strategy is implemented and takes into account the backups you took during the upgrade process.

·         Restart any services, applications, etc. stopped just for the upgrade process.



·         If you enabled autogrow for system databases, consider disabling it.

·         Re-enable startup processes, SQL Server trace flags, services that access SQL Server, etc.

·         Restart any replication.

·         Update your upgrade checklist/documentation based on your most recent experience. Many of the changes may also apply to your disaster recovery docs.

·         Review your documentation, best practices, troubleshooting scripts, profiler templates, etc. for changes that should be made. For example, in 2005 and later the process to rebuild master and the other system databases uses a different utility than 2000 did.

·         Consider turning on new options available in your new version such as database checksum instead of just torn page detection.

·         Take advantage of the more granular security options in the new versions. This includes better job/replication/SSIS security as well as more role options.

·         With 2008 and later set up a Central Management Server so everyone in a group (such as all production DBAs who support one sector of the business) has the same list of SQL Servers available to them in SSMS.

·         Start using new features/functionality in the new version.

·         Keep up to date with service packs and cumulative updates.

·         Rewrite anything using deprecated features. You can use SQL Profiler to capture an event every time a deprecated feature from the SQL engine is used. Starting with SQL Server 2008 there is also a perfmon counter for this.

·         Capture a new performance baseline/benchmark on the system and update the baseline periodically.

·         For a side-by-side upgrade, determine when and how you will decommission the old instance.

·         Migrate maintenance plans created in 2000 to the new SSIS format.



·         Ultimate guide to upgrading to SQL Server 2008

·         The application may have additional steps when you upgrade or move a database. For Sharepoint those steps are outlined here:!8983677073B13695!1484.entry

·         Upgrading a SQL Server 2008 Failover Cluster


If you are using replication, Analysis Services (AS), or DTS/SSIS, see these links:

Considerations for Upgrading Replicated Databases

Considerations for Upgrading Analysis Services
Considerations for Upgrading Integration Services
Considerations for Upgrading Data Transformation Services


x64 Windows – Upgrade from 32bit SQL Server to 64bit SQL Server

Many people are now upgrading from 32bit to 64bit SQL Servers. Most of you have a match between your operating system and your SQL Server platform. For example, most of you install a 32bit SQL Server on 32bit Windows, and if you have the x64 platform of Windows, you usually install the x64 SQL Server. But what happens when you have a 32bit SQL Server on an x64 system and you want to change it to be al x64? Note that you cannot install 32bit SQL Server on IA64 so this scenario does not apply to Itanium systems. In the example below both the platform and the version of SQL Server are changing.

You have an instance of SQL Server 2000 32bit installed on Windows 2003 SP2 x64. This means SQL Server is “running in the WOW”. WOW stands for Windows on Windows and means you have a 32bit application running inside a 64bit OS. This gives SQL Server a full 4GB of user addressable virtual memory space, which is more than any 32bit application can get on a 32bit OS without memory mapping (in SQL we do memory mapping of the buffer pool through “AWE”). However running in the WOW doesn’t give you the full memory advantages you would get from running a true x64 application on an x64 OS. SQL Server 2000 was not released in an x64 “flavor”, but once you upgrade to SQL Server 2000 SP4 Microsoft will support running it in the WOW. SP4 was required for this particular configuration even before we discontinued support for SP3. See 898042 Changes to SQL Server 2000 Service Pack 4 operating system support;EN-US;898042 Generally you should avoid installing 32bit applications on x64 systems whenever possible. Any recently purchased hardware will be x64 and putting a 32bit OS on it will throttle back its memory capabilities, so your best bet is going to be an x64 version of SQL Server on x64 Windows.


You want to upgrade this instance from SQL Server 2000 32bit to SQL Server 2005 x64 on the same box. You would like to keep the same instance name. However, we do not support an in-place upgrade from any 32bit SQL Server to any 64bit SQL Server. Additionally, you cannot restore system databases (master, model, tempdb, msdb) to a different version, not even a different service pack or hotfix level.

·         Version and Edition Upgrades “Upgrading a 32-bit instance of SQL Server 2000 from the 32-bit subsystem (WOW64) of a 64-bit server to SQL Server 2005 (64-bit) on the X64 platform is not supported. However, you can upgrade a 32-bit instance of SQL Server to SQL Server 2005 on the WOW64 of a 64-bit server as noted in the table above. You can also backup or detach databases from a 32-bit instance of SQL Server 2000, and restore or attach them to an instance of SQL Server 2005 (64-bit) if the databases are not published in replication. In this case, you must also recreate any logins and other user objects in master, msdb, and model system databases.”

·         You cannot restore system database backups to a different build of SQL Server “You cannot restore a backup of a system database (master, model, or msdb) on a server build that is different from the build on which the backup was originally performed.”

·         If the SQL Server versions are the same, even system databases can be restored between different platforms (x86/x64). However, you do sometimes have to make one update to the msdb database when you do this (because often the SQL Server install path has changed, such as using “program files (x86)” on an x64 system). For non-system databases the version you restore to doesn’t have to be identical, generally you can restore a user database to a higher version and the platform (x86/x64) is irrelevant. Error message when you restore or attach an msdb database or when you change the syssubsystems table in SQL Server 2005: “Subsystem % could not be loaded”


So in this case you have two basic options if you must keep the same server and instance name:

1.       Upgrade, reinstall, attach

a.       Make sure all users, applications, and services are totally off the system for the entire duration of the downtime

b.      Upgrade SQL 2000 SP4 32bit to SQL 2005 (or 2008) 32bit (NOT x64! – that is not a viable upgrade path)

c.       Backup all databases

d.      Detach the user databases (the detach does a checkpoint to ensure consistency)

e.      Make copies of the mdf/ldf files for user and system dbs

f.        Uninstall SQL Server 2005 32bit (to make the instance name available)

g.       Install SQL Server 2005 x64 to the same instance name and at the EXACT same version as what was just uninstalled

h.      Restore master, model, msdb

i.         Attach the user databases

j.        If needed, run the update from Error message when you restore or attach an msdb database or when you change the syssubsystems table in SQL Server 2005: “Subsystem % could not be loaded”

k.       Apply the appropriate Service Pack and/or Cumulative Update

l.         Take full backups

m.    Allow users back in the system

2.       Reinstall, attach, copy system db info

a.       Make sure all users, applications, and services are totally off the system for the entire duration of the downtime

b.      Backup all databases

c.       Extract all relevant information to allow re-creation of system database information. This includes logins/passwords, configuration settings, replication settings, linked servers (including login mappings), custom error messages, extended stored procedures, MSDB jobs, DTS/SSIS packages stored in MSDB, proxies, any objects manually created in any system database. If you go this route let me know and I’ll double check that this list is complete.

d.      Detach the user databases (the detach does a checkpoint to ensure consistency)

e.      Make copies of the mdf/ldf files for user and system dbs

f.        Uninstall SQL Server 2000 32bit (to make the instance name available)

g.       Install SQL Server 2005 x64 to the same instance name.

h.      Attach the user databases

i.         Apply all the system information you extracted above including sync’ing users to the new logins.

j.        If needed, run the update from Error message when you restore or attach an msdb database or when you change the syssubsystems table in SQL Server 2005: “Subsystem % could not be loaded”

k.       Apply the appropriate Service Pack and/or Cumulative Update

l.         Take full backups

m.    Allow users back in the system