Creating a Linked Server for Oracle in 64bit SQL Server

This post is a walkthrough of creating a Linked Server to Oracle from 64bit SQL Server.  There’s lots of information on doing this on the web, but much of it is out-of-date. First, install the correct Oracle drivers.  You want the latest version of the Oracle Data Access Components (ODAC), and you want the XCopy…

46

Entity Framework Schema Redirection

If your runtime schema differs from your design-time schema, you need to update your Entity Framework metadata to hit your new schema. Here’s a simple solution for doing it at runtime for Entity Framework 4.  I need to update it for EF 5 and 6. // Copyright (c) Microsoft Corporation.  All rights reserved using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.Objects; using System.Xml.Linq; using System.Xml; using System.Data.Metadata.Edm; using System.Data.SqlClient;…

5

How to Move a Database That Has Database Snapshots

Database snapshots cannot be attached/detached, so how do you move a database that has database snapshots to a different disk location? It turns out the _other_ method for moving databases works.  Take the database offline, alter the database to move the files, actually move the files, then online the database.  The twist with database snapshots…

3

Compressed Rowsets in SQL Server with CLR

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.  If you’ve been around Microsoft .NET’s data programming stack for long you’ll remember the ADO.NET…

1

Why is TSQL MERGE failing with a Primary Key Violation? Isn’t it atomic?

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.  This increases the concurrency when the data to be merged is disjoint, ie where you won’t…

2

Backup All Your SQL Server Databases to the Cloud with SQL 2012

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 data against a variety of failures.  If your database is running on-premises backup to the…

3

My Favorite Query For Investigating SQL Server Performance

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 plans and takes a snapshot of the system wait stats. …

7

New Samples Published for Analysis Service Security and Service Broker .NET coding

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 HTTP Security Sample                         This sample shows how to put a HTTP…

0