What am I doing? Creating a publically available version of the adventureworks 2012 oltp database but by putting it under source control, implementing unit tests and improving the design and code to fit modern development guidelines. Why? There are two aims, firstly to show how to take an existing database and safely get it into source control. Once it is under source control we can start to document how it works and write unit tests which will allow us to make changes to it knowing that we are not breaking backwards compatibility or losing any functionality.
I see the same question again and again “I am using SSDT but it takes a long time to publish changes, how do I make it faster?” or the ever useful “SSDT is too slow”. Anyone who has used SSDT knows that doing a publish can take time, how much time typically varies depending on a number of factors: Is the database local or on the other side of the earth (high latency / possibly low bandwidth)?
I have put the code for MergeUi on github now: https://github.com/GoEddie/MergeUi That is all.
What is MergeUi? A Visual Studio Addin to create and edit MERGE statements in SSDT projects. What have you done? I have released a new version of MergeUi, thanks to Luca Zavarella for prodding me into getting this release out and for pointing out some bugs! This has a number of bug fixes and a new UI, the old one was pretty ugly, although this still isn’t 100% perfect but it should be more usable.
Hopefully this should put the ORM debate to bed, either use an ORM or don’t but please don’t access tables directly… There are obviously reasons behind all of this but if you follow this golden rule you will have a happy DBA and user base. Comments: Anonymous May 21, 2015 - 17:28 well - no one wants a sad well - no one wants a sad panda. Ed Elliott May 21, 2015 - 20:15
When you deploy your Sql Server code, there are two approaches, there is the manual way and the automated “compare & deploy” way. Manual The manual way is where you track what changes you have made and when you want to release, build a script or set of scripts which contain the updates. If you are modifying objects then you should use alter or drop/create and you need to manually create rollback scripts (should you need them).
BIML, you either love it or hate it - either you use it to totally generate hundreds of packages or a bucket load of data flow components all at the push of a button or you are perfectly happy using the SSIS editor, you can see how the package is made up and drag the little lines from one task to another and it is simple right, why bother using xml that is more complicated, you only need to create a single package with a few tasks on it, maybe a script component here or there.
I had the honour of talking at the excellent Sql Saturday Exeter this morning and wanted to share my slides and say thanks to the organisers, sponsors and everyone who came along to my session - I really enjoyed it and I hope you did to. The slides are available: https://the.agilesql.club/assets/talks/sqlsat15/SC-SSDT-CI.pptx Enjoy!
It is sometimes useful to be able to use sqlpackage.exe to create a script that can be deployed manually or still automatically but at a later date plus you may not want to give the user or process that does this sysadmin permissions just so that it can generate a deploy script. The permissions you need in this case are: SELECT permissions on dbo.__Refactorlog VIEW DEFINITION at the database level
Ok so you have created an SSDT project and are currently congratulating yourself on how awesome and cool you are but then you realise that your project is like an empty void, completely devoid of any emotion, love or joy - there is no schema and code in the project and you have all that lovely T-SQL sitting in a database, now how the hell do you get it into SSDT?