SQL and SSIS Deployment/Execution PowerShell Modules

We've done a bunch of work to leverage PowerShell to completely automate our SQL Server and SSIS deployments.  This includes the management of ISPAC and DACPAC packages respectively.  I'd like to share this work with everyone at my blog repo, and specifically here.

I must note the overall approach is a DBA-centric one.  The modules make heavy use of SSISDB and the other packaging executables.  We did this instead of using the C# centric API.  The largest problem with not using that API is that when things change, the modules will have to change. The trade-off is now we have something that can be expanded upon by savvy DBAs, which in my opinion is the audience for these modules.

The modules can be had under the Attribution-ShareAlike Create Commons license.  Please let me know if you are having success with the modules.  So now onto the good part, what's included?!?

SqlDeploy.psm1 - a few common functions leverages by all the modules
DacPacDeploy.psm1 - all things DACPAC.  Includes drift reporting and creating sql scripts.
IsPacDeploy.psm1 - all things ISPAC.  Deploy, create environments, map variables etc.
SSISPS.psm1 - this controls package execution.

I hope these modules can help people out, send a comment if you find them useful!

NSBCon NYC Slides: Beyond the POC

DataBus Compression

We have a requirement to be a bit conservative about our network traffic.  We are using the DataBus to move larger data elements to distributed locations.  My first attempt was to implement a transport message mutator to perform some compression, but it did not compress the DataBus properties.

Come to find out, those are stripped after the file is written to disc, so the compression never hits.  What I found to be the simplest thing to do is to just override the IDataBus/FileShareDataBus altogether.  IDataBus has a couple of simple methods, Get and Put to override.  I used the existing FileShareDataBus as a model and just injected some simple compression.  Let's start with Put:

        public string Put(Stream stream, TimeSpan timeToBeReceived)
        {
            var key = GenerateKey(timeToBeReceived);

            var filePath = Path.Combine(basePath, key);

            Directory.CreateDirectory(Path.GetDirectoryName(filePath));

            var outStream = new FileStream(filePath, FileMode.CreateNew);

            using (var tinyStream = new GZipStream(outStream, CompressionMode.Compress))
            {
                var buffer = new byte[32 * 1024];
                Int32 read = 0;

                while ((read = stream.Read(buffer, 0, buffer.Length)) > 0)
                {
                    tinyStream.Write(buffer, 0, read);
                }
            }

            return key;
        }

Note that all I really did was slip in the GZipStream. Now for Get:

        public Stream Get(String key)
        {
            var bigStreamOut = new MemoryStream();

            using (var bigStream = new GZipStream(File.OpenRead(Path.Combine(this.basePath, key)), CompressionMode.Decompress))
            {
                bigStream.CopyTo(bigStreamOut);
            }

            bigStreamOut.Position = 0;

            return bigStreamOut;
        }

Lastly all we need is a little bit of configuration magic:

    public static class ConfigureCompressedFileShareDataBus
    {
        public static Configure CompressedFileShareDataBus(this Configure config, String basePath)
        {
            var bus = new CompressedFileShareDataBus(basePath);

            config.Configurer.RegisterSingleton<IDataBus>(bus);

            return config;
        }
    }

Now we have a heck of a lot less data on the network...enjoy!

NServiceBus Hands on Labs

 Please check out the new HOL on the NSB site.

NSB Custom Fault Handling in 15 Minutes

We had a need to divert some of our exceptions over to a level 2 help desk.  The help desk would then either fix the problem on behalf of the customer or initiate contact with the customer to fix the problem.  We implemented a custom fault handler and it really only took 15 minutes.

You must be aware that when you take control of the faults, it is up to you to handle all scenarios and in fact SLRs will not work.  In our case, that doesn't really matter since the majority of our exceptions will be handled by a person with this new process.

First we started out by adding some custom config to wire up the handler.  We wanted to maintain the existing process for exceptions that the help desk would not handle, so we borrowed the code from the forwarding handler.

 
public static Configure ForwardToHelpDeskInCaseOfFault(this Configure config)
{
      ....//left out for brevity
 config.Configurer.ConfigureComponent<HelpDeskFaultHandler>(DependencyLifecycle.InstancePerCall)
                .ConfigureProperty(fm => fm.ErrorQueue, ErrorQueue);

      return config;
}

