Recently we renamed a server (VM Guest) that was hosting a stand-alone SQL Server, default instance.  There always seems to be something I forget when this happens and this was no exception.

It seems this time I forgot about a linked server; in our environment we create a linked server called LocalHost, which loops back to itself (this is for a monitoring program that monitors long running jobs); when the server was renamed we started seeing lots of NT Authority\Anonymous Login failures in the SQL Error Log, and the process that monitors long running jobs was failing.

A prayer never hurts when renaming a box hosting SQL Server, and I'm the kind of guy who can be inspired by Britney even with the added weight!

So I decided to put this little blurp out there so I'd have a checklist to verify things.

It seems most of this is a rehash of something I found here: http://msdn.microsoft.com/en-us/library/ms143799.aspx , But I was too lazy too read down to the linked servers portion !  Sometimes articles on MSDN and other peoples blog posts I save the the links to have a way of disappearing, so I wanted to create my own blog post, just for selfish reason that I need my own library of "checklists" and "how to".

Checklist for renaming a sql server:

1.  Rename the server (this is the sql instance, the server I will defer to the proper operational teams to handle the necessary rename, active directory, dns stuff).

sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO

sp_dropserver <'old_name\instancename'>
GO
sp_addserver <'new_name\instancename'>, local
GO

2.  Fix Linked Servers

I just drop them and readd them

3.  Fix connections.

4.  Verify SQL Agent and SSIS Jobs.

One Item I'm looking for help on is the groups that SQL Server 2005 creates, these still exist with the old computer name in them, it definetly looks odd, but appears functional.

Found a snapping turtle far away from his home, we decided to return him.  They can move much faster than you think !  He was calm until I picked him, they sure do have a reach with that neck so keep your distance !

