Using Views

[Damon]

I've searched for information on this but so far have not been able to
find any advice.

We have several databases running on SQL Server 7.0 that are
essentially identical in structure but are used for different data.
There are several tables that are identical in all databases.
Currently, we make updates to those tables in one database and
propogate the new versions of those tables to the other databases.
The maintenance to keep after this is somewhat problematic in that the
user, or dba, has to initiate some action to apply the update to all
tables anytime a change is made. An idea I have to simplify this
process is create views that access the data in a central location.
I would create a database for these shared tables, and replace the
tables in each individual database with a view of the same name. The
other nice thing about this solution is it is transparent to
applications that rely on this data. I contemplated using the shared
database by making code changes to change all the references to those
tables to DB..table, but that would take considerable man hours. Can
anyone give me a reason why this would not be a good idea? Thanks.

[Erland Sommarskog]

Re: Using Views

Damon (damonl73@yahoo .com) writes:[color=blue]
> I've searched for information on this but so far have not been able to
> find any advice.
>
> We have several databases running on SQL Server 7.0 that are
> essentially identical in structure but are used for different data.
> There are several tables that are identical in all databases.
> Currently, we make updates to those tables in one database and
> propogate the new versions of those tables to the other databases.
> The maintenance to keep after this is somewhat problematic in that the
> user, or dba, has to initiate some action to apply the update to all
> tables anytime a change is made. An idea I have to simplify this
> process is create views that access the data in a central location.
> I would create a database for these shared tables, and replace the
> tables in each individual database with a view of the same name. The
> other nice thing about this solution is it is transparent to
> applications that rely on this data. I contemplated using the shared
> database by making code changes to change all the references to those
> tables to DB..table, but that would take considerable man hours. Can
> anyone give me a reason why this would not be a good idea? Thanks.[/color]

I'm afraid that the information is not really sufficient for a good
answer. For one thing: these updates to the tables you talk about,
are they updates to the data, or do add/remove/change columns?

If they are changes in data, I would look into replication.

If they are changes in table structure, I would look into a better build
process. Rather than making the changes in one database, all changes
should be made to source code in a version control system, and you should
develop change scripts to deploy the changes.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Damon Lichtenwalner]

Re: Using Views

Here is some additional information. The end user would never update or
change any of these tables. All structure and data changes would be
made by myself or the other admin. Since the user and application level
would only be accessing this data for lookup purposes, views seemed like
they would be a resonable solution to implement. Does this change your
recommendation? When you mention replication, are you referring to a
feature of SQL Server or a method of achieving what I want? Thanks.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Re: Using Views

Damon Lichtenwalner (damonl73@yahoo .com) writes:[color=blue]
> Here is some additional information. The end user would never update or
> change any of these tables. All structure and data changes would be
> made by myself or the other admin. Since the user and application level
> would only be accessing this data for lookup purposes, views seemed like
> they would be a resonable solution to implement. Does this change your
> recommendation? When you mention replication, are you referring to a
> feature of SQL Server or a method of achieving what I want? Thanks.[/color]

Yes, replication is a feature of SQL Server. But if the data is static and
only changed at maitenance points, replication is probably a little heavy-
duty for the task.

I still think the best way to go is develop methods to propagate the update
from scripts that are put under version control. This is a clean and
trackable way of doing it. For exrra security, you could add a table to
each database that permits you keep track of which scripts you have run
in that database.

If you go for views, you may still run into problem to change the views
if the underlying tables change. It also makes you less flexible if for
some reason some databases should be left on an earlier version. There is
also a limitation if you find that you need to scale out on more than one
server.

These may be minor costs, so you may be prepared to pay that price. But
the view solution is really only band-aid for insuffecient methods for
configuration management.

If the databases have different owners, there may also be issues with
cross-database ownership chains and all that. These are addressable,
but there is a risk that while the views relieves of some current hassles,
they will bring you new one.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp