40,000 Database Nightmare
-
My Company is evaluating an upgrade to an existing product that has been converted to SQL server. I have no control over how the application was written. They have developed a application that creates a new database for every single section of work being processed. This means that we will be starting off with over 10,000 databases and within 1-1/2 to 2 years we could be over 40,000 databases. Most databases will be small (4-5Mb in size), and there is not suppose to be a lot of activity going on in each database. I would expect that no more than 150 will be accessed at any given moment.
We were hoping to put this on our existing SQL Server (2000 Standard edition, Dual 1.13Ghz Processors, 2Gb of RAM). This would be on a separate instance of SQL Server. Also at the moment at least we don't have any rights to this instance. (after setting up the instance per their instructions I ran their install program and it removed all rights. All I can do is start and stop the instance) Also the Client portion of this program installs MSDE on the workstations. They have removed all rights for this as well.
I was hoping to ask a few questions about what they are doing.
1. Has any one had any experience with a system with this many databases?
What were your experiences?
(I know about the 32,767 databases per instance limit)
2. We were hoping to install another Gigabyte of RAM and use the /3GB switch. Are there any problems doing this with SQL 2000 Standard edition?
3. They are using a Trace Flag during startup (-T3608) to only load the Master database when the instance starts. They say this will lower the memory requirements. Each database is supposed to still be attached but not loaded into memory until it is used the first time after startup. I can find very little information on this Trace Flag except "Recovers master database only. Skips automatic recovery (at startup) for all databases except the master database."
Does anyone really know how this Trace Flag operates?
If it does not do the recovery process for any database except for the master couldn't this cause problems if something happened to the system? ( say lost power, etc.)
4. How much memory is used per database to just load a database into memory? (I can not find any documentation on this) Assuming the Trace Flag works as they say I could still expect to have 2000 databases loaded into memory at one time and wonder if I will experience memory problems.
5. Without rights to the SQL Instance or the MSDE Instances on the Clients what problems might I face? Some of the possibilities we thought of were: Unable to patch or update the instance, Unable to run diagnostics or utilities, Unable to do backups or restores (Though we don't want to do this manually on thousands of databases any way) And then the same issues on the MSDE workstations.
6. They say they are going to provide us with some sort of a backup & restore utility they are working on. If this does not happen any ideas on what could be done?
7. Any other problems or issues you can think of that I may not have?
This entire situation does not seem very hopeful. I look forward to any feedback you can give.
Thanks
Tom
-
I suggest running away as fast as possible...
1) No, we have thankfully not had any experience with more than a few hundred databases on a server. You will need to reserve a lot of memory address space outside SQL Server's buffer pool (-g option).
2) You cannot use the /3GB switch without upgrading the version of SQL Server. You will also need to upgrade the server's OS unless you're on Windows Server 2003 or Windows 2000 Advanced Server.
3) The vendor does not know what they are talking about. That trace flag is not designed nor supported for such use.
4) The biggest memory use per database is for tran log buffers. At least 64K (and up to 192K) of memory outside of SQL Server's buffer pool will be needed for each database that is updated. At least 5K per open database is used from within the buffer pool.
5) You will have all these problems and more, considering that the vendor has shown an amazing ignorance of SQL Server (and database concepts).
6) With great difficulty; as you have already surmised, each database must have its own backup. This vendor certainly does not have the knowledge to write any utility that will properly back up and restore SQL Server databases en masse.
7) Are you aware that you will need to purchase a server license for each instance if you don't upgrade to SQL Server 2000 Enterprise Edition? There's something very wrong about requiring each client to be running MSDE when there's a SQL Server in the mix.
From http://support.microsoft.com/?id=316749:
"Microsoft recommends that you take serious consideration before you run a server with more databases than [1500] because the overhead that is required for having this number of databases on the system is taking a lot of virtual memory away from the buffer pool, which may result in poor performance for the system as a whole."
Run, Tom, run.
--Jonathan -
Jonathan,
You are very right. Poor Tom. Sorry I can't help you.
mom
-
Im sure you've looked, but maybe still worth one more look at finding a way to trick the app into better behavior.
-
Just out of curiosity. This application was approved by someone other than the IT department correct??
If no as the others suggested RUN. Even if NO RUN.
From day 1 the application will cause you headaches. The application has stripped ALL of your rights so you cannot see under the covers and recommend changes or even modify ANYTHING.
You are going to be the victim of calling ABC tech support being on-hold for n amount of time, asked to provide information you don't have rights to get, etc....
Also, Has anyone done any research on the application for your business to see how long it takes to reach the MAX amount of databases.
It sounds like you will need to upgrade to stronger
A. Server to run the application
B. PC's for all the agents because I am sure the app. won't be running by itself
C. A network to handle all the increased traffic and overhead due to the poorly written monstrosity
And lastly, when the s#&$&% hits the fan everyone will blame you because you will the on-site physical embodiment of everything that has gone horribly wrong (I have had that pleasure)
Other than that it sounds like you should have no problems with polishing ye olde resume
Sorry man,
Good Hunting!AJ Ahrens
webmaster@kritter.net -
May the force be with you Tom! Remember dealing with vendors leads to anger, anger leads to hatred, hatred leads to suffering.
"I didn't do anything it just got complicated" - M Edwards -
OK. Besides running is there an option such as trying to find similar product that does the job without giving much headache. I am sure you could findout about that vendor's competitor and see if they have a better product.
mom
-
To clarify a couple of issues. The application is a totally re-written and updated version of an existing application that we have been using for a couple of years. It integrates with a couple of other applications written by the same company. All of these are major applications for our company. The backend on the current version does not use SQL technology at all. They said they wrote the application this way so that they could use MSDE on the server as well as the client to get around the 2Gb database limitation of MSDE. This way they could sell it to small firms as well as larger firms like mine. Because they say a small firm would not want to pay for SQL server.
The MSDE on the workstations are two purposes. 1. They "Check Out" the data, work on it then check it back in. 2. So they can work on it at a clients office, then come back to the office and synchronize with the main database on the server.
I have brought up most of these issues but they say everything is great. They are telling my boss they have had over 60,000 databases running on MSDE without any problems. (not clear on if multiple instances on one server or multiple servers)
I just credibility problems with the thoroughness of their testing etc based on past experiences with the current version. So I have got to find facts to support my concerns.
A couple of things I have found out:
Regarding Multiple instances: Under the Per Processor licensing model, you may install and run any number of copies (or "instances") of the SQL Server software on a server, provided that the required number of Processor licenses has been acquired.
http://www.microsoft.com/sql/howtobuy/multipleinstances.asp
We have already done the estimates on how long it will take to get to 40,000 databases. As a point of reference another firm in the same industry who is also looking at going to this product expects to be at 240,000 databases by this summer. They are somewhat larger than us and have been using the existing product longer and throughout a greater percentage of their firm. We were going to roll to the rest of our firm this summer.
We had evaluated several products when we went to the current version. This was the best choice plus it integrates with our other applications.
Tom
-
Whoa!!! I recently worked for a company developing a product that we had to completely re-design because it was rejected by a major prospective client for using FOUR databases (which was, I have to say, a moronic move on the client's part, but they paid for the six-month redesign so I couldn't complain too much)... We redesigned it to use a single database instead, which is what the client demanded.
How did a product that already uses TEN THOUSAND databases slip through? I would love to see this particular client evaluate that product
Anyway, I agree with the other respondants... RUN, don't walk!--
Adam Machanic
whoisactive -
If the vendor has come up with this as their technical solution I'd seriously worry about their technical aptitude!! Imagine what your backups and maintenance routines are going to be
I'd send the trial software back to them with a note attached telling them to resend it on April Fools day. Don't they know that you don't need a new database if you want to create a new table?
-
Hi Tomtom, have you gone straight to the source and asked Microsoft what their opinion is of running this many Db's ?, i.e is it technically feasible or even advisable etc etc. It may save lots of money and frustration/stress if your company gets a prefessional opinion from the people who wrote/supply & support SqlServer before your project progresses too far.
-
Hi Tom,
Sorry, I do not have any expertise in SQL server to offer any suggestion.
But I am just curious about the vendor selling this product.
Are you at liberty to mention the vendor name and the product name?
If yes, could you please mention the names here?
I would appreciate it.
Thanks
r_achar
-
WOW
The things vendors do to get money !
1 - I would not allow this unless the vendor agrees to tweak the client to use views, stored procedures to write to one database or a few DBs.
2 - What if you create a linkes server on each client MSDE to the server DB. Then create a view at the MSDE pointing to the main table on the server with a column that has a unique value representing the MSDE/section of work added to the PK, so queries from different clients look for different rows only pertaining to the specific client. This still needs the vendor succumming to reality ! and doing the necessary work ..
3 - Get management to understand the feat you are trying to achieve and get MS opinion on it ...
-
At this time I really shouldn't mention who the manufacturer of this software is. Lets just say that they are one of the larger developers of specialize software for our industry. I am not sure but I would not be suprised if this was their first venture into using SQL server.
The deployment of this software has at least been postponed for the time being. And management has sent a letter to the manufacturer outlining some of our concerns. I just found out that within the past week or so they released this version and sent out the CD's to many firms. Most of these firms are MUCH smaller than we are and would not have technical people on staff. I know that their helpdesk lines have gone crazy but that is kind normal for this time of the year. I am looking forward to seeing what happens with our letter and with the general release to other firms.
Thanks
Tom
-
1. Has any one had any experience with a system with this many databases:
I currently manage approx 6500 databases. They range from a couple hundred MBs to 50 GB in size, so it's a little diff than your situation
2. We were hoping to install another Gigabyte of RAM and use the /3GB switch. Are there any problems doing this with SQL 2000 Standard
edition.
Check this page out for max capacities
http://msdn.microsoft.com/en-us/library/aa933149(v=sql.80).aspx
3. -T3608:
"Trace flag 3608 skips automatic recovery for all databases except the Master database at the startup and with 4022 all the automatically started procedures will be bypassed during startup."
This will reduce startup time, and memory consumption. It's not a great ideas. I wouldn't use this flag for anything other than an emergency
If it does not do the recovery process for any database except for the master couldn't this cause problems if something happened to the system?
You wont know if you have corrupted databases until a process actually tries to access them. This is a bad idea in my opinion
4. How much memory is used per database to just load a database into memory? This is a coplicated subject
Read this
http://msdn.microsoft.com/en-us/library/aa902665(v=sql.80).aspx
32,767 is a theoretical number. If you are runnng x32 you will only be able to create a couple thousand DB's before you run out of Virtual Address Space (VAS)
Read this
http://support.microsoft.com/kb/316749
"For each updated database, SQL Server allocates at least one 64-KB block for use in formatting log records before they are written to disk. This allocation occurs when the first log record is generated for the database, such as during an INSERT, UPDATE, or DELETE statement. Depending on the activity and the size of the generated log records, subsequent modifications might trigger the allocation of additional 64-KB allocations. SQL Server 7.0 will allocate no more than three 54-KB blocks. In SQL Server 2000, the upper number of allocations for each database is a function of the number of processors that SQL Server is configured to use"
6. They say they are going to provide us with some sort of a backup & restore utility they are working on. If this does not happen any ideas on what could be done?
The easiest thing to do is to use native SQL Backup. A Maintenance Plan can be used to backup all databases in the instances. It will process each database in the instance one at a time. There are many, many other options include 3rd party software and custom scripting. The solution will depend on your requirements. Consider what items are of greatest importance: space, time, system impact, etc
7. Any other problems or issues you can think of that I may not have?
Lots, but to name a few:
Your hardware will not do. What OS? Disk IO is liley to be an issue. You need to consider both IO Per Second (IOPS), and bandwidth (Mb/Sec). You didnt mention disks, so Im going to assume you have local storage, and probably only a couple drives. Again depending on your requirements, this may be OK. It will be slow, but maybe that's not a problem for your specific use-case.
Hope this helps
-Steve
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply