Hi ,
As new versions of software get released,
companies and products also get into this natural transition of upgrading and
supporting the latest and greatest. And in the same space, SQL Server is no
exception -almost as an predictable cycle, every 3 years we can expect a
version of SQL Server getting released. This has been a trend since the SQL
Server 2005 version for sure. As part of my job in meeting customers, I have to
give recommendations and best practices to move to the latest versions. There
are so many nuances to keep in mind as we make a successful upgrade. Let me
take a moment to give you some of the suggestions I give to customers. Hope
these will be helpful for you too.
Bible to start
You need to always start with preparation
phase. This phase involves multiple steps and here are some of them for you to
start.
- Keep the SQL Server 2012 Upgrade Technical Guide
handy with you always. This is a very long document to read but it is
worth the reference. I am outlining this as the FIRST reference
because I will refrain from what this document covers as content. It is
exhaustive and as I said with ~450 pages has a lot of material on how to
upgrade. Also for reference SQL Server 2005 to 2008 Upgrade Whitepaper.
- If you still want to know WHY UPGRADE? I am attaching a link to
some of the new features document for your reference. Knowing the new
features is an important step before you upgrade. Later, you must be
looking at how you can start maximizing the new features introduced with
the new version.
Just upgrading by keeping the compatibility
level at a older version is not real upgrade. Evaluate at using some of the new
features to increase productivity, performance, maintainability and
scalability.
As management takes the decisions for
upgrade, DBA’s and developers need to also get themselves set for this task.
Any upgrade process cannot be a simple overnight activity but more of a
calculated informed step as line-of-business applications are at stake and it
can bring down the efficiency of the teams working inside the organizations.
- Take a BACKUP of your databases.
This is the first step irrespective of what you do next. A backup is
required to make sure we have a fallback mechanism if things go wrong.
Also make sure if you are using any specific system databases and objects
in those DB, these are appropriately backed-up or scripted out.
- Take FULL Backups
- Transaction Log Backups
- Backup Database Master Keys
- Backup Service Master Keys
- Backup any external resource used (CLR DLLs,
Extended SP) and anything that is required for the application outside
the database for proper functioning.
- Before the backup process, just run a DBCC CHECKDB across the
databases to make sure there is no corruption before the backup and they
are consistent. This step can reduce any heartburns at a later point in
time post upgrade :).
- As the upgrade talks happens, get started with tracking if any
deprecated features are being used. There are multiple ways to do this – I
highly recommend using the “SQL Server: Deprecated Features” Perfmon
counter to track it like a background task. As your application runs, this
gives you a rough idea of how many deprecated features you might be using
inside your application that might have an impact to your upgrade process.
There is also a Trace Event for capturing Deprecated features with SQL
Server Profiler which you can use but that will be to nail down which code
is causing that perfmon spike.
- Before initializing the upgrade process, please start by running
the Upgrade Advisor as a first step. This surely
guides you of potential problems upfront and is one of the essential step
too for any SQL Server upgrade process.
- Keep track of the Supported Versions and Editions as you plan for Upgrades.
With changes in licensing plans, LPIM (lock pages in memory), AWE gone
etc. It is critical to keep track of how we upgrade between environments.
- Take a typical workload using profiler and keep it handy for a
later test. Create a separate environment, bring the databases via a
backup / restore operation to the new edition. Look for warnings, errors,
changes in functionality and performance bottlenecks. Make sure to replay
the workload collected before to see if anything unusual behaviors or
errors. Document and then make sure these are mitigates during the actual
run.
- Also, review the KB article that talks about “How to move databases between computers running SQL Server”.
Especially, when we move between servers during upgrades we might need to
change the location of files and hence have to account for the same. Also
on a new setup, make sure the drivers for SAN or other hardware components
are done properly and are the latest as suggested by the vendor.
- There are interesting ways to install as we
move forward with SQL Server 2012 like, you can install SQL
Server with SMB Fileshares as storage options.
- Another, is to install it on a Windows Server
Core. This is interesting because now you don’t have an UI to
manage SQL Server and it also reduces the surface area of attack and
patch management is easy too.
- Considerations for SysPrep install for SQL
Server – read documentation.
- Also, since this is an upgrade to a new box, make sure you are
having enough space allocated for atleast 2-3 years of growth in mind for
the database locations. Now is a good time for you to reassess some of the
values defined for the auto-growth settings. Ideally, I suggest never
allow SQL Server to grow but you pre-allocate it as part of your
administration window cycle by properly monitoring SQL Server Data file
free space.
- Before you install the Database on a new server, make a note of the Collation requirements for your
applications. This is an important step and you don’t want get
into a server which is Case-Sensitive while the application is built
assuming case-insensitive.
- Don’t forget some of the critical addendum functions that you might
have configured like replication, mirroring, clustering, service broker,
maintenance plans, local user accounts, SQL Server Logins, service
accounts, Linked servers, common backup path locations, configuration
settings, DR strategies etc. All these need to be accounted and have to be
changed accordingly.
- Whatever step you take, document them meticulously and run the
document through a junior tester on a fresh SQL Server environment. This
will test your upgrade process documentation and at the same time make
sure that you are not missing any step to avoid last minute glitches.
Document every single error encountered and the solution for the same – it
is better to be prepared than to fail not being prepared.
- When I call out documentation, look out for hidden gems in your
process like – Startup Processes, SQL Server Trace Flags that were
enabled, Error Log settings, External DLL / CLR assemblies used, powershell
scripts, any application Windows services that have been
installed, external third party backup tools etc. Also make sure the
server is secure by default and make sure any unnecessary services (SQL
Browser, IIS, virus scanners, spooler etc) if running have been stopped.
- If you are planning to move into Virtualized environment, know what
does it take to run SQL Server in Hyper-V Environment (though
little outdated, is a worthy read). Also, read about running SQL Server with Dynamic Memory in HyperV Environments.
I saw a session video in NA TechEd on this very topic which can be a good
watch: Microsoft SQL Server Consolidation and Virtualization:
Myths and Realities.
- More documents worthy for reading on the Virtualization subject
are: Hyper-V is the Best Virtualization Solution for SQL
Server, High Performance SQL Server Workloads on Hyper-V,
Planning, Implementing, and Supporting SQL Server
Virtualization with Windows Server 2008 R2 Hyper-V and Live Migration
and Support
Policy for SQL Server Virtualization.
- What are your plans for upgrade: in-place upgrade, side-by-side on
the same box or side-by-side on a different box? Irrespective of the
method used Keep in mind Point 1 :).
- Have a small test-stub workload where you are aware of the
parameters and the expected results from the server. Make sure you run
this pre and then post the upgrade and debug any anomalies based on the
output. If you have automated test scripts, these are the best to be run
against the server for a quick test with expected values.
- Have a failsafe strategy if things don’t go as per the plan. Your
backup can come handy here and be careful especially if you plan to do a
in-place upgrade as there is not much choice to come back other than using
the backup route.
Know the fine prints
Please make sure you are aware of the
specific call-outs mentioned in the Upgrade Technical reference mentioned
above. During upgrade there can be specific features or settings that might
need special attention. I am calling out some of them as I have seen at
multiple customer upgrade process.
- The default setting for max worker threads in SQL Server 2000 is
255. When you upgrade from an instance of SQL Server 2000, the database
engine keeps setting the value of max worker threads. However, it is
recommended you change the value of max worker threads to 0 before the
migration, to allow the Database Engine calculates the optimal number of
threads.
- Changing the Compatibility Level while users are connected to the
database can generate incorrect results for sets of active queries. To
avoid inaccurate results, it is recommended to change the Compatibility
Level with the database in order to a SINGLE_USER mode and then back to
MULTI_USER. Do this post the upgrade activity.
- Look at server side settings from the older version, as you plan to
move to new hardware’s some of these old values doesn’t make sense. Keep
special note to MAXDOP settings, number of TempDB files etc.
- Just like the recommendation on Server Core, I highly recommend not
to install any new components that you may not be planning to use in the
immediate future like – Data
Quality Services, PowerPivot,
Master
Data Services, Analysis
Services etc.
- Look at the Security considerations and the protocols used for
communication as part of upgrade. Each new version there are some changes
and analyze the ports you have as standards open at the new environment. Security considerations during Install is
documented and worth reading for every version upgrade.
- In earlier versions of SQL Server, the values for
the table and index row counts and page may be incorrect. Thus, databases
that were created in versions prior to SQL Server 2005 may contain
incorrect counts. After you upgrade a database from SQL Server 2000, it is
recommended that you perform the DBCC UPDATEUSAGE to correct any invalid counts.
This DBCC statement corrects the count of rows, used pages, reserved
pages, leaf pages and data for each partition in a table or index.
- Update statistics for all Indexes after the
Upgrade process. This is one of the most important step. Keep in mind also
the Plan guides and hints that you have used in your application. Test
these sections for any potential performance problems. Given the upgrade,
SQL Server engine would have also changed and hence testing them matters.
In case of Full-text catalogs, make sure these are also updated.
- As a sanity check, make sure to run a DBCC CHECKDB on each of the databases which
have been part of the upgrade process.
- Over and above this, I also recommend checking each of the database
properties which are critical. Check the recovery models, Auto-Close, Auto-Create-Statistics, Auto-Shrink,
Auto-Update-Statistics and Auto-Update-statistics-Asynchronously.
Also in this list, I highly recommend setting value of CHECKSUM for the Page Verify property at the DB
properties level.
- Know and document the changes that need to be done on the
application to start pointing to the new upgraded server. This might
sometimes involve Connection String changes in Web Server deployment.
Also, track if any reporting server and Sharepoint installations are
pointing to the old servers as data sources. Even these need to be changed
as part of movement.
- After all this, don’t forget to take a Backup of your system. You
don’t want to do all the hard work and find out the HDD crashed the next
day :). Backup and secure on a different media and location.
I think this very much sums-up what I had in
mind based on various implementations that I have seen. These steps are very
much towards migration of the SQL Server Relational engine but doesn’t talk
about the SSAS, SSIS, SSRS workloads. I would again reiterate to look at the
Upgrade Reference Guide mentioned above as a good guide to start.
A successful upgrade uses a cycle of: Plan,
Document process, Test, Refine process, Test, Plan upgrade window, execute,
verify upgrade and then opens for business.
Now that we have upgraded into the next
version of SQL Server, now start looking actively at how we can start using the
new capabilities available inside the next new release. I am just calling out
some of them that are worth mentioning here for SQL Server 2012 release:
SQL Server: AlwaysOn
Technologies
xVelocity in-memory technologies of PowerView
and PowerPivot
Security Enhancements – Contained
Databases
Links worth keeping track
as you upgrade:
Deprecated features: http://technet.microsoft.com/en-us/library/cc707789
Discontinued features: http://technet.microsoft.com/en-us/library/cc707782
Breaking changes in 2012: http://technet.microsoft.com/en-us/library/cc707784
Behavior changes in 2012: http://technet.microsoft.com/en-us/library/cc707785
Full-Text Search Backward Compatibility in
SQL 2012: http://msdn.microsoft.com/en-us/library/ms143544
Hope these links and resources will be of
help for you as you plan your upgrade and movement to yet another version of
SQL Server. I would love to get your inputs and understand if there are any
glaring steps that I would have missed mentioning as part of the checklist
mentioned above.
SQL
Server 2012 Upgrade Technical Guide [454 pages and 9 MB]
Sources:
MSBI (SSIS/SSRS/SSAS) Online Training:
No comments:
Post a Comment