Friday, October 16, 2009

SSAS Deployment Error: No mapping between account names and security IDs was done. .

Check to make sure all the Active Directory ids in your role objects are valid. Most likely this error is being caused by a user or group that has been removed from your Active Directory.

Thursday, October 8, 2009

SQL Server Management Studio Default Isolation Level Can Cause Locking/Blocking

Many unsofisticated and/or underfunded organizations have people executing ad hoc queries against production SQL Server instances with little or no oversight. I am currently in an organization where employees on the operations team as well as the marketing and business intelligence teams have the ability to execute queries via SQL Server Management Studio, directly against production OLTP databases. If a query takes an hour to run, so be it. They need their data and they will wait. These users know nothing about locks, blocking, resource contention, IO bottlenecks, etc and therefore have no idea what problems they might be causing.

Unfortunately, the default behavior for SQL Server Management Studio is to default each connection's Isolation Level to Read Committed. This can cause blocking under a number of different scenarios. I won't detail them here but believe me, it can be a problem.
However, this problem can be avoided. SSMS provides the ability to set the default Isolation Level for all queries. The option can be found by navigating to Tools -> Options. From there, Query Execution -> SQL Server -> Advanced.


I suggest setting the TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED and the DEADLOCK_PRIORITY to Low. Setting the DEADLOCK_PRIORITY to Low will mean that, in the case of a deadlock, the SSMS query will most likely be aborted. Also note the LOCK TIMEOUT value of 30,000 milliseconds. This is the equivalent of 30 seconds and means that if an SSMS query encounters a lock for more than 30 seconds, the query will be aborted.
Note that you can also set the Isolation Level for the active connection by issuing the SET TRANSACTION ISOLATION LEVEL command. For example:
     SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

This command can also be used in stored procedures instead of using NOLOCK hints on all tables.

**Keep in mind that using this transaction isolation level can lead to inconsistent data in your result set since you can read uncommitted data. 
 

Below are the descriptions of the different Isolation Levels from SQL Server BOL.

READ COMMITTED
Specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.


READ UNCOMMITTED
Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels.


REPEATABLE READ
Locks are placed on all data that is used in a query, preventing other users from updating the data, but new phantom rows can be inserted into the data set by another user and are included in later reads in the current transaction. Because concurrency is lower than the default isolation level, use this option only when necessary.


SERIALIZABLE
Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

Wednesday, October 7, 2009

Linked Server NT AUTHORITY\ANONYMOUS LOGON Error - SPN Issue

I was recently struggling with a couple of linked servers. Every time I tried testing them or running a query that referenced the linked server I received the following error:
       Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON
The environment I was operating in had an overly complex network configuration that I won't begin to attempt to describe. Anyhow, I was convinced that this had something to do with Kerberos authentication. It turns out it does although not the way I had originally thought.


The SQL Server had recently been migrated to a new domain. In the process, an SPN (Service Principal Name) had not been modified accordingly. By using setspn -L servername I was able to see that the SPN referenced the old domain name.

To resolve the problem the SPN was deleted using setspn -D.