David Curlewis

Category: SQL Server

  • 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

  • Where am I?

    Well, right now I’m sitting in a hospital room waiting for my wife to come out of surgery – nothing too serious. In the mean time it feels like I’ve got a rather nice little office in fact; a comfy(ish) chair and desk in a private ward with good free wi-fi access, and coffee just down the hall.
    But I’m not here to write about my current physical surroundings, so, moving swiftly along…

    Where am I?Many of us regularly connect to more than one SQL server instance, even if it’s just your dev/test instances, and production.  This means we need to be sure of which servers we’re connecting to (although chances are it doesn’t cross your mind).

    A couple of folks here in the database team have, in the past, come across some strange behaviour with Management Studio where the bottom right-hand side of the query window will display a server/instance name, but running a select @@servername returns a completely different server name!

    We’ve also seen a case where scripting from the GUI (I think we were scripting out a shrink file command) created the script as usual, but the query was connected to a different server to the one we were scripting from.

    We never nailed down exactly what caused the issues, but I’ve never seen it myself with the latest patched version of SQL 2008 tools, so hopefully it was a rare bug that has since been fixed.

    Nevertheless, the thought of unintentionally truncating or dropping some production tables doesn’t fill me with warm fuzzy feelings, so I’m a little paranoid now.  If I’m running something potentially risky I will often check the server name, just in case.  I’ve set up “select @@servername” as a keyboard shortcut in Management Studio’s keyboard settings, so I just hit CTRL-0, and the server name pops up. Easy. 🙂

    Right, I’m off to find this mythical free coffee machine…

    Cheers
    DB Dave

    Edit: I’ve just found a blog post from Jason Jarrett from a few years ago, who seems to have come across a similar (if not the same) problem with SQL 2008 SSMS.  There’s also a link to a Connect item where Microsoft have commented (the old “it’ll be fixed in a future version” gem).