January 19, 2016 at 3:06 pm
I'm trying to get a feel for how database scripts are deployed. Currently, we are putting scripts into a release folder to be deployed with the next build (2-4 weeks) or to upgrade local developers' dbs. The issue is that trying to run them in the correct order is difficult when there are alot. I was thinking of creating a Master script that would have all the scripts listed in the correct order to run them. Then they could open and run via sqlcmd mode in SSMS. The issue is I don't know what folder they are in. I set a variable at the top that they would have to manually change before running the script (along with setting SQLCMD Mode). Since they are 'only ' developers ;-), I didn't want to overload them with too many things to do. I'm just wondering what other people are doing.
Thanks,
Mike
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 20, 2016 at 12:40 am
We're naming the scripts with a timestamp prefix, such as YYYYMMDDhhmm. All scripts are applied following that order.
-- Gianluca Sartori
January 20, 2016 at 2:53 am
this is a complex topic and there are lots of ways to get it done. I have long been an advocate for using source control for the databases so that we can generate deployment scripts in the same way that application code is generated. You can then look to third party tools to help out with that. Microsoft has Visual Studio Database Tools. Redgate (my employer) has SQL Source Control that works within Management Studio. If you like the manifest approach, there's ReadyRoll. All these tools help you define what you're going to deploy, all based off source control.
"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
January 21, 2016 at 8:40 am
What I've done is create a change order ticket for each deployment. ServiceNow happens to be the production control system we use. Within the source control system for each project (we use Team Foundation Server), there are folders for each product release, each with a dedicated \Deployment folder, and beneath that are sub-folders for named for each change order.
For example:
..\Product1\Release1\Deployment\CHG55435
Within each change order sub-folder are all the DDL and DML scripts required for a deployment. In addition to scripts are a text file with detailed deployment (and rollback) instructions (if needed) and also batch files containing a SQLCMD execution for each script in the proper sequence. There is one batch file for each deployment environment, because the server name paramters are different and possibly the steps and scripts as well.
For example:
CHG55435.txt
CHG55435_QA.bat
CHG55435_PROD.bat
Script1.sql
Script2.sql
...
Following this model we have complete version control, tracability, and a rollback plan for each deployment.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply