asked
how do syncing of development database in Real time
How do I sync my test database with production to ensure that my test database is always an ongoing live copy of production database. Any suggestions as to how to handle this task? Any help from you will be highly appreciated. Thanks
the test database is used by developers for testing purpose so the developers have both read and write permissions only to the test database. any changes to the test database will not be replicated to production.I thought about setting up replication but wanted to bounce the task off here to see if there is a better option of accomplishing this task.
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
You can't use Availability Groups because you need it to be read/write. Same thing goes for mirroring or log shipping. Replication could work, but what happens when a developer deletes a row that gets modified in production. Replication will attempt to update the row and it will break replication, requiring you to reset it again. In short, if you're going to let them modify the data, I don't think there's anything that's going to support you long term. Same thing goes if they modify the structure. It'll break replication. Also, you're not addressing the fact that by giving developers access to your production data you may be violating laws. If it's healthcare data, you absolutely are. If it's financial data, you might be. You also want to be very cautious letting developers have direct access to your production data because of accidents. I know a developer who sent a test email to our entire customer list while he was developing new functionality. Since that time I only ever let them have access to clean data. Most development is done on data that is at least a day old, sometimes weeks old. Why must they have up to the second data?
It sounds like management will have to reconsider. This is just not how things work. Development involves changing the database structure. There's no replication-type technology that can cope with that. Plus you don't want to load Production with the task of replicating to numerous development copies.
I'd have a chat with management then. The problem isn't getting them up to the second data. The problem is getting them up to the second data that they can then manipulate at will and it won't, in any way, bring the whole process crashing down. There isn't any way to do that. You have two options. Option 1) Up to the second data that you can read from. Option 2) Older data (really depends on the size of your database here) that you can read and write. Best approach, day old data from backups. The thing is, management can insist that the speed of light be broken, but that doesn't mean it will be.
We use this process: 1. Restore the live backup onto the test server. 2. Run a script to change some data to Test values. 3. Run a script to scramble sensitive data. This is wrapped up in a SQL Agent job. It makes for a nice repeatable process so you can get back to a known state and retry/re-test things.
It depends on your data but, for example, we overwrite the start of a person's email address with their PersonID (an identity column). It leaves a unique, technically valid email address but it's not real. Here's the function we use for this: set quoted_identifier on; set ansi_nulls on; go if exists (select * from dbo.sysobjects where id = object_id(N'dbo.fObfuscateEmailAddress') and xtype in (N'FN', N'IF', N'TF')) drop function dbo.fObfuscateEmailAddress; go create function dbo.fObfuscateEmailAddress ( @PersonID int , @Address varchar(100) ) returns varchar(100) as /* --------------------------------------------------------------------------------------------- Description : Overwrite the start with the PersonID, ensuring the address remains intact from the @ onwards. Version : 1.2 Date : 26/02/2009 --------------------------------------------------------------------------------------------- */ begin declare @RetVal varchar(100) , @AtSign int , @ID varchar(20) , @Middle int; set @RetVal = @Address; set @AtSign = patindex('%@%',@Address); set @ID = cast(@PersonID as varchar(20)); if @AtSign > 0 begin if len(@ID) < @AtSign begin set @RetVal = @ID + substring(@Address,len(@ID)+1,len(@Address)); end else begin set @RetVal = @ID + substring(@Address,@AtSign,len(@Address)); end end return @RetVal; end go
18 People are following this question.
