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.
| Author |
Topic |
|
ostinoh
Yak Posting Veteran
66 Posts |
 Posted - 2011-09-14 : 17:16:14
|
| Hello - We are looking to use Snapshot Replication to take some of the load off our reporting server and was wondering if someone could shed some light on the questions below.1. How many additional servers would I need besides my current ms sql db server?2. Can you go from SQL 2005 (Current Server) to SQL 2008 (new server)?Thank you in advance for any help. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-15 : 00:19:29
|
| 1. No one can tell you how many servers to scale out to without intimate knowledge of your system. But we can give you some clues on how to decide (more below).2. Yes, but the distributor needs to be 2008.Back to the 1st question...Are you actively monitoring CPU, Memory and I/O statistics in Perfmon?Are you experiencing unacceptable blocking -- or even worse, deadlocks?A reporting server is, in my experience, less likely to need a scale out solution than an OLTP server. IF you've optimized queries, indexed appropriately and perhaps enabled Resource Governor (if you're allowing ad-hoc queries).In short, to determine how many servers to scale out to, consider your I/O and CPU patterns and make very educated guesses about future growth, versus what you have now. And yes, RAM too. But RAM is more easily added than disk and CPU resources.Also, will this be physical hardware of VMs? VMs add a few other "gotchas" but nothing to be overwhelmed about if they are appropriately designed.OK, a couple of questions for you: - What is your current configuration: OS, SQL Edition and x32 or x64? - What is your average CPU and Peak CPU #s - What is your avg read disk queue length? - What is your peak read disk queue length? - What is your Page Life Expectancy? (high and low over say, 24 hours) |
 |
|
|
|
|
|
|
|
Snapshot Replication Questions 05 to 08 SQL?