Now we can just simply add that to our endpoint config.

.ForwardToHelpDeskInCaseOfFault();

Lastly, all we need to do is implement the interface and decide base on the exception where it should end up. With this, we are DONE!

 public class HelpDeskFaultHandler : IManageMessageFailures
    {
        private Address localAddress;
        private static readonly ILog Logger = LogManager.GetLogger("WebGateway.HelpDeskFaultHandler");

        public Address ErrorQueue { get; set; }

        public void Init(Address address)
        {
            this.localAddress = address;
        }

        public void ProcessingAlwaysFailsForMessage(TransportMessage message, Exception e)
        {
            this.SendToHelpDesk(message, e, "ProcessingFailed");
        }

        public void SerializationFailedForMessage(TransportMessage message, Exception e)
        {
            this.SendToErrorQueue(message, e, "SerializationFailed");
        }

        private void SendToErrorQueue(TransportMessage message, Exception e, String reason)
        {
            ...//left out for brevity
        }

        private void SendToHelpDesk(TransportMessage message, Exception e, String reason)
        {
            if ( e.GetType().IsAssignableFrom(typeof(MyCustomException)))
            {
                try
                {
                    HelpDeskException hd = new HelpDeskException(message, e.Message);
                    hd.Save();
                }
                catch (Exception ex)
                {
                    Logger.Warn("Failed to send to help desk, sending to error queue", ex);
                    this.SendToErrorQueue(message, e, "SendToHelpDeskFailed");
                }
            }
            else
            {
                this.SendToErrorQueue(message, e, reason);
            }
        }

        private void SetExceptionHeaders(TransportMessage message, Exception e, String reason)
        {
            ...//left out for brevity
        }
    }

NSB PowerShell Support in v3.3

Looks like this is the answer to my request for granular control over infrastructure installation. This works for me as PowerShell is our common denominator for our developers and admins. After getting through the new MSI installer, you will see a new short cut to a prompt.

I'm runing Windows 7 Enterprise and you will get an error OOTB

To get rid of this error you have to install PowerShell v3.  I won't show examples of all the commands available, but we do now have full control over what is installed.  Here is a listing of what is available:

Get-Message

Get-NServiceBusVersion

Install-Dtc

Install-License

Install-Msmq

Install-PerformanceCounters

Install-RavenDB

I think all of these are pretty self explanatory.  The great news is this takes care of allowing you to install only what you need instead of everything in the kitchen sink.

All I wanted was Perf Counters

We've been busy upgrading our endpoints and therefore there have been some reinstalls. NSB has some perf counters built-in to help you understand the relative health of your endpoint, (you can add your own). We still wanted those and therefore ran the Infrastructure installers. Then the fun started. Much to our admins chagrin, MSMQ got re-installed along with RavenDB(which we don't use). Everyone got all grumpy so I went off into the code(3.2.6) to check and make sure we had everything straight. Here is the summary I gave to our admins: Runmefirst.bat – this runs NServiceBus.Host.exe with the “/installInfrastructure” switch. This will install anything if it is not there or does not meet its requirements. NServiceBus.Host.exe – when installing the windows service, you can also use the “/installInfrastructure” switch. All OOTB profiles have “RunInfrastructureInstallers” = false. A custom profile can control this and be able to choose whether to run the install or not. When it comes to perf counters, it used to be the case that on startup if they didn’t exist, they would be created. In v3, the installation stuff has been moved and will pick up anything that implements a given interface. I don’t see a way currently to pick and choose what gets installed OOTB. So my advice would be to avoid running the infrastructure installers and we’ll just create a powershell script, or a custom profile, to isolate the perf counter install. Here is a sample of the script(non-validated) that I gave them:

$pc = New-Object NServiceBus.Unicast.Monitoring.PerformanceCounterInstaller $pc.Install([WindowsIdentity]::GetCurrent());

To follow up we opened a issue to get granular control over installation of infrastructure specifically.