Design Suggestions for "marking" databases?

microsoft.public.sqlserver.programming

1 Jul 2005 6:34 PM

Tore

I have an application that will be working with multiple databases (same
design different data). At the moment, I list the available SQL Servers, as
well as the existing databases when a user selects one of the servers. The
user can select one of the existing databases or create a new database based
on a template script. After selecting a database or creating a new
database, the user can "initialize" the database (i.e. load a set of initial
data which is produced elsewhere). This all works fine (using .Net, SQL
Server 2000, DMO, DTS, and launching osql with a script).

However, I would like to make a couple of improvements to this setup.

* First, I would like to list ONLY the databases that are for this
application (i.e. my application has created).
* Second, I need to "lock" a database against re-initialization after I get
to a certain point. Re-initialization will occur frequently during an
end-user's testing (consider it testing of the data and possibly
"application training"), but once the data goes live, that database may no
longer be re-initialized. I would like for a "locked" database to be
excluded from the list, or at least marked as "locked". Of course I can
check whether the chosen target is locked before I re-initialize based on
informatin inside that database, but I don't even want to provide the option
to re-initialize a locked database.
* Third, I expect this application to have extended longevity (think 10-40
years), so I need to implement a strong version control mechanism, and would
like to display the "database (schema) version" in my database list, as well
as use it as a filter for the list in some situations.

I know I could implement a control table inside each database and check it
for each database, but as the number of databases grows (it will), I foresee
a significant impact on the performance. I could also implement a "control
database" where I could keep track of the other databases, but I expect that
databases may be rolled out (detached or backed up and removed from system)
and restored when needed over time. Therefore I consider a separate list to
not be a dependable approach (remember the longevity issue, version numbers,
etc.). Using an accumulative list and checking against available databases
may be my approach if all else fails.

