Search Results

SSDT 16.5 Released part 2: Using the DacFx API and Samples!

Hello! Yesterday I posted about the new release of SSDT from the SQL Tools Team at Microsoft. Two of the big changes are the ability to create the deployment report, deployment script and execute the deployment all in one command. The other change is that now for Azure two scripts are generated: one for any changes that need a connection to master, and the other script for changes to the user database. The samples yesterday showed how to execute the new method using SQLPackage, but a lot of people, myself included, have automated the deployment using the DacFx API through

Do Azure SQL Database External Tables have a place in a micro-service?

I was recently in discussions on using External Tables to link Azure SQL Databases across micro-service boundaries. This has led to some challenging discussions with a client and unexpected opinions internally here at sabin.io . My simple view of a micro-service is of a data store fronted by code, which is in turn behind an API or message subscriber. Importantly only this code accesses the store. I have arrived at this opinion though many (often heated) discussions with developers implementing services, and though working with teams breaking large services into micro-services to clarify ownership and responsibility, remove dependencies and simplify

T SQL Tuesday: Shipping Database Changes with SSDT

Hello! Let’s see how this goes: this months subject for T SQL Tuesday is about shipping database changes, something we here are all familiar with. So I thought I’d make some notes about a tool I’m very familiar with, SQL Server Data Tools. The Good It’s free! SSDT works with Visual Studio Community up to Ultimate, and from Visual Studio 2015 onwards it comes with it’s own Visual Studio IDE. SSDT Has a NuGet package available. So you don’t need to install Visual Studio to get builds running, and crucially can control which version is used to compile at a

SSDT 16.5 Released

Hello! Recently the SQL Tools Team released a new version of both SQL Server Data Tools ( SSDT ) and SQL Server Management Studio (SSMS.)  There’s a range of bug fixes, but two new features that I am particualrly interested in. Firstly, SQLPAckage.exe and the DacFx API can now generate deployment report, deployment script and publish to a datbase all in one action. Neat! This is useful because it’s important to keep track of exactly what has changed on a database. Of course there’s nothing stopping you right now from creating executin these options in three separate actions, but there

SQL Server Configuration Manager - Where is it?

Are you trying to find SQL Configuration Manager to start and stop or configure SQL Services. Well its been removed from the start menu but its still available. You can read about the change here https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-configuration-manager Essentially you need to type SQLServerManager<version>.msc where version is the version you have installed. SQL 2008 = 10, SQL 2016 = 13. You can create a shortcut to replace the one that was removed and put it on your desktop to make it easier to find it.

SQL Server Container Performance

Is SQL Server in a container faster than a VM? I briefly looked at SQL Server containers when Windows Server 2016 was released. Containers offer the ability for rapid provisioning, and denser utilization of hardware because the container shares the base OS’s kernel. There is not a need for a Hyper-Visor layer in between. As a recap for those that are not up speed with containers, the traditional architecture of databases in a VM is like so: The Hyper-Visor OS is installed onto the host hardware, a physical server in the data centre. Many VMs are created on the Hyper-Visor

SQL Supper Scripts

Hello! Thanks to everyone who turned up yesterday at SQL Supper: there was a good turnout of both new and familiar faces. The Demo Gods were with me and I was able to log on to both my Azure VM and able to deploy to SQL Azure. I’ve uploaded the scripts to gist and shared below. I also spoke about raising a Connect Issue so that Microsoft.Build.Utilities.Core NuGet package will work with Microsoft.Data.Tools.Msbuild. I’d like to see this so that we do not have to install the Microsoft Build Tools 2015 MSI on the box. And this is important because

Assist Deploy Is Available on GitHub

Hello! For some time now I have been working on automating SSIS deployments, and earlier this week I published my efforts on GitHub . But before I get into the what/how, let’s focus on the why and let me catch you up on how I got here… The task to take an ispac and deploy in and of itself is quite a straightforward process as there are multiple ways to do this . For those of you who want the abridged version of the linked post, the choices are as follows: Integration Services Deploy Wizard SSIS Catalog T-SQL API PowerShell

Automating SQL Server Performance Testing

You run performance tests as well as functional tests when deploying new code changes to SQL Server, right? Not many people do, I think you should, and this article will show you how to do it by harnessing an existing performance tool, rather than writing your own monitoring infrastructure from scratch. Any good performance monitoring tool that records information to a database will do fine, and we prefer to use Sentry One . Here are the steps to accomplish this. Create a baseline database When you release your database change, you want to have something to compare against as an

