October 29, 2009 at 4:09 am
I am presented a plan to transfer data on a daily bases from several 100 databases to a central server through log-shipping, and I have big doubts about the feasability of this set-up.
Conceptually we need data from customers who have their own local database transferred to a reporting database, so said customers can produce reports via the internet from a central reporting database. In this reporting database is more data stored then the data to be transferred.
Technically some1 has come with the idea to do this through log-shipping. The data will be log-shipped to a duplicate database at our company and from there cleaned, aggregated and transferred to a central reporting database. I have no experience with log-shipping, I do know replication; what I understand about log-shipping this can be done technically, but there are several issues I can think off:
As I understand it the source database and the target database have to have the same name. Since this local database at the customers all have the same name we need to have an instance for each customer. I wonder how many instances we can create on a server?
I worry about management issues. At first we will create a proof-of-concept with a few customers, but if several 100 customers will be using this proces there will be severe management issues I presume. My fantasy can come up with all kinds of thing going wrong. From that viewpoint I am not happy with this setup. However, an external expert is said to have experience with a similar situation and that in a certain configuration log-shipping can be used as such without causing problems.
I hope I have made the situation clear. My question is: can any1 point me to info on such a setup? Is log-shipping a good solution for these requirements? As mentioned, I have doubst, but I am in unfamiliar territory.
Greetz,
Hans Brouwer
October 29, 2009 at 3:22 pm
How much administrative control do you have over the remote servers? I would think log shipping or replication would be difficult to manage if there wasn't strong control over the servers. Do your customers maintain these servers?
How current does the reporting data need to be? Log shipping could give you near-real time reporting if that is what you need.
Are you going to log ship over the internet or is there some private network in place?
How much data is being transferred/changing?
If the data sets were small and it was OK to have delayed data I would bcp out the data, encrypt, upload and then insert.
October 29, 2009 at 4:41 pm
If it helps allay your fears you can log ship to a database of a different name, so you could have them all in one instance.
the external expert is probably talking about having the target databases at your end in standby mode so they are readable, rather than in recovery mode awaiting the next log to be loaded.
You would have to be sure the source databases were all in full or bulk logged mode and never had their logs truncated. the log restores would also have to be timed to avoid the data cleansing.
Having said that I would have thought this type of setup would normally be done using replication.
---------------------------------------------------------------------
October 29, 2009 at 4:44 pm
I'd lean towards replication myself.
If you log ship, you need a separate database on your central server for each database in your environment. Then you need another database that you would use to summarize all data. If you log ship, you'd need to read the data in and summarize it when you are not restoring a log.
I would tend to replicate the data to a staging database, make sure it's clean, and then summarize it into the main reporting database.
October 30, 2009 at 2:47 am
Tnx for answering all. SOme answers for more info:
We have no control what-so-ever over the customer's databases, that is what is worrying me.
The log-shipping will be done once a day, real-time reporting is not necessarry. This will be done over the internet.
The databases involved are not that large: mostly less then a Gb, a few larger than that. The data to be updated is not that large amount either.
The targetdatabases are not ment to be a backup, just a source for processes to fill up a central reporting database.
Initially replication would be my choice, but can that savely be done over the Internet? I have no experience with that situation. There is no trust between our customer's environment and our environment.
What would be more complex, either to set-up and to maintain: log-shipping or replication?
Greetz,
Hans Brouwer
October 30, 2009 at 10:31 am
First off I have no experience with either log-shipping or replication.
>>We have no control what-so-ever over the customer's databases, that is what is worrying me.
I'd be worried. Consider the relationship. How are you going to get the customers to configure this? What if they say no? Whats their incentive for participating? What if they don't have the resources? Are you going to provide support?
Technically there are probably many solutions that would work. If these were your internal branch offices you could exert you command and control and make it happen.
>>Initially replication would be my choice, but can that savely be done over the Internet?
You'll need to address security in any solution. You won't want to configure a 100 VPNs so encryption would be needed over the internet.
>>There is no trust between our customer's environment and our environment.
I am not sure you can even configure log shipping or replication without trust. Maybe someone else can chime in here.
How about you give them a batch script that they can schedule that extracts the data, encrypts, and then ftps. Low maintenance, easy to implement, no trust needed.
November 2, 2009 at 1:54 am
How about you give them a batch script that they can schedule that extracts the data, encrypts, and then ftps. Low maintenance, easy to implement, no trust needed.
That would be my first choice, before replication, but currently we are talking already about solutions, while I don't know what info exactly is required for this new reporting environment. You bring up good points, some I had not thought about, butthe cooperation of our customers is required in whatever solution will be implemented. The reporting functionality is being build on their request.
Tnx for your input.
Greetz,
Hans Brouwer
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply