Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
the_importer
Starting Member
1 Post |
 Posted - 2013-06-17 : 09:31:05
|
| 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.We're using terminal licences because they are rather cost effective 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.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.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 InstancesSaaS 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 ResourcesIf 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 |
|
|
|
|
|
|
|
Questions on resources and removing instances