Our SQL Server “Denali” experiences – Part 1

I got around to installing SQL Server 2011 (Denali) on a production server last week, and fortunately it’s been pretty slick so far. And in case you’re wondering why it’s on a production server instead of a dev or test box, this particular SQL server is one of many that we use as a “read-only” layer between the webs and our core databases, so it’s actually pretty easy for us to add a new production server and siphon a small amount of load onto it. We can then slowly ramp up the amount of production load to see how it performs. If it turns to custard, we can quickly remove load from it as well.

To start with I had to subscribe the database to a few of our core SQL 2005 transactional replication publications, which was (fortunately) totally uneventful.

We use full-text quite extensively, and going from SQL 2005 to Denali meant that I needed to create a new data file and filegroup for the full-text index to use. I didn’t have to create a new physical file for the full-text filegroup, but we put our full-text catalogues on RAM drives, so I needed to be able to move it. Once that was done, setting up full-text itself wasn’t very different from SQL 2005 (other than needing to specify a filegroup destination rather than a directory path).

Once replication latency had come down, and the full-text catalogue was fully populated (which didn’t take long at all), I needed to synchronise the database objects with another production server to make sure there were no differences such as missing tables and indexes, different version of stored procedures, etc. This was the biggest hurdle so far, since our current comparison tool (Red Gate SQL Compare) doesn’t yet support Denali. I tried a few alternative products, and eventually found a tool called “SQL Delta” which worked! It’s only an evaluation copy though, so for the next 14 days I’ll be okay… after that I’ll need to figure something else out. 🙂

That’s as far as I’ve managed to get so far, mostly because actual work keeps getting in the way of the cool stuff (like testing new hardware, new versions of SQL, etc.), so I’ll follow up next week with another post on how the first round of testing goes.

Now go on and read part 2.

Cheers
DB Dave