SQL Agent depLoyment Tasks Out Now!

Hello! In my time since leaving university, I’ve worked across the spectrum, from tester to DBA, it has always been abundantly clear that SQL Agent Jobs are one of those things that are really difficult to get into source control and deploy. Sure, you can script them out, but that doesn’t really factor in changes. And in many places, the phrase “whatever is in prod” has often been the answer to the question “what are the SQL Agent Jobs supposed to look like?” And frequently, relying on msdb being backed up as been the backup process for SQL Agent Jobs.

How to install SQL Server on Windows Server Core?

As part of automation of database and application deployments, it makes sense to be able to create new SQL Server instances quickly and with minimal resources. I have already explored containers and written about it on this blog, but I’d like to turn your attention to setting up SQL Server on Windows Server Core for those of you that run SQL Server on-premise or within VMs in the cloud. In a domain environment it should be pretty simple to just create a PowerShell session to your target Windows Server where your account is a local administrator and then simply run

Running SQL Server in an Azure Container Instance

Azure Container Instances are still in Preview and not officially available for Windows yet, which made me smile. It took me a while to figure out how to get this working so I thought I’d share what I’ve found. Containers are great for lightweight testing of code before deployment to production servers because they can be created so quickly and they give the same environment to test in very reliably. Now that Microsoft is offering container instances in Azure it means you don’t have to worry about provisioning and configuring your own docker host/cluster. The options for deploying SQL Server

Continuous Integration with Jenkins, SQL Server and Windows Containers

Why use Windows Containers? When creating database applications we need consistency in all our environments to ensure quality releases. Traditionally developers might have their own instance of SQL Server on their workstation to develop against. Database projects would be created in SSDT and pushed to source control when ready for testing. If you’re not using SSDT for database development already, then you should seriously consider it to make your life easier and increase the quality of your releases. Ed Elliot explains why in this blog post . A problem with CI for databases is that databases are a shared resource

Are you running VMware ESX 6 Update 1 and connecting to SQL - be warned

If you are running any of your application on windows 2012 and up and on VMware ESX 6 with Update 1 connecting to SQL you must read on. We’ve been working at a client recently on an issue where requests to SQL have a 500ms latency between requests. What’s really odd is that the time seems to be lost between the client code making a request and the request coming to SQL. Profiler shows a gap of 500ms between the end time of one request and the start time of another request. The issue would only affect some applications and

Running a SQL Server workload using PowerShell

In February 2018, myself and Paul Anderton gave a presentation on how to correlate database deployments with performance issues within the context of a DevOps pipeline. We used Sentry One as our monitoring tool in a Performance Test environment so that we could catch badly performing deployments before they got to production and caused havoc. If you would like to see the recorded video, then you can download it from here: http://info.sentryone.com/partner-webinar-performance-problems As part of this presentation we had a workload running on a workstation, which executed a couple of stored procedures repeatedly, and we’ve had some requests from people

Microsoft Release SQL Server 2016 SP1 and Give Stuff Away!

Today was the Connection (); 2016 Keynote, and it was certainly was worth tuning into to catch-up with what Microsoft have been up to. I was going to write a brief summary of the Keynote and made some notes in a text file, but an hour in to the 2 hour presentation I gave up because there was so much going on. Microsoft have been pushing for an Incremental Service Modelling , to the point that they changed the recommendation from Cumulative Updates being deployed only if you are affected by any of the issues to always update . This

Automatic Tuning Enabled By Default In Azure SQL Database Happens Today!

Hello! From January 15th (ie, this Monday) Automatic Tuning will be enabled by default and gradually rolled out to ALL Azure subscriptions. If you are the owner of a subscriber you would have received an email two weeks ago alerting you of this fact. However if you’re not, and this has not been communicated out to you, this may be something of a surprise. With regards to how the rollout impacts you, this blog post states that “All servers that do not have automatic tuning explicitly configured will inherit Azure defaults, making automatic tuning enabled. Similarly, all databases that do

CosmosDb and CRUD with a S

