RSS

Monthly Archives: January 2016

Learning about an old join syntax, the hard way

Today I discovered that there is an interesting way to join tables

select c.name, o.order_amount 
from customers c, orders o
where c.id *= o.customer_id

But before you get excited, this syntax has been long gone for years. *= is no more.

We have had a critical system that started a never-ending migration from one version of the application to another. Remaining were a few sets of users that were still on the old version for a couple years. I kept putting off upgrading the SQL Server in hopes that it would one day go away but the time came that I couldn’t wait any longer. In a farm of 200+ SQL Servers, this was my last SQL 2005 server.

During the migration planning I took a trace and ran it through upgrade adviser. It was about a 15 minute trace during peak business hours and captured a decent amount of activity. Everything checked out with no critical findings. We did discover that the backup client would need upgraded but that would be done anyway as part of the parallel server upgrade.

I got a little greedy, skipped Windows 2008, and spun up a Windows 2012/SQL 2012 server and restored a copy of the database to that server. Some testing was done but not quite enough.

When the big day came to migrate the database to a new server, I followed these steps
1. restore the most recent full backup and leave it in recovery (done a day before)
2. set single user mode and disable all the user accounts on the old server
3. take a differential backup on the old server
4. restore the differential and recover the database on the new server
5. power down the old server and change its host A record to a cname to the new server (I’d rather change connection strings, but this thing is really old and we couldn’t be sure we found them all)
6. Map logins and fix any orphans
7. Take a full backup and start a new log backup chain

Everything went better than expected. I actually planned for 3 hours of downtime in case I had to do a full backup and restore for some reason. It ended up only taking 20 minutes and I could see the clients automatically reconnecting so things looked good. We had planned a thorough checkout, but after the 3 hours so there was about 2 hours and 40 minutes of users connecting before we were really sure everything was ok.

As it turns out, my upgrade adviser trace wasn’t enough data. This old syntax was used in some startup procedure so it didn’t show up in the trace. I’m not sure if upgrade adviser would have caught this but the deprecated code is clearly documented.

https://technet.microsoft.com/en-us/library/ms143729(v=sql.105).aspx

Another interesting thing I learned was SQL2012 does not have the SQL 2000 compatibility level for databases available. That means during my restore, the database was upgraded to 2005 and this old syntax broke. However, SQL 2008 R2 does have the SQL 2000 level available.

I was considering aborting, changing dns back, powering down the new server and powering up the old server. That would have been relatively quick and I could have had my day back. But, that would sign myself and others up for another weekend maintenance window.

I decided to double check this was the problem and I powered up the old server and verified that the query worked on the old database server and not on the new database server. I then checked another 2008 server and tried the syntax out on another old database I found, sure enough it worked so I had another option.

I took the next couple hours to build a new VM, install SQL 2008. I followed the same steps as before and fortunately this time things worked out much better. Just a 6 hour upgrade that should have taken 20 minutes. If you have any SQL 2005 servers still around, waiting until that April 12th end of support deadline, make sure to watch out for the “*=”.

 
Leave a comment

Posted by on January 12, 2016 in SQL Admin