| Author |
Topic |
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
 Posted - 2014-04-21 : 18:43:39
|
| Firstly, we don't use Enterprise Edition for cost reasons and I am using 2014.I want to offload some of the reporting load from our application to a reporting server. A couple of options I've been investigating...1. Mirroring. This introduces some network waits since it is synchronous in standard edition. It also doesn't allow me to tailor indexes for reports on the reporting server. I know this feature is deprecated and that a 2-node Always on availability group replaces it. I assume it is synchronous too??? Does anyone know if the second node can be used for reporting (read-only).???2. Transaction log shipping. No network waits but will be down for 15-30 minutes every night. Also, don't believe I can tailor indexes on the reporting server (not sure).Any comments on these two methods or better solutions?Thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-04-21 : 19:36:54
|
| Use transactional replication. Isn't that available in Standard edition? I can't remember. All of my systems use Enterprise.1. Mirroring isn't readable unless you create a database snapshot on the mirrored database, but then it's not up to date. I really don't think this is a viable solution for a reporting environment.2. Users will get disconnected each time the restores need to run. Our log shipping, when we used it, was done every 15 minutes. No you can't tailor indexes as it's backup/restore.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2014-04-22 : 11:12:25
|
quote:
Originally posted by tkizer
Use transactional replication. Isn't that available in Standard edition? I can't remember. All of my systems use Enterprise.1. Mirroring isn't readable unless you create a database snapshot on the mirrored database, but then it's not up to date. I really don't think this is a viable solution for a reporting environment.2. Users will get disconnected each time the restores need to run. Our log shipping, when we used it, was done every 15 minutes. No you can't tailor indexes as it's backup/restore.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
That looks perfect. Now I just need to deal with my developers complaining about how this impacts their entity framework ;) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-04-22 : 14:03:53
|
| How does that affect their entity framework?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2014-04-22 : 18:01:26
|
quote:
Originally posted by tkizer
How does that affect their entity framework?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
After having consulted with them, it doesn't unless they reference a table that I've chosen not to include in the replication. I guess the same danger would exist with T-SQL but the extra level of abstraction makes the chances of it happening a bit greater. |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2014-05-06 : 11:33:55
|
| Tara,I have one follow up question. We've decided to proceed with transactional replication but I want to describe the setup and see if I'm missing something (it sounds too good to be true).We will setup one server using standard edition that will serve primarily as a transaction server (inserts, updates, deletes). The second server, the replica, will be web edition, and most of the read-only functions in our application will hit it.This will allow me to greatly reduce the number of indexes required on the transaction server thereby dramatically improving performance.Am I missing something here?Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-05-06 : 15:31:28
|
| The only thing that needs to be checked is if web edition can be a subscriber in transactional replication. I have never used web edition.Yes you can have a different set of indexes on them as they are independent databases. We generally kept the indexes the same though as we would revert to the publisher for the read-only stuff if we lost the subscriber for some reason. Though indexes do impact DML operations, adequate hardware should help with the index maintenance overhead. It sounds like you may have an IO bottleneck that you are wanting to workaround by decreasing the indexes on the publisher.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2014-05-06 : 16:40:32
|
quote:
It sounds like you may have an IO bottleneck that you are wanting to workaround by decreasing the indexes on the publisher.
I ran some tests inserting 100,000 records into a "Users" table (which started with 2 million records). Without transactional replication in place it took 11 seconds (only a PK). With transactional replication in place it took 15 seconds (only a PK on both publisher and replica). When I added three non-clustered index to the publisher database only the inserts took 78 seconds. When I added the indexes to the replica database only the inserts took 20 seconds. This indicates to me that I stand to see HUGE transactional performance gains if I can transfer many of the indexes to the replica.And yes, the licensing allows for the replica to be web edition which will give us a huge cost savings since the publisher database doesn't need to be as powerful. |
|
|
|
|
Question about a reporting server