Best, or at least non-wtf, practice for working with Visual Studio SQL project

[Bulb]

On this project we have two Visual Studio SQL projects that define the two (Azure MS-SQL) databases it uses. I wouldn't chose that, but we have people experienced with MSSQL and the main architect is a kind of MS fan, so that's what we've got. Now I know almost nothing about the technology, I am only concerned with deployment.

For the test environment, we build the database projects, and then apply the .dacpac build products with sqltool from the build server. But for releases to staging and production the DB dev said that it is not appropriate, and instead exports SQL scripts from it that the operations admin must apply by hand (it is internal app, so we can talk to the operations admin any time, but the data are sensitive, so we are not allowed any direct access).

Of course such process does not fit my configuration manager hat well, because

• The release process is very manual—apparently the SQL scripts are tweaked by hand before being checked in the repository—which makes it prone to mistakes.
• The release scripts are not well tested. They are not what CI/CD does to the development environment, so it relies on the DB dev testing them well.
• There is no protection from the operations missing some of them, or trying to apply it a second time or such, because they are just per-release directory of numbered scripts with no tooling around them.

Is there some better standard practice for doing releases from VS SQL projects?

[Jaime]

@Bulb If you're using VS SQL projects, deploy the dacpac. There really isn't any other reason to use a VS SQL project other than that.

If that isn't how you want to deploy, then do something different. Some people create scripts by doing database diffs, some people do migrations, some people check the actual database delta scripts into source control.

One of my favorites is to check scripts into source control and put a trigger on the source control project that automatically runs any script checked in on the dev server. To deploy, roll all of the scripts into one and give to the DBA.

[Bulb]

@Jaime I am not sure why the DB dev said that applying the .dacpac is not appropriate for deploying to production, but he did. So he uses it to develop the database on the test environment, but then creates migrations to throw over the wall to operations.

If it was up to me, the proper tool for the job is something like liquibase (that the main Java dev suggested), flywaydb (that colleagues use on another project), alembic or similar—where you put the migrations and the tool keeps track of which ones have already been applied to each environment. But that's designed with writing the migrations during the development in mind, which apparently isn't the way it's done with the SQL project.

[Jaime]

@Bulb Right... but since the DB dev shot down deploying the dacpac... all of your effort in the VS SQL project is wasted. So, stop it and switch to something that is acceptable to him.

I'm not saying stop using SQL Server, I'm saying to stop using VS SQL projects. I've been using SQL Server for more than twenty years and I've never used a VS SQL project on anything that mattered. I'm not saying they're bad, just saying there's like a gazillion ways to skin this cat.

[Zenith]

I'm not even sure what a SQL VS project is and I basically reinvented the dacpac.

What I ended up doing to deploy massive volumes of scripts was write a program that looped through a directory and pumped them through ADO command objects. There are events or log outputs that signify warnings (like installing an SP that depends on missing SP) and outright failures. Half on the failures, rerun the warnings (because many times it's because you're running in alphabetical order and not order of dependency) until they stop or top resolving themselves. All this can be logged. Downside is...if you have some idiots that like to mix character sets inside the same script, it's going to choke on that and you'd better have a plan if they were data fix scripts.

[boomzilla]

