November 6, 2015 at 11:11 am
Hi All,
I am a Database Developer trainee, being trained on administration tasks too.
We wanted to automate the process of moving the changes in development database server across the environments. Till now we use to get the objects that are modified and created on, at the end of the day, get scripts of that changes and place them in a tool that was created by our seniors we call them TASK MANAGER.
So when ever we want to update the version we migrate the scripts that were placed in TASK MANAGER to the environment. Now we want to automate the process of scripting out the objects and place them in TASK MANAGER. I found tracking DML changes and script out them on internet but what I am searching is to track DDL Changes and script out them which are ready run again. We got an audit database and DDL Triggers noting every DDL change to audit db. But the script noted there are in XML format that are not ready to run on SQL Server again.
My management is not ready to purchase third party tools and we are supposed to develop it.
Can any one give some way to achieve this.
As I should be, I am ready to learn new technology for this.
WE ARE USING SQL SERVER 2008 R2
November 7, 2015 at 5:15 am
Doing all of this by hand is going to be a ton of work. Management is willing to have you guys spend weeks worth of man-hours to build something that third party tools have already built. That's pretty counter-intuitive.
However, I can at least point you in some directions that should help. First and foremost, instead of trying to figure out what changes have occurred, get your database into a source control system. Best bet is to use the one in use by your developers, but there are free ones out there. Us the source control as the building point for all the database builds going forward. You can use branches and labels to always know what's going on with your system.
Next, instead of building software to automate these steps, look to open-source solutions. TeamCity is a continuous integration manager that you can get for free. It will work with your source control system to understand what changes have occurred over time or in an automated fashion.
With all that in hand, you have most of what you need. The last step is the hardest. You're going to have to pull together the scripts and put them in the correct order. There's no easy way around this without purchasing third party help, so you'll have to do a bunch of coding to make it happen. I'd suggest using an external language such as PowerShell or C# to put this bit together. You'll need to have a mechanism that walks the dependencies within SQL Server, following primary keys and foreign keys programmatically, to put your scripts into the correct order AND make them into ALTER or CREATE as needed. This is going to be the hardest part of this process. It's also where you would be so much better off looking at third party tooling.
One alternative that might be free for you, use SQL Server Database Tools which is built into Visual Studio. Otherwise, you need to look at alternatives like Redgate Software.
"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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply