Log all performed SQL Statements

Hi there,

I have a little problem concerning the replication of a SQL Server database. The situation is as follows:

We have an application and a SQL server database running at location A. We now want to have a copy at a Location B of the application and the database. Location B does not need to change anything in the program or the database - it's all just read.

The two locations are only connected by a very slow VPN line once in while. The program files can be transferred by creating a compressed backup file and sending it over the VPN line. The database, however is too large - even compressed it takes far too long to send it. The replication with SQL Server did not work out as well.

Now my idea was to perform only once a full backup at Site A and then send only the differential backups to Site B. Since they are performing full backups at Site A every night, unfortunately this does not work either.

Now my question is, if there is any possibility to do some thing like this. I thought it would be good to just create a log file storing every SQL Statement that has been performed on the Site A server and just send this text file. Is this possible?

Thank you very much for you help in advance. Any other solutions or hints are appreciated as well.

So long

TheSentinel85

P.S. I am using MSDE / SQL Server 2000

Transactional replication is just design for this case.

It will replay every command at Server B that already issued at Server A.

It's based on logs. And it will keep the data at Server B consistent with Server A within the same transactional.

Also SQL Server 2000 and MSDE already support it.

Thanks,

Zuomin
|||

Hm...well I just tried to use the replication but it just did not work. It justs does not synchronize the databases and I don't get any errors.

Do you have any good online resource where I can read about replication of SQL Server?

Thanks,

TheSentinel85

|||Sure. Here are some:

Plan: http://msdn2.microsoft.com/en-us/library/aa179423(SQL.80).aspx

Implement: http://msdn2.microsoft.com/en-us/library/aa237152(SQL.80).aspx

Also for prototype, you can setup the replication from Enterprise Manager. The generate the script as start up.

Thanks,

Zuomin
|||

I have now tried to set up the Replication...again.

But I just get the thing to work. I can set up the server for distribution and I can add a publication. On the B site I can add a Subscription (Pull). The problem is: it just does not synchronize the databases.

Thanks for the web pages but I think that is quite the same what is found in the help file - and that was the first resource I looked at.

Is there anything I need to take special care of when setting up the replication?

Thanks,

TheSentinel85

|||Sync replication:

Option 1:
run executalble file, distrib.exe for transactional replication, replmerg.exe for merge replication.

Option 2:
Set up SQL Agent Job do the sync.

Thanks,

Zuomin
|||

Have you looked at log shipping to a read only standby database? Depending on the data change volumne, it may or may not be an answer, but is another approach to replication.

And what do you mean that replication did not synchronize the databases? I did not see in the thread how you knew?

|||

Thanks to you both.

@.ZUOMIN:

ok I will try it manually.

@.JRPM:

Isn't the log empty if they do a complete backup at site A every night? Or is log shipping different from the transaction log backup?

How I know it did not replicate? The database was just empty and the SQL Agent did not do anything.

EDIT: I just saw that log shipping is not working as it is only available in SQL Server Enterprise Edition.... :-(

So long

|||

You could still perform log shipping within Standard edition, http://www.sql-server-performance.com/articles/clustering/log_shipping_70_p1.aspx fyi.

|||

Don't give up on replication - it can be a little confusing at first, but it is actually quite useful. Check out these links to help you find the problem:

http://technet.microsoft.com/en-us/library/ms151756.aspx

Buck Woody|||

Hi everyone,

sry I didn't respond a little bit faster. I have now set up the whole thing in a different way. What I do now is perform a Trace on INSERTS, UPDATES and EXEC of SPs on the Site A server and just send this trace file to my other server. I will have to see if this costs too much performance, but I can not imagine that. The trace file upload which is done via FTP should be much quicker than replicating or log shipping or anything, as the compressed trace has only some hundrer kb.

Hopefully we are going to test the whole thing in the next days...

I will let you know what the results are....

Thank you all so far for your suggestions...

So long

TheSentinel85