More details about concurrency, locking and blocking in SQL Server.

The performance of your application on a SQL Server database is for most customers xtremely important. In most situations applications from different providors will connect to the same database. For instance: Excel, SSRS, Crystal Reports, MRP Application X etc.. Connections to a database can be made in different ways:

1. Read Uncommitted
2. Read Ccommitted
3. Repeatable Read
4. Serializable
5. Read Commotted Snapshot
6. Snapshot Isolation

There is not one best isolation level to use. It depends on your application. If different application uses  different isolation levels to connect to the same database, it can result in unexpected lockings. Sunil Agarwal, Principal Program Manager in SQL Server Storage Engine Group, has made some great blog posts about these isolation levels. These blog posts will explain in detail the behaviour of every isolation level. Beside this he made some demo scripts to show lockings which can occur in situations you do not expect.

1. Basics of Transaction Isolation Levels
2. Why do I get blocking when I use Read Uncommitted isolation level or use NOLOCK hint?
3. Why do I get blocked when no one has locked the row(s) being queried?
4. Minimizing blocking between updaters.
5. My application was running fine yesterday but why is it blocking today?

The big question during the analyze of unexpected blocking issues in your database is which application is responsible for this. The first step in this analyze process is to understand which isolation levels are used by the different applications. In this blog post: "Bad performance and lockings occur random on my database."
you can find a query to retrieve the different isolation levels of the different applications which are connecting to your database.

Enjoy it and good luck in finding the root cause of the unexpected locking and blockings.

SQL Azure backup and restore strategy. Things to keep in mind.

With SQL Azure you have a SQL Server database in the cloud. This can be very useful, however there are some differences between the on premise SQL Server databases and the cloud SQL Server databases. One of the differences is the Backup and Restore Strategy. In general, the purpose of creating backups is to enable you to recover from:

• Hardware failures
• Application errors
• User errors.

The SQL Azure environment is designed to maintain the server available along with data integrity of your data in case of hardware failure. In other words, your backup and restore strategy doesn’t have to plan for hardware failure of your SQL Azure databases. For application or user errors, you still need a backup and restore strategy. SQL Azure requires its own backup and restore strategy because of the environment and tools available. In many ways the risks have been mediated by the database being in the Microsoft data centers. The tools that are available as of today cover the other risk factors, however better tools are coming to make the job much easier. Please read SQL Azure Backup and Restore strategy for more details about the available tools and commands.

Viewing reporting service reports (SSRS) on your iPad or iPhone.

I use my iPad to consume a lot of different data. This can be internet pages, rss feeds etc. Recently I started to view reporting service reports (SSRS). In the Safari browser you can start your reporting service reports as you are doing in your Internet Explorer. However the Safari browser does not always show your report in the correct way. Using Internet Explorer is still the best browser to view your SSRS reports. However on my iPad I have no choice to install Internet Explorer. To workaround this problem I found a specific app named: Sketch Reports. With this app I can view my reporting service reports.

I will give you one example of a SSRS report in the safari browser on the iPad.

 Now the same report but now running on the SketchMD app.


 

Click here to download the Sketch Reports app in de Apple App Store. The Sketch Reports version is the free version. This version only works on the iPad and NOT on the iPhone. You can run one report from one folder. The Sketch Reports Pro version is the full version which runs on the iPad and iPhone. More information can be found on the website of SketchMD

The full version has next features:

• Download and View Reporting Services Reports
• Supports SSRS 2005, 2008, 2008 R2 in native and sharepoint modes
• SSL Support
• Parameter support
• Supports Drill Down and Drill through report navigation
• Eamil reports

Enjoy using your iPad to view reporting services reports.

No mapping between account names and security IDs was done

Last week I encountered this error when I was processing my SSAS Cube for the Januari 2011 figures. This error also occured if I tried to process a dimension. In the last months no changes were made in my datawarehouse project, so I was very suprised to get this error message. This error messsage is nice from a technical perspective but it will not tell what I need to do. It would be nice if it could tell me which account names or security ID's are not mapped. After some research in my cube I found the problem. In my datawarehouse project I have defined roles for people who can access my cube. In one of these roles, an active directory account was configured for a user who left my company this month. His Active Directory account was removed from Active Directory. So my datawarehouse project roles contained Active Directory accounts which does not exist anymore.

Solution: Remove all Active Directory accounts from the roles which does not exists anymore in Active Directory.

Credit: Nick J Webb