2

We have a Windows 2012 VM running SQL Server 2012. There are around 20 databases with a total size of 5.83 TBs. There is 64GB of RAM and 16 logical processors on the box.

The server is extremely busy with a lot of users hitting the databases with queries during 7 - 6 window and a lot of jobs hitting it in the evening after 6. Most of the jobs run integration server packages that are stored in MSDB. On top of all this there is snapshot replication on one dB and backup/restore on 2 other databases every night.

Now, there is a need to load balance on the server, we have been talking on a couple of points which are below:

  1. Separate into two servers, A & B, A only runs jobs and B is for users, every night back databases from A and put them on B.
  2. Always On Availability Groups with readable secondary.
  3. Separate Integration Services and database engine into 2 servers. We have earlier separated SSRS and SSAS from database engine.
  4. Have two servers with heavy load dBs on one server and other dBs on other.

Which approach would you guys go with?

Approach 1 & 2 -- are kind of automatically out of the picture as users need read/write access. They not only read data from the databases but also write the data in their own schema.

Approach 3 -- separate SSIS & database engine -- I am against this as I have noticed that IS does not take more that 2GB of memory at its heaviest load and not to forget that even though we separate IS & engine the IS will still be hitting the databases and will be locking tables which users are trying to read. I agree that putting it on different server will allow IS to have its own processors and memory but whatever little performance we see will be offset by transferring data over the network.

Approach 4 -- is seeming most feasible with taking 1/4th of busy databases on other server and keep 3/4th of less busy one on same server.

I have allotted 50GB to the database engine and left 14GB for OS and other processes.

  • Re. Option 4 - why would you put both of the busy DBs on the same iron? Why not one on each, with the small stuff spread around to balance the loads? – Michael Green Jul 24 '18 at 11:41
  • @MichaelGreen The busier ones are interdependent. We floated the idea of separating the busier ones on different boxes...it is still in works.. – Jacob Jul 24 '18 at 14:06
0

Community wiki answer:

Have you considered just migrating the VM to a bigger host and giving it more cores and RAM?

How to move a VM to a new host and assign additional resources is dependent on your hypervisor, and is something whoever manages your VMs can do. For Hyper-V see: Windows 10 and Windows Server 2016 PowerShell - Move-VM

Choosing one of the four options mentioned in the question, go for approach 4 - keep it simple.

0

If you do AlwaysOn with readable secondaries you don't have to backup and restore every night, SQL Server will send all transactions to the secondary as they are executed.

You will also need to make sure the database drivers your application uses support read intent. I'm saying this because I've worked in an organization that used Java and the particular Java drivers we used did not support this feature. There is also some development overhead since your developers will have to create a read-only connection separate from the connection that writes and changes data. I've never used this in production, but I've done some testing about a year ago with MS Access and this is how I had to set it up.

You can always use SQL replication. I've worked with transactional and snapshot replication and in some cases prefer it to readable AlwaysOn secondaries. The reason being is that I can customize indexes on a replicated databases separate from the writable copy of that database. And this is a good solution if you have users running queries directly against SQL server.

I've set up SQL servers that do nothing but run job, but there is still overhead on the servers where you're reading from and changing data on.

  • I appreciate the response Allen. However, in our case we need users to have not only Read but Write access as well. I understand Always ON and read only intent but the reason why I am saying this will not work for us is because users need read/write access and if we use Always ON read will go to secondary but write will happen in primary. In some of the queries they write they are reading from some tables and writing in their schema. This will compromise the performance of their queries and cause a lot of distress. – Jacob Jul 23 '18 at 20:02
  • With Replication, I can move the objects to new databases on a separate server but they will need to write as well – Jacob Jul 23 '18 at 20:03
  • With transactional replication you can have updatable subscriptions. I've never used them but I understand you can change data on subscribers. Otherwise your users would need two connections. One of the repilcated/readable secondary and one for the primary server. For an application this will have to be coded into the application. For applications like Excel or Access you will have to create two connections if you were to use this. – Alen Jul 23 '18 at 20:07
  • Thanks for the suggestion, appreciate your time. However, replicating 30 databases every night might prove to be a nightmare. If you had two options, one to take SSIS to other server and the other one to split those 35 dBs to two servers which one would you advice? – Jacob Jul 23 '18 at 20:16
  • You don't have to replicate entire databases. AlwaysOn and transactional replication will replicate at transaction and command levels. Every AlwaysOn cluster I've managed has always been up to the minute most of the time and transactional replication usually no more than 15 minutes behind or a few hours in some instances. I've seen someone set up backup and restore of some databases on a nightly basis but he didn't speak English very well and did weird things sometimes. – Alen Jul 23 '18 at 20:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.