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 |
|
bpric
Starting Member
1 Post |
 Posted - 2010-09-15 : 10:15:16
|
| I was just assigned the task of 'cleaning up' a database that has evolved over the last eight or ten years. The DB never had a 'big picture' design -- tables, views, etc. were just added on an as-needed basis by a few staff programmers (myself included) that were learning about SQL Server as they went along. Any recommendations on the best way to proceed?Here's a general description of the DB situation...* About 100 tables, 100 views* A few hundred stored procedures, user-defined functions, triggers* The DB is primarily accessed by a dozen or so vb.net applications that were created in-house.* Numerous DB objects probably aren't even used anymore.Recommendations for books I should read or courses I should take before I even begin would be useful as well.Cheers,Bryan |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-09-15 : 10:50:55
|
| yeah -- start with the backups!and a backup isn't a backup until you've restored it at least once.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-15 : 13:56:21
|
| If you are going to want to refactor the code then a couple of things spring to mind.1. Build an automated regression-test suite first. Then you can test everything well as you make your refactoring changes, and rest fairly easy that you have not broken anything (or get early warning of what needs fixing).2. When you want to change a table renaming it completely to your new naming conventions and design style (if its already that add "_V2" suffix) and ditto the column names. Split the table into multiple tables if that's what's needed.Then create a VIEW with the original name so the app continues to run. Doing INSERTS and UPDATES through such a view tends to have undesireable side effects, or a bunch of work arounds (even if, and in some cases "especially if"!, you use an INSTEAD OF trigger on the View), so I would change the App to use the new table for any Inserts / Updates / Deletes. |
|
|
|
Jim Beam
Posting Yak Master
137 Posts |
Posted - 2010-09-17 : 12:26:04
|
| I was going to post up pretty damn near the same situation, so if it's not an imposition, I'll plagiarise the original poster's initial post as a template, and alter accordingly. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-17 : 13:17:47
|
| For anyone stumbling over this thread in the future: Jim asked his similar question here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=150306 |
|
|
|
|
|
|
|
|
I inherited a sloppy DB / Where do I start?