Automate validation of scripts at command line

Jul 3, 2013 at 11:37 AM
Hi,

I am trying to setup a stage in my automated build/deployment to validate our database patch scripts against sql azure.

We are using RoundhousE to manage migrations and have a rule that previous scripts cannot be modified once they reach a certain environment, so I want to be able to catch incompatibilities as soon as possible. If a problem is found then ideally I would like to have the build fail so that a developer can fix the script (with the tool) and then check it back in to source control.

Is there a way to run the "Analyze" option of SQLAzureMW.exe against a file (or ideally folder of files) from the command line? Or alternatively should I be using SQLAzureMWParseTSQL.exe?

In either case, is there a way to throw an error (or just detect) if an incompatible script is found, rather than just outputting the corrected file?

Thanks,
Rob
Coordinator
Jul 3, 2013 at 3:53 PM
Hi Rob,

I almost have what you want and it really wouldn't be that hard to modify the code to do what you want. I would use SQLAzureMWBatchBackup. I would have to modify it so that you could tell it to analyze a folder and to throw an error instead of just writing a message to a error file. But before we run down that path, I have a suggestion for you. What I would do in your shoes is look at SQL Server Data Tools (SSDT). Here are a few links:

http://msdn.microsoft.com/en-us/data/tools.aspx
http://msdn.microsoft.com/en-us/data/hh322942
http://www.databasejournal.com/features/mssql/sql-server-data-tools-in-sql-server-2012-part-1.html
http://www.microsoft.com/en-us/download/details.aspx?id=36843

Basically, with SSDT, you can have all of your database schema in a project and have that project in TFS. You can check in changes and do builds. You can tell SSDT what your target server is (like SQL Azure) and it will analyze everything and if anything is incompatible, it will through an error during the build process. You can also compare one database against another to see if any changes has been made.

Anyway, I would check out SSDT and see if that does what you want. If it does not do what you need, we can make some changes to SQLAzureWM to get you want you need.

Regards,
George
Jul 3, 2013 at 4:29 PM
Hi George,

Thank you so much for such a quick reply! That's really really helpful.

Actually we were previously using SSDT which did initially work well, but now we have a much larger system with several client databases to manage need to be much more strict with our database migrations after running into problems with this approach. We also have many developers working on different versions of the database. So instead of having a changing schema (stored in TFS as create scripts) and then comparing live systems to the latest version, we now store delta scripts (up and down) against a version.

I really can't see us going back in that direction, as Azure is only one environment we need to support and that would mean a lot of change internally.

I'm definitely interested in your suggestion about using SQLAzureMWBatchBackup. I haven't taken an in-depth look at this yet and it sounds very promising. I really appreciate any help you can give me, even if you just point me to areas of the code and I can take a look.

Cheers,
Rob
Coordinator
Jul 4, 2013 at 2:08 AM
Hi Rob,

Are you just looking for a program that analyzes sql script files? I actually have a program that I never published that does just that. I have a rules engine (99% SQLAzureMW rules engine) that I can call from any .NET program. In my test case, I have a win form app that ask for a file name and then processes it. It would be real simple to take my test program and turn it into a console app. Basically, the app passes a file to process to the rules engine. The rules engine parses the file and sends back a list of results that have:

1) True or False -- Command passed evaluation
2) Original command
3) Recommend command
4) Type of command

From there, you can evaluate the results and throw an error that would stop the build process.

Would this be something that you would be interested in?

Regards,
George
Jul 4, 2013 at 8:20 AM
Hi George,

Yes that's exactly what I'm looking for. I just want to parse a set of script files and throw an error that will stop the build if any scripts fail. The more data about why it failed the better, but ultimately we can use SQLAzureMWParseTSQL.exe against the failing files to correct the problem.

I'd definitely be interested in any help you can give me! Either something custom you have already, or a pointer to anything in SQLAzureMWUtils that I could call to do this.

Thanks again for your help.

Cheers,
Rob
Coordinator
Jul 4, 2013 at 5:02 PM
Hi Rob,

I sent you an email via codeplex. I will send you my program offline so we can play with it.

Regards,
George
Jul 5, 2013 at 6:59 PM
Thanks George.

That's amazing, I really appreciate your help!

I will take a look and let you know how I get on.

Regards,
Rob