Polling v14/SQL Server 2014 upgrade experiences

> 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.

So far there's only been on major SQL incompatibility, spread over a number of extremely old procedures: SQL Server 2016 no longer supports the old RAISERROR syntax of 

RAISERROR [error level] [error string]

(Looks like it actually ended in 2012, but was still available in 2008)

The "RAISERROR" errors were a surprise to me, too. Documented somewhere, I think, but as usual not in the docs I read. ;-)

New Issue.  This issue is particular to being a RAMP user, to be sure, but could affect anyone who has made the same assumptions I have.

Background: most tables in Tessitura have a set of "audit" columns, like this:

create_loc varchar(16),

created_by varchar(8),

create_dt datetime,

last_update_by varchar(8),

last_update_dt datetime

I have duplicated this in all of my custom tables, of which there are maybe 50?

When these tables are updated by one of my stored procedures I make sure to modify the appropriate rows, using a few built in SQL functions: user_name() for created_by and last_update_by, host_name() for create_loc, and CURRENT_TIMESTAMP for the dates.

Generally when run in Tessitura you would get your userid (e.g. 'gawainl'), since those are synonymous with the sql user associated with the account.  If run by an administrative account, you would get 'dbo'.  So you can generally tell when something in Tessitura was created or update by a procedure, as the columns will show 'dbo' instead of a userid.

But in updating to v14, RAMP is also tightening up security on the admin accounts handed out to licensees (wise), and not giving them full access.   However now I'm getting a different user name back for user_name(): "KRIOSTESS\calpuser18".

But the space for a user name in all those tables is still varchar(8).

This behavior is all over the place in my code.  Most of the SQL Server Agent Jobs I've attempted have failed on account of this.  Pretty much any procedure is going to fail when run outside of a Tessitura Client.  Updating any custom System Table outside of the System Tables pane in Tessitura will fail, since they all use triggers to update those columns.  I'm kind of astonished that anything works, since all customer account updates fire about 7 custom stored procedures.  Maybe those show the userid of the logged in user of the client.

Anyway, the solution is simple enough, just labor intensive, so I thought I’d put out a warning.

Gawain Lavers said:

But the space for a user name in all those tables is still varchar(8).

An embarrassing, Windows-95-era limitation. "Sorry, new user, your Tess username can't match your AD username because... ¯\_(ツ)_/¯"

Ugh: so I was looking to replace instances of user_name() (as per above), but it looks like in some places I used the built in USER, which is going to be a lot harder to search for.

Which brings up: at some point I was looking to organize my SQL code into a solution, but was dissuaded, I think with the claim that solutions or projects were being dropped by SSMS. Am I delusional? Looking back into it, they seem to be alive and well.

Gawain Lavers said:

at some point I was looking to organize my SQL code into a solution, but was dissuaded, I think with the claim that solutions or projects were being dropped by SSMS.

That would be sad for me, since I just started using them. :-)

I had grand plans at first, but ultimately my most-used SSMS solution is a "stuff I have open all the time" solution, a nice labor-saver for reboots/SSMS restarts.

Ryan Creps has alerted me to the fact that there are Tessitura functions that I should have been using for this: dbo.FS_USER() and dbo.FS_LOCATION().

Gawain Lavers said:

functions that I should have been using for this: dbo.FS_USER()

...which does a substring(... 1,8) for you. :-)

Oh!  I just used it in our Live DB (pre RAMP changes) and it gave me 'dbo', so I thought it was just returning that as a default if it didn't match to T_METUSER, but I see that it is just truncating, and that's bad, because any admin user will appear as 'KRIOSTES'.

I wound up creating my own function: it's nothing fancy, it allows you to override the returned id (I use "admin", not a username we have, to set apart updates created by scripts: hope to make it do something more clever in the future).

We completed our 14.0 Upgrade and so far as I can tell, these are the only SQL Server 2008->2016 incompatibilities that I tripped.  Hope this proves useful to other people doing the upgrade (which should start coming to other RAMP licensees early next year).

--Gawain