Voice of the DBA

Tag: upgrades

  • The Migration Checklist

    One of the things I always recommend is that when you upgrade a SQL Server instance, you perform a side by side migration to a new host instead of an in-place upgrade. My main concern is risk. While the upgrade process is fairly smooth, I still have hiccups installing SQL Server at times, and for a live server, the last thing I want to do is have to uninstall SQL Server and reinstall an old version.

    Apart from the risk, I also think an upgrade is a great time to refresh hardware. If you’re paying for the latest bits, I’d spend a little more for newer hardware if I can. The cost usually isn’t much compared to SQL licenses, especially these days with hardware being very cheap and powerful. New hardware also gives me a staging place to test the migrations, without disturbing the existing system.

    Planning the migration across hosts usually isn’t too difficult, but that there can always be small issues that I need to fix after the job is done. In most cases, that’s not a problem. In some, it can cause downtime (and plenty of embarassment), not to mention a loss of confidence in the DBA team. I find that I often end up building a checklist, working through the existing instance to ensure I don’t forget any items, fixing my list as I run test migrations to new hardware and find issues.

    That makes me think I should have a good checklist that I can start from, marking this up for my specific instances. BOL doesn’t give much information. I did see a nice one in a forum post on SQLServerCentral, but I’d really like a good, solid checklist. An overall list from @spaghettidba might be a good start, and I’m hoping he’ll write an article on each of these, with the list for someone to check. However, I’m hoping to give him, and others, a jump start.

    What items do you need in a checklist for a SQL Server migration?

    This could be just moving the existing SQL Server instance to new hardware. It could be a version upgrade, consolidation, or any other reason to move. In any case, I’ll start with a general list of things to check. Let me know what I’ve missed:

    Instance Level

    • Check new hardware/software meets requirements for SQL Server
    • Verify patches levels are the same (with items needed for installs)
    • Map paths from old to new drives, verifying space
    • Ensure all logins, server roles, credentials, and permissions are migrated.
    • Migrate all sp_configure items
    • Migrate startup stored procedures
    • Migrate all linked servers
    • Migrate all XE sessions
    • Migrate Audits
    • Migrate any server level cryptographic objects
    • Migrate all jobs and agent settings (operators, alerts)
    • Migrate SSIS stuff
    • Migrate Resource Governor data
    • Migrate Database Mail settings
    • migrate replication settings at the instance.

    Database Level

    • Backup all databases
    • Backup and certificates needed for TDE
    • Restore databases with new paths
    • Verify database ownership
    • Ensure backups are running on the new instance

    As a side note, dbatools will perform much, or maybe all (still trying to determine that), of what you need. There is a Start-SqlMigration that is very impressive. While I would still want a checklist to ensure the new system works as needed, I think I’d use the PoSh tools and then add anything else I need to them.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.7MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • What’s Your Test Plan?

    I ran across a post on upgrading a SQL Server instance, where the original poster (OP) was asking about a document to upgrade from SQL Server 2008 to 2014. That’s a big ask, especially as not many documents tend to be written to go across three versions. The official ones, or the people that document well, tend to upgrade every version, and so they have a document to go from 2008 to R2, and from R2 to 2012, etc. However, given the pace of Microsoft releasing things, perhaps we ought to build a document and checklist across every 2-3 versions since many people may be upgrading from 2005/2008 to 2014 (or soon, 2016).

    Apart from having the document, one of the questions was a list of what to test. That struck me, as I’m big on testing, and have tried to keep comprehensive plans when I had important systems. However, many of you might be like me and don’t consider most systems to be “important”. For those systems, a patch, a Service Pack, an application upgrade was really tested by applying the change to a test server and asking users to see if they could use the application. I’m not confident that there was any sort of comprehensive look at the system in these cases, but this system worked most of the time.

    There were some instances that we deemed important, usually measured as such because a failure would mean some high level manager would call my manager’s boss and smelly things would slide in my direction. In those cases, we had a list of the functions and processes that needed to work. These could be application functions, queries, ETL packages, reports, or anything that would cause a user to complain. This list became our test plan, and it was kept up to date. Usually back dated, since we weren’t sure what new things were important until they failed for some reason, but once we received a ticket on an item, we added it to our list. We went through the entire list for upgrades, ensuring each item worked.

    I’m wondering, do many of you have a test plan for your systems? Any system? It doesn’t matter if it’s automated or manual, but if you had to patch/upgrade instance X, are there a list of things you’d verify? Or is the system not that important, so you’d just make sure the database service was running? Let us know what your test plans look like.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.1MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • The Push to Upgrade from SQL Server 2005

    It was interesting to see the results of my straw poll last week on SQL Server 2005. Quite a few people still run that version, and some aren’t looking to upgrade anytime soon. However when support ends, you should be aware that there is potentially an impact to your organization.

    If you work for a government, or you are bound by regulations such as PCI, HIPAA, or something else, you need to upgrade. I would hope that an audit sometime in the last 3-4 years would have alerted you to the issues of running unsupported software, but given the wide variability in auditors in the world, perhaps not. In any case, consider this a warning that you should be sure any databases that contain data regulated by law are upgraded by April 12, 2016. Otherwise your organization will likely be out of compliance with the regulations.

    If you haven’t upgraded, and want to avoid an upgrade for the next decade, this is a tough time. I’m not sure that SQL Server 2016 will RTM before support ends, which is the version you’d like to pick. This means that counting on this event is unwise. Even if you were sure SQL Server 2016 will release on April 1, is that really enough time to test your application?

    That’s really the bottom line for most of us. We need to test and be sure that an upgrade will, in fact, work for our particular environment. We need extensive testing, which probably means that those of us aiming to upgrade should be aiming for SQL Server 2014 (or SQL Azure Database). Microsoft would like you to choose one of these, as it benefits their bottom line, and they are pushing customers to upgrade. However, that’s somewhat the nature of software. We need to upgrade at times in order to maintain our systems, and vendors won’t support old software indefinitely.

    There are a couple months left, and I think you’ll need all that time to be sure your software still runs fine. I’d recommend you use new hardware, a completely separate instance (side by side upgrade) and use Distributed Replay to capture a workload on your existing instance and replay it on the upgraded server to check that your system performs well. To do this, you really need to be sure you are monitoring your current (and the upgraded) server to measure timings and performance.

    Monitoring isn’t simple, but it’s not that hard either. You should always be monitoring your system (we use SQL Monitor at SQLServerCentral), as well as keeping baselines, including wait statistics, that you can use to analyze the performance of your database. This is invaluable in determining if your new database will outperform the old (hint, it should).

    However you approach your upgrade from SQL Server 2005, be aware that you need to be on SP4. I recommend you use the Upgrade Advisor, perform a side by side upgrade, and don’t be afraid to use the compatibility level to allow the database to continue to behave as a 2005 instance. Above all, be sure you have good backups you’ve tested, just in case things fall apart.

    Lastly, SQL Server 2008 and SQL Server 2008 R2 are both out of mainstream support, so don’t forget about future planning for those versions as well. Best of luck with your upgrades, and please let us know if you find specific issues or problems in upgrading. Post a note in the forums to get help, or maybe help someone else complete their own upgrade.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 4.4MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Who’s Got a Preteen SQL Server?

    This year we will have the end of extended support for SQL Server 2005. Microsoft has been reminding people they should upgrade, which may be important if your organization requires support for the running software. Personally I think if you’re still running SQL Server 2005, and it works for you, why upgrade?

    It’s been ten years since that version was released, and while it was a huge step forward with the SQL CLR, better mail, and more, it had plenty of issues. Since that time we’ve had 4 major releases with a fifth coming this year. All of those versions have built on the base of SQL Server 2005’s completely rewritten codebase and have improved many features.

    Recently I got a note from someone that noted that they have been upgrading their SQL 2005 systems over the last few years (along with SQL 2008 instances) and have made great progress. however they still have a few SQL 2005 instances that they are racing to upgrade. This person was wondering how many others are out there facing similar struggles.

    So this week I’m wondering. How many of you have those instances growing up and getting close to double digit ages? Are you working to upgrade SQL 2005 instances? Have you been thinking about those 2008 instances who are already out of mainstream support and will leave extended support in a few years?

    Let us know how you think about your aging database software.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.0MB) podcast or subscribe to the feed at iTunes and LibSyn.