Database Deployments for Non-Developers

Ever since my first disaster of a manual deployment as a DBA I’ve been on the DevOps train. Maybe I didn’t know exactly what it was called but certainly this is a solved problem I said to myself? How do other organizations find the changes that need to be deployed from their dev/test environment into production? How can I ensure that we have a process that has the best chance of succeeding when we go to deploy? And most importantly: can I get out of logging in after hours to manually run scripts?

This, dear reader, is what brought me to Redgate originally: the SQL Compare tool was exactly what I was looking for in early 2014. Working backwards I started requiring that we wrap tooling around our SQL deployments. It started small (and slow): prior to deployments developers needed to meet with me to go over the change report to identify production ready changes they wanted deployed but ended big: last year the DBA team was out of the deployment game altogether since the development team had implemented SQL Change Automation steps in Team City and Octopus Deploy. But not so fast: what about the database development not done by the development team? I am sad to report that the DBA owns its own data repository, helper databases on every SQL instance and the occasional one off database that ends up under our care. Additionally our BI team fully owns the development of our data warehouse and staging databases. Few of these were in source control and in the case of our help db there was nothing in place to ensure they were up to date across our environment. For various reasons we decided we needed to own our deployment tools and we recently found ourselves with our own Team City and Octopus Deploy server. The next couple of blog posts will document the places where I had to do some quick learning/trouble shooting to get it all working right since: I have not been in the development world since 2003 and having solely been a production DBA since my return to tech in 2013. Hopefully if you are doing database development—but aren’t what folks would consider a traditional developer—this guide will prove helpful for standing up and taking advantage of what all this automation is able to offer you!

  1. Configuring Team City
  2. Configuring Octopus Deploy
  3. Building a deployment pipeline.

SQL Saturday #446 & RedGate SQL in the City

So I’m missing out on the Summit 15 keynote this morning as I was sadly paged last night. Between that and the first timers meetup and welcome reception I was completely unable to get up at an appropriate time today. While my work colleague gets ready I wanted to jot down a few impressions from the last few days.

SQL Saturday #446: Portland, OR

This was my third SQL Saturday in Portland and the event continues to be fantastic for a number of reasons. It’s right before Summit so a number of speakers at summer present in Portland that Saturday before so you can open up your Summit schedule if you get in on the sessions early. It’s a great networking event (I think there was around 400 SQL Family that showed up?).  I attended a great session on auditing in SQL Server from Colleen Morrow (bt) The really short and skinny: in SQL 2008 Standard you can only audit at the instance level but in 2008 Enterprise and SQL 2012 and above you can drill down into specific databases with your auditing. It all makes use of extended events so it’s light weight. It’s configurable in filtering, retention and handling auditing errors (lose the audit info, cancel the transaction that triggered it etc). She also shared a plan for centralizing the collection and ingestion of the audit logs that were created in a central location for reporting purposes. Scripts and slide deck here.

SQL in the City

This was my second SQL in the City and it was even better than my previous experience as well: new products like ReadyRoll and InstantClone really speak to some pain points at our organization and I’m looking forward to see we can fully implement them to make it all better. Or at least mostly better. Or at least version or databases: I hear that’s a good start!

I’m super excited and it’s time to hop on a train to start hitting Summit sessions. If you found this post and found it useful let me know!