situation: I'm working on a financial application that's meant to be used in a single-user way. Right now it uses MS Access but it can easily use SQL Server when I just switch the connection string. Since some users are sharing the same Access database with multiple users, I've already added a crude mechanism to avoid locking errors. Basically, all data is stored in documents, with a document table linked to dozens of others. When a user opens a document, data is read from all related tables into an XML document and the document record is marked as "Opened by...". When it's saved, all tables are updated and the document is put back to the "Closed" state. This works quite well, even for a customer who has up to 15 users working simultaneous, on Access. So it should work well with SQL Server too.
Customer request: But recently, a large organisation asked me if they can have up to 500 users sharing a single database. My first thought was "WOW!"... Then I asked for additional requirements. And basically, they have about 200 offices with 2 or 3 persons in every office, who all use their own MS Access database. But system administrators would prefer to see all data in a single database.
The problem: The main problem is that employees of one office are not allowed to see data from the other offices. So if all data is stored inside a single database, I would need to add an additional system to my code to manage users and offices and then connect documents to offices or whatever. It would be a lot of work which could introduce additional bugs to my code. Worse, I have thousands of users and most just use it on a small scale. Only this user wants to use it on a big scale. They are willing to pay for the adjustments but it would have impact for the other users too.
Solution I consider: Still, after some research I decided that I could solve this problem without modifying the code of my application. I can create multiple schema's in the database. Every schema would be linked to a single office and have a single database user/login which will look at this schema by default. Since the login information can be specified in the connection string, the application would immediately look at the right schema after login. A test with SQL Server 2005 and four schema's proved to me that my code would work just fine. All it would need is an experienced DBA to set it all up. Or a special management tool which would automate the tasks of managing offices within the database.
Basically, I would end up with about 200 database users, logins and schema's and 200 different connection strings. All schema's would contain the same table structures and not much else, since I only use it as a data store. (MS Access was also used for just the table functionality and nothing else.) There would be no change to the code and their own DBA could easily manage it themselves. They could even implement this whole solution by themselves if they want to! The current version of my application already supports SQL Server. (Btw, the user accounts that I create will have very limited access: select, insert, update and delete on all tables within their own schema, no rights outside the schema.)
I am very experienced with SQL in general. (started in 1988 with SQL, when the language was still limited to the "Select" command.) I have good experiences with SQL Server 2005 and I'm not afraid to learn new things. I'm not a DBA, though. And I'm also inexperienced with such a multi-schema solution. (I never needed more than one.) So I need some help with this.
My question Considering the problem and the solution I'm suggesting, would you choose for the same solution? Not just as a quick-fix but also for a long-term solution? Are there any useful sites with do's and don't in relation with schema's? I've also suggested this as solution to the big customer but the people whom I talked to did consider it practical but also lacked the experience to tell me if this is a good idea or not.
If there's a reason why I should not do this, I would like to know this right now.
Note: the application is developed as a Delphi 2007/WIN32 application and uses raw ADO commands to connect to the database.
The main reason why this customer wants everything in a single database is because of several maintenance tasks. A single database is easier to backup. It's also easier to upgrade when needed, since everything is centralized in a single database. Right now, they're working with 200 MS Access databases, which is an even worse nightmare. Basically, it's an improvement if things go from real worse to slightly bad. :-) There will be future improvements, where I will modify the database to support users and offices in a better way but this process takes a lot of time. This solution is quick to implement and technically, all it needs is some way to manage these schema's. But I can't estimate if this solution will be replaced within the next to years or if it's going to last for a long time.
And yes, I realize that maintenance is a nightmare. Maintenance of 200+ MS Access databases is worse, though. It's a quick solution to bring some stress relieve to an unhappy customer. It's also irritating to create the amount of objects every new office would need, but I already have a solution that can create a complete SQL Script to create a new office, including login, tables and user. Creating this tool wasn't difficult, because the database originated as an MS Access database and isn't very complex. It has primary keys but I never added any relations between the tables since relations are maintained within the XML document that's used internally by the application. An additional management tool can be developed to maintain all those offices and I've done some testing and am confident that I can automate most -if not all- the tasks for maintaining these offices.
And the customer did indicate that they would like to do some data mining but the application is importing/exporting data to another application for exactly that purpose. So data mining is a nice-to-have function.
There is another drawback when I move all data in a single database, instead of keeping them separate. The documents in the MS Access database are unique per database, but users can import/export data to other databases. As a result, some data is likely being maintained by more than one office. (And yes, they are aware of this! It's done on purpose.) I would need to add additional intelligence to my code just to keep the documents unique per office, if I don't use schema's.
Still, this could just end up as a temporary solution. It would solve the problem right now instead of over 6 months, when I'm done implementing the new functionality plus anything else on the wish-list.
Update I've been testing a bit more and created an SQL Script with a single variable for the office name. I then created a batch file which invokes SQLCMD.exe to execute this SQL script for 300 different offices, with 300 different names. The database used for this test is SQL Server Express (2005) and it seems to run just fine. So, while maintenance could be a bit of a nightmare, I think it can be managed with a good set of SQL Script for all maintenance actions.
