Sync production and test database in SQL Server 2005 + Access 2007 ADP

[Hellgofi]

Hope somebody will show me the right path to my problem. I am preparing and adp file by using Access 2007 and BE is SQL Server 2005.

The problem is making necessary changes b/w production and development database.

Production database is running on a server and users are entering data or accessing data. At the same time we are making changes to development database.


How can I transfer all kinds of changes to production database without deleting any data that entered by the users. I am deleting any fields in any table in development database but adding some fields some tables and procedures. I would like to sync all changes made in the dev database to production database.

Would u pls provide me some support? I tried replication (using sql enterprise version) but I am not sure whether it's doing the right thing for what I actually need.

best regards.

 

[SQL_Hell]

It really depends what kind of changes you are making,
replication wont work because it deals with data changes and it sounds like you are making DDL changes.

The best way is to make sql scripts of all your changes and then apply them to production.

If you are making changes to stored procs and views, just right click on them on the development server and select "script stored procedure as" alter, then run that code against production.

 

[Hellgofi]

Thank you SQL Hell, you got me right. I dont want to change data in production database cause they r real. I only want to apply changes, like new procedures, new functions, new views, new field in a table or modifications in procs.

Let's say, there are procA and tableA with fields field1, field2 at the beginning, in both database. Ppl are using the production db and developers are making changes in dev db. Let's assume that developer added a 3rd field to tableA named field3 and modified the code in procA.

Does your method work for both changes?

thanks again for your comment.

 

[SQL_Hell]

Hey,

For the table in the example you have given.. I would get the table change written using the ALTER TABLE t-sql statement. You can either let the developers write the alter table script or you can do it for them.

So for your example the table code would be

ALTER TABLE dbo.TableA
ADD field3 varchar(20)

One huge advantage of using this method is you can implement an accurate record of change control, you now have a text file with the code that changed the table so you can store this under the date of the event. If you continue to build up this kind of information then when it comes to bugs or errors in the system you have a history of changes to refer to.

When moving the change to production, be careful!!
make a backup of the database, or better still make a backup of the table itself and apply the change to the table backup first.. so you can check it's going to work.

To make a backup of the production table (providing its not 80 million records or something), do the following

SELECT *  INTO TableA_backup FROM TableA

For the stored procedure, after the change has been made to development, right click on the stored proc in management studio and select "script stored procedure" as "alter". You will now have a script for the "alter procedure" code. Store it in your change log (as mentioned in the table bit). Also make a script of the production stored proc, so you have a backup if you need to roll back the change.

To apply to production click the "change connection" button in management studio (top left near the database drop down) and change the connection to your production database and execute the "alter procedure code"

 

[Hellgofi]

thanks SQL Hell,

I think I need to make database of changes made to dev database before appliying them. It looked to me quite manual and risky operation, at least for me.

Thanks for the recommendations.