Whenever we need to apply changes to the DB, we create a script which is checked into our code repository. We review, it, etc, then our DBA wrote a script that wraps it with some admin stuff (mainly audit trail type stuff). It gets rolled into our normal deploy process, which I think is semi-manual (not completely sure, since I'm not part of that process, but it seems to be getting more devops-automated over time ).

We use Jira and svn, and we put a link to the file in the Jira description. There are scripts that someone runs that takes the Jira ticket number as input and then wraps the file, attaches it to the ticket and then (I think) another script that will run the SQL in whichever DB schemas (we have a bunch for developers, CI and test locally). Deploying to the customer's environment is a bit more involved.

We use this basic process for both Oracle and MS SQL DBs that we have in our application.

[Kamil Podlesak]

@Zenith said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:
Downside is...if you have some idiots that like to mix character sets inside the same script, it's going to choke on that and you'd better have a plan if they were data fix scripts.

The plan is called "transaction rollback".

Of course, that does not work a database that don't support transactions in DDL - but that's what you get for using Oracle product (btw, I still find it funny that they cornered the market of non-transactional-DDL database engines without any regulatory oversight).

In one of my previous jobs, we have created a tool for such deployment, too (there were no usable opensource tools for this 10 years ago, but the situation is much better today).

[boomzilla]

@Kamil-Podlesak said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:

mix character sets inside the same script

How do they do that? Doesn't it look screwy in their editor or whatever? Do they just not care?

[Gribnit]

@boomzilla said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:

@Kamil-Podlesak said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:

mix character sets inside the same script

How do they do that? Doesn't it look screwy in their editor or whatever? Do they just not care?

Utf8

[boomzilla]

@Gribnit said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:

@boomzilla said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:

@Kamil-Podlesak said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:

mix character sets inside the same script

How do they do that? Doesn't it look screwy in their editor or whatever? Do they just not care?

Utf8

So...don't they notice the mojibake stuff? Again, how does this slip through?

[Gribnit]

@boomzilla said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:

@Gribnit said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:

@boomzilla said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:

@Kamil-Podlesak said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:

mix character sets inside the same script

How do they do that? Doesn't it look screwy in their editor or whatever? Do they just not care?

Utf8

So...don't they notice the mojibake stuff? Again, how does this slip through?

My guess is via the Standard Process, eventually only the expected has time to be seen.

[Zenith]

@boomzilla I think they just had different defaults on their machine as half of them were secretly overseas.

If you look at the scripts in SSMS, it will put up a warning, both as squiggly lines and a prompt. Somehow it still manages to run them though. TFS would also show them as normal. I don't recall if I opened any in Notepad but I do remember the logs, in Notepad, showing something like "syntax error: select * from BLOCK_OF_WEIRD_CONTROL_CHARACTERS." I went looking for a solution on SO after every check I thought of failed, but they told me it was a people problem so it never got resolved.

All I could do wwas send scripts like that back when I hit them, which gobbled up a lot of the time I was hoping to save by writing the tool, until manglement decided I should stop using the tool because the developers were sad about having so many scripts sent back.

[Bulb]

@Jaime said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:

@Bulb Right... but since the DB dev shot down deploying the dacpac... all of your effort in the VS SQL project is wasted. So, stop it and switch to something that is acceptable to him.

He apparently likes to do it with the VS SQL project… and then generate the migration SQL scripts from it. Not sure why, but since basically he does all of it. Might be a form of job security.

@Zenith said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:

I basically reinvented the dacpac.
What I ended up doing to deploy massive volumes of scripts was write a program that looped through a directory and pumped them through ADO command objects.

There is about trillion tools that do that by now. One was written for almost every ORM and then some. Maybe except the MS ones, because MS has the SQL projects instead. Colleagues use flywaydb on the other project (that uses mysql for all the wrong reasons—postgresql would have been much better—but it does not really matter as flywaydb just feeds the scripts through jdbc connection and keeps track of what passed).

@boomzilla said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:

Whenever we need to apply changes to the DB, we create a script which is checked into our code repository. We review, it, etc, then our DBA wrote a script that wraps it with some admin stuff (mainly audit trail type stuff). It gets rolled into our normal deploy process, which I think is semi-manual

That's similar to what we ended up with, except the DB developer still uses the SQL project for developing the migrations. For apparently little reason then.

@Kamil-Podlesak said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:

market of non-transactional-DDL database engines

There apparently are people who think databases should work that way! Python bindings for SQLite (which can do DDL in transactions just fine) contained an insanely horrible hack that automatically committed before DDL statements for bug-to-bug compatidebility with mysql. It was only fixed in 3.6!

[Gribnit]

@Zenith said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:

@boomzilla I think they just had different defaults on their machine as half of them were secretly overseas.

If you look at the scripts in SSMS, it will put up a warning, both as squiggly lines and a prompt. Somehow it still manages to run them though. TFS would also show them as normal. I don't recall if I opened any in Notepad but I do remember the logs, in Notepad, showing something like "syntax error: select * from BLOCK_OF_WEIRD_CONTROL_CHARACTERS." I went looking for a solution on SO after every check I thought of failed, but they told me it was a people problem so it never got resolved.

I am well convinced that a large proportion of developers deal with IDE warnings by suppressing them. This is also the "green" choice, as not checking anything saves a bit of electricity.

[HardwareGeek]

@Bulb said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:

uses mysql for all the wrong reasons

Are there any right reasons?

[Gribnit]

@HardwareGeek said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:

@Bulb said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:

uses mysql for all the wrong reasons

Are there any right reasons?

MySql maintainers should maybe use it sometimes.

[Zenith]

@Gribnit I only suppress the stupid warnings.

0162 - oh me oh my break; in a switch case so scary
0168 - not using the exception variable in the exception block right at this moment
0612 - obsolete...so what?
0618 - obsolete...so what?
1591 - not going to XML comment every single getter and setter

[Gribnit]

@Zenith said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:

XML comment

You poor bastard.

...

I make available a class with documented compile-time constant values that indicate the case for validly suppressing a warning, so that I have some idea when it was a bad idea to do so. If I were to say "please suppress" a single warning the juniors would have suppressed them all before I got to "warning", and possibly before having said anything at all.

0168

is valid, you should either recover orr log orr rethrow. If it's a canthappen consider System.exit.

[Zenith]

@Gribnit said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:

0168 is valid, you should either recover orr log orr rethrow

It may be the age of the software that I work with but this is a warning and not an exception. The only place that I remember seeing it offhand right now is in some of the networking calls relating to IPv4. I want to say some of the memory attributes that aren't explicitly 32-bit or 64-bit have that or a deprecated warning on them.

[Zecc]

@Zenith said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:

0162 - oh me oh my break; in a switch case so scary

CS0162.

Oh, unreachable break in switch. 'kay.

[dkf]

@Zenith said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:

If you look at the scripts in SSMS, it will put up a warning, both as squiggly lines and a prompt. Somehow it still manages to run them though. TFS would also show them as normal. I don't recall if I opened any in Notepad but I do remember the logs, in Notepad, showing something like "syntax error: select * from BLOCK_OF_WEIRD_CONTROL_CHARACTERS." I went looking for a solution on SO after every check I thought of failed, but they told me it was a people problem so it never got resolved.

When such things are about, iconv is your friend. You can throw weird mixed encoding stuff in there and things out in a consistent encoding that you specify. I remember using it to clean up data from a production database where the data entry clients had obviously thought that "UTF-8" means "ISO 8859-1" or something like that. In some cases. It varied from row to row and column to column, with no consistency at all. None of this mattered until we put the results of the query into XML and got the consumer of that blowing up with a " is this " basic parse error.

That was the same project that put me massively off using Ruby for anything at all, ever. It's far worse than even Python for having an iceberg of horrible code hiding out there in the language implementation, ready to sink the Titanic of your project at the least opportune moment.

[Gribnit]

@dkf said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:

Titanic of your project

My project's not a Titanic! It's a fleet of somewhat smaller ships, only one of which (the largest) is declared unsinkable, and if there's any problem they're all connected to the same giant submarine for power and laundry services. And so what if a submarine sinks?

[dkf]

@Gribnit said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:

And so what if a submarine sinks?

Kursk submarine disaster - Wikipedia

[Gribnit]

@dkf said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:

@Gribnit said in Best, or at least non-wtf, practice for working with Visual Studio SQL project:

And so what if a submarine sinks?

Kursk submarine disaster - Wikipedia

Thanks, but I doubt I can get the DBAs to read it.