We're hoping to upgrade shortly to v14, and we have a very, very large corpus of custom objects in our database, so my primary concern with the upgrade is if anything in our customizations is going to not be compatible with SQL Server 2014.
I know this happened during the last SQL Server upgrade for us: a large number of reports, agent jobs and the like caused all sorts of curious havoc, and it came down to 2008 not allowing implicit truncation of strings.
We are on a brutally tight upgrade schedule, so I was hoping to hear from any early adopters if they'd run across any big differences between SQL versions such as that example.
Thanks,
Gawain
> if anything in our customizations is going to not be compatible with SQL Server 2014.
Over the last ten years we've upgraded (mostly by moving from VM to VM, rather than upgrading in place) from SQL 2005 to 2008R2, 2012, 2014, and most recently 2016, and haven't ever run into anything breaking in an earth-shattering way due to the SQL Server upgrade.
A few vaguely remembered snags, nothing that ever took more than a few hours to clean up. Kind of surprising, actually, compared to some other things, e.g. changing backup software has been much more time-consuming.
Am I hallucinating a memory, or does Microsoft provide a tool that will check all of the code in your database for compatibility with an upgraded SQL Server version?
This kind of situation also makes an excellent case for T-SQL unit tests. :-)
> does Microsoft provide a tool that will check all of the code in your database for compatibility with an upgraded SQL Server version?
I've not heard of such a thing; you might be remembering this?:
https://msdn.microsoft.com/en-us/library/ms144256(v=sql.120).aspx
I'd have used it for the SS2014 upgrade if I'd heard about it then!
This looks like it would be an interesting thing to try:
http://www.sqlservercentral.com/blogs/gorandalfs-sql-blog/2015/04/13/how-to-test-existing-t-sql-code-before-changing-the-compatibility-level/
That looks cool...wonder if I can get RAMP to run it for me.
Chris Jensen, I was wondering what your take is on doing a side-by-side vs new VM install of SQL 2016. We've been thinking of installing SQL 16 on the same VM that has our Test and Staging SQL 2008 instances but don't want to break everything. How big of a project would it be to migrate everything to a fresh server? Any IP/port/other considerations we should know about? Thanks!
Caitlin Boake said:How big of a project would it be to migrate everything to a fresh server?
I tend to like the idea of starting fresh on a new VM, and while scripting/exporting/importing everything possible (e.g. SA jobs, Windows scheduled tasks, etc.) is helpful, there are lots of little things to remember, e.g. permissions on shares, paths in ini & config files, paths to images in reports, etc..
On balance, I've found in-place SQL Server upgrades to be less trouble, though I'm sure we'll move to new VMs again, probably no later than whenever we move to a newer major version of the underlying OS.
Caitlin Boake said:Any IP/port/other considerations we should know about?
None that I can remember. DNS is your friend. :-)