Next week is my webinar on why the database is the heart of DevOps. You can still register today to discuss and debate why we need to ensure that database development is integrated into any DevOps work we do for other software.
Register: https://attendee.gotowebinar.com/register/3005019550714731265?source=KB
One of the things that I think about DevOps is that it’s really hard to focus on the hard things that create friction in your process. Most of us want to move faster, and tackle the easy things to automate, the easy things to fix. We want to trust developers to move quickly and pick those items that get in their way.
They do, but they often stop before the database. Or they start to try and find ways to use a different database (ugh). Or they just start putting strange data into existing columns to get things done.
The database matters, and I’ll be talking about why.
I’m doing a webinar on Feb 6 for 30 minutes to talk about the database in DevOps. You can register here: https://attendee.gotowebinar.com/register/3005019550714731265?source=KB
One of the ways that you can more easily perform database development tasks is with SQL Change Automation (SCA). This is a plug-in from Redgate that works with Visual Studio, and while there are tutorials on Setting up SCA and starting in VS, I wanted to add my own take that might make this a little easier for some people.
This is a short Proof of Concept (PoC) that gets you started, and I’ll add in more features over time in future articles. I’ll use the idea of a database of builds for SQL Server, which is a project that you might find useful in your organization.
If you’re an application developer, you are familiar with building new projects. If you’re not, you can probably figure this out, but I’ll just describe and explain the process. In the File Menu, we start our PoC with a File New.
Once you do this, you’ll see a large list of project templates or types. You can see the recent, installed, or even online types. These are broken into a weird mix of languages and target platforms, but in any case, SQL Server is relegated to the “Other”. Along with SSDT projects, if SCA (or ReadyRoll) is installed, you can choose this type.
Once we do this, the project is created, and an outline appears in the Solution Explorer. Before you can go there, SCA pops up a Getting Started dialog. This is designed to help you get moving since the project doesn’t necessarily lend itself to an intuitive flow.
Let’s get started, as it says. The next screen you see is the database connection. This is a slightly confusing dialog to some, but it makes sense once you understand the purpose. We have two connections with buttons and some links, as shown here.
The left side is the development database. The instance chosen is also going to be the place where your shadow database is created. You can pick an existing database here by picking the instance and database in the connection dialog. Alternatively you can create a new database, where you’ll specify the details.
The right side is the target database, which is the downstream target you’ll use for deploying code. This could be production, or it could be a QA database. Either way, this is a default target, and this can be overridden with parameters in the SCA PowerShell cmdlets.
For now, let’s ignore the left and leave it blank. For the right, let’s create a new database. Once we click the link, we get this.
The default database is the name of the project. The default instance is a LocalDB instance, which is an in process SQL Server. This is named (loadldb)\Projectsv13 in the current (Aug 2018) versions of SCA. I’ll leave this alone, but the Edit Connection link would allow you to pick a new instance and database.
Once this is created, we return to the dialog, seeing the dev database and the target, which we left blank. When we move forward, we see this dialog.
This is a reminder that we didn’t create a target, but that’s OK. We can do that later, especially since this is a new project. There is no baseline, which would be an initial script to rebuild the database to its current state. Right now our baseline is nothing.
I’ll create the project and go back to VS. Once I go that, I can see the database on the LocaDB instance in the Object Explorer. I also see the Shadow database here.
If I click Refresh in the SCA pane, I’ll get no results since there are no objects in the database.
Once this runs, I see that all objects are identical. This means the dev database and the shadow database have verified each other.
My project is similarly blank. In solution explorer, I see the outline of the project.
At this point, I can begin development. Migrations scripts I create will appear under the Migrations folder and if necessary, I can edit the pre and post scripts. Or add more scripts here. The default scripts are in place already, but I can change them as needed.
In the next post, we’ll start development.
SQL Monitor has grown from a basic alerting system to an amazing product over the years. From it’s early days as SQL Response, where Brad and I weren’t sure this was a good idea to the current version that has two teams and releases change almost every week. In fact, new features often come out before we bundle them into a major release.
Not that the other teams aren’t doing well at Redgate Software, but across the last 3-4 years the SQL Monitor group has been the best development team in the company. First under Daniel and now under Ben, they’ve done amazing work and it’s hard to put into words how proud and impressed I am with their results. Lots of kudos to Adam as well, who helps with UX. I enjoy our monthly chats about the progress they’ve made and future directions they’re considering.
This post covers one of those areas.
There’s a new tab that’s appeared at monitor.red-gate.com:Estate. Awhile ago we started talking about the idea of scale and how do fewer DBA resources manage all their instances easily. With pressure to be more efficient and still provide rapid responses when there are issues, there has been quite a bit of work over time to help users keep track of all their database resources.
The Estate tab is one of those areas, which has grown to 4 areas: Installed Versions, Disk Usage, Backups, and SQL Agent Jobs. Some of these are in preview, with more work planned in the future.
If you have ideas or requests for features, let us know. Our goal is to find ways to better ensure you get alerted to issues, can solve problems, and keep track of work that needs to be done on all your databases, no matter where they are located.
One of the tasks I’ve often had as a DBA or sysadmin is patching systems. It’s a hassle to keep track of versions and current patches, even with resources like the SQLServerCentral Build Lists. I’ve heard similar challenges from other DBAs.
I made a suggestion to the SQL Monitor team and they came up with the Installed Versions on the Estate tab. This let’s you easily see which versions you have installed in your monitored environment. At a quick view, you can see which SQL Server versions are installed and are up to date.
You can play with filters to get a quick look at your estate, which is helpful when planning your patching resources.
If you look below here, you’ll see more details on the instances, broken out into the groups you’ve configured. What’s nice here is that you see each database, as well as the version and an icon to let you know if you are behind in patching.
Perhaps even more helpful, there’s a link to the download for the latest patch. Makes it easy for you to find the files you need to update an instance. Perhaps even nicer, you can easily see when support ends, and use that to make plans for upgrades if you need to do so.
This is one of the simpler, but amazingly handy features to have in a monitoring system. I’ve built scripts and tools to do this in the past, and while it’s not hard, it’s also not something that is necessarily a good use of my time. This is a task that’s tedious with limited value add for my salary. Much better to have a tool that gathers and manages this for me.
If you haven’t tried SQL Monitor, run over to monitor.red-gate.com and give it a run, or even better, download an eval and try it in your environment.
