Managing SQL Server Service Accounts

One thing I haven't been sure about is the best way to set up service accounts in SQL Server 2008. I know you should use a unique account for each service (Database Engine, SQL Agent, AS, IS and RS), but I was wondering about multiple instances. Is it a best practice to use a separate set of accounts for each instance, or (for example) should one RS account be used for all the Reporting Services on the machine?

After consulting Books Online (which says, "Run separate SQL Server services under separate Windows accounts.") and reading this editorial and the resulting discussion on SQLServerCentral.com, I decided that each service of each instance should use a unique account. Here's my naming convention:

You can see that I've collected the accounts into a Group named 'SQL Server Accounts', and that they follow this naming convention:

• SQL - so all the SQL Server accounts will be listed together
• Instance Name - 'PJM' in this case
• Account Name - SQL Agent, Database Engine, Analysis Services, Integration Services or Reporting Services

When Is a Number Not a Number?

I've seen sample tables that have fields like Zip code or Social Security Number. These columns are invariably defined using an integer data type. I don't think this data is numeric; I think it's character data that happens to consist of all digits.

In my opinion, if an entity doesn't have arithmetic operations performed on it, then it shouldn't be defined as a number. 

Here's an example using SQL Server 2008 that demonstrates one of the pitfalls of using a numeric data type: leading zeros won't be displayed.  

CREATE TABLE ZipTest
(
 Zip1 SMALLINT,
 Zip2 VARCHAR(5) CHECK (Zip2 BETWEEN '00000' AND '99999')
)

INSERT INTO ZipTest VALUES(05678,'05678');

SELECT * FROM ZipTest

(1 row(s) affected)
Zip1   Zip2
------ -----
5678   05678

(1 row(s) affected)

These two values are obviously not the same.

So, to sum up (no pun intended): just because data looks numeric, doesn't necessarily mean it is numeric.

Meme Monday (So It's Late, So What?)

I just found out about Thomas LaRock's (Blog|Twitter) Meme Monday. Here's my (late) contribution:

The data is more important than whatever database it's stored in.
 
I'm not going to tag anyone, though.

Question of the Day

SQLServerCentral.com published my Question of the Day submission today! It's been pretty-well received so far.

I feel like I'm contributing something.

4/8/2011 Update:

It's probably OK to post the question now, so here it is.

Note: multiple commenters pointed out that option C should be phrased as 'The last INSERT statement will fail.'

Foreign Keys

What will the result of this code be?

USE AdventureWorks2008 
GO 

CREATE TABLE dbo.Students (
 StudentID INT UNIQUE, 
 LastName VARCHAR(20), 
 FirstName VARCHAR(20) 
)

INSERT INTO dbo.Students 
 VALUES(1,'Washington','George'),
       (2,'Adams','John'),
       (3,'Jefferson','Thomas')

CREATE TABLE dbo.ClassList (
 ClassID VARCHAR(6),
 StudentID INT REFERENCES dbo.Students(StudentID)
) 

INSERT INTO dbo.ClassList VALUES('HIS101',4)

A. The ClassList table will not be created.
B. The code will complete successfully.
C. The INSERT statement will fail.

Correct Answer: 

C. The INSERT statement will fail.

Explanation:

You may think that a referenced key must be a primary key, but according to Books Online, a foreign key is linked to a primary key or unique key. Therefore, the ClassList table has a valid foreign key constraint, which prevents the record from being inserted.

Reference: Creating and Modifying FOREIGN KEY Constraints - http://msdn.microsoft.com/en-us/library/ms177463.aspx