Yes you can have sql server 2005 and sql server 2008 on the same cluster, this came up while turning an active / passive cluster into an active / active cluster to make better use of resources.  [They say not to use the terms active / passive any more, but it'll take me until sql 2010 to get correct my jargon].

See the section on ‘Multiple SQL Server Versions Within the Same Windows Server Cluster (SQL Server 2000 and 2005 with SQL Server 2008)’ in the link below:

http://download.microsoft.com/download/6/9/D/69D1FEA7-5B42-437A-B3BA-A4AD13E34EF6/SQLServer2008FailoverCluster.docx

I've uploaded the document here,SQLServer2008FailoverCluster.docx (3.75 MB), as I have a tendency to need to support things longer than Microsoft makes them available.

I did run into an issue with a necessary patch for the operating system that the installer did not catch until I'd filled out all the information, it then through a warning on the missing patch and would not let me proceed, of course the damned patch requires a reboot so I had to start all over.

This patch was for windows 2003, filestream support... of course I did not select to enable filestreaming, but no matter, it insisted on it.   At least it (the installer) found the issue before it started, as I really hate bombing or failed installations, but it was still a pia to go back, down load the patch, install it, reboot it, and then restart setup....i thought the whole point of the prerequisite checks was to find pre-reqs !  http://support.microsoft.com/kb/937444 

 

It's becoming an annual event, snake round up.

Links to previous years !

http://www.lifeasbob.com/2009/03/22/PiedPiperOfGrandview.aspx
http://www.lifeasbob.com/2009/04/27/PiedPiperOfGrandviewII.aspx

Less of them this year so maybe we're starting to get rid of them.  I saw 5 in the front yard at once, I managed to grab 4 of them all at once, but 1 got away !

I always thought the definition of getting screwed twice was this; A German student who auctioned her virginity and ended up paying 50% in taxes.

But it turns out that identity values in SQL Server can also screw you twice!

I used to really like identity values and problems have been rare, but this week, inside of a few days I've been screwed twice by identity values.  This is also the year of embracing merge replication, time to meet your new friend the guid!

No need to rehash what happenned with identity values, but altering a table from int to bigint with 30 billion+ rows is not possible.  Found two very helpful posts, i include their links here as they were most helpful:

To resolve (albeit temporarily) identity values running out for an int, do not forget you have the negative values.

DBCC CHECKIDENT ('ProcessActionHistory', RESEED, -2147483648);

http://sqlfool.com/2008/11/max-int-identity-value-reached-dbcc-checkident/
http://dbwhisperer.blogspot.com/2009/04/which-identity-column-is-running-out-of.html

Definetly we'll add a new automated health check, will probably fit in nicely with our script that check row counts occasionally to also start checking identity columns and raising alerts on certain thresholds.

I'll also be pushing back much harder on development teams that want identity columns with an int data type.

Well, it continues to be a pain in my ass, the inability to grant truncate table permissions on a table to specific users without giving them excessive rights.  SQL 2K5, no solution, SQL 2K8 no solution, well we did get a bunch of other stuff, so we'll continue to suffer.  I originally posted an article on Granting Truncate Table permissions in 2008, here is that link.  Recently the need has arisen to have the ability log failures for the solution.  The code was modified by Holland Humphrey and I'm posting the solution again with his updates.

The solution basically uses a schema, dba, to hold 3 tables for a stored procedure which is used to truncate tables.  The 3 tables, Truncate_List, Truncate_Audit and Truncate_Fail.  A user is granted permissions to a stored procedure in the dbo schema that performs the work and verifies that the table is authorized for truncate, and logs the success to truncate_audit and the failure to truncate_fail.  One could argue that truncate_audit could hold both success and failure, but I'll leave any possible enhancements up to those that desire to do so.

The original inspiration for this actually came from the Oracle group where I work.  Oracle has the same problem as sql "Grant Truncate on {table} to {User}"...so simple....but it's not there in SQL or Oracle.  I basically translated the Oracle solution into SQL Server.

Every upgrade we do from SQL 2000 to 2K5 or 2K8 uses this solution and allows us to reduce our permissions set to the lowest possible necessary.

Here are the scripts for the solution.

1.  Create a Schema Called DBA {if you prefer something else, adjust the tables and proc}.

2.  TruncateSolution_CreateTables.txt (4.67 KB)

3.  TruncateSolution_CreateProc.txt (4.34 KB)

Starting in December I purchased the following books.  Most of these were related to new projects where I work and knew I'd need to sharpen a few skills, Encryption, SQL Express, Merge Replication and security.

• Beginning SQL Server 2008 Express for Developers: From Novice to Professional - Robin Dewson
• Enterprise Data Synchronization with Microsoft SQL Server 2008 and SQL Server Compact 3.5
• Mobile Merge Replication - Rob Tiffany
• SQL Server MVP Deep Dives
• Expert SQL Server 2008 Encryption - Michael Coles, Rodney Landrum
• SQL Server Forensic Analysis - Kevvie Fowler [bought but not reviewed]
  

Reviews:

Beginning SQL Server 2008 Express for Developers: From Novice to Professional - Robin Dewson

This book was a disappointment, but I knew when purchasing it that it was probably not going to be of much help; but I had to start somewhere.  We're preparing to migrate from MSDE to SQL Express 2K8 in 10,000 locations; I was involved in the intial deployment (and patchings) of MSDE and I really need to figure out how to avoid the gotchas with SQL Express; this book wasn't it.  Over-all it's a good book, but it's not meant for an existing SQL Server DBA, I would expect even a Jr. DBA to already be familiar with the content.  It truly is meant for an access developer who wants to learn SQL Express; I gave it to our local access programmer at work {though it's actually a she and she's not in IT or a programmer, seems that is where most access db's begin life}, maybe she'll develop a new career path, we seriously need more women in IT, I digress.

Enterprise Data Synchronization with Microsoft SQL Server 2008 and SQL Server Compact 3.5 Mobile Merge

This book was great.  We're deploying Merge replication with up to possibly 10,000 differnt locations to SQL Express 2k5 and that is why I purchased this book.  While not an exact guide or road map for what we're doing [we're not using SQL Server compact edition], most of the concepts apply.  It has really been helpful, we're still in the planning and proof of concept stages, so it remains to be seen if we'll continue with this design at work, but I feel this book helped us greatly.

SQL Server MVP Deep Dives

This book was great.  I kind of avoided it at first, as I don't like books that too many people jump on as good, but this one lived up to that promise.  Very few parts of the book were lame ! [there's a glowing recommendation].  I put a sticky next to each new thing I learned and when I was done, i'd say there were at least 15-20 stickeys across the book, that's worth the money I paid for it.  It's also nice as you can read this book in quick "snippets" flipping to what subject interests you and what you have time to read.

Expert SQL Server 2008 Encryption - Michael Coles, Rodney Landrum

This book is good, though a bit dry, I'm still not all the way through it...reading about encryption is not what I'd consider an exciting topic, but it is a skill i'm going to need and it was seriously lacking.  We're preparing to bring in a 3rd party hardware device to provide consistent encryption and key management across the entire enterprise, which is why I needed this book.  So far it's been a great help, as I don't feel like a complete moron as I attend the endless meetings on implementing enterprise encryption where i work.

SQL Server Forensic Analysis - Kevvie Fowler

I haven't got to reading this one yet.  I'm looking forward to it, as security has become a major focus in ways I couldn't even imagine 2 years ago.  I thought the description of this book is interesting, as it approaches security from the aspect of determine what was compromised, from that I hope to learn better practices in security data....I'll update on that later.