Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««45678»»

A Simple way of Automating Scripts Expand / Collapse
Author
Message
Posted Friday, December 30, 2011 8:12 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 11:35 AM
Points: 311, Visits: 284
Very long discussion... Visual Studio was mentioned just once I'd say...
Why do not use Visual Studio Database project functionality (with any code version control system)?
Use normal build functionality, get *.DBSchema file, deploy it with VSDBCMD utility .... ?
Post #1228393
Posted Friday, December 30, 2011 8:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 10, 2013 12:48 PM
Points: 19, Visits: 135
For me SSDT is one of the great things in 2012.. looking forward to the release next year
Post #1228424
Posted Friday, December 30, 2011 8:53 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 11:35 AM
Points: 311, Visits: 284
I mentioned Visual Studio 2010 Ultimate or Premium Editions.
It has everything you need now - don't wait for 2012
The only problem - no multi-sever deployment tool
Post #1228435
Posted Friday, December 30, 2011 1:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 10, 2013 12:48 PM
Points: 19, Visits: 135
Cost is the usual reason given for not using VS database projects.
Post #1228547
Posted Monday, January 2, 2012 7:48 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 11:54 AM
Points: 4,427, Visits: 3,423
gchornenkyy (12/30/2011)
I mentioned Visual Studio 2010 Ultimate or Premium Editions.
It has everything you need now - don't wait for 2012
The only problem - no multi-sever deployment tool

Db deployment was made available in VS 2008 Database Edition.

Multiserver deployment and Azure deployment are coming in 2012.
Post #1229024
Posted Monday, January 2, 2012 7:50 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 11:54 AM
Points: 4,427, Visits: 3,423
Martin Bell-290511 (12/30/2011)
Cost is the usual reason given for not using VS database projects.

With 2008, usually not a problem if you do it right, meaning that you keep your projects in TFS ad deploy from TFS.

The big f***up is that 2010 does not support BI and that you cannot do deployments of cubes and reports from the same version of VS.
Post #1229025
Posted Tuesday, January 3, 2012 1:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 10, 2013 12:48 PM
Points: 19, Visits: 135
Adding TFS to the equation iwill increase the cost!
Post #1229088
Posted Tuesday, January 3, 2012 9:51 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 11:54 AM
Points: 4,427, Visits: 3,423
Martin Bell-290511 (1/3/2012)
Adding TFS to the equation iwill increase the cost!

Not if you cost it over say a year, and factor in that TFS is a management tool, a key part of test environment, and at the same time a deployment tool.
Post #1229387
Posted Tuesday, January 3, 2012 10:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 2, 2014 11:04 AM
Points: 3, Visits: 115
We use something very similar where I work. The key difference for us is that we have a table in the database to record which scripts have run. We execute a batch file in our script directory and it proceeds to run the SQL files in the same directory in list order (we prefix filenames with descending date format; multiples on one day would be 20120103a, b, and so on). Each SQL file consists of 2 parts: the wrapper SQL and the payload SQL. The wrapper sql executes a stored procedure to see if this script has already been run in the db. If it has not, the payload is executed and the script name is inserted into the database table. We also write the output of the cmd window to a text file.

The main benefit of the table in the database is that it essentially 'versions' the database. We have dozens of environments in development and QA, and developers will restore newer backups at their own pace. This means all these databases can be from various points in time with scripts run up to the point the DB was backed up/sanitized from production. Since we draw from a common SQL folder in each branch, this allows us to automate running the scripts after any db restore, regardless of the date of the db. After restoring a db, just get latest code and execute the batch script. We archive SQL scripts quarterly in the script folder so a developer would need to update their db at that rate, which is quite reasonable.

It should be noted that we do not use this process for production deployment, as we have a release DBA execute the scripts in order manually, although there is no reason this process could not be done in production. Although we run the batch repeatedly on restored test dbs prior to a release, things can happen in a moving target like production (moving wrt data) and we prefer to slow things down with single script execution. We have a GUI in our application that we can monitor the executed scripts table to verify that the dba is running them in order.

The one hiccup we had recently was relating to updating some strings that contained some javascript syntax (with @ signs, I believe) and the SQLCMD was treating it like a variable. Since our scripts in production are run via SSMS, we could not use the same escape mechanism for SQLCMD since they are not compatible. We ended up breaking up the updates into a couple parts using concatenation of existing column values.

There's probably a slicker way to do parts of what I have described. I'm a developer, not a DBA, though with many SQL server installations, the developer gets to wear that hat a little, and not always by choice.
Post #1229417
Posted Tuesday, January 17, 2012 11:24 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
Would this also work for SQL Express databases?

Kindest Regards,

Rod
Connect with me on LinkedIn.
Post #1237405
« Prev Topic | Next Topic »

Add to briefcase «««45678»»

Permissions Expand / Collapse