Resource Manager Powershell Script to Enable SQL Server Managed Backup to Azure

The information in SQL Server Books Online about how to generate the Shared Access Signature key for SQL Server Managed Backup to Azure is (at the moment) out-of-date and also not very helpful. https://msdn.microsoft.com/en-us/library/dn449491.aspx So here’s a Powershell script that creates the SAS key, and also will create the Storage Account and Container if they…

0

Installing SQL Server Express and Restoring a Database With Powershell

Here’s a powershell script to install SQL Server Express Edition and restore a database from a commmandline.  It’s intended to be used as part of an installation script for an application that needs a local SQL Express instance.  But it also demonstrates several SQL Server and Powershell interop features like handling InfoMessages from the server,…

0

TransparentNetworkIPResolution in SQLClient for .NET 4.6.1

A new Connection String property has been added for SqlConnection in .NET 4.6.1.  See SqlConnection.ConnectionString But as of this writing the property has not been properly documented.  And the new property is set to true by default:             var con = new SqlConnection(“server=.;Integrated Security=true”); var cb = new SqlConnectionStringBuilder(con.ConnectionString); Console.WriteLine($”TransparentNetworkIPResolution= {cb.TransparentNetworkIPResolution}”); So this post is…

0

How to create a Linked Server to SQL 2000 from SQL 2012 or later

So SQL 2000 has been out of support for a while now.  But I know some of you still run it.  As you upgrade your environments you might find you are upgrading a SQL Server 2000, 2005, 2008 or 2008 R2 server that has linked server connections to a SQL 2000 server.  If so, it can…

1

How to connect to Oracle from a .NET Application

Use this: ODP.NET, Managed Driver – Official         http://www.nuget.org/packages/Oracle.ManagedDataAccess/ This short post is just because most of the information you will find on this topic is out-of-date. Oracle finally wrote a 100% managed code ADO.NET provider and it’s available on NuGet.  So you can just right-click on your references and grab the NuGet package.  Not install,…

4

How to load an Assembly in a SSIS script task that isn’t in the GAC

The documented and recommended way to reference a custom assembly from an SSIS Script Task or Script Component is to install it in the Global Assembly Cache (GAC).  However this is not always possible or simple to do. Here’s a simple workaround for loading an assembly from an arbitrary location.  The idea is to register…

26

Compatible Encryption/Decryption for Windows Store Apps and Full .NET Framework

I recently needed to encrypt some data in a Windows Store App and decrypt it on the server in a full .NET Framework application.  The Encryption APIs available in the two environments are different, so I came up with the following class that implements a compatible pair of encryption and decryption functions to enable encrypting…

1

New Sample Published: ObjectDataReader

I just published a new sample in the MSDN sample gallery:  ObjectDataReader.  This is an update of an older sample (EntityDataReader) that is an IDataReader adapter for object collections.  This is useful primarily for bulk loading SQL Server from a collection of .NET objects. ObjectDataReader is intended to be simple and fast.  It uses dynamically-compiled expressions to…

0

Copying SQL Server Backups to Windows Azure Storage using AzCopy

There are at least three easy ways to get your SQL Server backups to Azure storage, which, being offsite, bottomless, and reasonably-priced is a good thing. First you can use SQL Server 2012’s native backup to URL function. Also you can use the Microsoft® SQL Server® Backup to Microsoft Windows® Azure®Tool, available here: http://www.microsoft.com/en-us/download/details.aspx?id=40740 But…

1

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