This is a scale limit of the Windows Server Network Load Balancing component.

Here is how we implement SQL Server Load balancing in our projects.

Load balancing a SQL Server Database Environment

For example: You have a SQL Server 2008/2008R2/2012 Production Cluster environment in an Active / Passive configuration. You have 2 single / standalone SQL Servers 2008 R2 that receive production data via SQL Replication. For SQL Replication the destination SQL versions do not need to be equal to the source SQL version. Therefore a SQL Server 2012 production server can replicate data to SQL Server 2008.

On the front end you have a reporting application or a website or any other application that is querying the Reporting Environment.

That application is creating a connection on demand to a specific DNS or IP address. The application chooses which SQL Server to connect to.

The loadbalancing solution consists of implementing Network Load Balancing on (Windows Server 2000, 2003, 2008(R2), 2012 and up) and configuring it.
By implementing NLB you will generate a new virtual IP address. Add a DNS entry on your DNS server to point to that virtual IP address.

Once you yave done that, you can connect to your SQL Server box via the newly created DNS or IP address. If you perform these steps on the other node (up to a maximum of 32 nodes) by joining that host in the NLB cluster you just created you will not end up with many new IP addresses but only 1 new virtual IP address that is shared amongst all hosts that you place in a cluster.

Any new request to that DNS or IP address is load balanced over your 2 hosts. You can configure the actual load balance rule (round robin, 50/50 split, … ) in NLB.

Scalability. Network Load Balancing scales the performance of a server-based program, such as a Web server, by distributing its client requests across multiple servers within the cluster. As traffic increases, additional servers can be added to the cluster, with up to 32 servers possible in any one cluster.

High availability. Network Load Balancing provides high availability by automatically detecting the failure of a server and repartitioning client traffic among the remaining servers within ten seconds, while providing users with continuous service.

I was trying to solve SQL Treeo’s performance issue on slow connection. This posts is also covers feature request of having separate folder structures for each developer (store folder structure in local file). SQL Treeo currently stores object’s folder name to its extended property named “VirtualFolder”. This helps to identify which object belongs to which folder.

Advantages of this apporach:

- folder integration is completely seamless as it’s part of database data (extended properties of e.g. stored procedure)
- if it’s shared database, other developers with SQL Treeo installed have access to same folder structure

Disadvantages:

- it’s slower on non-local connections. It’s because of fetching extended properties using fn_listextendedproperty one-by-one. For database with thousands of objects accessed via e.g. VPN, it’s slow. (fetch all extended properties in batch is possible but not usable within add-in).
- it’s limit for storing more attributes to folders (such as color, completely own structure etc.)

Based on that, I would suggest to completely get rid of storing folder name to extended properties and store them to local file instead.

Advantages:

- performance, no access to database is needed (probably only EXISTS for objects within folders)
- each user could have its own folder structure
- more flexibility for future enhancements (such as mixing more SQL object types in one folder.)

Disadvantages:

- local file will not be shared with other database developers by default. However this could be solved by storing this file to any source control or placing it to network shared folder.

one of SQL Treeo users developed clever workaround for SQL Treeo SSMS Add-in to be more compatible with RedGate SQL Search. Enhancement causes that RedGate SQL Search can now jump in folders you created using SQL Treeo Add-in. This allows you to organize your SQL objects in folders and search them using RedGate SQL Search.

Since last version I worked especially on supporting all version of SSMS, fixing major bugs and on adding few more features. From outside view it seemed I worked with snail speed because first version of my add-in was release 3 month ago. Truth is that I develop this add-in only in my spare time which is very rare and that’s main reason why I am so slow

SQL Server DMV Views might be very helpful. Let’s imagine that you have a couple of nested stored procedures and you find out that it’s slow. It would be nice if you could discover which single query/ies in your procedures causes that it is slow. Few DMV views can tell you that.

Ian W. Stirk wrote whole book about practical use DMV views which is definitely worth to read. I’ve received comment from Ian to one of my post recently which contained helpful piece of advice I would like to share here.

He post me query which is using DMV view to basically order part of your stored procedure’s source code from slowest to fastest. After some time of using it I found out that it had really great value for me because it discovered things you couldn’t realize (e.g. that scalar functions are terribly slow).

Core of the query are two views – sys.dm_exec_query_stat and sys.dm_exec_sql_text.

Sys.dm_exec_query_stat query contains performance statistics for each query executed on your SQL Server instance. No rocket science. But it contains even offset which can lead you to exact portion of query which is slow – this means that you can find out statistics for e.g. one SELECT query in your procedure. Sys.dm_exec_sql_text contains source code of executed SQL objects. If you combine those two views together you will find out statistics for each individual part of your stored procedure.

There’s one but – I found out these DMV views contain all statistics data from start of your SQL Server Instance. I didn’t find out how to display these statistics only for last query. I always restart SQL Server instance if I don’t want to get mix of slow queries which are not related to last batch I executed. It’s dumb but I didn’t make up better solution. If you know better solution, please share.

Microsoft SQL Server guys extended periods for Denali’s feedback on Microsoft Connect. And they motivates with really nice gifts for 30$. All you have to do is to report a bug or make a suggestion till September 10, 2011 and be in 300 of first.

Archive posts

Subscribe to SQLTreeo Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

SQLTreeo.com 2015. All Rights Reserved. Some trademarks and registered trademarks on this web are the property of their respective owners.
Netherlands, Leersumseveld 42 - 3452NC VLEUTEN, Tel: +31-85-4014693