After 145 months of employment with Microsoft, at the beginning of March 2016 I quit the SQL Server team to pursue my dream of creating a better solution for SQL Server performance monitoring and configuration management. I am now dedicating all my time to building DBHistory.com.

What is DBHistory.com?

At first DBHistory.com will allow you to track the history of all configuration and schema changes on SQL Server. You will see when the change occurred, which user initiated it, on which server, in which database, what objects where affected, and the exact T-SQL text of the statement that triggered the change. Using DBHistory.com you will be able to quickly asses the history of an object, what changes occurred in a certain time interval, view all changes done by a particular user and so on.

There is no tool to purchase, no code to deploy, no storage to reserve, no contracts to sign. Only a configuration wizard to run, DBHistory.com uses SQL Server’s own Event Notifications mechanisms to push all change notifications from the monitored servers to the DBHistory.com service. The change notifications leave the monitored server immediately and are then safely stored with DBHistory.com.

You can use DBHistory.com if you are a software vendor that deploys a solution on-premise at your clients and need to know if any configuration or schema change occurred in your applications. You can use DBHistory.com if you are a remote DBA company that needs to know the history of changes on any of your client’s SQL Server databases, no matter who or when did the change. You can use DBHistory.com if you are a consultant that needs to know what changed since your last visit. You can use DBHistory.com if you have many employees with database access and want to know what changes were done by whom and when. You can use DBHistory.com if you are database development company that needs a history of changes done directly to the database in the development environment. You can use DBHistory.com if you distribute an application with free SQL Server Express Edition and want to understand how many times your application was deployed.

You will be able to set alarms to get notifications when certain changes occur, or when important objects are modified.

The road ahead will add more functionality to DBHistory.com, including performance monitoring

When can I try DBHistory.com?

If you are interested, please sign up now at DBHistory.com. Soon I will extend beta invites for those interested in trying out the service.

One of the most useful performance tools I used internally at Microsoft was the WindowsXRay. I’m pleased to find that it was released the new name of Media eXperience Analyzer. The tool was internally developed by the Windows media perf team and the release info is targeted toward the media developers. But rest assured, the tool can be used for all sort of performance analysis and I had successfully used in analysis of SQL Server performance problems. Using the Media eXperience Analyzer (XA) you typically start by collecting one or more ETL traces using the platform tools like the Windows Performance Recorder (WPR). XA is a visualization tool used to inspect these ETL traces, much like the Windows Performance Analyzer.

Now that the TechEd 2013 presentations are up online on Channel9 you can check out Brian Mitchell’s session What’s New for Columnstore Indexes and Batch Mode Processing. Brian does a great job at presenting the new updatable clustered columnstore indexes and the enhancements done to the vectorized query execution (aka. batch mode). Besides the TechEd presentation there is also another excellent resource available online right now for your education on the topic: the SIGMOD 2013 paper Enhancements to SQL Server Column Stores. Besides the obvious updatability, this paper cites some more improvements that are available in clustered columnstores:

• Improved Index Build
• Sampling Support
• Bookmark Support
• Schema modification support
• Support for short strings
• Mixed Execution Mode
• Hash Join support
• Improvements in Bitmap filters
• Archival support

It should be no surprise to anyone studying columnar storage that the updatable clustered columnstores coming with the next version of SQL Server are based on deltastores. I talked before about the SQL Server 2012 Columnstore internals and I explained why the highly compressed format that makes columnar storage so fast it also makes it basically impossible to update in-place. The technique of having a ‘deltastore’ which stores updates and, during scans, merge the updates with the columnar data is not new and is employed by several of the columnar storage solution vendors.

Read the rest of this entry »

With the SQL Server 2012 SP1 CU2 release a new important feature was added: ability to back up and restore a database straight from Azure Blob storage:

This feature released in SQL Server 2012 SP1 CU2, enables SQL Server backup and restore directly to the Windows Azure Blob service. This feature can be used to backup SQL Server databases on an on-premises instance or an instance of SQL Server running a hosted environment such as Windows Azure Virtual Machine. Backup to cloud offers benefits such as availability, limitless geo-replicated off-site storage, and ease of migration of data to and from the cloud.

Read the rest of this entry »

Few days ago I noticed that my StackOverflow profile shows 995 consecutive visited days. So naturally I started thinking about what does it mean to come back every day for a thousand days in a row. Looking back at the post I wrote almost 3 years ago to the day: stackoverflow.com: how to execute well on a good idea I can say that not much has changed: StackOverflow (and now the entire StackExchange network) is first and foremost a great community. The technical execution and the social nurturing of the site makes for a low friction environment that invites and rewards contribution, and it keeps getting better and better.

SQL Server supports online index and table rebuild operations which allow for maintenance operations to occur w/o significant downtime. While a table is being rebuild, or a new index is being built on it, the table is fully utilizable. It can be queried and any updates done to the table while the online rebuild operation is occurring will be contained in the final rebuilt table. A detailed explanation on how these online rebuild operations work can be found in the Online Indexing Operations in SQL Server 2005 white paper. But Online Index Build operations in SQL Server 2005, 2008 and 2008 R2 do not support tables that contain LOB columns, attempting to do so would trigger an error:

Read the rest of this entry »

Prior to SQL Server 2012 when you add a new non-NULLable column with default values to an existing table a size-of data operation occurs: every row in the table is updated to add the default value of the new column. For small tables this is insignificant, but for large tables this can be so problematic as to completely prohibit the operation. But starting with SQL Server 2012 the operation is, in most cases, instantaneous: only the table metadata is changed, no rows are being updated.

Lets look at a simple example, we’ll create a table with some rows and then add a non-NULL column with default values. First create and populate the table:

Read the rest of this entry »

This year SIGMOG keynotes are available online at https://services.choruscall.eu/links/sigmod1106.html

• June 13^th: A Quest for Beauty and Wealth (or, Business Processes for Database Researchers)
• June 14^th: Internet-Scale Storage (I highly recommend this session)
• June 15^th: SIGMOD Awards (this includes a segment on the SQL Server team)
• June 16^th: Managing Scientific Data

How do large-scale sites and applications remain SQL-based? is a recent article from Michael Rys (Blog|Twitter) that highlights the relational SQL Server based web-scale deployment at MySpace. I have talked before about how MySpace uses Service Broker as a reliable messaging backbone to power the communications between +1000 databases, allowing them to scale-out and partition the user information into individual shards. Here are some more details about this architecture:

• MySpace Uses SQL Server Service Broker to Protect Integrity of 1 Petabyte of Data
• Developing Large Scale Web Applications and Services(video)
• MySpace SQL Server Service Broker (slides)

Read the rest of this entry »

If you have an MDF file laying around and you don’t know what version is, how can you determine the version, preferably without altering the file? You could try to attach it to a SQL Server instance, but if you’re not careful the operation may end up upgrading the MDF file to that instance’s current version. Even if it doesn’t upgrade it because it happens to be the same version as the one supported by that instance, it can still run recovery on the database and thus alter the file. Is there a way to determine the version in a non destructive fashion?

Lets look into a database and dump a page. Not any page, but page 9 of the primary filegroup. This page happens to be the database boot page:

Read the rest of this entry »