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 |
|
lazerath
Constraint Violating Yak Guru
343 Posts |
 Posted - 2005-06-20 : 16:30:28
|
| We have an OLTP database that is composed of a handful of logical systems with inter-dependancies where a lot of data is batch loaded during non-peak hours with a much smaller number of real-time transactions taking place during business hours. Due to our understaffed environment and changing business needs, the database has undergone heavy growth and is in need some some refactoring. I am looking at splitting the main database and I am looking for some suggestions/advice/experiences from the community in order to insure I'm following the right course.Basically, the server is composed of the following non-system databases:1.) ApplicationAccessContains application/presentation level objects.2.) DBAInfoPlace to store trace files & DBA objects.3.) ODSPrime (50 GB)The main course - the business critical data and processing objects.4.) ODSStageA copy of ODSPrime objects with non-production data used for testing.5.) ReportAccessEncapsulates the reporting objects.6.) UserAccessFree range user area.7.) Several misc loading DBs (25 GB)8.) A couple misc application specific DBs9.) Report Server DBsThere is only so much I can do at this time to improve the situation but, luckily, I'm starting on a project that will allow me to make some of the changes I have envisioned. Specifically, I'm looking at creating two additional databases:10.) ETLStaging database to encapsulate all extract, transform and load related objects. This DB would contain only temporal data except for data specific to the ETL process.11.) ArchiveOnce data has been processed through ETL, it may be necessary to keep it accessible online for a finite period of time before getting purged. This DB would contain those objects related to that process.Medium to long-term, I plan to transition objects into the new databases, but for the immediate future I simply plan on creating the framework and accomplishing the tasks required for my project.My questions are:1.) Does the approach I'm taking with the new ETL and Archive databases make sense and do you have any critism/advice/suggestions?2.) In the case of the ApplicationAccess and ReportAccess databases, they most commonly access data in the ODS. Does it make sense to have separate databases for them? I've found that it's nice to encapsulate the logic and datastructures, but if you know of any compelling reasons against this I'd be willing to listen.3.) Are there any articles, books, or links that have some good guidelines/tips/best practices for the ETL database? I was simply going to write my own, but if something exists that would is more industry accepted I am willing to consider it.4.) Does it makes sense to divide the ODS into individual sub-systems or groups of sub-systems, despite some of the relations them? 5.) I have enabled cross-database ownership chaining for some of the databases, but as far as I can tell, the major exploits rely on DBO rights and its not really a problem as long as users are restricted to minimal permissions. Comments?As a note, we have development, staging and production versions of every server we have, so I'll be looking at removing ODSStage eventually because its not really necessary to have 6 copies of the same database. |
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-06-20 : 16:53:37
|
| "1.) Does the approach I'm taking with the new ETL and Archive databases make sense and do you have any critism/advice/suggestions?"I would not have a DB for testing and development on my production machine.I usually write my trace file to another DB server wether they are db tables or flatfiles as to not consume space and resources on my production machines."Free range user area" just sounds scary."2.) In the case of the ApplicationAccess and ReportAccess databases, they most commonly access data in the ODS. Does it make sense to have separate databases for them? I've found that it's nice to encapsulate the logic and datastructures, but if you know of any compelling reasons against this I'd be willing to listen."It is a good idea to have a seperate highly indexed and even denormalized copy of your data for reporting purposes.It is 5. Gotta go.Sean RoussyPlease backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own. |
 |
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2005-06-20 : 17:13:09
|
quote:
Originally posted by ThrasymachusI would not have a DB for testing and development on my production machine.
As I mentioned at the end of my post, I am removing ODSStage when I get a chance. It was an artifact from a time when we didn't have multiple servers. As a note, it's not used in the context of technical testing, but it's used by some business folks right now to test the data we get from new customers and provide reports back on the results.quote:
I usually write my trace file to another DB server wether they are db tables or flatfiles as to not consume space and resources on my production machines.
Good feedback. As of yet, I've never written a trace file to DBAInfo, but it's there if I ever need it. For the most part, it contains a couple stored procedures and views.quote:
"Free range user area" just sounds scary.
Can't really get away from this as it's basically for a few users who need more than MS Access can provide. At least it's in a centralized area we can backup and access w/queries if need be.quote:
It is a good idea to have a seperate highly indexed and even denormalized copy of your data for reporting purposes.
At some point when we have time, we will address this. For now, we have to make due with stored procedures... |
|
|
|
|
|
|
|
|
Multiple Databases - ETL Best Practices?