Saturday, May 28, 2011

Setup.rll Contains an Error

Problems with SQL2008 to SQL 2008 R2 Upgrade.

After an upgrade to SQL2008 R2 from SQL 2008 failed, I tried re-starting the upgrade.  The error:
Clearly states the setup.rll file is the problem.   

I replaced the setup.rll file in my install folder (E:\SQL2008R2\resources\1033\setup.rll) which did not resolve the issue.  My assumption was that when running setup from the install directory that fresh copies sent to the target installation folder (c:\Program Files\Microsoft SQL Server\100\Setup Bootstrap…etc…). 

After replacing my install copy of software with binaries from another ISO file and experiencing the same failed results, I finally realized that I needed to copy the setup.rll file manually to the target installation folder.  This solved the problem.

Thursday, May 26, 2011

Let's Build our DBA Community

The Exceptional DBA award rocks! 
It's a cool to recognize your peers.  It helps build the DBA Communty.

Wednesday, May 25, 2011

Copy-Only Backups

Copy-Only Backups are useful as special or one-off backups between scheduled full/differential/transaction log backups.   They are useful when doing backups for special purposes for client DBA databases where you don’t have to keep the backup as part of the continuous LSN for recovery purposes.

Syntax:   BACKUP DATABASE database_name TO <backup_device> … WITH COPY_ONLY

SQL 2008 R2 / Windows 2008 R2 Cluster install

Discovered there are two options for installation of an SQL 2008 R2 in a Windows 2008 R2 Cluster.  We currently install with option 1.  Here's a description of both options:

Option1:   Integration Installation with Add Node

SQL Server integrated failover cluster installation consists of the following steps:
·    Create and configure a single-node SQL Server failover cluster instance.  When you configure the node successfully, you have a fully functional failover cluster instance.  At this point, it does not have high availability because there is only one node in the failover cluster.
·    On each node to be added to the SQL Server failover cluster, run Setup with Add Node functionality to add that node.

Option 2:   Advanced/Enterprise Installation

SQL Server Advanced/Enterprise failover cluster installation consists of the following steps:
·     On each node that will be a possible owner of the new SQL Server failover cluster that you are creating, follow the Prepare Failover Cluster setup steps that are listed in the Prepare section.  After you run the Prepare Failover Cluster on one node, Setup creates the Configuration.ini file that lists all the settings that you specified.  On the additional nodes to be prepared, instead of following these steps, you can supply the auto generated Configuration.ini file from first node as an input to the Setup command line.  This step prepares the nodes ready to be clustered, but there is no operational instance of SQL Server at the end of this step.
·    After the nodes are prepared for clustering, run Setup on one of the prepared nodes, preferably on the node that owns the shared disk that has the Complete Failover Cluster functionality.  This step configures and finishes the failover cluster instance.  At the end of this step, you will have an operational SQL Server failover cluster instance and all the nodes that were prepared previously for that instance will be the possible owners of the newly created SQL Server failover cluster.

From technet

Sunday, May 22, 2011

SQL Server-Level Principal Login Certificate

On one of our SQL2005 instances, a certificate used for SQL Server-level principal login, ##MS_SQLAgentSigningCertificate##, is currently not trusted as a CA Root cert.  It needs to be installed/added back as a Trusted Root CA.

Technet discusses these certificates.  I have not found much on these certs except an incident where MSDB was moved to another instance and permissions needed to be fixed.

My assumption is the cert store on the server lost its reference to the CA Cert and just needs to be added back in.  I added in the cert and that solved the problem.

Saturday, May 21, 2011

Cluster Problems when Upgrading to SQL2008

While upgrading from SQL2005 to SQL2008 (not R2) in a 2-node 2-instance Windows 2003 R2 clustered environment, one of the DBAs ran into the problem where one of the SQL resouce groups would not move from one node to the other.  Error message:

An error occurred attempting to move the '<groupname>' group.
A cluster node is not available for this operation.
Error ID: 5005 (0000138d).

Found this which gave the answer.  One of the resources (the network name) did not have the node we we were moving to in its preferred node list.  Once that was corrected, the resource group moved as it should.

Saturday, May 7, 2011

Transactional Replication and Primary Key

When creating a publication, I discovered one of the tables was not allowed as an article in the publication since it had no primary key:

Create table VendorData (
  colUniqueID1    int NOT NULL,
  colUniqueID2    int NOT NULL,
  colFiller1      varchar(1000),
  colInclude1     char(4),
  colInclude2     int,
  colFiller4      varchar(1200),
  colClusterCol   int NOT NULL);

Create unique index ix_VendorData_colUniqueID1_ID2
      on VendorData(colUniqueID1, colUniqueID2)
      include (colInclude1, colInclude2);

Create clustered index ix_VendorDataClusterCol on VendorData(colClusterCol);

I was not sure how to create the primary key for the purpose of replication and not change the table in a way that would change the way query results were returned.

The only difference (I am aware of) between a primary key and a unique index is that a unique index allows nulls and the primary key does not.  Since the unique index in this case is composed of two columns that each have the NOT NULL constraint, the unique index is functionally the same as a primary key and should produce the same query results.  However, this unique index in this scenario is also a covering index.  I do not know a way of creating a primary key that is also a covering index.

The solution I came up with was to keep the unique covering index and add the primary key constraint to the table using the columns in the unique index as components of the primary key – without the included columns of course.

Alter table VendorData WITH NOCHECK
Add constraint PK_VendorData_colUniqueID1_colUniqueID2 PRIMARY KEY (colUniqueID1, colUniqueID2)

This did work.  I was not sure if the index would actually be created or if the SQL parser would realize the index already existed and point the primary key to the existing unique index (with the caveat that the columns would retain their NOT NULL constraint).  The index was created in seconds on a table with >120mil rows.  My guess is that the index for the primary key was not re-generated, but used the existing unique index.

Comments welcome.

Monday, May 2, 2011

What's a hierarchyid?

While studying for the MCTIP SQL 2008 Developer exam, I ran across a data type I'd never seen before (and I'm sure I'll discover more in my career):  the hierarchyid.  References in Technet and an informative blog by Simon Ince.

This CLR based data type is designed to provide a simple way to represent hierarchical data - a bill of materials or empoyee org chart are examples. 

It is interesting that recursive Common Table Expressions were emphasized in the MCTS SQL Developer exam, and hierarchyid data type replaces this construct with something much easier ot use.

Sunday, May 1, 2011

Studying for Exam 70-451

Recently earned my MCTS SQL 2008 Database Developer certification using the Microsoft self-paced training kit.  Studying for  Microsoft Certification exam 70-451 .  Exam 70-451 will earn my MCITP SQL 2008 Database Developer certification.  My goal is to take the exam 5/25/11.

There's no Microsoft self-paced training kit, so I'm putting together everything I can find on this exam.  The Microsft certification web site gives a good explation of the skills tested on.  I'm using Technet/MSDN/BOL as my guide on the subject.

Practice tests not available from MeasureUp, but found one on Kaplan