Sometimes things get a bit frustruating, recently we had to setup a standard Active / Passive (though they, Micro$oft, call it something different now) cluster.  The new thing here for us was it is SQL 2008 on Windows 2008 R2.

So maybe these two little items will help you avoid some issues...

1.  Slip Stream Service pack 1 before installing.

I don't remember the exact error, but the solution to the problem is to slip stream service pack 1 into the sql server media before installing the cluster.

2.  Uncheck the DHCP option and put in the Virtual IP Address.

Also be aware the Windows 2008 is just a bit quirky at first, the new UAC (User Access Controls) take a bit too get used to, as well as right clicking on your executables and selecting "Run as Administrator", even though you are logged in as an administrator.

My other favorite Windows 2008 message is when you are browsing a folder structure in Exploder (err... I mean Explorer) and the OS throws up a prompt about "Not having permissions, would you like to continue anyway ?".... How the f*** can you not have permissions, but than continue anyway ?  Stupid Computer ! 

Anyway, lots of good information on how UAC Protects you from yourself (sounds very much like a Democrat !); read up on it:  http://technet.microsoft.com/en-us/library/dd446675%28WS.10%29.aspx 

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 

You know Microsoft SQL Server is considered an enterprise database when Management tells you it costs too much !  Micro$oft has finally done it, and management has declared they don't want it.  I don't necessarily agree, but I do follow orders well.

The cost increase of 25% and virtualization licensing changes in R2 are unacceptable.

We're now reviewing Open Source databases and conversion costs, we're also contacting all our vendors and will be insisting on supporting some other DBMS than Microsoft SQL Server either now or by the end of the current contracts.

Microsoft has done a great job with sql 2008 r2, most features we don't need  [BI / MDM], didn't ask for, don't want and a cost increase to boot.  There are good features in SQL 2008 R2, but not at this cost; especially when we won't be utilizing most of the new features.

I'm not looking forward to Open Source, but I might be impressed, 10 years ago IBM DB2 pricing was reaching the tipping point and I began to learn a little respected database called Microsoft SQL Server, now it's time to begin learning something new.

I'd love to hear from some shops that began supporting open source databases, as obviously it will take some time to convert; but all new implementations on sql will most likely cease.

Hard to believe, but it's true !

I've had a nice machine that came with windows vista, 64 bit; but it locked up constantly.  Over the months i've tried many things, even went with installing windows xp 32 bit, same behavior.  Finally I had concluded it must be a bad peice of hardware causing this mystery lock-ups...and let the thing become a paper weight.

Finally decided to try windows 7 last week, popped the dvd in, formatted the hard drive (no sense upgrading a non-working os) and installed windows 7.

Wow, windows 7, right out of the box, my machine finally works.

It's been on for a week without a single "lock-up", amazing !  Micro$oft may have gotten one right.

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.

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.

This has to be one of the worst ever toys for kids.  There are some positive reviews out there for this, and I'm convinced those are paid reviews or something.

This toy does not work, it requires heavy objects to weigh it down and it is near impossible to get the car to run on the rope.

A huge disappointment for the kid awaits with this toy.

I don't blog product reviews, but this one sucks so bad i had no choice.

I'd pay money for someone to show me this toy working...

January and February are peak times for my company's business; now I can start to focus on new things, here is what I learned during peak and where 2010 will focus:

