Category Archives: DBA

SQL 2008 Cluster + Quest LiteSpeed

In today’s episode of provisioning, we encountered a slight hiccup with installing Quest LiteSpeed (arguably, the fastest most flexible SQL server backup and recovery solution.)  It seems version 5 now has some built in support for sql 2008 clustering, however during the install, we encountered an error when trying to install extended stored procedures to the alternate node within the cluster.  More details can be found here: http://sqlserver.quest.com/thread.jspa?threadID=6997&tstart=1

The provided solution, which essentially involved temporarily adding the machine account (AD computer object) of the alternate node to local admin group – enabled the Quest install to complete.

Hope this helps, should you encounter a similar issue.

SQL 2008 Cluster – Add Node 2, get Invalid SKU

When you use the GUI to add a node to an existing SQL Server 2008 Cluster you get an Invalid SKU error without any details on how to troubleshoot it.

It’s been reported to Microsoft that this is a bug and it should be corrected in the next release or maybe they will come up with some type of hotfix.

SQL Server 2008 Failover cluster installation works a little different than in previous versions (2005 and earlier). In SQL 2008 you first have to install the cluster in one (Active) node then you need to do the installation in the other nodes separately.

In Order to get around the “Invalid SKU” error you need to add the node by performing an installation using the command prompt bypassing the GUI.

Here are the Steps:

1) Open a Command Window

2) Change Directory to where your installation Files are at.

3) Edit the code below with your credentials, copy/paste the code and hit Enter.

NOTE:

Notice that my code I am passing parameters for Analysis Services and Integration Services Accounts and password; that is because I am also installing Analysis Services and Integrations Services; so if you are not installing those you do not need to pass those parameters.

/q = Silent or Quite mode. This will not display the GUI; However if you still want to use the GUI along with the Command prompt you can omit this parameter, but you will have to Click Next on the GUI. It still will work.

——————————————————->

CODE:

setup.exe /q /ACTION=AddNode /INSTANCENAME=”MSSQLSERVER”
/SQLSVCACCOUNT=”<Domain\Account>” /SQLSVCPASSWORD=”<Password>”
/AGTSVCACCOUNT=”<Domain/Account>”, /AGTSVCPASSWORD=”<Password>”
/ASSVCACCOUNT=”<Account/Password>” /ASSVCPASSWORD=”<Password>” /INDICATEPROGRESS

SQL 2008 Cluster w/SP1 Slipstream

We recently deployed a new iSCSI SAN solution by Dell/EqualLogic, which has gone very well.  Earlier this week, we began a new SQL 2008 Cluster, utilizing new brand new Dell R610s – which we’re also pretty excited about.  We opted to keep the OS on local 15K SAS drives, while dropping the data/logs/indexes, etc. on the iSCSI backend.  While we did discuss a boot from SAN option, which would come in handy with geographically dispersed datacenter and DR failovers, that’s slightly out of scope, for this project.

Since we’ve got all this shinny new hardware, we figured we should go ahead and deploy the latest software as well – opting to go straight to Windows 2008 R2!  I must admit, the combination of our new hardware, and windows 2008 R2, is SUPER fast.  A complete OS load, from bare-bone equipment can be completed in well under an hour, which for us was a huge improvement over W2K3.

So we were speeding along, OS was built, all patches, drivers, iSCSI initiated and cluster prereq’s met – even building the windows cluster was a breeze – then came SQL 2008, which turned out to be a nightmare!  Install wizard was very straight-forward, until we got the “SQL Error Log is being generated” message, below:

TITLE: Microsoft SQL Server 2008 Setup
——————————

The following error has occurred: There was an error setting private property ‘RequireKerberos’ to value ‘1’ for resource ‘SQL Network Name (33SQLCluster)’. Error: Value does not fall within the expected range.

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=10.0.1600.22&EvtType=0xDCFFB30D%25400X1251FB33

——————————-

Initially, we thought it was related to us trying to reuse a existing [virtual] cluster name, which was already an active directory object (even though we couldn’t find it.)  The install was fail, partially and you could not re-install nor uninstall – nice..  It seems the virtual server name didnt create an entry in the registry, therefore uninstall would say “server name ” could not be found” and re-install would state ‘default instance already in use’ – so we had not choice but to flat-line the machine, and try, try, try again.  Five rebuilds later, we finally uncovered an option to slipstream SP1 into the install, which helped us to finally solve our issue.    Briefly, those steps are:

  1. Install the required pre-requirements for SQL Server 2008.
    • .NET Frameworks from here
    • MSI 4.5 from here
  2. Download the Service Pack package that matches you system architecture. For these steps you only need to download the one package that matches your system architecture. You can download from here. For example download the x64 package if your system is an x64 system.
  3. Extract the Service Pack that you downloaded as follows:
    • SQLServer2008SP1-KB968369-x64-ENU.exe /x:C:\SP1
  4. Run the Service Pack to install Setup files onto the machine. You will see the following dialogs if the Setup files have not been installed yet.
  5. Run Setup.exe from the SQL Server 2008 source media, but specify the /PCUSource parameter (i.e. Setup.exe /PCUSource=C:\SP1)

If you experience the same issue, you might find these links helpful:

Hopeful, that will be the last of the SQL issues we’ll have to face and we can get back to business.  Hope this helps you save some time!

Find Missing SQL 2005 Indexes

The following script helps you to find the top 10 missing indexes using SQL 2005 DMV.

SELECT TOP 10 migs_adv.index_advantage,
mid.database_id,
mid.object_id,
mid.statement as table_name,
mig.index_handle as index_handle
FROM  (select (user_seeks+user_scans) * avg_total_user_cost * (avg_user_impact * 0.01) as index_advantage, migs.*
from sys.dm_db_missing_index_group_stats migs) as migs_adv,
sys.dm_db_missing_index_groups mig,
sys.dm_db_missing_index_details mid
WHERE migs_adv.group_handle = mig.index_group_handle
AND mig.index_handle = mid.index_handle
ORDER BY migs_adv.index_advantage DESC

Now take the index handle of the top missing index and apply below to determine which columns should be included in the newly created index:

SELECT * FROM sys.dm_db_missing_index_details where index_handle = 45

Once you’ve got your columns, create your new index: (we tend to put indexes in their own fileGroups so they may reside on their own disk spindle for increased performance)

CREATE INDEX missingIndex_xxx
ON [DB].[dbo].[TBL]([col1], [col2], [col3])
INCLUDE ([col4], [col5])
ON FG_XXX_Index;

Find Unused SQL 2005 Indexes

SELECT o.name AS object_name, i.name AS index_name
   , i.type_desc, u.user_seeks, u.user_scans, u.user_lookups
, u.user_updates, u.last_user_seek, u.last_user_scan
, 'Drop index ' + i.name + ' on ' + o.name as DropIndexStatement
FROM sys.indexes i
JOIN sys.objects o ON  i.object_id = o.object_id
LEFT JOIN  sys.dm_db_index_usage_stats u ON i.object_id = u.object_id
          AND    i.index_id = u.index_id
          AND    u.database_id = DB_ID()
WHERE    o.type <> 'S'
and isnull(u.user_updates,0) > 0
and i.type_desc <> 'HEAP'
ORDER BY    (convert(decimal(19,4),ISNULL(u.user_seeks, 0))
+ ISNULL(u.user_scans, 0)
+ ISNULL(u.user_lookups, 0))/ISNULL(u.user_updates, 0) asc
, user_updates desc, o.name, i.name

The results of this will show you how many times each index has been used, and how often it’s been updated.  If you have 0 seeks, scans, and lookups but a ton of updates, it’s a good bet that the index in question is a waste of time and can be deleted. Conversly, if you have an index that has lots of seeks/scans and minimal updates – it’s really earning it’s paycheck.

The query even includes the DROP command as part of the results, so all you need to do is cut, paste, and execute.

New t-SQL features for SQL 2k8


Inline Variable Assignments

When wearing my developer hat, I spend a lot of time writing stored procedures and playing around with variables. Since I also spend plenty of time programming I’ve always been a bit jealous of the ability to new-up a variable in C# (or VB.NET) to a specified value. For example, prior to SQL Server 2008, if I wanted to create a new datetime variable and set it equal to right now, I’d have to do that in two steps, like so:

DECLARE @now datetime

SET @now = GETDATE()
SELECT @now [now]

But with SQL Server 2008, I can do that in a single line of code, as follows:

DECLARE @now datetime = GETDATE()

SELECT @now [now]

This minor change helps improve code readability by letting you instantiate variables and assign their initial value inline while simultaneously providing a minor productivity boost as well.

Row Constructors

MySQL has two features that many db pros want SQL Server to adopt. The first is the dump function, which allows you to dump an entire table or database to a DDL script along with supporting DML INSERT scripts that make it easy to persist an entire table or database to a single text file. This file can then be executed on another server to recreate any output tables and repopulate them with data by executing the accompanying INSERT statements.

The second feature that is frequently referenced that SQL Server could borrow from MySQL is what some have called Bulk Inserts or the ability to INSERT multiple, static, rows with a single INSERT statement. For example, if I’m creating a simple table defining user types and I want to populate it with some initial values, each value will require its own INSERT statement like so:

CREATE TABLE UserTypes (

UserTypeId tinyint IDENTITY(1,1) NOT NULL,

UserType varchar(20) NOT NULL

)

INSERT INTO UserTypes (UserType)

VALUES (‘User’)

INSERT INTO UserTypes (UserType)

VALUES (‘Manager’)

INSERT INTO UserTypes (UserType)

VALUES (‘Admin’)

