I am working on a large project at the minute and I am having trouble tracking my changed .sql files and I am looking for other peoples 'best practice' for managint this so that I implement the best method for me please.
Like many people I have two servers for this project, the local server on my laptop that I do my development on, plus the deployment server connected to the web. I make my changes and development on the local server by making CREATE TABLE and CREATE PROCEDURE
text and saving them as individual .sql files. When I am happy that this is all okay and that it runs on the development system okay I then run the .sql files on the deployment server.
I have had a few issues recently where I have run 9 out of 10 of these files because I missed one or forgot I changed a procedure and brought down the website or corrupted data because that last one procedure wasn't in there when it should be. How does everyone
else manage this? Am I simply being stupid in my method of dealing with this?
What you want is to add yet another server. Call it Staging or Test. When you are ready to release some files, you release them first to the Staging server, and test your application and data to make sure nothing went awry in your release. Then you
know you can release the exact same set of files to production.
But that may not be an option, in which case I recommend keeping a release file, like a text document or XL file dedicated to your next release. Each time you modify a .sql file, add it to your release file. Then consult the file when doing the release
to make sure you don't forget anything. It takes self-discipline to be sure.
btw, I'm not saying put all your .sql code into one file and execute it. The release file is more like a checklist of the names of the files you are releasing. That's all.
Thank you both for your replies. The link to Open DBDiff is something I didn't even realise existed!
I thought a diary / notebook / tracking & logging document might be the way to go but wondered if there was a naming convention etc. that people employed. Traditionally (when working with MS Access apps) I would use an underscore to mark files that were
begin modified and kept a record elsewhere of what was required for deployment. It took disapline I found I struggled to achieve but I will try Open DBDiff and have another go at being disaplined with a notebook as well.
Again, thank you for your replies and the time you have spent.
UselessChimp
Member
210 Points
110 Posts
Best practice for tracking changed sql files.
Oct 10, 2011 05:30 AM|LINK
Hello everyone,
I am working on a large project at the minute and I am having trouble tracking my changed .sql files and I am looking for other peoples 'best practice' for managint this so that I implement the best method for me please.
Like many people I have two servers for this project, the local server on my laptop that I do my development on, plus the deployment server connected to the web. I make my changes and development on the local server by making CREATE TABLE and CREATE PROCEDURE text and saving them as individual .sql files. When I am happy that this is all okay and that it runs on the development system okay I then run the .sql files on the deployment server.
I have had a few issues recently where I have run 9 out of 10 of these files because I missed one or forgot I changed a procedure and brought down the website or corrupted data because that last one procedure wasn't in there when it should be. How does everyone else manage this? Am I simply being stupid in my method of dealing with this?
urenjoy
Star
12171 Points
1824 Posts
Re: Best practice for tracking changed sql files.
Oct 10, 2011 06:48 AM|LINK
I would recommend to use OpenDBDiff tool. It's a good tool to compare databases.
TabAlleman
All-Star
15571 Points
2700 Posts
Re: Best practice for tracking changed sql files.
Oct 10, 2011 01:44 PM|LINK
What you want is to add yet another server. Call it Staging or Test. When you are ready to release some files, you release them first to the Staging server, and test your application and data to make sure nothing went awry in your release. Then you know you can release the exact same set of files to production.
But that may not be an option, in which case I recommend keeping a release file, like a text document or XL file dedicated to your next release. Each time you modify a .sql file, add it to your release file. Then consult the file when doing the release to make sure you don't forget anything. It takes self-discipline to be sure.
btw, I'm not saying put all your .sql code into one file and execute it. The release file is more like a checklist of the names of the files you are releasing. That's all.
UselessChimp
Member
210 Points
110 Posts
Re: Best practice for tracking changed sql files.
Oct 10, 2011 07:41 PM|LINK
Thank you both for your replies. The link to Open DBDiff is something I didn't even realise existed!
I thought a diary / notebook / tracking & logging document might be the way to go but wondered if there was a naming convention etc. that people employed. Traditionally (when working with MS Access apps) I would use an underscore to mark files that were begin modified and kept a record elsewhere of what was required for deployment. It took disapline I found I struggled to achieve but I will try Open DBDiff and have another go at being disaplined with a notebook as well.
Again, thank you for your replies and the time you have spent.