In preparation for my talk on Azure SQL Database vs. CosmosDb - How do you choose   at giving a talk at sqlbits I spent a lot of time thinking about create, read, update and delete (CRUD) operations, and concluded that with the advent of CosmosDb and its multi-model abstraction layer, CRUD needs a S. CRUD as an acronym has served software engineering very well by encapsulating the core actions of data persistence and retrieval. In the transition from the “ye olde” closed and monolithic systems to the current open, loosely coupled and distributed micro-service architectures via client server and

ARM Template Snippet - Allow Azure Resources To Access Azure SQL Instance With This One Simple Trick!

  Hello! I’m really getting into using ARM Templates to deploy Azure Resources. For those of you not ITK, and for the sake of brevity I’ll summarise in a sentence, ARM Templates are basically Infrastructure as Code. It’s all very interesting, go and have a read . In addition to having our code in source, the ability for us to deploy our entire infrastructure from source into Azure is fantastic, because now we can run one single script to build deploy the infrastructure and the code from source, and we can all have our own personal environment in Azure. And

Create an Azure Active Directory Application and Key using PowerShell

I’ve been a SQL developer for a good few years now, and have also developed numerous web applications, web services and various console apps. However, lately I find myself getting into the world of DevOps, Azure, and necessarily, PowerShell. Whilst familiar with PowerShell to a degree, I’ve learnt a lot over the past few weeks about the Azure PowerShell module, and how we can use it to script tasks that you might not want to do manually in the Azure portal if you’re thinking about automation. This post should help if you want to create an Azure Active Directory application

The problem with TDE and the challenge of T

I recently gave a SQL Supper talk as part of the Microsoft Future Decoded evening community events, and I made the point of not being impressed by Transparent Data Encryption (TDE), be it SQL Server, Azure SQL Database or Cosmos Db. I would like to explain why. The problem of TDE I have worked with data and storage engines for some time and therefore TDE seems straight-forward to me. I think a good overview of TDE for SQL Server, Azure SQL Database and Azure SQL Data Warehouse is given here , and I think a similarly good overview of TDE

Webinar: 8th Feb 2018

Preventing SQL Server Performance Problems Before They Hit Production Join Mark Allison, Paul Anderton and Kevin Kline at 3pm UTC on 8th Feb 2018 Wouldn’t it be great if performance problems in your SQL estate could be detected BEFORE they reach your production databases? In this demo-centred webinar we will review: How to detect and prevent releases of code that could reduce performance of your SQL Server database Ways to prevent the most common performance problems before they reach production: missing indexes, deadlocks and excessive key lookups How effective SentryOne can be in a DevOps pipeline both on-premise and in

In a partitioned world, don’t violate core directive

This is another short post steming from a recent talk I gave on Azure Cosmos Db vs. SQL Database, and there will be more based on discussion and feedback I received and things I learnt along the way. The point I want to make is that when implementing a scale out data storage then regardless of whether you are considerng Azure SQL Database, Cosmos Db or another storage engine, you have to think differently about your read and write patterns. To paraphrase Conor Cunningham linkedin | blog from his excellent OLTP Sharding Techniques for Massive Scale presetation at SQL PASS

SSDT And Pre-Model Scripts Part 2: Execute Pre-Model Scripts Using Octopus Deploy

