July 13, 2012 at 12:36 pm
In my organisation we have three main SQL 2005 servers: Development, UAT , Production (all standard editions).
We use versioning software to add schema changes made in development to UAT then finally to production.
Here is the issue, the data itself in dev and UAT is older than that in production and I seek a method to keep the data in sync. I know there are third party applications out there (Redgate SQL Data Compare etc) but the databases are large and I do not want to impact on the production box when using third party tools. Also there is a matter of cost in purchasing such 3rd party applications (as you will understand, the boss wants a solution at little or no cost to the company).
Using SQL Server replication is out of the question as this will replicate both data and schema and, in my experience, restricts the ability to amend the schema with replication in force. I also think that log shipping is not available for Standard edition of SQL 2005.
At present, all I can do is back up production a restore over UAT etc thus losing any changes made in UAT, this then means we have to use the versioning software to re-apply the last set of changes made. This is time consuming and very wasteful of man hours.
If I have made sense, does anyone have ideas?
Regards
Daz
July 13, 2012 at 1:55 pm
Logshipping is available in standard edition. You could use logshipping to keep a current version of the production database (including schema changes moved to production) in Dev and/or QA. You could then use in-house developed SSIS packages to refresh data in Dev/QA when needed by having the logshipped database in standby mode (read-only).
July 13, 2012 at 4:52 pm
darren.franklin (7/13/2012)
In my organisation we have three main SQL 2005 servers: Development, UAT , Production (all standard editions).We use versioning software to add schema changes made in development to UAT then finally to production.
Here is the issue, the data itself in dev and UAT is older than that in production and I seek a method to keep the data in sync. I know there are third party applications out there (Redgate SQL Data Compare etc) but the databases are large and I do not want to impact on the production box when using third party tools. Also there is a matter of cost in purchasing such 3rd party applications (as you will understand, the boss wants a solution at little or no cost to the company).
Using SQL Server replication is out of the question as this will replicate both data and schema and, in my experience, restricts the ability to amend the schema with replication in force. I also think that log shipping is not available for Standard edition of SQL 2005.
At present, all I can do is back up production a restore over UAT etc thus losing any changes made in UAT, this then means we have to use the versioning software to re-apply the last set of changes made. This is time consuming and very wasteful of man hours.
If I have made sense, does anyone have ideas?
Regards
Daz
What do you mean by "versioning software"?
Restoring a prod backup to UAT or DEV is a pretty typical approach but implies you have post-restore data-cleansing routines to maintain compliance as needed, and that you have an easy way to deploy database changes required for the next release. This implies that all developers are keeping their database change scripts accessible in a usable format that supports the end goal.
Here is an excellent article that cuts to the heart of the issue you are facing in terms of how to manage database code in a multi-user, multi-level (i.e. dev, uat, etc., prod) environment without the assistance of an intelligent set of database development, or environment management tools:
Database Changes Done Right by Alex Papadimoulis
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply