0

I am planning migration from old SQL server 2000 db to 2014 (through 2008 extra step). I Wonder if stored procedures will work with no tweaks or arrangements are required

  • Why only upgrade to SQL Server 2014? – MrTCS May 31 '18 at 16:20
  • Well, yes 2017! – Riccardo Jun 5 '18 at 6:51
3

You should check breaking changes.

you can use upgrade advisor or

dbatools - Test-DbaMigrationConstraint

This function only validate SQL Server 2008 versions or higher.

I have written an extensive list of things you should check when you migrate from 2000 to higher versions

2

For the most part...yes. However, there is no way for anyone to be certain. I would suggest you run the Upgrade Advisor to scan your database for potential issues. https://msdn.microsoft.com/en-us/library/ms144256(v=sql.120).aspx

The tool will tell if you are doing anything that is no longer supported in later versions of SQL Server. This will catch about 90% of the issues that you may encounter.

Besides running this tool against your database, you want to test, test, and test again. This will be the only way to ensure that your applications will work post upgrade.

  • Thanks. Could you clarify test? Just run client app and see if something unexpected will happen? – Riccardo May 31 '18 at 15:53
  • BTW sever running win 2003!!!! – Riccardo May 31 '18 at 15:54
  • Testing from a DBA perspective is basic smoke testing. Does the app connect, does it appear to run without error? This is the extent of a DBA test. However, the business needs to be involved with any migration. They are the ones that need to come up with a proper test to ensure that things are working post migration. They will be the ones that actually run the stored procedures and queries and will know what to expect upon execution. If things look wrong, they can raise that during testing. – Chris Lumnah May 31 '18 at 15:58
1

This is something that needs to be tested since many commands have been deprecated between 2000 and 2014. Assuming you are moving the database to another server I would test this first.

Restore the database to 2008 and then to 2014 and then attempt to run each procedure. You may even be able to use the distributed replay controller feature to automate this. On the 2014 server try to start a profiler trace with the DRC template against your 2000 server. Then do a test migration and replay it while running an extended events trace for deprecated commands. Some procedures may even fail to run outright.

  • Thanks Alen, should I also expect some changes in queries done form client to server? – Riccardo May 31 '18 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.