Category Archives: SQL Server

Book Review: Programming Microsoft SQL Server 2012

Programming Microsoft SQL Server 2012 (Leonard Lobel and Andrew Burst; Microsoft Press) is an excellent book if you want to learn the ins and outs of SQL Server. The book is meticulous when it comes to explaining SQL Server features. Much of this is because of the elaborate examples and the detailed explanation of what’s going on. This attention to detail is however both its strength and its weakness. If you already know about database development, you already know much of what is being explained. For instance, you already know what transactions are for and how they work. Explaining the subject as if you’ve never seen a database feels a little overdone. This is also where the book is a little ambiguous. The chapter that deals with T-SQL, only covers the newer T-SQL statements, so you are expected to have prior knowledge. Of course finding the right balance is hard, and this is a good book regardless. It just means that if you already know the topic, you can just skip to where the content is new. It also means that the book is an excellent reference. In fact, reading this book from beginning to end might not be the best use. Explore it for new features, and then keep it around.

Why SQL Audit is insufficient (and what you can do about it)

Recently I had the pleasure of diving into audit logging. I’m working on a government project which involves the law making process, so it is imperative that all database changes are completely traceable. That means that we need to be able to trace who made which changes and when. We’re working with latest and greatest version of SQL Server (i.e. SQL Server 2008 R2), which has a feature called SQL Audit. Reading the documentation SQL Audit seemed to do everything we need, except that it doesn’t know which application user is making the changes. This is logical since it is a web application and we’re using delegation. For this reason we were already planning to have the application send along the user id when it does an insert, update or delete, and we decided to only logically delete a record. So far so good.

When it came to testing, we quickly found that SQL Audit logs the SQL statement making the change. Sounds right doesn’t it? Well actually it isn’t. LINQ-to-SQL, LINQ-to-Entities and other O/R Mappers use parameter queries, and in fact if you edit records in the SQL Management Studio UI, the same is true. The problem is that the parameters are not part of the SQL statement being logged! So we can see which database user made what kind of change, but not which data was changed, and hence not which application user made the change either. Back to the drawing board :(.

In reviewing our options, we looked at:

  • All logging in the O/R Mapper: Not an option, because we need to know what DBA’s do too.
  • SQL Trace: not recommended by Redmond, and it takes a huge performance hit.
  • Triggers: in transaction, taking enormous performance hit.
  • C2 auditing: tracks all changes, so it gathers huge amounts of data, not easily searchable.
  • Change Data Capture: really for BI purposes, deleted after three days, no indication of the user making the change.

All of the above options have some sort of problem associated with it. The conclusion is that there is no single solution, unless Microsoft fixes the SQL Audit issue (you can vote on it here: https://connect.microsoft.com/SQLServer/feedback/details/624935/sql-server-2008-database-audit-on-insert-update-and-delete-actual-sql-and-not-parameter-values).

We now do the following:

  1. In the Data Access Layer add the application user that did the insert or update in an extra field on the table.
  2. Only do logical deletes (i.e. add a “Deleted” flag to a table).
  3. Track all changes using Change Data Capture (which uses the transaction log and therefore has less impact on performance).
  4. Export CDC data to an “Audit Database” periodically (like using a data warehouse).
  5. Use SQL Audit for all changes done by a database user other than the DB account used by the application.
  6. Export SQL Audit logs to the Audit Database periodically.

By cross referencing SQL Audit and CDC data, we can figure out who changed what if the change was made outside the application.

Debugging SQL Server (large) stored procedures

Recently I found myself having to debug a large stored procedure that calls all sorts of sub-procedures. Also, the data set was rather large and it only failed sometimes. SQL Management Studio in this scenario isn’t very helpful for several reasons:



  • The number of PRINT messages that it’ll show is limited, so if the error is out of range… tough luck.

  • Even with PRINT messages it can be hard to pin down which statement is actually in error.

  • Line numbers are never accurate.

  • It seems View Dendencies sometimes misses out on dependencies.

  • View Dependencies doesn’t show the number of times a proc is called from another proc.

SQL Profiler, which also comes with SQL Server is a much better tool when it comes to pin-pointing the erronous statement(s), after which you can modify your code to show an error message that prints the values causing the hickup. When you use SQL Profiler, be sure to select more than just the default events. Click Show All Events and select all Error events, as well as the Stored Procedure events that show Starting and Completed of SQL statements with stored procedures. Then, once the root procedure is done, do a Find through the trace for “Error”, and you can see exactly which statement is causing the error.


 


 

Note to self: SQL Server Management Studio Error “Saving Changes not permitted”

Every once in a while (when I use a new dev environment) I hit this error:


Saving Changes in not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created


Each time I forget how to fix this, so by posting here I know I’ll never forget. Check Pinal Dave’s blog post on this error (hint: it’s in the Option menu). Thanks Pinal!

The never shrinking transaction log

I keep forgetting how to force the transaction log in SQL Server to shrink, so I’m posting here primarily so I know where to find the how to :). Often the log does not shrink when you try to shrink from SQL Managent Studio. The solution, before you shrink the database do:


BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY


Thanks to Pinal Dave for this one (see his post SQL SERVER – Shrinking Truncate Log File – Log Full)

SQL Server error with ASP.NET Membership pointing to the local server

We have an ASP.NET application that we normally run under Forms Authentication using the ASP.NET Membership API. For a particular client we changed this to using Windows Authentication instead. On the production environment, we were running into the following exception:


A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)


We checked all the connection strings the app uses… all good. Finally, we figured out what was wrong. We had removed the Membership section from web.config so it was going back to the default in machine.config. That setting uses the LocalSqlServer connection string, which we don’t use. However, in the default machine.config, this points to the App_Data folder using SQL Server Express. In most environments, this wouldn’t be an issue immediately, because SQL Server Express would just create the  aspnet database and use that. However, in a hardened environment SQL Server Express is either not there (our case) or has no rights to create the App_Data folder and/or place create a new database. ASP.NET doesn’t know this… it just can’t access the SQL Server instance it is looking for, hence the above exception.

DevConnections workshop na DevDays

De dag na DevDays organiseert DevConnections twee workshops:



  • ASP.NET AJAX Extensions Inside Out door Dino Esposito

  • Optimising and best practices for all SQL Server 2005 features door Bob Beauchemin

Als je nog wat wilt leren na DevDays, dan is dit een hele goede kans. Beide sprekers kennen het onderwerp van haver tot gort, dus het is zeker de moeite waard. Nog niet helemaal overtuigd? Bob en Dino spreken allebei op DevDays, dus als je daar bent kun je eerst even een kijkje nemen. Je kunt je op de DevDays eventueel ook inschrijven voor een van de workshops.