Hello! Part one of this post concerned the notion of SSDT and Pre-Model Scripts . This post relates to an actual implementation of executing a SQL script using Octopus Deploy. My pull request on Github was approved, so my step template can be exported from the Octopus Library . The only part that I feel requires some explanation is the SQL Scripts and the option to select a previous step. If you've read part one of this post you'll know that you keep the pre-model scripts in a folder in the database solution. These are not compiled (ie not in

Team City Meta Runner - Get Build Status

Hello! When building a deployment pipeline, the choice of tool is less important than the use of the tool: do you go for a tool that centrally controls the flow of a release, from build to running tests to actual deployment, or do you choose separate tools that are loosely hung together and execute a particular part of a release? From personal experience, I have preferred to use a tool to act as a control flow of the deployment pipeline, but leverage tools where there is clear sense to use them. A case in point is using TeamCity to run

Feedback requests to Microsoft

If you didn’t know Microsoft has a number of channels to provide feedback. Most historically user connect (connect.microsoft.com), it integrated with their internal bug tracking systems and meant that items flowed from the users to engineering and back. Well supposed to.   The SQL product group still use connect https://connect.microsoft.com/sql with a few teams also using Trello https://trello.com/b/NEerYXUU/powershell-sql-client-tools-sqlps-ssms and or Slack Slack - sqlcommunity.slack.com Visual studio is moving to https://developercommunity.visualstudio.com/spaces/8/index.html from connect and also has https://visualstudio.uservoice.com/forums/121579-visual-studio-ide for ideas VSTS has a great support and also uses MSDN, and takes requests on Uservoice https://visualstudio.uservoice.com/forums/330519-team-services PowerBI has forums and uses user voice

How to move a replication subscriber to a new server with no downtime to the publisher?

In a recent data centre migration for a client we had a problem where we needed to move a subscriber to a new data centre without incurring any downtime to the publisher or loss of data after the subscription migration. The application was sending hundreds of transactions per second to the publisher. An additional complication was an upgrade to SQL Server 2016 from SQL Server 2008 R2 on the subscriber. The first phase of the migration was to move the subscriber to a new server in a different domain, but without incurring any downtime to the publishing application. How to

Fix For Using Azure Active Directory and DacFX

Hello! As part of an SSDT project we have a contained user that authenticates against an Azure Active Directory group (read more on the CREATE USER page). However the account we are executing deployments with is the SQL Admin account on the Azure SQL Instance. And so we get this error - The executed script: CREATE USER [myUser] FOR EXTERNAL PROVIDER; &amp;amp;amp;#39; Reason: &amp;amp;amp;#39;&amp;amp;amp;#39; At line:94 char:13 + Throw $toThrow + ~~~~~~~~~~~~~~ + CategoryInfo : OperationStopped: (Deployment fail... &amp;amp;amp;#39; Reason: &amp;amp;amp;#39;&amp;amp;amp;#39;:String) [], RuntimeException + FullyQualifiedErrorId : Deployment failed: &amp;amp;amp;#39;Could not deploy package. Error SQL72014: .Net SqlClient Data Provider: Msg 33159,

Migrating SSIS Packages to SSIS Azure

Hello! In case you missed the announcement (and there were a lot of announcements during MSIgnite), SQL Server Integration Services is in Public Preview on Azure! I’ve written about it elsewhere in greater depth , but here are the headlines: It makes use of SSIS Scale Out , which was released as part of SQL Server 2017 . Although it is based on SSIS Scale Out, you can’t actually configure SSIS Scale Out to run on the instance. If this confuses you then read my in-depth post. SSISDB is installed in either SQL Azure or on a Managed Instance. You

Hardening Sentry One for Security

If you have an environment where you need to lock down Sentry One as much as possible, then this article should help. It is well known that the Sentry One service account needs to be a member of the Local Administrators group in each server it monitors, and also a member of the sysadmin role for each SQL Server instance. At the moment this is still a requirement, but if you can live without the Windows metrics, then you could run Sentry One in Limited Mode which will only gather SQL Server specific metrics. Some of our clients run in

How to check Sentry One requirements

I was at a client site recently and implemented Sentry One for them, a great monitoring system for SQL Server. It proved challenging because some servers were in a DMZ on a separate network and domain and some servers were in the same domain. All servers connected via a router and were firewalled off from each other with only the minimum ports open required for them to fully function and communicate. Sentry One operates in two modes, Full and Limited. Full mode allows Sentry One to gather Windows metrics as well as SQL Server metrics Limited mode does not allow

Azure Powershell 4.0 may break your scripts

Ensuring backwards compatibility is something that one has to consider very carefully when doing continuous delivery. We are all to well aware of the challenges of this with database systems as, generally, the database lives much longer than the apps that interact with it and thus one has to maintain the data. SQL has far too many “legacy features” that can’t be changed due to potential breaking changes. Thankfully the SQL team now have a more robust way of managing change and that’s through the compatibility level for the database. This allows you to upgrade to the latest runtime but

Using a Cloud Witness for Clusters

On a client site recently a question was asked about the file share witness in a SQL Server failover cluster on-premise, and where to put it if you only have 2 sites. As always, it depends! Let’s look at some scenarios. Bear in mind that use of the Azure Cloud Witness requires Windows Server 2016 or later. Topology 1 Three node cluster with 2 nodes at primary and 1 at disaster recovery (DR). Most people want high availability at their “primary site” and are happy to have standalone capability at the business continuity (DR) site. To save storage costs, I

SqlServer PowerShell Modules NuGet Package Now Available

Hello! Back in the July Update of SSMS 2016 , a bunch of new SQL PowerShell functions were added, plus two neat additions to Invoke-Sqlcmd : -outputas, which allowed you to output the result set into a data object (eg, data row, data table etc), and -ConnectionString, which allows you to pass in a connection string instead of using the pre-defined parameters. All very useful stuff, go and have a read . However, this update has two issues: firstly, it's not updating the classic sqlps module, but rather has created a new module: sqlserver. This new module will be regularly

SqlServer PowerShell Modules NuGet Package Now Available

Hello! Back in the July Update of SSMS 2016 , a bunch of new SQL PowerShell functions were added, plus two neat additions to Invoke-Sqlcmd : -outputas, which allowed you to output the result set into a data object (eg, data row, data table etc), and -ConnectionString, which allows you to pass in a connection string instead of using the pre-defined parameters. All very useful stuff, go and have a read . However, this update has two issues: firstly, it's not updating the classic sqlps module, but rather has created a new module: sqlserver. This new module will be regularly

Where To Find Us at SQLBits

Hello! SQLBits is back! This year SQLBits is being hosted in the Grand Hallway at the Olympia which was opened way back in 1886. This marks the return of Bits to London, and in fact the south of England, for the first time since 2015. Back then it was hosted at the Excel Exhibition Centre in the East of London. If you have never heard of SQLBits before, I’d be very surprised as it is the largest SQL Server Conference in Europe and offers world class training. But enough about the brief history lesson, let’s talk about what’s coming up

Using CloneDatabase

One of the most interesting features of SQL Server 2014 Service Pack 2 is the new management command DBCC CLONEDATABASE. The idea of it is to create an "empty" copy of the database; all the metadata and statistics of the original and clone are identical, but the clone contains no data. So I ran this on a very small (50mb) database, and within a few seconds it was completed. I then ran this on AdventureWorks2014, which is 250mb, and the time to clone was roughly the same. Your mileage may vary. The console window provides some information on what is

VMWare network performance bug - Getting a repro

If you’ve read my previous post about an issue with VMware ESX 6 and connecting to SQL and 500ms latency , you might be interested in the process we went through to get to the repro. Getting a repro (being able to reproduce a bug/feature) is often a complex and time consuming task. The challenge is like being Sherlock Holmes and using your experience to focus on the aspects of the situation that is important. The challenge is that without a repro, You can’t give anything to a supplier to enable them to triage and find a fix for it

Log Shipping: It's Better Than Bad It's Good!

I'm probably showing my age by quoting an old Ren and Stimpy cartoon here, but to be fair it probably sums up log shipping pretty well. This post is focusing on using a read-only log shipping database for reporting purposes, and the limitations of read-only log shipped databases. I also share some monitoring scripts and a few ideas on how to improve restore performance without having to upgrade the hardware/software. Despite the development of AlwaysOn in recent releases of SQL Server, log shipping is still a great way to set up a copy of databases to be used for reporting.

Notes From The Field: Using Invoke-Sqlcmd

Lately I’ve been working quite a bit with Invoke-Sqlcmd and there’s a few issues with how it handles errors that I feel make it a poor choice of tool to connect and execute SQL. Let’s take a look at a script that will return an expected error:   Funnily enough, this does not return an error. The “$?” is LASTEXITCODE, which means that as it returned as “True” (ie no issues) so PowerShell considers the query to be a success. This is a real problem. Even if we add error handling to the script, we still see the same result.

Migrating SSIS Packages to SSIS Azure part Two – Automating the Deployment

Hello! If you’ve read and followed through my previous post, you will have World Wide Importers Integration Services project running in SSIS Azure. It’s all very interesting, go and have a read . One thing that is missing form that guide, the documentation, and SSIS in general, is how to automate SSIS Deployments. In the WWI SSIS project, there are connection managers that we had to manually update the values of to get it to work post-deploy. This is exactly the opposite of what we want to do. Back when SQL Server 2012 was known as Denali, one of the

Keeping The Database Dev Ops Overhead Lightweight

Hello! One very important aspect of Dev Ops that is perhaps over-looked is the overhead that comes with adopting Dev Ops practices. To help explain what I mean, let’s break that sentence down a bit. What Do I Mean By “Dev Ops Practices” I have a strong suspicion that for each of the posts for this T-SQL Tuesday on Database Dev Ops, everyone will have a slightly different take. Or rather, they are going to articulate what Dev Ops means to them. And so here is my take: broadly speaking, Dev Ops is about increasing the cadence of a feature

How To Use MSBuild Arguments in Visual Studio Builds

When creating MSBuild.proj files for builds in Visual Studio, there are times when you might want some targets called, and other times you don't want the targets called. In my case, our Production builds compile all the web and windows service solutions as well asthe deployment scripts for databases and our BIDS projects. Essentially everything I would want if I was going to run an entire deployment for a release. However when deploying a dll hotfix, I don't want the SQL scripts and BIDS stuff built. When we want to deploy a hotfix, speed is of the essence. Our build

CosmosDb, know your partition costs, well more or less

In my previous post Cosmos Db know your costs, and remember I made the point that by understanding RU costs early, you can make informed decisions in relation to document design and application CRUD and query operations. While it is easy and most certainly useful to arrive at a projected RU cost, using for example, the Request Units (RU) and Data Storage calculator or directly against a fixed 10GB collection via the Azure Portal (incidentally the same costs), the problem is these do not highlight RU costs when partitioning is required to support scale-out . Now if you know your

Team City Meta Runner: Get Build Number

One of the neat things about TeamCity is that it gives you a great deal of control over your process. TeamCity is great to use as a control flow for your pipeline, especially if you are using Octopus to deploy. But one of the pain points here is keeping the parity between build/deploy versions in Octopus the same as in TeamCity. It's important because it prevents users from having to jump between the UI's to keep track of what is deployed where, especially if you are automating the whole process. There's several ways to keep the build numbers aligned, the

SSDTPokedex: Integrating Slack and VSTS Into GitHub Repo - An Infinite Improvement

Hello! One of the home projects I’m currently working on is migrating a database over from SQLite to SQL Server. There’s several tasks that need to be accomplished before we can say that this is successful. Broadly speaking they fit into the key pillars of successful software development: Plan Develop Deploy Measure   So the development part is well under way: there is a repo in GitHub of an SSDT solution that will compile locally (it works on my machine anyway.) Plus I have a couple of releases: one intentionally broken and one fixed. Now there’s plenty of tasks I

Why is SSDT Always Rebuilding My Constraints?

Hello! Let me begin by saying that I’m a big fan of SSDT. It’s free, it works with all flavours of Visual Studio, the team do their very best to keep up-to-date with features that are released with increasing regularity by Microsfot in both Azure and SQl Server on-prem. I’ve met the team a few times, and they’re genuinely keen to engage with the users of SSDT in how it can be better, and how it can be extensible. So, SSDT is a great tool. I said great, but not perfect. It has it's limitations, the same as any tool.

Home

Plan Develop Deploy Measure Sabin.io is a Data Engineering practice. Our focus is on helping companies deliver data systems. We help companies build sustainable applications that consider the support and ongoing development. The use of agile methodologies is fantastic but needs to be aligned with the engineering practices of testing, continuous integration/delivery and a feedback loop to enable continual improvement. Our experience stretches from windows server management and automation through to the support and management of BI systems. We have strong application development experience which means we are able to deliver and support solutions that work with all those involved

SSDT and Pre-Model Scripts

Hello! pre-deploy scripts in SSDT scripts may be executed before the main diff script is executed, but not before the diff script is generated. This is an important fact to understand . If you want scripts to be executed before the diff is generated, then you need to execute pre-model scripts. How you go about this is up to you, but there needs to be a few rules that must be followed: Idempotent : big word, and is used in maths. But don't let that put you off. What it means in this context is that a script can be

SSDTPokedex: Migrating a Database Into SSDT

Hello!   If you want to have the best chance of something being successful, you have to be committed to it from the start. That’s a pretty fatuous sounding statement, almost as bad as “to make something better you have to do more of the good stuff and less of the bad stuff”, so let me contextualise: if you desire to have good testing coverage on an application, then you need to be serious about testing form the first day you write code for the application. Be it manual or automated testing, you need to put the effort in terms

Turbo LogShip 1.0 Released

Earlier this week, I wrote a long post about log shipping. One of the key points I mentioned was how slow restoring can be for a read only log shipped database. If going and reading the whole thing is too much effort for you now, I'll save you the effort (you child of the internet you) and tell you it's because the database needs to be kept transactionally consistent in between restores when making it available for read-only. It creates a .tuf file (transaction undo file) to keep the progress of all the pages that have uncommitted transactions saved to

Turbo LogShip 1.0 Released

A long long time ago, on a blog far far away, I wrote a long post about log shipping . One of the key points I mentioned was how slow restoring can be for a read only log shipped database. If going and reading the whole thing is too much effort for you now, I'll save you the effort (you child of the internet you) and tell you it's because the database needs to be kept transactionally consistent in between restores when making it available for read-only. It creates a .tuf file (transaction undo file) to keep the progress of

Automating adding servers to Sentry One

Overview Sentry One is a great tool for monitoring many servers. For new installations, it can be a bit of a bind to add your existing servers into the tool to be monitored. I have written a PowerShell module to make this much easier and to validate that servers that you thought were being monitored, are in fact monitored. There is full documentation for the module in the Sentry One user guide which explains how to use the functions within it, but a brief explanation is shown below. it is worth mentioning that all the PowerShell cmdlets are doing is

How To Compile SQLProj Files Using Cmdline MSBuild... Errors Included!

I recently needed to build and deploy about 40 small database projects that were in 4 or 5 different database solutions. And I needed to do this several times a day, so compiling via Visual Studio would be a boring and tedious process. So to speed up the process I decided to write the build process in an MSBuild target file and call initiate the build process through PowerShell. The targets file was simple enough to put together. This would be saved in the root location of all the solution folders as "BuildAllDBProjects.targets.xml". Then the PowerShell would be simple enough;

AssistDeploy 1.2 Is Now Live

Evening! AssistDeploy , our attempt to fully automate SSIS Deployments, is not yet a week old, yet we’re already on release 1.2 , which is our 4th release. If you’re wondering how we can be on a 4th release, when the number is .2, I suggest you have a read up on SemVer . Unlike the previous post , this will be brief. But like that post, I’m going to delve into why I’ve made the changes before what, so that the context is understood. What most IT projects attempt to achieve is take some knowledge of a subject matter

Access Denied –How To Prevent a Failure Mid-Deploy

Hello! As part of any decent path-to-live, it is obviously crucial to deploy to other environments. This is crucial because not only do we need to test the changes being made, but just as importantly that the deployment will succeed. An ideally these environments should match as closely to production as possible. The less difference there is between production and all environments up to production, the greater the chances a deployment will succeed. Sounds simple, and perhaps even trivial, but if there is one thing that always, and I mean always catches deployments out, it’s permissions. Chances are production permissions

PowerQuery – The power of M

I love PowerBI, actually I love PowerQuery. It's a great way to combine data from around your business, across the web from anywhere. What I really like is very little is done automatically, i.e. it doesn't do the nice data type detection you get with Excel that screws your imports if the data type in a column differs from the first few rows to the rest of the file. Does that make it difficult to use. No its not. The nice thing is that its very easy to add additional columns, change data types, use expressions, combine datasets, and do

SSIS Package Execution In Azure Is Now Available

Well, it’s been some time coming but SSIS packages are the latest product to make the move from on premise to Azure. You can now take your SSIS projects and deploy them to the new Platform as a Service (PaaS) offering in Azure. The aim of the team at Microsoft was for users to take their current SSIS packages and just “lift and shift” these to Azure. So in development terms that means that there are minimum to no changes to be made in the solution at least. But before we get into the deployment and running of SSIS packages

Why is Sqlpackage Using All The Build Server Memory?

Sqlpackage can be particularly resource-intensive when scripting a database that has a considerable amount of objects. In this post I'm going to discuss the options available when scripting out a database deployment file from a dacpac when using sqlpackage.exe. I'm also going to investigate how resource intensive they are and what we can do to limit the hardware resources used and how much of an impact this has on our waiting times, with some interesting results on where we were taking the performance hit. Recently I've noticed that when we have more than one build running at the same time

Git: How to work on a feature branch but pull in later commits from another branch?

On a client site last week the question was raised: I want to work on a feature for a project which will take longer than other people merging their branches into the dev branch. I therefore want to merge any changes on the dev branch into mine, test my changes before pushing back to the dev branch, and then and ultimately master for a release. We are using a git flow methodology whereby the development manager will merge feature branches from developers’ branches via pull requests into the dev branch. Tests are run and if they pass, we merge into