2

I am looking into the possibilities of how to scale out the sql server 2008R2. simply scaling up wont be enough.

"p2p replication" and "distributed partitioned views" look interesting, but both require (multiple?!) enterprise licences which are very expensive, and still both arent perfect solutions.

One very simple Idea was:

Read from Random Server - Write to all Servers

Nicely wrapped in a transaction so that it could be rolled back if it doesnt succed on all servers .I am having fear of some serious locking issues if something goes wrong on one server or it is waiting because of some other lock on one server.

A very similar solution would be:

Read from Random Server - Write to Server A (which replicates automaticly to all others)

This would be more expensive because Standard licence is needed and the first one could be done with express, but would it be better in matters of performance? How fast are those automated replicates? Does all this make sense at all or is there a better nicer way?

Flo
  • 21
  • 1
  • How much data do you have? What are the requirements? You seem to be looking after "big enterprise" scale options but are looking to implement it with SQL Server Express ideally, Standard if you must. Do I understand correctly? – Mike Walsh Oct 24 '11 at 13:22
  • isnt to much data over all, some mio rows tops. Problem is that the servers run at the companies that run our software, and most of them just have express right now. And if they have performance problems telling them to buy a second server is one thing, telling them to buy a new server and sql server licences for 5figures is something else. – Flo Oct 24 '11 at 13:30
  • What does your application do? Is it architected to have multiple database back ends? I am not asking to be a pain but it sounds like you install your application on a SQL Express back end and in some cases your customer load is larger than the hardware you put them on or beyond what you should expect out of SQL Express. You may find it easier and less expensive a development effort to tune your code and update your hardware recommendations and consider going with SQL Standard or Enterprise for certain size installations? – Mike Walsh Oct 24 '11 at 14:14
  • You also said "Scaling up won't be enough" - Can you just clarify that one. What kind of waits and challenges are you facing? I am really not trying to be difficult but I just want to understand your application better to help. I've helped a lot of my own clients with vendor apps that are built on SQL Express and don't perform. In each of the cases the issue was the resources allocated and/or the database code and design. I've had to scale out large SQL environments behind web sites, but never SQL Express based vendor apps. Always went with "scale up" or tuning/optimizing. – Mike Walsh Oct 24 '11 at 14:30
  • basically there is a programm called "server" running which along with the configuration tool is the only thing that can acccess the database. Reading your comment i just noticed the limitations for express(1gb, 1cpu). Maybe the servers are just idle, i will look into this, thanks for pointing that out. – Flo Oct 24 '11 at 14:33

3 Answers3

1

Set up merge replication from your centralized server to each of these Express instances; Express can't serve as publisher but it can be a satellite, and this includes merge updates.

adaptr
  • 16,576
  • 23
  • 34
1

Based on the answers that the OP provided in the comments section above, this doesn't really sound like a scale out solution. I think your time would be best spent looking at the following:

1.) Database design and query performance --> Analyze the workload that is running in SQL on this instance and ensure it is performing as expected. Look for queries that take longer than you'd expect, look at the underlying design of the tables and stored procedures/SQL code involved.

2.) Have a look at what resources you are driving on the server. Are you maxing out any limitations of SQL Express? If so, then I would consider running some test scenarios and analyze the workload to see if moving up to a higher specification server and perhaps moving up to standard or enterprise edition and the increased resource allocation would help. Are you deploying on 64Bit based boxes? How much memory? Do bottleneck analysis to identify which bottlenecks are slowing you down.

3.) Make sure Database maintenance is being done (index rebuilds, statistics updates and proper backup maintenance - I often find vendor provided SQL Server Express edition installations without ever being backed up, without ever having maintenance done - http://expressmaint.codeplex.com/ for a script to help automate and schedule some of these tasks)

4.) Are you hosting your application and SQL Server database engine on the same server? It may be time to consider splitting these out. Applications and SQL Server often have competing priorities for system resources and don't often complement each other well on the same box. Test and see. Look at perfmon to understand the impact.

If after doing all of these changes, you still experience performance issues you might consider looking at the scale out approach but I think these steps are more basic and less costly for you and your customers in the long run.

Mike Walsh
  • 437
  • 2
  • 8
0

Sounds to me like you need to do some SQL performance tuning. If the database is small enough to run in SQL Express then odds are you don't need to configure some big scale out setup. You probably just need to start with index tuning to fix your poorly performing queries and setup some index maintenance jobs.

mrdenny
  • 27,174
  • 4
  • 41
  • 69