SQL Server Performance Forum – Threads Archive
Scaling Out solutions on SQL Server 2000/ 2005
Hi all, I am looking for different scaleout solutions for one of my application.. and doing r&d on these: – • Distributed partitioned views• Data-dependent routing
• Replication
• Message Queuing above I found from microsoft site, got some hints from BOL scaling topic.. but rest ‘Data-dependent routing’ and ‘Message Queuing’ are still untouched. can anyone help me to findout diff scaleout solutions and also are there more options avilable with SQL 2005. http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part10/c3861.mspx
Regards,
Deepak Kumar.
Deepak Kumar
I have tested 2 options avilable with SQL 2000, and found not helping much… do we have other also?
1. SQL Merge Replication
2. FEDERATED DATABASES: Using Distributed Partitioned Views
Can ‘Data Partitioning’ help in mine requirments which is a leading feature of SQL 2005? or what else I should try? Deepak Kumar
I suggest you may wait until the full version release of SQL 2005 to compare the versions, as SQL2K5 is still under beta testing. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Satya: do we have some options avilable for ScaleOut solutions other then ‘SQL Merge Replication’ & ‘FEDERATED DATABASES’ ?? can you/ someone find little time to have look onhttp://www.scaleoutsoftware.com and let me know by their experiences if this kinda solutions can be better alternatives.. Thanks in advance.. Deepak Kumar
What exactly are you trying to accomplish with this scale out project? What are the current issues you are facing? What is the environment like? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Deepak The referred website seems to be good and is more on about their objectives. It will notbe assured until one uses their services, so no further comments on that website activities. I agree with Derrick to explain what exactly you want to achieve with the scaling exercises. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
I have a production server(OS: win2003, SQL 2000 all Enterprises edition) which is running with 9 user databases and cpu utilization on this server has touched 90% (+-5) contineously.. over that i need to create some db’s for reporting applicaitons.. those reporting db’s should be contianing all real time changes from production.. so i want to devide these 9 db’s into 2 server’s. also i am looking for more scaleout solutions.
I can not add more number of CPU’s into that server model.. i can not use SQL Tran replication due to additional overhead.. federated database will not be able to help.. Merge replication can not be applied coz of database design constraints.. in the last i m planning for moving one/two database from that server to another new one for scaleOut solution.. (I suppose No more SQL performance tuning or application changes can be happen to get performance..but as per bussiness growth resource utilization will increase more and more..) 🙁 do we have any solution for replicating some tables from diff db’s to reporting server which put less overhead on production server.. this may give me some time to think and wait for SQL2005 Deepak Kumar
Why not host the reporting databases on other server and if possible archive the data from the current databases. FOr the intermittent performance issues you can follow the defragmentation methods and regular monitoring of logs. Also add more physical memory in order to cater the application query needs, this is a interim solution. But at the large scale you must take out the volatile databases and keep them in a dedicated server if the growth is inevitable. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
actually, reporting server should be having real time updates.. we do archiving of databases but now there are new reports which require latest data !! There are well maintained schedules for defragments as per DBCC Showcontig..even we do SAN disk defrag. mermory is not constraint but its CPU..which is almost finished and make me fearful to think future after 3 or 6 months. What do you say about solution where i take out one extremly being used database(say 50%) and put on secondary server..then make changes in application to use (SQLServerName.databaseName.OwnerName.ObjectName)4 part queries with linked server i know sql queries will not be same efficients due to network latency and wait stats from remote server.. but what will be the waitaige of slowness?? any idea??
Thanks..
Deepak Kumar
Do we have any article/ links to know more about distributed database? Deepak Kumar
Books online andhttp://www.sql-server-performance.com/federated_databases.asp,http://vyaskn.tripod.com/federated.htm and SQL Server performs two types of optimization specific to distributed queries to improve performance:
-Remote query execution used with OLE DB SQL Command Providers.
-Indexed access used with OLE DB Index Providers. Ensure to maintain similar levels of service pack, MDAC, hotfixes etc. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
I think you should take a look at NCache . NCache is an important addition to .NET for Enterprise Applications that are high transaction and require high peformance and high availability. NCache is a clustered object cache for .NET and helps greatly improve performance of .NET applications. NCache implements the ASP.NET Cache API but for clustered and distributed environments. NCache also provides an excellent mechanism for distributed .NET applications to seemlessly share native .NET data with each other (similar to publish/subscribe methodology). You can find out more about NCache at http://www.alachisoft.com. Please let me know if you have any questions about NCache.
]]>