Source Control of database objects

[Brian Lewis]

I have put up on bitbucket a public project softwords\DbScripter.

This is a Console application tht takes a Sql Server database and scripts each object (table, view, stored proc, function, role, schema) to a separate file, in a folder hierarchy that matches that of SSMS.

My thinking is that :

1) we incorporate the executable of this project in a folder (DbScripter) under the Pineapples root.

2) we point its output to a folder SqlScripts also under the Pineapples folder.

3) We generate all the scripts from pineapples_develop into SqlScripts, and commit them.

4) at release points , after we have brought all new artefacts into pineapples_develop, we regenerate the scripts. any objects that have changed will modify their corresponding scripts - these new scripts can then be committed.

The purpose of this is not to develop or deploy these database objects - but to be more systematic about tracking changes when they have occurred, and tying database changes to program code changes.

[Brian Lewis]

all honesty here are my thoughts without looking at it or even trying it.

I like the approach of including with the commits the related schema changes. This is how I have been used to do it with modern framework such as Django in Python. I don't like that this is yet another project that needs to be managed by us. ... I typically prefer the approach of "subcontracting" stuff as much as possible. For example, this is out of the box in a framework like Django. I would expect such feature to be out of the box with EF6. Why can't we more often make use of such features?<<< ..ghislain

[Brian Lewis]

IThere are 3 tasks relating to database maintenance for which we may use external tools: - generating POCO objects; ie C# objects that represents tables in the datbase; - scripting database objects for source control - deploying database changes to production sites.

Taking these one at a time: POCO Objects: EF6.1 does now include a function 'Code First from database', which can generate a POCO class from a database table or view. This will work, but will need a bit of cleanup when it is done - it is not updatable, and will generate a new DbContext class every time you use it, that will need to be removed. But - this is an alternative to the POCO Generator standalone program.

Scripting I've installed SSMS 2017 (version 17.2) and used the Tasks->Generate Scripts option to generate scripts. Again, this has the advantage of being part of the MS toolset, but the downsides are: - puts all files in a single folder - you cannot save the options, which you have to specify every time you use it. Getting these options wrong will mean the script will be different, which defeats the purpose for source control. - you have to manually clear out the target folder before starting, otherwise you will not pick up in git any sql objects that have been removed.

That aside, I have played with the various options, and made changes to DbScripter so that the scripts it produces are identical to those generated by SSMS17.2 if you set these options:

Highlighted items are changed from the default. DbScriter matches the file name pattern used by SSMS: <schema>.<name>.<type>.sql e.g. dbo.Enrollments.Table.sql

So with these options you may generate a selection of scripts into a nominated folder. DbScripter becomes a short cut way of generating scripts that could otherwise be produced straight out of SSMS, ensuring consistency by automating the selection of options. So I propsoe we document both ways of generating these scripts. Further, we don;t include dbScripter.exe in the repo; but can document for those who want to use this approach wher eto get it.

[Brian Lewis]

Deployment

EF6 in Code First mode does have a "migration" architecture that lets you construct a script to move between a previously stored snapshot and the current structure. But - this is only tables, there's no support for deploying stored procs or any other database artefact. Sql Delta is a much more full featured solution.

We may find in future others prefer their own tools (Red Gate Sql Compare, or the lame but free Schema Compare in VS2015) - but main task for the developers is to keep the master database schema up-to-date.

[Ghislain Hachey]

If there is no support for anything but table then it won't do in this case. I will try your script today so see if I can not merge it as part of the School Accreditation work for RMI.

[Brian Lewis]

Trialling with the whole db project reveals a number of circumstances that can give "false positives" - trailing white space - blank lines - brackets around object names in the CREATE statement

So dbScripter now has some rudimentary "linting " capacity - and I think we should obser e these standards when editing files by hand:

No blank line between initial comments and CREATE ... statement, e.g. in Stored procedures No trailing white space Always use square brackets around schema and object name in its create statement. No more than 2 consecutive blank lines in the body of a procedure , view or function

i have liaised with Sql Delta developers on this; they have included a new option for the next version 'Strict Compare'; which will flag files as different based on the bracketing of the object name in the CREATE statement. So we can be confident that variations don;t creep in between instances of the database as new objects are disseminated.

[Ghislain Hachey]

Changing priority if this. Not important to clients but mostly for developers. Using SQLDelta with a well maintained master copy is sufficient for now.

[Ghislain Hachey]

Get back to this when time permits and properly document it for other developers. For the time being, @softwords can generate the scripts and SQLDelta is used to manage migrations.