Anyway, what I really would like to do is find some supported way of using
the system tables or meta data to track this, so that a (proper) backup and
restore would also restore the "indicators" of database type, status, and
version number/identifier. Something that I could query through a single
connection, probably to the master db. Also due to the longevity, I would
expect that the database server may be updated with new versions of SQL
Server. Therefore, I need a *supported* method (i.e. I can't use a "not
used" or "reserved" item). But the only thing I can see that may be useful,
is the database creation date, which I can use together with a lookup table
to determine "database/schema version".

Any suggestions? Has anyone dealt with this type of issue before? I can
target SQL Server 2005 if that helps.

TIA,
Tore.

1 Jul 2005 7:01 PM

I assume that the databases associated with your application following some
standard naming convention. You can query from sp_databases or
master..sysdatabases and filter on the name.
For example:
select * from sysdatabases where name like 'myapp_%'

To block users from using a database while it is in a transitional state,
you can use ALTER DATABASE.. to set the DB to READ_ONLY or RESTRICTED_USER
mode. I believe that the [status] or [mode] columns in sysdatabases should
reflect this.

Basically, it sounds like you are wanting to build some sort of
datawarehouse. In that case, you can refer to the great books by Ralph
Kimball for standard design patterns.

Show quote

"Tore" <pointzero at vbdreamteam dot net> wrote in message
news:uQ$w7tmfFHA.3436@tk2msftngp13.phx.gbl...
> I have an application that will be working with multiple databases (same
> design different data). At the moment, I list the available SQL Servers,
as
> well as the existing databases when a user selects one of the servers.
The
> user can select one of the existing databases or create a new database
based
> on a template script. After selecting a database or creating a new
> database, the user can "initialize" the database (i.e. load a set of
initial
> data which is produced elsewhere). This all works fine (using .Net, SQL
> Server 2000, DMO, DTS, and launching osql with a script).
>
> However, I would like to make a couple of improvements to this setup.
>
> * First, I would like to list ONLY the databases that are for this
> application (i.e. my application has created).
> * Second, I need to "lock" a database against re-initialization after I
get
> to a certain point. Re-initialization will occur frequently during an
> end-user's testing (consider it testing of the data and possibly
> "application training"), but once the data goes live, that database may no
> longer be re-initialized. I would like for a "locked" database to be
> excluded from the list, or at least marked as "locked". Of course I can
> check whether the chosen target is locked before I re-initialize based on
> informatin inside that database, but I don't even want to provide the
option
> to re-initialize a locked database.
> * Third, I expect this application to have extended longevity (think 10-40
> years), so I need to implement a strong version control mechanism, and
would
> like to display the "database (schema) version" in my database list, as
well
> as use it as a filter for the list in some situations.
>
> I know I could implement a control table inside each database and check it
> for each database, but as the number of databases grows (it will), I
foresee
> a significant impact on the performance. I could also implement a
"control
> database" where I could keep track of the other databases, but I expect
that
> databases may be rolled out (detached or backed up and removed from
system)
> and restored when needed over time. Therefore I consider a separate list
to
> not be a dependable approach (remember the longevity issue, version
numbers,
> etc.). Using an accumulative list and checking against available
databases
> may be my approach if all else fails.
>
> Anyway, what I really would like to do is find some supported way of using
> the system tables or meta data to track this, so that a (proper) backup
and
> restore would also restore the "indicators" of database type, status, and
> version number/identifier. Something that I could query through a single
> connection, probably to the master db. Also due to the longevity, I would
> expect that the database server may be updated with new versions of SQL
> Server. Therefore, I need a *supported* method (i.e. I can't use a "not
> used" or "reserved" item). But the only thing I can see that may be
useful,
> is the database creation date, which I can use together with a lookup
table
> to determine "database/schema version".
>
> Any suggestions? Has anyone dealt with this type of issue before? I can
> target SQL Server 2005 if that helps.
>
>
> TIA,
> Tore.
>
>

1 Jul 2005 8:05 PM

Tore

Thanks for responding.

I'll consider whether I can use the database state RESTRICTED_USER. But
read on:

First - no - I don't really have a naming standard - the end users create
the individual databases and give them names that are (hopefully) meaningful
to them. Theoretically, there could be multiple events per database (which
will actually complicate my "locking" approach), and there can be multiple
databases per event - normally all but one are likely to be used for test
only, but it is also possible that the same event in different locations
share a server. There may also be "consolidated" databases (not DW
aggregates) containing the data from multiple location databases. So even
if I could come up with a naming scheme, in order for it to provide the
required data (event name, event date, location, version, other description)
it would make for an excessively ugly database name and/or an unwieldy
codification procedure.

Second - no - this isn't really intended to be a data warehouse - at least
not in the comnmon sense of the word. These are databases reflecting data
about separate "events". I could conceivably keep it all in a single
database, but I need to maintain the data integrity over the long term, and
therefore I would prefer to keep the data as separate as possible. Each set
of data has a lifecycle which can be described with the following stages:

* New - create/initialize
* Test - and optionally re-initialize
* Live - will (or ready to) receive production data. At this point, the
database may no longer be re-initialized.
* Freeze/Audit - no more updates allowed, auditing may take place. At this
point, the data will also be backed up to permanent media for medium long
term (a couple of years) off-site storage
* Historical - data is used for historical lookups, etc. At this point the
database may or may not be available on-line. This would also be a
candidate for using a DW and importing the data into it to provide
additional capabilities, such as trending, etc..

I would use the READ_ONLY db mode for the Freeze/Audit and Historical
stages. Production data will be entered through a Web Service as well as by
end users during the Live stage. Maybe I can allow the users to be given a
dbcreator role, I'll have to chew on that...

Thanks,
Tore.

Show quote

"JT" <some***@microsoft.com> wrote in message
news:ukLUe%23mfFHA.3940@tk2msftngp13.phx.gbl...
>I assume that the databases associated with your application following some
> standard naming convention. You can query from sp_databases or
> master..sysdatabases and filter on the name.
> For example:
> select * from sysdatabases where name like 'myapp_%'
>
> To block users from using a database while it is in a transitional state,
> you can use ALTER DATABASE.. to set the DB to READ_ONLY or RESTRICTED_USER
> mode. I believe that the [status] or [mode] columns in sysdatabases should
> reflect this.
>
> Basically, it sounds like you are wanting to build some sort of
> datawarehouse. In that case, you can refer to the great books by Ralph
> Kimball for standard design patterns.
>
> "Tore" <pointzero at vbdreamteam dot net> wrote in message
> news:uQ$w7tmfFHA.3436@tk2msftngp13.phx.gbl...
>> I have an application that will be working with multiple databases (same
>> design different data). At the moment, I list the available SQL Servers,
> as
>> well as the existing databases when a user selects one of the servers.
> The
>> user can select one of the existing databases or create a new database
> based
>> on a template script. After selecting a database or creating a new
>> database, the user can "initialize" the database (i.e. load a set of
> initial
>> data which is produced elsewhere). This all works fine (using .Net, SQL
>> Server 2000, DMO, DTS, and launching osql with a script).
>>
>> However, I would like to make a couple of improvements to this setup.
>>
>> * First, I would like to list ONLY the databases that are for this
>> application (i.e. my application has created).
>> * Second, I need to "lock" a database against re-initialization after I
> get
>> to a certain point. Re-initialization will occur frequently during an
>> end-user's testing (consider it testing of the data and possibly
>> "application training"), but once the data goes live, that database may
>> no
>> longer be re-initialized. I would like for a "locked" database to be
>> excluded from the list, or at least marked as "locked". Of course I can
>> check whether the chosen target is locked before I re-initialize based on
>> informatin inside that database, but I don't even want to provide the
> option
>> to re-initialize a locked database.
>> * Third, I expect this application to have extended longevity (think
>> 10-40
>> years), so I need to implement a strong version control mechanism, and
> would
>> like to display the "database (schema) version" in my database list, as
> well
>> as use it as a filter for the list in some situations.
>>
>> I know I could implement a control table inside each database and check
>> it
>> for each database, but as the number of databases grows (it will), I
> foresee
>> a significant impact on the performance. I could also implement a
> "control
>> database" where I could keep track of the other databases, but I expect
> that
>> databases may be rolled out (detached or backed up and removed from
> system)
>> and restored when needed over time. Therefore I consider a separate list
> to
>> not be a dependable approach (remember the longevity issue, version
> numbers,
>> etc.). Using an accumulative list and checking against available
> databases
>> may be my approach if all else fails.
>>
>> Anyway, what I really would like to do is find some supported way of
>> using
>> the system tables or meta data to track this, so that a (proper) backup
> and
>> restore would also restore the "indicators" of database type, status, and
>> version number/identifier. Something that I could query through a single
>> connection, probably to the master db. Also due to the longevity, I
>> would
>> expect that the database server may be updated with new versions of SQL
>> Server. Therefore, I need a *supported* method (i.e. I can't use a "not
>> used" or "reserved" item). But the only thing I can see that may be
> useful,
>> is the database creation date, which I can use together with a lookup
> table
>> to determine "database/schema version".
>>
>> Any suggestions? Has anyone dealt with this type of issue before? I can
>> target SQL Server 2005 if that helps.
>>
>>
>> TIA,
>> Tore.
>>
>>
>
>