Right clicking the rows in the grid allows us to:
- review the generated script
- view revert scripts which can be used to reverse the changes
- view the object differences
If you click “view differences”, be aware that ReadyRoll opens a tab in Visual Studio but doesn’t switch focus to it automatically. Clicking the “Import and Generate Script” button will apply the changes to our ReadyRoll project.
Check out the entire series if you’re new to database deployment.
Before we continue, I’d like to point out that I was very skeptical about this training at first.
In general I’m not a fan of product specific training, especially not for a broad topic such as DLM.
You always risk just getting a sales person in front of you that wants to rant on about how great their product is.
If you’re unlucky you get comparisons to the leading competing products that apparently are the worst in the world.
Since these workshops are part of Redgate’s training program you can imagine my fear before entering them.
I think most people in the community know Redgate as a positive company that’s very active in the SQL Server Community.
I also don’t think I’ve ever seen something bad written about them, either on a blog or just on twitter.
It sounds like it went well overall, and that’s good: understanding how to automate your administrative tasks and deployments is a critical part of modern data management.
According to the blurb, deployment contributors can perform custom actions when deploying a SQL script. And one such use of deployment contributors would be to alter index builds to be an online operation. Microsoft also have a Github DACExtensions repo, and this is very useful because, and in the interests of full disclosure, I have never written a deployment contributor myself. This is partly because the repo has some very good examples, including the online index issue (this post nicely covers how to make use of deployment contributors.) I know those that have and have explained how they work very well. But I think there are a few challenges w/r/t deployment contributors:
No one has ever heard of them
You have to use C#
They’re not entirely straightforward.
This is a good discussion of deployment contributors, including why we don’t see them more frequently.
None of the above mentioned scenarios appeals to Team Foundation Server(TFS) and in order to get into the no-sweat zone during release time, we need to build our deployments around TFS; The obvious choice when working with Microsoft.
Natively Visual Studio, or more precisely MSBuild, does not support dwprojfiles which are used for Analysis Services (SSAS) projects. So obviously this has to involve some kind of magic. But as it turns out, it’s not all that magic. However there is not much documentation on this particular scenario out there but I managed to find one good resource, which is this. It gave me just enough assistance to complete the task.
This is a long post, but well worth reading.
ReadyRoll automatically generates and adds migration scripts to our project in Visual Studio. This means almost all the manual work of writing (or generating with compare tools) migration scripts is done by ReadyRoll. ReadyRoll can also organise these scripts, using semantic versioning, into a logical folder structure within our project.
This post is a continuation in his database deployment automation series; this post talks mostly about what ReadyRoll is and how to install it.
There have been a few discussions on stack overflow recently about how to manage deployments in uncontrolled environments, specifically data migrations. The questions were from an SSDT perspective, I don’t think that SSDT is a great choice for these uncontrolled environments and there are some additional requirements for these uncontrolled environments that need some additional thought and care when creating release scripts (whether manually or using a tool).
Ed has some interesting thoughts here, and I agree with the idea that SQL Server Data Tools deployment scripts are not the best choice when you have people changing schema all around you in unexpected ways.
Creating a DACPAC is easy*. Within SSMS, you simply select the database node, Tasks, Extract Data-Tier Application. I had need to get a database reference to the SSISDB for some reporting we were building out so I clicked along my merry way.
Looks like the command line works just fine, at least.
At any rate, the script is generated and maybe reviewed….. so then what? In SSDT there is no way to create and deploy script in one step; they are two distinct steps. And even if they were one step, this would still not resolve the issue that troubles me. So what is this issue?
The issue is that by creating a script, and then running the deploy, you cannot be sure that the database is in the exact same state that it was when the initial script was generated. If you don’t already know, SSDT runs a deploy entirely in memory, so as mentioned there is no script created. You have to explicitly create the script as part of the process. Or, if you have already created one, you have to re-create the script.
I’m on the fence here. In simpler environments, I think Richie has a good point. But in a complex environment, I wouldn’t even use auto-generated deployment scripts; when you’re changing hundreds of database objects (including adding and modifying columns, backfilling data, modifying indexes, etc.), that automated deployment script is almost guaranteed to fail. And if it does fail, it could leave you in a state of irreparable harm.
For years I’ve dream’t of having one set of tools for developing SSIS packages! not a lot to ask really and great step towards this from Microsoft was decoupling the development IDE from the main SQL Server install to produce the standalone SSDT (SQL Server data tools)
But like most people I work in an environment which has legacy versions for SQL Server in production, but equally like most tech folk (giddy kids wanting new toys) I always try and use the most current and exciting version of VS. This however proves a problem when developing for SSIS, for example if you developed a SSIS package in VS 2013 you’d not be able to deploy this correctly to a SQL Server 2012 version of Integration services catalog. In the past this resulted in having two IDE’s installed, SSDT 2012 (VS shell) for any 2012 catalog development and VS 2013 installed for other work.
I had one person mention during a talk I gave that this isn’t foolproof, but my experience (limited to SQL Server 2012 and 2014) was that deployment worked fine. As always, test before making changes.
For a while I meandered between the two approaches until the ssdt team announced that they had released a nuget package with the DacFx in and I decided that I would move over to that as it meant that I no longer had to check in the dll’s into source control which in itself is a big win. I also decided to fix the extensions thing and so figured out a (slightly hacky) way to get the DacFx dll’s in the nuget package to behave like sqlpackage and allow a sub-directory to be used to load dll’s – I fixed that using this powershell module that wraps a .net dll (https://the.agilesql.club/blogs/Ed-Elliott/DacFxed-Nugetized-DacFx-Power…). Now I have the problem of not having to check in dll’s and still being able to load contributors without having to install into program files sorted BUT I still had the problem of lots of command line args which I was sharing in powershell scripts and passing in some custom bits like server/db names etc.
I’m not very familiar with dacpacs, so this was an interesting read for me.