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.

Paul White
  • 94,921
  • 30
  • 437
  • 687
Jacob
  • 21
  • 4

2 Answers2

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.

Alen
  • 539
  • 3
  • 9
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.