1

I have a WSFC with SQL Server 2016 Enterprise Edition and 4 Availability Groups. Each server has 1 CPU with 6 cores @ 3.4GHz and 256GB of RAM.

Currently I have one instance on each, they serve 4 Availability Groups with a total of ~300 GB data in 95 databases.

I have the task of migrating more databases (35 databases, 235 GB data) into this environment, which I'm sure would be a breeze - had it not been for the fact that these databases come from a cluster with another collation. Hence, I'm thinking of adding an instance on each server and creating a separate Availability Group for this collation.

To help keep things sorted and sort of simple I will add an IP address on each server for the new instance. That way I can address both instances on port 1433 and won't need to sort that out in the availability Groups Creation - at least that's my thinking. (I've used this approach on a single server with 3 instances - they all have their own Ip addresses which removes the need to address them by instance name - keeps it easy in case the db needs to migrate to another server. )

I found a similar question from a few years back but since there was no evident reason for the multiple instances the TS received a lot of whys, donts and whats. I my case I really need to have an extra instance due to the collation issue, my question to the Community is: is there anyone who could tell me "Yes" or "No"? And if either answer - could you please give me a good motivation?

If I can't add the instances I would have to go with another server, and due to the license costs of Enterprise Edition this would then be a non-clustered solution - reducing uptime, complicating patch routines, removing redundancy and so on. So: could I do this?

2

Each server has 1 CPU with 6 cores @ 3.4GHz and 256GB of RAM.

Currently I have one instance on each, they serve 4 Availability Groups with a total of ~300 GB data in 95 databases.

I have the task of migrating more databases (35 databases, 235 GB data) into this environment [...]

Considering there is a single socket with 6 cores and will hold 130 databases, that's already a good portion of available threads just for Always On usage and not counting anything else.

had it not been for the fact that these databases come from a cluster with another collation. Hence, I'm thinking of adding an instance on each server and creating a separate Availability Group for this collation.

Is it a requirement that the instance level collation be the same as the database collation? This is unclear.

Adding another instance is only going to make troubleshooting the inevitable issues you're going to have, that much harder as now you'll need to monitor both instances to see how one is impacting the other while the other is having issues.

I, personally, would not do this.

I found a similar question from a few years back but since there was no evident reason for the multiple instances the TS received a lot of whys, donts and whats. I my case I really need to have an extra instance due to the collation issue, my question to the Community is: is there anyone who could tell me "Yes" or "No"? And if either answer - could you please give me a good motivation?

You're throwing aside whatever has already been discussed and want an absolutist answer to an environment no one but yourself has access which is quite a hard question.

Instead of adding another instance, I'd add another set of nodes to the cluster or create a new cluster so that items were separate. It's still not clear if the requirement is instance level collation to match database level collation, so we're not even sure another instance is required.

If I can't add the instances I would have to go with another server, and due to the license costs of Enterprise Edition this would then be a non-clustered solution - reducing uptime, complicating patch routines, removing redundancy and so on.

Licensing aside, a stand alone instance would be simplified patching - using an AG complicates patching.

I'm afraid it's more or less down to two things:

  1. Multiple Instance (if even required) are annoying beyond belief in terms of maintenance and troubleshooting. If you want to do this, then you need to be okay with knowing the investments of time and energy to troubleshoot small issues as BOTH instances would need to be investigated, mostly regardless of which was having the issue since one can easily affect the other.
  2. The limited hardware, just 6 cores. Even if you added the databases to the current instance, the hardware just doesn't seem adequate. Since we aren't in your environment we don't know the current level of load on both servers, their current specs, and what this would do when migrated so it's really not possible for anyone to say. Having said that, it still doesn't make me feel good, considering there just isn't that much hardware available.
| improve this answer | |
  • Thanks for your input @Sean. Do I need another server collation for these databases? I'm not sure - but it's databases related to Lync and FIM. To get full support from microsoft, I want to follow their recommendations. Your Points are valid and I have decided to go with a separate virtual server for these databases. – Hans Lindahl May 9 '19 at 12:29
1

After reading the input from Sean, I decided not to risk bogging down a well working cluster with possibly dire consequences. Since I wasn't sure I could overlook the collation difference, I installed a separate SQL Server on a virtual machine.

| improve this answer | |

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.