Questions on resources and removing instances

  • Hi there,

    I've been working with SQL Databases for over 5 years now. Prior to this job, I has no working knowledge of SQL in general, so everything I've learned, I did by trial and error. I work for an accounting software company and last summer, we've introduced a SaaS solution to allow our customers (who subscribed to this service) to use the software on a remote virtual machine using an RDP connexion.

    To concept is as follow, we have 2 virtual machine for the software (a mid version and a high version of the software) as well as dedicated virtual machines for SQL servers. We're up to our 3rd SQL server now since we've respected the 50 instances limit rule for stand-alone SQL server. Each instance belongs to a customer with his security group in it to prevent the other customers to see his databases.

    So this is our basic setup but we've ran into a few issues that I will try to address with the best of my experiences:

    1- Removing Dead Instances

    SaaS is not for everyone and a small percentage of our customers have realised that their Internet connexion is not stable enough to run this, so we've put them back to a local usage with the software. That being said, we're now stuck with SQL instances that are no longer active and that we can't reuses since they were named using the customer licence codes, so I've checked my options:

    a) Renaming the instances: I found multiple sites stating that trying to rename instances is not a great idea, can anyone here confirm this? If this is false, how would you go about renaming one?

    b) Removing the instances: Although this seems to work on servers with only a few instances, our first 2 SQL servers which are now full are not cooperating with the uninstallation. The uninstall process can be left running for over 2 hours and nothing happens, doesn't seem to be uninstalling anything (no error messages). Any ideas why it's doing that and is there another (safe) way to remove an SQL instance in Windows 2008 without affecting the working ones?

    c) Leaving them off: Disabling them and turning them off will free the resources but I am left with a question, this 50 instance rule, does it include inactive instances? If I got 45 active instances and 5 inactive ones, can I create 5 more without breaking anything?

    2- Managing Resources

    If there's one thing I've learned quickly about SQL is that it loves RAM, there never seems to be enough of it. I've read that it consumes everything that it can to put in a pool for future usage. Now that's all great, but when you have 50 instances running, they all seem to be competing for resources. Now I had the idea of perhaps limiting the amount of RAM that an instance can use based on the number of users in an instance (we have customers with more than 1 user) and the the total size of the database(s).

    Not too much info I could find online regarding this, so does anyone here care to share their opinion on that strategy?

    If it's a good idea, what's the magic formula I should use to calculate the amount of RAM I should limit the instances based on the number of users and total size of their database(s)?

    We currently have 16GB of RAM per SQL server, will this be enough for this strategy?

    If this is plainly a bad idea, what would you recommend that would allow enough resources for all of the instances?

    Thank you for your time

  • A few thoughts here.

    I have no idea on the uninstall and active v in active licenses. I would guess that it's installed licenses, so there is no concept of active/inactive in terms of the services.

    In terms of an overall architecture, your licensing should be the same whether you have 50 machines with one instance or 1 with 50 instances, correct? I think that's how SQL 2008 worked. In that case, why not use a separate virtual machine for each customer. When they bail, you kill the VM. When you need a new one, just set one up.

    However if you have customers that are set up this way and you are trying to maintain something, migrate all customers to use a FQDN (fully qualified name) to connect to their SQL Server. Instead of connecting to Accounting/Cust25, make them connect co Cust25.mycompany.com. Then you never care about what the instance names are. If you lose a customer, you drop the databases, create new ones, change security, and add a new DNS entry for a new customer.

    I dislike instances in that they cause competing resources, and the movement of the instance database if it's needed. These days I'd look at virtualization instead. In terms of RAM, you could then allocate RAM separately for each VM/instance.

    I have never tried to run a database with less than 1GB of RAM with SQL 2005+. The minimum number of 512MB, but with that you'd be limited to 32 instances with set memory.

    Does your software need sysadmin level rights? Or do customers execute jobs with their logins? If not, why not use one instance and set security properly?

  • Hi there,

    Thank you for your reply. Perhaps I should go more into more details about how and why we are using this procedure.

    We're using terminal licences because they are rather cost affective and that in the fee that we charge our customers, that cost is included. If we were to use individual virtual machines for each SQL instance, we would need to full Windows licences and that would cost more and we would need to charge the customers a lot more than $35 a month (per user).

    Also, we are using SQL 2008 R2 Express, not the commercial version. We are using the free version also for cost measures but also because our mid-version software is only compatible with SQL Express (per design) and since we don't want to separate the SQL servers based on our software version, it's best to use SQL Express since it's compatible for both our mid and high version.

    I did think about making generic names for the SQL instances, but it was voted as bad idea due to maintenance time, having to look at a list to see who Cust047 is would be a pain since we already have to look at a 6 digit customer number in our systems to see the customer's name.

    As for the user rights, each user has rights on his own SQL instance and although they can't delete a DB from our software (this requires Studio Management), they can create new databases and overwrite an existing one. This is why we needed to have separate instances because they need that freedom and putting everyone on the same instance would have required us to create their DB every time and put in their security group inside to give them the option to open it.

    Thank you for your time

  • That makes more sense. I'm not familiar with terminal services licensing, but can you have 2 or 3 terminal servers under the same licensing? Is it by CAL or is it a CPU thing?

    If you are stuck with 50 instances per server, I still think going to generic instance names is best, with aliases. If I have instances named Cust01 and Cust02, I can connect to them like this (assume my Windows machine is TS01).

    TS01\Cust01

    TS02\Cust02

    However I can also set up an alias to TS01\Cust01 that might be Cust465, and both your staff and the customer can do this. A few references that might help:

    http://36chambers.wordpress.com/2010/04/08/how-to-set-up-aliases-for-named-instances-in-sql-server/

    http://www.sqlservercentral.com/Forums/Topic748728-146-1.aspx

    I think you are going to be limited to 50 instances no matter what. The rename doesn't work and the uninstall can be flaky, especially if you are looking to reinstall another instance again.

  • Hi, thanks again for your reply

    Yes, we are using CAL licencing, it's actually pretty cheap compared to full Windows licences. As for aliases, that would have been a good idea but since we've already started our 3rd SQL server, it's too late to change that strategy without being confusing.

    Thank you for your time

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply