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 |
|
alainnisam
Starting Member
2 Posts |
 Posted - 2008-04-08 : 11:00:21
|
| I am trying to figure out the best way to setup my SQL environment, any help would be greatly appreciated...I currently have two SQL servers: 1. UTIL_Server_1 and 2. PROD_Server_1. UTIL_Server_1 processes around 2.5 million records (in one table) a day and uses transaction replication to replicate data to PROD_Server_1. PROD_Server_1 is available for public queries and can NOT have any downtime. The data that is processed on UTIL_Server_1 is processed in batches of 2-3 thousand records up to 150 thousand+ records and this data needs to be replicated to the PROD_Server_1 server immediately upon completion. In other words, as the data is processed, it must be migrated ASAP.Questions:1. I need to expand my UTIL environment so that I can process more data more quickly. Is there a way to add an additional server (or even more than one) to my UTIL environment (let's call it UTIL_Server_2) and replicate data from both UTIL_Server_1 and UTIL_Server_2 to PROD at the same time?2. The data in my table is broken down into about 80 different categories. Would there any benefit to maintaining 80 separate tables with identical schemas for these 80 different categories? In other words, would queries run faster if the maximum number of records in any one table was never more than 150,000?Any additional suggestions for optimizing this model would also be greatly appreciated.Tx,ALAINAlain Nisam |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-08 : 11:18:07
|
| Not really. Try to increase your network bandwidth between servers. |
 |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-04-08 : 11:19:08
|
| Is the problem on the I/O? Are the transactions queing? If so, then splitting the publication table would make no difference.Are you dealing with indexes on the PROD server? These may cause issues as well.Are you limiting the throughput in some way?As you can see, there are many ways you could make inmprovements or that could be causing issues, throwing a sledgehammer at the problem will not always solve the issue.Could you run some traces to find out the answers to the above? |
|
|
|
alainnisam
Starting Member
2 Posts |
Posted - 2008-04-08 : 18:40:49
|
quote:
Originally posted by RickD
Is the problem on the I/O? Are the transactions queing? If so, then splitting the publication table would make no difference.Are you dealing with indexes on the PROD server? These may cause issues as well.Are you limiting the throughput in some way?As you can see, there are many ways you could make inmprovements or that could be causing issues, throwing a sledgehammer at the problem will not always solve the issue.Could you run some traces to find out the answers to the above?
I appreciate your help on this... Here is some additional clarification:I am mainly trying to plan for the future and determine how I will double or even triple the number of records I am processing daily without doubling or tripling the amount of time it takes. In other words, I am trying to design a system that will allow me to add an unlimited number of SQL servers all processing their own batch of records simultaneously and then replicating to one (or more if need be) production server. This would allow me to process 5 million or even 10 million records in the same amount of time it takes to currently process 2.5 million. BTW- These records all have to be processed between midnight and about 10am.I don't have an issue with setting up multiple servers in the UTIL environment, my problem is in determining the best way replicate the data from the multiple UTIL servers to the one (or possibly more) production server.In other words, how do you efficiently replicate from multiple publishers to one subscriber?Alain Nisam |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-08 : 19:18:46
|
| You will have locks issue if you do that. If you put that table in seperate drives that will help.The main issue is Network bandwidth.you should have really good network throughput.check this out:http://technet.microsoft.com/en-us/library/ms151762.aspx[http://www.sql-server-performance.com/tips/replication_tuning_p1.aspx |
|
|
|
|
|
|
|
|
Architecture and Replication Questions