2

While dumping SQL Server's schema, I have these two options: single file or multiple files. Single file is good because is can be used to install schema on clean server - parents are installed before children so there are no problems during installation. On the other hand separate files are good because they can be versioned and changed objects (tables, view, procedures) can be easily identified - but dependency ordering is lost.

Is there a way to have best of both worlds, that is have all objects in separate files and at the same time preserve dependencies ordering? I am asking about existing schema that needs to be versioned after years of neglecting, not about best practices for starting new project.

2
  • 2
    Create a new database project in visual studio, import existing schema, add project to source control. Let Visual Studio do the schema compare and deployments going forward (it will figure out dependencies). Commented Apr 17, 2020 at 12:17
  • @JonathanFite Thanks, this is what I've been doing for now, this is why I have one gigantic script with all objects and set of consecutive patches - I can live with it but I'd like something better. Are you aware of any way to perform schema comparison in VS from command line? Commented Apr 18, 2020 at 13:13

1 Answer 1

2

The best way to get this done is to use a tool to manage the process of getting stuff in and out of source control and then deploying it.

If you have an MSDN license, Visual Studio has a database project that can do this. It's free with the license and works pretty well. It has some limitations, especially when it comes to dealing with code that can lead to data loss. In those cases you may find manual intervention is necessary. Also, you'll have to do some of your database work within Visual Studio because there's no way to use Azure Data Studio or SQL Server Management Studio. However, this approach solves the dependency issue.

There are also third party solutions. I work for a vendor, Redgate, that makes a few of them. We solve the data loss problem and we work within SSMS (and soon ADS). There are other vendors as well (I just think we're better). These tools also solve the dependency problem while still letting you have individual files so that you can track all changes independently through your source control management tool.

5
  • How can I find information reg. this Visual Studio database project? Does it have any name or something similiar? I looked at Redgate's products and they seem pretty good, but 1) I couldn't run demo on virtual machine and 2) they are too expensive when we take into consideration how infrequently they will be used. Commented Apr 18, 2020 at 13:27
  • 1
    SQL Server Data Tools (SSDT). learn.microsoft.com/en-us/sql/ssdt/… As for how often these tools are used, if you're doing development it should be extremely constant. All changes through source control (the tools get used), automated testing (tools again), deployments to QA/preprod/prod (more tools). In theory, you'll be using these tools all day every day. Commented Apr 20, 2020 at 12:36
  • Also, the demo should work on VMs. I do it that way all the time. Also, I use the Redgate tools with containers, AWS, Azure. They really work. Commented Apr 20, 2020 at 12:39
  • Thanks. Our database is not central to our operations - this is "data store", not "data processor" and changes are rare and they are pretty straightforward. I need tools to automate this process mainly because we lack manpower, not because they are hard to perform by hand. After reviewing my notes it seems that I had problems with JetBrains' DataGrip, not your products - sorry for FUD. Considering scenario that I presented in this comment and in original question - which tools should I check? There are quite a few of them. Commented Apr 22, 2020 at 8:33
  • 1
    SSDT as already listed. Redgate SQL Change Automation. Redgate Flyway (community license on this one, meaning free, that could help due to the infrequency of planned deployments). My opinion, those are the tools to look at for this. There are a bunch of other tools, but none support automation as well as SSDT or my tools (and I lean heavily towards mine). Commented Apr 22, 2020 at 12:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.