Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Question about a reporting server

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



That looks perfect. Now I just need to deal with my developers complaining about how this impacts their entity framework ;)
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-04-22 : 14:03:53
How does that affect their entity framework?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://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.
Go to Top of Page

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.
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -