September 26, 2010 at 4:28 pm
This is a little weird, so bear with me and hopefully it’ll make sense.
We need to create a reporting server for our ERP system which is on, let’s call it \\ERPServ. Let’s call the reporting server \\ReportServ. Both boxes are SQL 2005 Enterprise, the source is 32 bit and the destination 64 bit. We don’t have any high availability resources at this time. So what I’m considering is the following:
1. Mirror ERPDB from ERPServ to ReportServ. That database would be unavailable as long as the production server is up.
2. At the same time, Log Ship ERPDB from ERPServ to a different DB on ReportServ. Then use subscription replication to copy the tables that we want to a ReportDB. I currently do log backups on the production DB at 10 minute intervals during business hours and at hourly intervals during business hours on the weekend. The DB is backed up and DBCC'd nightly.
I’m thinking this gives us an up-to-date copy in the mirrored DB in case the primary fails. We also have a near-up-to-date copy via log shipping that we can copy from with impunity and it’s current enough for reporting purposes.
The mirror, log-ship, and reporting DBs would all be on the same server instance on a different box than the production system.
We don’t need a witness server as the ERP system has an application server, so if the ERP production DB SQL Server died, we’d have to repoint the application server to the new host. I don’t know that we can easily do automatic failover because of the application server (the vendor has a really screwy architecture).
Now here’s the final (hopefully!) complicating factor. The ERP manager, at any time, may copy the production database to up to four test databases for the general users to do weird stuff with. I’m familiar with log shipping and know that, though the configuration will travel from the production DB to the copies, the jobs won’t and I won’t have multiple databases trying to replicate to the same target. But what about mirroring? Is that going to be an issue when multiple copies of the same database exist? I know that the act of the ERP manager making a backup can interfere with log shipping, I think we can work around that.
Thoughts? Is this totally stupid and shouldn’t be attempted? Need more info?
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
September 27, 2010 at 1:01 am
I think you're trying to do too much with too little.
Your biggest worry is if the server crashes, and if you lose the secondary server, what good will the log ship or replication database do for you? You lose all 3 with 1 server outage. If you can't put the databases on different servers, then it's not really much benefit to you.
My recommendation would be to put another server in a different data center in a different geographical location and use that for the log shipping secondary. This way, if the whole data center goes down, you're still protected.
I would put the replication database on wither a dedicated server or on the same server as the mirror database. If the mirror server goes down, it is very quick to convert a log shipping secondary to a replication subscriber.
September 27, 2010 at 7:28 am
Another server in another data center is not an option, we just have the one server room at this time. That might become a possibility at some point in the future. An additional server in the same center is a possibility so we'd have three boxes involved, but software licensing makes it easier to put the two copies on the same server as we have a per processor license on the report server box. If the production database goes down, the log shipping and reporting server are temporarily irrelevant. If the report server goes down with the mirrored copy, it doesn't matter as long as the production server stays up. We could put the mirrored copy in a separate instance, so we'd have the production server plus the second server that has a mirrored DB in one instance and a transaction log shipped copy plus a reporting copy in another instance.
Both of the servers in question have been rocks, they have never crashed in over 18 months. They're both using local storage for OS and SQL, the databases are on a SAN which is a potential point of failure. I'm not sure if the storage pools for the two physical servers are distributed into separate boxes, I need to check on that. [EDIT: there are two SAN pools, each with two controllers, so not too much of a single point of failure.]
There would be two servers, so if the ERP server goes down, we'd still have the mirror on the report server. If the report server goes down, it doesn't matter temporarily.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply