| Author |
Topic |
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
 Posted - 2011-09-14 : 18:49:11
|
| Our database is getting large enough that running real time reports is stressing the server too much and causing the non-reporting aspect of the database to have performance issues. If I setup an asynchronous mirrored server and pointed the reports to it would that work? Alternatively, we could drop another two CPUs in the main server. But going the mirrored route would have the added benefit of improved disaster recovery.Your thoughts? |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-15 : 00:41:52
|
| If you're going to use the OLTP design for reporting, then replication is what you want, not mirroring.Else, start reading up on data warehouse design. |
 |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-09-15 : 07:15:51
|
| Replication will work ONLY if your DB was designed keeping in mind that replication will be implemented in future.Ex each table that needs to be replicated should have a primary key etc etc.What is the data acceptable latency ? With mirroring your mirror DB will be in a No Recovery mode and you cannot query it.One way would be to create a snapshot of the Mirror Db and query the snapshot.Another way would be implement Log Shipping and have set up the secondary server in Recovery mode or Stand By Mode.All the above criteria of course depends on the network bandwidth and data latency.PBUH |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2011-09-15 : 10:24:13
|
| A MIRRORED DATABASE IS NOT A DATA WAREHOUSE!It is just a copy of a legacy, typically 3rd party, OLTP system.If you want a data warehouse, then build a true data warehouse.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2011-09-15 : 14:43:24
|
| Most clients have stated that 24 hour latency on most reports is acceptable. The one concern is that our application is global so there isn't really a good time to do replication (not sure how it impacts performance).Note: Not sure how relevant it is but we host with Rackspace. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-09-15 : 15:08:35
|
| A mirrored database is in the recovering state and inaccessible. Providing the mirror is synchronised (not synchronous mirroring, in the synchronised state) and you are on Enterprise Edition, you can put a snapshot on the mirror and read it. However that is not a 'data warehouse' by any definition of the term. It's a HA solution that's also providing some reporting capabilities.--Gail ShawSQL Server MVP |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-15 : 15:22:55
|
| If they're already reporting off the OLTP schema, then transactional, or snapshot replication is totally acceptable.Heck, restoring backups is too (as long as sensitive data is nulled out or obfuscated, or encrypted). In fact, this has the added bonus of making sure the backups are good.I've used both methods before and while building out a DW. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-09-16 : 03:13:21
|
quote:
Originally posted by ferrethouse
Most clients have stated that 24 hour latency on most reports is acceptable. The one concern is that our application is global so there isn't really a good time to do replication (not sure how it impacts performance).Note: Not sure how relevant it is but we host with Rackspace.
Replication works on fly.As I said before if you want to implement replication there will be a lot of issues wrt to the database design. I would suggest to go for Log Shipping in Standby mode option if latency of 24 hrs is acceptable and set up the log backup say like every 22 hrs.PBUH |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-16 : 08:05:11
|
| But the only issue with db design is primary keys. Which, in most cases, is easy to remedy if there are tables without 'em.Good idea to mark triggers as not for replication.But that's it. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-09-16 : 08:59:27
|
quote:
Originally posted by russell
But the only issue with db design is primary keys. Which, in most cases, is easy to remedy if there are tables without 'em.Good idea to mark triggers as not for replication.But that's it.
I think you are wrong.Every table in a replication NEEDS to have a primary key.Also there are lot of issues where in your identity columns and foreign keys are not designed keeping replication in perspective.PBUH |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-16 : 09:45:53
|
| I know that it needs to have a PK, I just don't think that's an obstacle. In the OP's case, it's an OLTP system, which hopefully means that most, if not all tables already have a PK. It isn't that difficult to add them usually for tables that don't. And tables that won't or shouldn't -- things like audit/history tables for example, probably wouldn't be replicated anyway. It's rare to find a table that you can't come up with a unique key. And if you can't, just add an identity column. Then the only issue is to make sure that inserts and selects specify columns.Nothing needs to be done in regards to identity columns -- as long as you don't do inserts on the subscriber -- which should never be done anyway.Foreign Keys are a non-issue too. You don't need to do anything at all for them. Using replication does nothing to change the integrity of a FK and replication won't raise errors on the subscriber by making inserts/updates out of order -- as long as no one is deleting keyed records at the subscriber(s). And again, subscribers (as well as reporting servers) are treated as read only, so that's not an issue.Replication is very easy to setup and doesn't require downtime on the target server like mirroring and log-shipping do.I'm not saying it's the only way, but it's a very good way to accomplush what the OP is after. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-09-16 : 14:44:49
|
| I suggest you read thishttp://msdn.microsoft.com/en-us/library/ms152529.aspxandhttp://msdn.microsoft.com/en-us/library/ms152543.aspxYou said that identity columns should not be inserted in subscribers and were suggesting DB design review which is what I am trying to say from the start.If the DB is not designed with best practices then replication is not useful.Also many a times a identity column is defined as a primary key which then has to be replicated over to the subscriber.My original point was that you will have to set "Not for replication" property true for all the foreign keys and identity columns in the tables which will involve lot of manual effort.PBUH |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-16 : 18:03:58
|
| But you don't have to mark FKs or identity columns NOT FOR REPLICATION. The only thing you have to do is make sure there are PKs.I'm saying NOTHING should be written at the subscribers. Identity columns will only break if you perform inserts at the subscriber that didn't come from replication.And since the OP is reporting off of it, then he's not performing write operations anyway, so it's a viable solution, with nothing to worry about other than perhaps adding PKs and marking the triggers.And yes, I'm well aware of those articles. |
|
|
|
|
Mirrored Server as Data Warehouse