January 3, 2012 at 9:05 am
I just joined a project were the tech lead is in charge of the database and DAL, the project is using the 'code first' approach. He is forcing (however, I'm pushing back on it), to create a master script file and generate the database everytime from the master script file and never to use alteration scripts for table changes (add/remove columns), add new table(s), etc..
Now I'm recommending to use 1 master script for the database from prod, and if there are any changes needed, create them within an alter script and add those changes to the master or regenerate the master script from the production database.
My question is, should the database be dropped and recreated everytime from a master script file, or should alteration script files be used to make the necessary changes to the database? The database will grow and dropping the database for every change will be time consuming to say the least.
Any suggestions or ideas is greatly appreciated.
January 3, 2012 at 10:21 am
That seems like a recipe for disaster to me. Not to mention deployments could take hours instead of minutes when all objects and data have to be scripted. That seems like it is 100% backwards to me. I do rollup deployment scripts that scripts whatever changes need to happen both the ddl but also data during development. Seems like you would have to perform full regression testing for every single deployment if the entire database is dropped and recreated every deployment.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 3, 2012 at 10:24 am
Thanks for the reply, so my thought and recommendation is good then, I agree dropping and recreating the database everytime will be a nightmare and time consuming. Now if I can just 'nicely' convince the lead of the new approach that will be taken place for deployments. 🙂
January 3, 2012 at 10:30 am
Do you have any large tables? The size of the scripts are going to become unwieldy as the amount of data increases. What happens when somebody accidentally removes some inserts or an entire table from the "master" script? Oops that table doesn't exist anymore. This is the kind of thing that would required full regression testing for each and every deployment. There may be some valid reason that this is being done. If the tech lead has a valid reason then this approach may actually be the best approach. I can't think of a situation where that would be but that certainly doesn't mean it isn't possible. 😛
I don't know all the dynamics of your work place or your team but questioning something that absurd sounding is probably a good idea. What about things like identity columns and foreign keys? If there are identity columns this approach is destined to fail at some point.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 3, 2012 at 10:35 am
Right now the tables are small, however alot of tables (over 50) so the data is small due to its still in a pilot/beta mode. IMO, the scripts should only be for table changes, adding tables, etc. that's it, even though they're using a 'code first' approach, creating the database everytime is just insane
January 3, 2012 at 10:39 am
Like I said too, if you have identity columns and those values are referenced in other tables....how can you maintain a script of that? Let's say you have tablea with an identity column and tableb with a reference to tablea. Then let's say there are 50 records in tablea. Then we delete tablea where ID = 20. How can you create a script for tableb that will now change the values? Do you see where I am going?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 3, 2012 at 10:44 am
I follow ya, its going to be a nightmare overall
January 3, 2012 at 11:19 am
Source control and automation are your friends. You can go in the direction you're trying to go, but not through the use of a single giant script. You'll need lots and lots of scripts. You can do it all through manual labor, but it's easier to use some of the tools available. Visual Studio database projects is one option. Red Gate SQL Source Control is another. Both are documented in the book SQL Server Team-based Development. I wrote the chapters on deployments, so if you have more questions, I can help you out. It's a free download[/url].
Disclosure, I work for Red Gate now, but I didn't when I wrote the book.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply