question

zillabaug avatar image
zillabaug 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
sql-server-2005replicationadministrationlog-shippingmirroring
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

There are several options for this. What are you trying to solve by doing this and what will you use the test database for? Reading data, writing data, both? Push schema changes? Make other changes? Your answers will help dictate an answer.
0 Likes 0 ·
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.
0 Likes 0 ·
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.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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?
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
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.
1 Like 1 ·
Management insist that developers must have up to the second data. Thanks a lot for your input Grant.
0 Likes 0 ·
I really appreciate your contributions, Grant. I will have a chat with management this week.
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

do you mind sharing the script to change some data to Test values and scramble sensitive data?
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you David for sharing the script and your contribution to this thread...
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.