I’ve always been a bit lazy when populating tables in this fashion and have gotten into the habit of just SELECTing rows into my table using a UNION, mostly because I hate typing VALUES and open/close parenthesis:

INSERT INTO UserTypes (UserType)

SELECT ‘User’

UNION SELECT ‘Manager’

UNION SELECT ‘Admin’

But SQL Server 2008 has caught up with MySQL, and I can now insert multiple rows within a single INSERT statement with T-SQL 2008’s new Row Constructors:

CREATE TABLE UserTypes (

UserTypeId tinyint IDENTITY(1,1) NOT NULL,

UserType varchar(20) NOT NULL

)

INSERT INTO UserTypes (UserType)

VALUES (‘User’),(‘Manager’),(‘Admin’)

Again, this isn’t a huge or earth-shattering change, but a very nice improvement that can really help boost usability and increase productivity. (Too bad there’s no sensible way to ditch those parenthesis though.)

Compound Assignment Operators

Another great, but subtle, improvement to T-SQL in SQL Server 2008 is the inclusion of new Compound Assignment Operators (though Microsoft just calls them Compound Operators). Like the other improvements mentioned, these aren’t life-savers by any stretch of the imagination, but they do make some interactions easier, especially if you’re comfortable with the greater range of assignment operators available in C# (and VB.NET ).

For example, prior to SQL Server 2008, if you wanted to append text or increment a variable, you needed to do it in a rather verbose manner, as follows:

— declare:

DECLARE @int int

DECLARE @string varchar(10)

— initialize:

SET @int = 5

SET @string = ‘123’

— increment:

SET @int = @int + 5

SET @string = @string + ‘456’

SELECT @int, @string

But with SQL Server 2008, you can do it a bit more logically and more concise:

— declare and init:

DECLARE @int int = 5

DECLARE @string varchar(10) = ‘123’

— increment:

SET @int += 5

SET @string += ‘456’

SELECT @int, @string

These new assignment operators work with all of the standard mathematical assignment operators including bitwise operations which can help make interacting with bitmaps a bit easier as well.

Making Your Life Easier

Ultimately, all three of these new additions are rather minor when compared to things like Full Text Integration, sparse columns, FILESTREAM storage, Table-Valued Parameters, and a host of other improvements and additions. But they all represent subtle improvements that can really help to both improve productivity and ease usability, so they’re definitely worth adding to your repertoire.

SQL 2005 Cluster Startup Params

Having recently deployed a new SQL 2005 cluster, I thought I’d share a couple things that I originally got hung up on. 

First, startup parameters, It took me some time to find that when more than one parameter is used as Startup Parameters in SQL Sever, they should be separated by semicolon rather than space.

For example:
-dC:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\mastlog.ldf;-T4616

Other, BIG lesson learned, is when moving SQL 2005 System Databases, you’re again required to update startup params.  If you happen to make a mistake, there’s a small hoop you have to jump through to remedy the problem, as simply fixing the startup parameters won’t recover the SQL Service, as it simply won’t restart.  In a clustered environment, when you change a protocol parameter for a clustered instance of Microsoft SQL Server 2000 or 2005 to a value that is not valid, the SQL Server service cannot start. Additionally, you cannot change the protocol parameter back to the original value.

This problem occurs because the cluster service obtains an incorrect protocol setting from a cluster checkpoint when you start the SQL Server service.  Use the steps below for a work around:

  1. Bring the SQL Server resource offline by using Cluster Administrator.
  2. At a command prompt, run one of the following commands to disable the cluster checkpoint for the specific registry subkey:
    For an instance of SQL Server 2005, run the following command:

    cluster res “SQL Server” /removecheck: “Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER”

    Note In this command, MSSQL.x is a placeholder for the instance ID for the instance of SQL Server. You can determine the corresponding value for the system from the value of the MSSQLSERVER registry entry in the following registry subkey:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\
    For a default instance of SQL Server 2000, run the following command:

    cluster res “SQL Server” /removecheck: “Software\Microsoft\MSSQLServer\MSSQLSERVER”
    For a named instance of SQL Server 2000, run the following command:

    cluster res “SQL Server” /removecheck: “SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\MSSQLSERVER”

    Note In this command, InstanceName is a placeholder for the name of the SQL Server 2000 instance.

     

  3. Correct the protocol parameter for the clustered instance of SQL Server on all nodes.
  4. At a command prompt, run one of the following commands to enable the cluster checkpoint for the specific registry subkey:
    For an instance of SQL Server 2005, run the following command:

    cluster res “SQL Server” /addcheck: “Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER”
    For a default instance of SQL Server 2000, run the following command:

    cluster res “SQL Server” /addcheck: “Software\Microsoft\MSSQLServer\MSSQLSERVER”
    For a named instance of SQL Server 2000, run the following command:

    cluster res “SQL Server” /addcheck: “SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\MSSQLSERVER”

     

  5. Bring the SQL Server resource online.

Happy Recovery!