SQL Server 2008: N Small VS 1 Database Databases with N Schemas
I have a database server with several main databases and several dozen smaller ones.
These small databases are staging / staging databases for importing data from different sources into the main database. Data import is a daily task. They are all very similar in structure as the implementation of this data is similar, so basically they have configuration tables that define mapping, transformations, etc. And data tables that contain the import results.
A while ago there were only a few small ones, but now I have over 20 of them will grow further with the number of data feeds supported.
I just migrated my entire server environment to SQL Server 2008, and having had some cleanup / refactoring time, I'm going to merge all the data import databases into one database and use them database schema
to separate them.
Question-0: Any other ideas for the described situation?
Question-1: Can I change from separate database
to separate schema
?
Question-2: !!!: Any tricky thing to be careful about in implementation database schema
?
Edit-1: highlighted question-2 as the most "unanswered" question currently.
source to share
In your case, I would probably include the database concatenation in one. I really don't see any reason why they were split and merging them will reduce the amount of work you have to do to maintain backups etc. If you've imported data from a data source once, and then never used staging tables, I could see the reason for creating separate databases to handle the data transformation. Since you are using these tables on a permanent basis, I would rather store them together, so I only need to go to one place to find the complete final state of production data and data load states.
2008 does a really good job of splitting the database, if the db gets too large or you need to split the data for security reasons, you get the benefit of having one db with benefits like several smaller ones. You won't get this with a few smaller dbs.
source to share
Since the database is a unit of referential integrity and backup, if you add large amounts of staging data that does not need to be backed up on the same schedule, it would be easier to store it in a separate database.
You can use a single database with multiple filegroups and different backups, but this requires a lot more design.
The main factors that will affect this are the recovery model, backup goals, usage patterns, and the amount of effort involved in developing and maintaining the design of the group of files.
source to share
All of the previous answers work for me, especially your comment on selectively merging databases - if some of them are very busy, very large, or handling sensitive data, you might want to keep them separate or in separate groups. This will make it easier to set up backup / restore and disk / disk allocation (keep busy with your own set of spindles).
Like most database developers, I have focused almost exclusively on objects in the dbo schema, but I have done some recent work with other schemas. The main problem I ran into was remembering to always specify the schema when accessing any database object. Never assume that any given connection will refer to an object in the schema you want it to - always be clear and precise!
source to share
I would put all of your import storage tables in one database separate from your normal production database, as backup needs may be different. This database should also contain items such as configuration management for SSIS packages, any log tables, any import metadata tables (we track every import run and the status of that run, as well as other import options such as filename, normal file size, and etc. Useful for investigating problems and for adding checks for processing We use a schema that is located by the client, and then an additional schema for objects implemented in the import / export process (logs, metadata, etc.)
source to share