• Capacity management is the 2nd most important thing dba's do.
• Performance tuning and monitoring is the most important thing (during peak)
• Locking and Blocking is the biggest scale issue i regularly see.
• Mirroring on SQL Server standard edition sucks (especially for geographically disperse sites).
• Mirroring on SQL Server enterprise edition rocks (though the cost factor is prohibitive).
• Informatica is no better an ETL Tool than SSIS
• Oracle and SQL Server are a pain in the ass to make work together (Oracle DBAs are'nt much help either)
• 1 terrabyte of disk space is almost enough !
• Re-indexing and defragging is analogous to politics and religion and DBA's should be barred from discussing the topic
• Encryption is a pain in the ass.
• Surprise - Merge replication actually works and is not near the PIA I thought
• 6gb of RAM is never enough
• Polyserve is not worth the effort
• VM Ware with VMotion is awesome
• VM Ware is a good alternative to Polyserve
• Hyper-V is not there yet
• SQL 2008 R2 licensing sucks
• SQL 2000 is finally no longer meeting the business requirements and we may finally get funding to upgrade the remaining instances !

Need an H&R Block Coupon ?

Coupon's for new H&R Block Customers, $30.00.

Drop me a comment.

Stock Break Even Work Sheet.

This is something I worked out a while back.  Oddly I couldn't find an easy formula to plug in for excel, so I had to create one.  Recently somebody asked me for it, so I wanted to post it here so I could reference it.  The reason for creating this is I was trying to buy some ford stock at a 1.60 a share and wanted to know what the break even price was if I had to bale out of it.  Of course I didn't bail out and today it's a $9 something a share, "Quality is job 1"; Ford made my year !

Basically I needed a formula that would help me calculate how much a stock must rise in price to cover my commissions and taxes and break even.

There is some really good reading here in this book, Page 116, "Calculating the Stock Break Even Sales Price", my formula is adapted from that, good book to read.

http://books.google.com/books?id=YmZV-Jxt28kC&pg=PA114&lpg=PA114&dq=break+even+formula+for+selling+stocks&source=bl&ots=dR84EvLZGH&sig=dL-LdOG4oEptNx1wrrW9xaisjUY&hl=en&ei=NUGxSeHSAo3Btgequ-TDBw&sa=X&oi=book_result&resnum=1&ct=result#v=onepage&q=&f=false

Technincally my formula is not 100% accurate as depending on your particular tax situation the rate can can change between 15% or 28% and there is some "cost basis" versus "individual lots".  The forumula is just meant to be used as a guide, your actual mileage may vary.

I've updloaded the excel spreadhseet here.
Stock_Break_Even_Price.xls (22.5 KB)

Here is the formula, surprisingly my Algebra was terrible and it took me awhile to solve this.

Variable Description
 
N # of Shares
B  Buy Price per Share
C1  Buy Commission
C2 Sell Commission
P  Purchase Cost (Cash out)
X Sell Price Per Share
T Total Cash In
TR  Tax Rate
TX Total Taxes
G Gain
 Calculate Gain

Example Ford Stock 1.60 per share, 100 shares, 10 commission for both buy and sell
 
Description   Formulas                          Example
Cost            (N*B) + C1 = P                 (100 * 1.6) + 10 = 170
Gross           (N*X) - C2 = T                 (100 * X) - 10 = T
Taxes           ((N*X) - (N*B))*TR=TX     (( 100 * X) - (100 * 1.6)) * .15 = TX
Break Even    T-P-TX=0                        ((100 * X) - 10) - 170 - ((( 100 * X ) - (100 * 1.6)) * .15) = 0
                                                        ((100 * X) - 10) - ((( 100 * X ) - (100 * 1.6)) * .15) = 170
 How to solve for x ?                            (100X - 10) - ((100X  - 160) * .15) = 170
                                                        (100X - 10) - (15X  + 24) = 170
                                                        100X - 10 - 15X  + 24 = 170
                                                        100X  - 15X = 156
                                                        85X = 156
                                                        X=1.835294118

Migrating from one cluster technology to another or even with-in the same technology is fairly easy.  Recently I'm run into an issue where we need to migrate a SQL Server Instance from a HP Polyserve Cluster to a Microsoft Cluster.

There were two issues I found in setting this up:

• Installing SQL Server on a Microsoft Cluster requires a virtual name
• Keeping the exact same Port Number

The reason for the above two issues was to keep the down time to an absolute minimum and ensure there were no changes necessary to the application or infrastructure (firewalls).

Fortunately both HP Polyserve and Microsoft Clustering use virtual names, this is what makes this possible.

I found the following two links helpful:

How to: Rename a SQL Server 2005 Virtual Server
How to change the network IP addresses of SQL Server failover cluster instances

The key to making this happen is to install SQL Server using a temporary Virtual name and IP Address and ensure to use the EXACT Same instance name.  Instance names can not be changed with SQL Server 2005 (or at least it's not supported to change them).  Changing the port number is pretty standard stuff.

Now you can pre-test your migration of databases and user logins, and load test the new hardware. 

At the designated change time we performed the following.

• Take the Microsoft Cluster Off line
• Take the Instance on HP Polyserve and delete the Instance and virtual name (binaries and data files will be kept as a backout plan)
• Using the SQL Server Configuration editor, change the IP address on all nodes in the Microsoft Cluster:
• Using the Cluster Administrator change the SQL Server IP Address
• Using the Cluster Administrator change the SQL Server network name
• Bring the Cluster on-line
• Test

The one issue we ran into was with logical networks and VLANS, I don't have a complete understanding of network topology, but only certain logical networks with-in our environment can host different ranges of IP Addresses.  Initiall we built the new cluster on a logical network that was unable to host the existing virtual name and the switch failed, ensure to talk to your network, windows and DNS engineers about exactly what your wanting to do so they can build things properly the first time, as they don't like switching and changing things twice any more than DBA's do !

SQL Server security, logins, auditing and reporting still not where it needs to be ?  Maybe someone can help me with ideas, if so email, me or leave me some comments.

I've been through a lot of requirements and implementing everything the information security group wants is like Macgyver with duct tape.  Currently this is for SQL 2005, but I'd like to know how to implement it for SQL 2008 as well.

Currently I'm meeting the following Information Security requirements:

• Limit Logins by specific IP or IP Ranges in combination with Time of Day [login trigger]
• Limit Logins by Time of Day [login trigger]
• Disconnect logins that are connected past there authorized time. [job running every 5 minutes]
• Keep Record of All Logins (success or Failures) [daily job to scrape error log]
• Keep Counts of Login Failures [daily job to scrape error log]
• Keep track of Date and Time Password was changed (sql authenticated only) [Tracing]
• Provide reporting and alerting that shows counts of failure, by month, by id etc. [Reporting Services and SQL Agent]
• Limit number of concurrent connections by ID and by Global Count [login trigger], so the max number of connections for an instance may be 1000, and the max for a user maybe 5, and the max for the application id may be 300.

Now they want me to selectively enforce password length and expiration based on the following requirements for SQL Server Authenticated:

• Service accounts  28 characters, expire yearly  (accounts that are not, or should not be used for general sign on’s and limited by what machine(s) they can login from)
• Admin accounts 15 characters, expire yearly (accounts that are used by DBA’s)
• User accounts 8 characters, expire every 90 days (normal user accounts) [this is easy as our Active Directory can enforce this]

It just seems that meeting all these requirements in SQL becomes very difficult to administer; we're running a combination of:

• Dedicated database for reporting, configuration and capture of statistics
• Login Trigger (difficult to administer and one little issue everyone is locked out)
• Tracing (to capture when a user changes their password)
• SQL Agent jobs (to clean up, scrape and roll over reporting tables)
• Reporting Services (for reporting)
• Enabling the DAC
• Enabling Login Auditing
• Enabling CCC (Common Criteria Compliance)

My counter-parts in Oracle seem to have an easier time meeting all these requirements, with less overhead, easier to administer and keep running, and less danger of locking everyone out.

SQL Server continues to frustrate me with this, anyone have suggestions or an easier time with SQL Server 2005 or 2008?

All these things should be available out of the box, duct taping all that stuff together in sql server is absolutely brutal, documenting and training someone on it is even worse.

It does provide good job security, as let the suits out source the DBA Staff after implementing all of these requirements and they'd sure have a tough time finding someone to run this stuff easily!

The "Suits" and "Information Security"!