Organising Dbs and tables in SSMS

Question

This is a repost of a question I asked 4 or 5 days ago, with zero response. Hoping for more luck this time...

(Using SQL Server 2008)

Hi

Within the next few weeks I plan to introduce SQL server to an office that is in dire need of a proper data server. Currently there is a heavy reliance on loose Excel and Access file (supplemented with frighteningly large amount of impenetrable VB code to do data manipulations) strewn all over the internal network.

We need SQL server for two things:
1. For internal databases that will be designed upfront and will be capturing data on an ongoing basis
2. For ad hoc uploads of datasets received from clients, which we then analyse

I am the only person in this office who is familiar with SQL. I will have to train the other 5 or 6 people to use it.

Now, my question is this: how would you guys set up the DBs so that it would be easy using Management Studio to visually recognize where what is being stored? To be more precise: if this were a windows file system it would look something like this:

c:\client work\client 1\piece of work 1 (db with 10 tables)\
c:\client work\client 1\piece of work 2 (db with 8 tables)\
c:\client work\client 1\piece of work 3 (db with 7 tables)\

c:\internal\accounting system\some db with 8 tables\
c:\internal\accounting system\some db with 5 tables\
c:\internal\some other system\some db with 7 tables\

etc.

So briefly, I need to visually split by internal and client work. Client work I need to split by different clients. For each client I need to split out the different distinct sets of work. (Internal work follows a similar pattern).

Solutions that I am aware of:
- Run multiple data servers (e.g. one internal, one for client work). Not sure what the cons of this would be though
- Assign schemas to tables

I would love to hear your suggestions!

Thanks Karl

Answer 1

IMO, users of your databases should not have to know or care where or how your databases are set up. And they shouldn't be given access to SSMS unless they are well trained in SQL. This is a disaster waiting to happen. You should be creating applications and/or reports that allow the user access to the data they need. That way they don't care where the data sits, and don't need to know.

Answer 2

Your organizational tools for managing SQL Server are instances, databases and schemas:

A server can run multiple instances. An instance is basically a completely separate server instance on the same machine.

An instance can manage multiple databases. The database is the standard boundary of integrity - you (usually) back up an entire database, referential integrity is constrained to being between objects in the same database, etc.

Each database can contain multiple schemas, which allow you to organize code.

All these "containers" relate to security in some way.

I recommend that you take an organization data and process inventory first, so that you understand what data you are dealing with, who uses it and how - with special attention on data which is public or collaborative (data used by certain people together) and which needs to be compartmentalized access (only used by a particular role). SQL Server is not really a great place of choice to be storing unstructured data - I would not view it as a simple replacement of a file server, for instance.

From there, proceed to define roles for your users. Having roles is a lot better strategy than assigning rights to individual users. It documents the semantic meaning of the access (any person performing this role needs this access as opposed to the user's identity - john and kate need access - this tells you nothing about why they need access). Be certain that the roles are sufficiently fine-grained. A departmental role like AccountsReceivable isn't nearly as useful as PaymentApprover or InvoiceProcessor or AccountsSupervisor. Users can act in multiple roles - this will give you a lot more self-documenting ability in your infrastructure and a lot fewer security holes and headaches.

This should help to define which containers you will need and what access to grant and guide your data infrastructure from there.

As far as giving users direct access, I'm with Randy Minder, SQL Server is only an expert user tool at best. If they are familiar with Access, a good option is to let them use Access against carefully designed and chosen views in SQL Server until they are ready for a more systematic data engineering approach.