I am supposed to migrate a database into VS2012 sql server project. I have already imported all the schema from the target database. The seed data will be added to the a Post-Deployment script in the project. However I also foresee lot of scripts being added by developers while working on various enhancements. These can be alter schema scripts as well data add edit delete scripts. And all of them have to run post deployment and in an order, ideally by the script names with 1.0.0.0 executing followed by 1.0.0.2 and then by 1.0.1.0. SQL Server project doesnt allow for multiple post-deployment files. All I can find online is how to create a project while no-one talks about creating a structure which will take into account the changes(alter scripts) made by developers during different release cycles. Can someone please help me here ?
-
1In order to use multiple post-deploy scripts, you need to flag your one main post-deploy script as "post-deploy" for the build type, then in that script you use the ":r .\script.sql" sqlcmd syntax to include the actual scripts. However, as Keith noted, you should let the project handle the schema changes in 99% of the cases. That's what it's designed to do. Rarely, you might have to do a schema change in a post-deploy script, but until it becomes necessary, I'd try to avoid it. – Peter Schott Jul 11 '14 at 17:03
-
Yes, the need for a post-deploy schema update script should be very rare. Do you really have the need? If so, can you provide an example? – David Atkinson Jul 13 '14 at 09:00
-
More than the schema update, the multiple data insert update scripts are my worry. The seed data for tables would increase as the product increases its functionality. For example, if I am creating a Loan management system, I would seed the status of a Loan(InProcess, Rejected, Granted) in a table. Likewise for every enhancement/story being worked upon might require some seed data to be present in the system. I am unable to understand how this would be take care of. Surely putting all such data in one post-deployment script isnt a good idea. – BatNetMan Jul 13 '14 at 10:27
2 Answers
Schema Comparison removes the need to code your own alter scripts. Schema Comparison automatically creates the alter script for you by comparing the database project's schema to your database server.
Here's a good walk-through of Schema Comparison.
- 20,636
- 11
- 84
- 125
-
Thanks Keith, I think this would solve my problem of schema scripts. However I am at a fix about handling the data scripts. Any system requires some seed data and the same grows as the number of functionalities increase. How will I handle those scripts. These data scripts have to be versioned too since its required to have the flexibility to pull out code-base from source control based on a particular version. – BatNetMan Jul 13 '14 at 10:32
-
Unfortunately VS does not provide a straight forward solution. I think the most common approach is to create a post-deployment script that inserts the seed data. Alternatively, you could insert seed data as part of your Entity Framework/other ORM tier. – Keith Jul 13 '14 at 15:13
Ok here is what I have done so far.
- Followed Keith's inputs and created a project
- Used schema compare for handling the schema based changes
- Created a single master script with Build Action = PostDeploy
- For the data scripts, created folders named as versions to segregate scripts based on different versions
- Added entry of these scripts in the Master post deploy script with the syntax :r Folder\MyScript.sql
This would work for the time being, but there should be a better way to ensure that the custom scripts in folders are automatically picked up in the Master post-deploy script. With this solution, every-time a script is added by the developer, an entry has to be made into the Master deploy script(:r MyCustomeDataScript.sql)
I think there maybe a better way to do this, but I am unaware of it. I am working to find a way where the manual entry of new scripts in the master deploy script can be automated.
- 23
- 3
-
You may consider a product such as Red-Gate's SQL Data Compare for this. If you can keep a "master" copy of your database w/ data somewhere, you can use that to generate the "seed" script. I don't know how many versions you need to support, but you might be able to simplify those scripts a bit if you don't support many versions. However, you do need to have discipline to get those data insert scripts checked in and used. (not necessarily bad as it ensures they aren't missed when you release, but can be tedious) – Peter Schott Jul 14 '14 at 15:50