To Replicate or Not to Replicate...?

[dougcoulter]

Hello everyone - I have a question... actually several.

I have a customer who is collecting data in an MSDE database (on a local workstation). They would like to, at their discretion, "archive" data from the MSDE database to a standard version of SQL Server. This archival would be done when the MSDE nears its database limits. At a later date, they may need to restore some of the archived data from the standard version of SQL Server back to the MSDE instance (size permitting).

Based on this scenario, I have some questions. The first involves detecting when MSDE is reaching its storage limitations. Is this a matter of actually inspecting the size of the associated .mdf file - or is there a SQL-native way? If some data is in fact archived to the standard SQL Server instance, would this in effect free up space on the MSDE instance, or would some additional clean up have to be performed? Not being familiar with replication - I am not sure at this time if it would be a useful tool in this case. Any thoughts?

Thanks!

 

[mrdenny]

It doesn't sound like replication will work for you.

Replication keeps the subscribers in sync with the publisher. What you want to do is instead of copy the data from the MSDE installation, you want to move the data from the MSDE installation to the SQL installation.

Since your already going to be paying for the SQL license, why not just use the SQL installation for the entire process, and remove the MSDE. It seams like the reason that you're doing this at all is because of the size limitations of MSDE.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[dougcoulter]

Thanks Denny - I was thinking along the same lines. I may have some political hurdles to overcome, but it would sure simplify everything.

In case this is not acceptable - is there any clean way of monitoring the size of the MSDE instance?

 

[mrdenny]

sp_spaceused will show you how much space has been used by the database.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[SemperFiDownUnda]

you can use sp_helpDB to get the size of the database.
To me what you want is also not replication but it is one way of doing it.
What you should do in your archival procedure is shrink the database.

Why use MSDE instead of just using the main SQL Server?

The local MSDE may grant faster access to the data then the main SQL Server depending on the network setup

The local machine will function atleast in a limited facility, no viewing of archive data, even if the connection of the main SQL server is not present.

Difficulties are that you have to design and impliment your archiving procedures and that your application has to be designed with being able to pull data from multiple sources. Even if this means you just use distributed queries. But you have to concider that the connection may not be up.

Other conciderations are that even if the local machine is slower then pulling the data from the main SQL server is it acceptible and that the saving of bandwidth on the network makes it more appealing. Its all fine and dandy that you can get the data back from the main SQL server in about 15 seconds but if during that 15 seconds you hog 95% of the bandwidth on part of your network other things will suffer.




Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[dougcoulter]

Thanks to you both for your responses - they are greatly appreciated.

The company that provided the workstation running MSDE is my customer. This company sold the system to an end user. In the eye of my customer (provider of the system), they want to maintain a clear delineation between their system and the end user's existing systems - for better or worse. In any event, I want to be prepared to discuss the details of each solution.

With that said, I am going to do my best to make them realize the inherent shortcomings of this architecture.

Thanks again .