October 30, 2015 at 11:36 am
I have a SQL Server 2008 instance that is running on "LiveServer" our production database (ProdDB) - and we need to upgrade to 2014. In order to do some upgrade testing, I spun up a VM with the same version of SQL server on the test VM (TestServer), did a backup of the production DB from the live server, and restored it to TestServer under a different name (ProdDBUA).
I then installed SQL2014 Upgrade advisor onto TestServer, and ran it, checking all the boxes (reporting services etc..) and it all came back clean - no issues whatsoever - not a single warning even. I'm under the impression that stored procs/functions etc... all reside within the DB, so a backup will include those. Is that correct?
The problem is, I know I have stored Procs, functions and views that use deprecated joins in that LiveServer.ProdDB. What do I need to do/configure/check in order to make sure that the Upgrade Advisor is actually checking through all that T-SQL that has deprecated code? I want to have a list to give to my report writers of procs/functions/views that need to be rewritten prior to the upgrade going live.
I'm not a DBA by any means, I'm a business analyst that works in the IT Department - so I know a bit about how the data flows, can read SQL, and understand the concepts of SSRS, SSIS and relational database design - I'm just not as technical as the rest of my department.
Anything I need to do? Suggestions? If there is a modification that needs to be run on the TestServer.ProdDBUA, a cursor to change the path etc, I can get one of my guys to do it.
Edit: I just kind of got thrown into this position, so I now have a reason to belong to a SQL forum. Hi! I'm pretty active on the MS PowerBI forums, so I look forward to learning a lot while I'm here as well. Thanks in advance for helping a newbie out.
Edit2: DB is running in Compatibility mode 90.
November 3, 2015 at 6:33 am
Welcome 🙂
Try changing the compatibility mode to 120 and see if it complains about anything?
November 3, 2015 at 6:54 am
Well, right now the only DB instance that I can be sure is working properly is my Live production one. I don't think just changing Compatibility mode to 120 on a Live Server is the wisest choice without testing. I just don't think UA is actually checking all the code in my restored DB - because I know I have deprecated code in the SProcs.
The plan is run UA to find what I need to change, change those things, and re-run UA until all the warnings are taken care of. Then bump Compatibility mode to 100, run again, migrate to new server, test application, bump to 120, test application.
So my issue now is just that I don't think UA is checking all the code in the DB.
November 3, 2015 at 7:06 am
Sorry, I meant ask to change it to 120 on your test server?
You say you installed 2014 on your test server and have restored the DB from LIVE to test right?
The best way you could find out if this upgrade will work, is to run a production workload against it? Ask your testers to test against it, create another VM, install the application(s) and do a fully functional test against it. Run a production workload against it and see if there are issues with your batches etc.
Collect a replay trace from LIVE and execute it against test?
http://www.keepitsimpleandfast.com/2010/08/use-sql-profiler-replay-traces-to.html
November 3, 2015 at 8:16 am
Ohh, I get what you were saying now.
No, we installed 2008 on our test server, in order to run the 2014 Upgrade Advisor - but got no results. We backed up our C90 Live DB from 2008, and restored it to a test server running 2008.
I'll take a look at that trace option to simulate a production workload on our test server once we get UA working. I got some advice that we can just run UA against our live server because it is so lightweight - so, we'll do that, and begin fixing problems per that log - and re-run UA etc... until we get a clean-ish log. After that, we can install 2014 on the test server, restore a fresh live backup to test, and start working on C100 and C120.
Thanks for the advice!
November 9, 2015 at 12:08 pm
We were able to run UA this weekend on our production server (2008r2), and it came back with only 2 warnings, one about max of 64gb ram, and the other about migrating reporting services - both of which we were already aware of. The UA was run through an RDP connection to the server, with a local install (on the server) of UA, using a domain administrator account - the same one we use for backups/restores etc... so it -should- have full access to the SQL Server instance.
It didn't find any of the deprecated TSQL joins that I know exist within the Stored Procedures. Do we need to run it as the SQL server SA account? I'm at a loss for why it isn't catching any of the stuff it should be in the stored procedures. Any ideas?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply