I've recently taken over development on a SQL Server 2000 database that needs some help. We're planning on upgrading it to SQL Server 2005 soon. This database has no audit fields on the tables (CreatedBy, CreatedDate, etc.), no foreign keys, and terrible overall design. There are half a dozen programs that directly access the database using inline SQL, and other old/bad practices.
我最近接管了需要一些帮助的SQL Server 2000数据库的开发。我们计划很快将其升级到SQL Server 2005。这个数据库没有表上的审计字段(CreatedBy,CreatedDate等),没有外键,可怕的整体设计。有六个程序使用内联SQL和其他旧/坏实践直接访问数据库。
I would like to clean up the schema, and the data access. Do you have any suggestions for a good place to start? This is a production database, and it has to continue to work while it's being improved. Thanks.
我想清理架构和数据访问。你对一个好的起点有什么建议吗?这是一个生产数据库,它必须在改进时继续工作。谢谢。
2 个解决方案
#1
1
You're probably going to have to start with the applications that access the database. More than likely you will find that any changes to the database schema will break those other applications. The most common culprit I've found is select * sql followed by accessing the data based on column position. If you insert a column before the last column, that code will break. Also, unless you use default values for your new columns, any insert commands will fail.
您可能必须从访问数据库的应用程序开始。您很可能会发现对数据库模式的任何更改都会破坏其他应用程序。我发现的最常见的罪魁祸首是select * sql,然后根据列位置访问数据。如果在最后一列之前插入一列,则该代码将中断。此外,除非您为新列使用默认值,否则任何插入命令都将失败。
Your best bet is to understand how those external programs are using the database, then design a new database and then migrate each of those programs over to the new database one at a time.
最好的办法是了解这些外部程序如何使用数据库,然后设计一个新数据库,然后将每个程序一次一个地迁移到新数据库。
Making a change to this database while it is in production is almost guaranteed to break the other applications.
在数据库处于生产状态时对其进行更改几乎可以保证打破其他应用程序。
#2
1
You may be able to correct, analyse, normalise etc the schema while maintaining the current schema/interface behind views.
您可以在维护当前架构/接口后面的视图的同时纠正,分析,规范化等架构。
Using before trigger on the views can ensure apps write as well as read as they expect.
在视图上使用触发器之前可以确保应用程序按照预期进行写入和读取。
This way you can start to migrate the client apps onto new schema whilst allowing the current app to work. And your data is safer (DRI,FK,DF,CK etc) in it's new schema.
通过这种方式,您可以开始将客户端应用程序迁移到新架构,同时允许当前应用程序工作。并且您的数据在其新架构中更安全(DRI,FK,DF,CK等)。
This also keeps the interface contract consistent for that unexpected spreadsheet that runs once a month and no-one knows about it is essential for that end-of-month report...
这也使得界面合约与每月运行一次的意外电子表格保持一致,没有人知道它对于月末报告至关重要......
#1
1
You're probably going to have to start with the applications that access the database. More than likely you will find that any changes to the database schema will break those other applications. The most common culprit I've found is select * sql followed by accessing the data based on column position. If you insert a column before the last column, that code will break. Also, unless you use default values for your new columns, any insert commands will fail.
您可能必须从访问数据库的应用程序开始。您很可能会发现对数据库模式的任何更改都会破坏其他应用程序。我发现的最常见的罪魁祸首是select * sql,然后根据列位置访问数据。如果在最后一列之前插入一列,则该代码将中断。此外,除非您为新列使用默认值,否则任何插入命令都将失败。
Your best bet is to understand how those external programs are using the database, then design a new database and then migrate each of those programs over to the new database one at a time.
最好的办法是了解这些外部程序如何使用数据库,然后设计一个新数据库,然后将每个程序一次一个地迁移到新数据库。
Making a change to this database while it is in production is almost guaranteed to break the other applications.
在数据库处于生产状态时对其进行更改几乎可以保证打破其他应用程序。
#2
1
You may be able to correct, analyse, normalise etc the schema while maintaining the current schema/interface behind views.
您可以在维护当前架构/接口后面的视图的同时纠正,分析,规范化等架构。
Using before trigger on the views can ensure apps write as well as read as they expect.
在视图上使用触发器之前可以确保应用程序按照预期进行写入和读取。
This way you can start to migrate the client apps onto new schema whilst allowing the current app to work. And your data is safer (DRI,FK,DF,CK etc) in it's new schema.
通过这种方式,您可以开始将客户端应用程序迁移到新架构,同时允许当前应用程序工作。并且您的数据在其新架构中更安全(DRI,FK,DF,CK等)。
This also keeps the interface contract consistent for that unexpected spreadsheet that runs once a month and no-one knows about it is essential for that end-of-month report...
这也使得界面合约与每月运行一次的意外电子表格保持一致,没有人知道它对于月末报告至关重要......