David Browne's Web Log

I’d like to demonstrate a technique for storing compressed rowsets in SQL Server using CLR integration.  This is really the story of several .NET and SQL Server technologies coming together.  Some have been around for a while, some are new. ...

I have gotten this question a couple of times, so I thought I’d post the answer. No, MERGE doesn’t have strong isolation guarantees by default. By default MERGE will perform the "match" phase of the query without using exclusive locking...

SQL 2012 SP1 CU2   has introduced support for backup to Azure Cloud storage right in the database engine.  This is cool because Azure storage is low-cost, offsite and optionally geo-redundant.  So this can be a simple way to protect your...

I work in a lab environment, often examining running SQL workloads for performance analysis. This is a query I've used many times to see what's running _right now_ on a server and what kind of resources it's using. It dumps the running queries and query...

I recently published a couple of new samples. One for doing custom security with Analysis Services is a HTTP Handler that proxies XML\A Requests to the SSAS Data Pump, and allows you to front-end SSAS with a custom security solution. SSASProxy Custom...

A customer asked me the other day for a sample of synchronizing a Replication subscription from PowerShell. The scenario is that they are running SQL Express on disconnected systems, and need to kick of a sync from the client. SQL Express doesn't have...

There’s a Windows function to create sequential GUIDs, called UuidCreateSequential http://msdn.microsoft.com/en-us/library/windows/desktop/aa379322(v=vs.85).aspx But SQL Server uses a different sort order for GUIDs, and the results of UuidCreateSequential...

It’s well-known that inserts using monotonically increasing key values at the end of an index minimize page splits, and that using a randomly generated key value causes lots of expensive page splits. But a colleague and I were recently discussing...

A Hostname Alias SQL Server instance is a simple Hostname that allows clients to connect to an instance of SQL Server without specifying an instance name or a port number. They are very useful for upgrades, server migration, consolidation, and disaster...

SQL Server Fast Track Reference Architecture is a set of prescriptive hardware configurations and operational practices for building large data warehouses on SQL Server. There are reference configurations ranging from a few terabytes to 40TB and more...

[This post uses the old staging functionality from MDS 2008 R2. With MDS 2012, you should be using entity-based staging which will provision the staging tables for you. See eg http://msdn.microsoft.com/en-us/sqlserver/Video/hh802433 ] In the previous...

I’ve been working with a number of customers to implement SQL Server Master Data Services, and found some common requests from customers that require some custom code to implement. Over the course of those engagements I wrote a set of utilities...

Here’s how to run SSIS packages on a remote server using PowerShell 2.0 and PowerShell remoting. This post is short, since this is so incredibly easy. First install PowerShell 2.0. For Windows Server 2008 R2 and Windows 7, these are in the box...

When you are connecting to a web service from an SSIS Script component or transform using a WCF client, the normal method of configuring the WCF client from the application configuration file doesn’t work well. Your package will be running in some...

In SQL Server members of the local administrators group are often configured to log in as sysadmins. A sysadmin, of course, has complete control of the SQL Server instance. Some people remove the local administrators login from SQL Server to prevent access...

Hopefully you are familiar with the greatness of the TransactionScope class in .NET. It provides implicit ambient transaction handling, which can greatly simplify transaction handling in your code. But this ease of use comes with a significant caveat...

Most developers building an OLTP system know that you should declare and enforce relationships between tables using Foreign Keys. But what physical database structures should you implement to support those relationships? Every Foreign Key is a relationship...

There's lots of blog posts out there on how to get your SQL Server BI environment to talk to Oracle, but lot's of it is a bit incomplete, so I'm going to take a crack an publishing a complete guide. I'll start with installing the Oracle client and getting...