Building out processes and mechanisms for automated code deployments and testing can be quite a lot of work and isn't easy. Now, try the same thing with data, and the challenges just shot through the roof. Anything from the simple fact that you must maintain the persistence of the data to data size to up time, and you have real problems in front of you. However, adopting database deployment automation and testing has enormous benefits. Faster, safer, production deployment enhances the protection built around your production systems. Whether we want to use the loaded term of DevOps or not, the benefits of this style of development and deployment are easily documented and measured. So, why are so few people doing it? Conservation of Momentum If we were talking about a…
I'm at the just barely scratching the surface level of getting started with AWS Deployment Pipelines. Of course, the first thing I want to do with them is get a database deployed. A couple of web searches and I find this bit of documentation from the AWS team. Perfect. Not only is this using AWS tools all the way from Commit (source control) to Build (automation) to Deploy (pipelines), but it's using Flyway for the magic sauce of the database deployment (database deployments need magic sauce). Because I'm just learning, it actually took me two days to get to the point where this code was working. Or rather, where it was supposed to work. There's one small bit missing or changed since that article was published. If you're attempting this,…
You’ve set up your access to a SQL Azure site so you’re ready to go. Now how do you get your database up there? Well, TSQL works just fine, so you can script it all out to a file or set of files and apply them. Because you can connect with SSMS, you could use the GUI to build out your database. You can also use the Azure development platform and it’s web based GUI to create your database structures. Personally, the scripting method doesn’t seem too bad to me because you can get your code into source control that way. But, Microsoft has something additional in mind. It’s called Data-tier Applications or DAC for short (and yes, there are other things called DAC in SQL Server, what can I…
I'm stuck. I've been advocating that our company use composite projects for our deployments using the VSTSDBE GDR (Visual Studio Team System Database Edition, General Distribution Release for those not instantly geeky). In a nutshell, VSTSDBE offers two mechanisms for deployment across multiple environments. Both of these work wonderfully well for automation when you are doing a full tear-down and rebuild. When you're doing incremental deployments, they both fail. Option 1: Use SQL Command variables to set environment specific variables such as file location, etc., and post-deployment scripts to set security. This works. It's the method we used prior to the GDR. Unfortunately, security and other environment specific information is hidden inside scripts rather than visible to a given configuration directly within the VS interface. Option 2: Create a composite…
The invitation this month for #PGSqlPhriday comes from Dian Fay. The topic is pretty simple, database change management. Now, I may have, once or twice, spoken about database change management, database DevOps, automating deployments, and all that sort of thing. Maybe. Once or twice. OK. This is my topic. I've got some great examples on taking changes from the schema on your PostgreSQL databases and then deploying them. All the technical stuff you could want. However, I don't want to talk about that today. Instead, I want to talk about something really important, the concept of rollbacks when it comes to database deployments. Why Are Rollbacks Difficult? The entire purpose of your PostgreSQL database is to persist, that is to store, the data. Then, of course, offer up a way…
I was talking with some developers from my team about monitoring, and I said, "We all use the same tools," referring to other monitoring software. Then, it hit me. How is AWS collecting monitoring data on it's RDS servers, specifically, the SQL Server instances. So, I set out to determine what happens on AWS RDS when it comes to the native monitoring. The Setup This part should be as obvious as it is easy. I'm going to use Extended Events. I've written before about how AWS RDS supports Extended Events, so I won't repeat all that here. I'll just leave you with the session I'm running to see what happens on AWS RDS: CREATE EVENT SESSION [ExEventTesting] ON SERVER ADD EVENT sqlserver.rpc_completed, ADD EVENT sqlserver.sql_batch_completed ADD TARGET package0.event_file (SET filename…
In a few weeks at the PASS Data Community Summit, I'll be joining several other Redgaters to put on an all-day precon where we take you on a database DevOps journey. Please let me tell you all about it. From Nothing The plan is simple. We're going to take you from a fully manual deployment process, to a fully automated process over the course of the day. We'll be deploying something about every 30 minutes. As the day progresses, those deployments will become more and more automatic. We'll be using a variety of tools, but the big driver will be Redgate's Flyway. The fact is, most people recognize the need for a DevOps-style approach to their database deployments. However, doing it just isn't easy. This all-day seminar is intended to…
One of the things I love the most about Platform as a Service when it comes to data is the fact that you get RDS backups, built in. Go to the forums. Evidently, backups are one, or more, of the following:a) insanely difficult, so no one does themb) not considered important so no one does themc) not necessary in modern systems because of X technology so no one does them Then, someone deletes a row the business wants back. Someone drops a table. Someone drops a database. Then you find that one, or more, of the following is true:a) no one has ever tried to restore from X technology and you can'tb) come to find out, X technology doesn't really do backupsc) backups are still fundamental to protecting data Enter,…
In case you don't know, I've been writing a series of articles over on Simple-Talk as I learn PostgreSQL. It's all from the point of view of a SQL Server person, expanding into a new technology. In other words, a true story. I thought I'd take a moment here on my own blog to talk about the tools I'm using and why I chose those. AWS RDS Let's establish up front, I'm lazy. Very lazy. So yeah, I'm trying to learn this new technology, but I'm going to find as many ways to use the knowledge, skills & tools I already have as I can. Now, I first started learning PostgreSQL because I wanted to learn more about how Flyway works. Further, as I also needed to learn how to…
There are a lot of uses for Query Store, but one of the most interesting is as an upgrade tool. We all know that upgrades in SQL Server can be more than a little bit nerve wracking. No matter how much you tested stuff in lower environments, deploying an update to production might result in performance issues as your code hits a regression. This is even more true when upgrading from versions of SQL Server prior to 2014 to anything 2014 and above. That's because of the new cardinality estimation engine introduced in 2014. Most queries won't notice it. Some queries will benefit from the better estimates. A few, problematic, queries will suffer. This is where Query Store can be used as an upgrade tool. The Steps We're going to…