June 24, 2010 at 12:52 pm
Hi everyone,
Does anyone know if SQL 2008 can allow multiple databases to access one database? What we want to do is have one production database that is updateable and another server/database accessing the production database in read-only mode. Thanks.
June 24, 2010 at 12:58 pm
Databases don't access databases. Clients do.
I'm not sure what you mean.
June 24, 2010 at 4:09 pm
Hi Steve, you are right, and I am sorry for not thinking this out clearly. Here's what we are trying to do:
We have a production environment with some 6 databases that together are about 3TB (data + index).
We need to replicate those databases to another server on a nightly basis for reporting/analysis purposes. As many as 10 analysts are working on this server and it's very I/O, RAM, and CPU intensive.
What we want to do is remove this replication step somehow. We were looking at database snapshots, log shipping, and replication. Database snapshots seem to be very easy to implement, but not good on I/O, so it's definitely out. I've never been involved with log shipping, and will be researching it this weekend to see if it's performance is acceptable. I also need to know if a database involved in log shipping can be put into read-only mode and then more logs applied to it later on? Do you know?
As far as replication goes, it looks like the best option for us.
Do you have any thoughts on this Steve? Any technologies that I've missed? Any input that you can give me would be VERY much appreciated. Thanks in advance!
June 24, 2010 at 4:43 pm
If I understand, you have replication running, moving data from 6 separate servers/instances to 1 (or 6) other servers?
Replication typically is fairly low load. Can you explain what isn't working? Is this snapshot replication or transactional? I'm surprised this is a problem.
Do you need a complete load, as in everything in a stable state, or can you load some tables and not others (as transactional replication will do)?
The issue with mirroring/snapshots are more that when you update the snapshot, you need to drop it and recreate it. Or create a new one (new name). However that means that users disconnect.
With log shipping, you can restore in STANDBY mode and read the db, and still apply more logs later, however, when you apply logs, you need to disconnect users. It's a restore operation.
Backup restore works, but if you can easily determine what data needs to move (changes/new data), then SSIS might be what I recommend. Perhaps you can move and transform data so that it's more useful to analysts as a warehouse of some sort?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply