Data Dictionary project

We have several databases on SQL 2005 which were built seperately. Ive
been asked to come up with a plan for making sure that data elements are
consistent across all of them, and conform to an outside standard
definition which I have a document for. Ive also been asked to make it
easy for any changes in the external definition to be quickly
transferred to all databases. e.g. If the field for Surname changes to
Varchar(55) we should eb able to change it on all databases without
hunting around for places it occurs.
One idea Ive had is to create our own internal set of User Defined Data
Types matching the external dictionary. But ive then got to somehow map
this to all our databases and change them.
Has anyone out there been involved in anything similar or have any ideas?
tia, Matt"Matt" <ma77g@.clara.co.uk> wrote in message
news:1162363143.30942.0@.iris.uk.clara.net...
> We have several databases on SQL 2005 which were built seperately. Ive
> been asked to come up with a plan for making sure that data elements are
> consistent across all of them, and conform to an outside standard
> definition which I have a document for. Ive also been asked to make it
> easy for any changes in the external definition to be quickly transferred
> to all databases. e.g. If the field for Surname changes to Varchar(55) we
> should eb able to change it on all databases without hunting around for
> places it occurs.
> One idea Ive had is to create our own internal set of User Defined Data
> Types matching the external dictionary. But ive then got to somehow map
> this to all our databases and change them.
> Has anyone out there been involved in anything similar or have any ideas?
RedGate software's SQLCompare application does database schema comparisons
and helps keep schemas in sync. Another option is to script out all your
changes ahead of time, apply strict QA and source control policies to them,
and make sure you apply any change scripts to all databases as necessary
(probably a good idea if you're not doing it already anyway). After all, in
a properly normalized database how many different places does Surname
actually occur? (My guess would be once, maybe twice if you have some sort
of pre-load "work table" in place). I'm not too big a fan of the old-style
User-Defined Types, but to each his/her own...|||> If the field for Surname changes to Varchar(55) we should eb able to
> change it on all databases without hunting around for places it occu
I wouldn't use user-defined datatypes for this requirement. Although they
may facilitate a standard definition for types don't change, UDTs complicate
changes to the type after implementation.
Most data modeling tools have features that allow you to address your
dictionary requirements and create change scripts as well. You can also
store dictionary meta-data using extended properties. Also, VS 2005 Team
Edition for Database Professionals (currently in beta) provides refactoring
features and related tools.
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt" <ma77g@.clara.co.uk> wrote in message
news:1162363143.30942.0@.iris.uk.clara.net...
> We have several databases on SQL 2005 which were built seperately. Ive
> been asked to come up with a plan for making sure that data elements are
> consistent across all of them, and conform to an outside standard
> definition which I have a document for. Ive also been asked to make it
> easy for any changes in the external definition to be quickly transferred
> to all databases. e.g. If the field for Surname changes to Varchar(55) we
> should eb able to change it on all databases without hunting around for
> places it occurs.
> One idea Ive had is to create our own internal set of User Defined Data
> Types matching the external dictionary. But ive then got to somehow map
> this to all our databases and change them.
> Has anyone out there been involved in anything similar or have any ideas?
> tia, Mattsql

data corruption question

sql2k sp3
Based on recent experience Ive got a few questions about data corruption:
1> How does it happen?
2> What can be done to prevent it?
3> If it does happen, what are the proper steps to try to fix it without
restoring?
4> If I do need to restore, how do I know how far back to restore? I wouldnt
want to go through a lengthy restore process and then find out I still have
the corruption. Is there a way to know the day/time it first started?
TIA, ChrisRHi
99.999% of all corruption is caused by hardware.
Get good and reputable hardware and don't cut corners when installing it.
Have a UPS to keep the server up during power interruptions. Have battery
backed up RAID controller cards.
Make very regular backups, including transaction logs and get them off to
tape ASAP. Run regular DBCC's to check the consistency of the databases, and
have 'Torn page detection' on for each database. This will tell you very
quickly that some corruption has occurred. How often? Well, depending on
your uptime requirements, DBCC's daily does not sound like a bad idea.
Some times, corruption is not detected until weeks after it occurred so you
loose a lot. Daily, at worst case you loose a day's data.
Run the latest BIOS and driver versions, keep up to date with Windows SP's
and hotfixes and SQL SP's.
Don't plug you server and the kettle into the same wall plug, and keep the
Managers away from the servers. LOL.
Most of it is just common sense.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1A640B78-E981-4F57-B8A9-AF324C0ECE10@.microsoft.com...
> sql2k sp3
> Based on recent experience Ive got a few questions about data corruption:
> 1> How does it happen?
> 2> What can be done to prevent it?
> 3> If it does happen, what are the proper steps to try to fix it without
> restoring?
> 4> If I do need to restore, how do I know how far back to restore? I
wouldnt
> want to go through a lengthy restore process and then find out I still
have
> the corruption. Is there a way to know the day/time it first started?
> TIA, ChrisR

data corruption question

sql2k sp3
Based on recent experience Ive got a few questions about data corruption:
1> How does it happen?
2> What can be done to prevent it?
3> If it does happen, what are the proper steps to try to fix it without
restoring?
4> If I do need to restore, how do I know how far back to restore? I wouldnt
want to go through a lengthy restore process and then find out I still have
the corruption. Is there a way to know the day/time it first started?
TIA, ChrisR
Hi
99.999% of all corruption is caused by hardware.
Get good and reputable hardware and don't cut corners when installing it.
Have a UPS to keep the server up during power interruptions. Have battery
backed up RAID controller cards.
Make very regular backups, including transaction logs and get them off to
tape ASAP. Run regular DBCC's to check the consistency of the databases, and
have 'Torn page detection' on for each database. This will tell you very
quickly that some corruption has occurred. How often? Well, depending on
your uptime requirements, DBCC's daily does not sound like a bad idea.
Some times, corruption is not detected until weeks after it occurred so you
loose a lot. Daily, at worst case you loose a day's data.
Run the latest BIOS and driver versions, keep up to date with Windows SP's
and hotfixes and SQL SP's.
Don't plug you server and the kettle into the same wall plug, and keep the
Managers away from the servers. LOL.
Most of it is just common sense.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1A640B78-E981-4F57-B8A9-AF324C0ECE10@.microsoft.com...
> sql2k sp3
> Based on recent experience Ive got a few questions about data corruption:
> 1> How does it happen?
> 2> What can be done to prevent it?
> 3> If it does happen, what are the proper steps to try to fix it without
> restoring?
> 4> If I do need to restore, how do I know how far back to restore? I
wouldnt
> want to go through a lengthy restore process and then find out I still
have
> the corruption. Is there a way to know the day/time it first started?
> TIA, ChrisR

data corruption question

sql2k sp3
Based on recent experience Ive got a few questions about data corruption:
1> How does it happen?
2> What can be done to prevent it?
3> If it does happen, what are the proper steps to try to fix it without
restoring?
4> If I do need to restore, how do I know how far back to restore? I wouldnt
want to go through a lengthy restore process and then find out I still have
the corruption. Is there a way to know the day/time it first started?
TIA, ChrisRHi
99.999% of all corruption is caused by hardware.
Get good and reputable hardware and don't cut corners when installing it.
Have a UPS to keep the server up during power interruptions. Have battery
backed up RAID controller cards.
Make very regular backups, including transaction logs and get them off to
tape ASAP. Run regular DBCC's to check the consistency of the databases, and
have 'Torn page detection' on for each database. This will tell you very
quickly that some corruption has occurred. How often? Well, depending on
your uptime requirements, DBCC's daily does not sound like a bad idea.
Some times, corruption is not detected until weeks after it occurred so you
loose a lot. Daily, at worst case you loose a day's data.
Run the latest BIOS and driver versions, keep up to date with Windows SP's
and hotfixes and SQL SP's.
Don't plug you server and the kettle into the same wall plug, and keep the
Managers away from the servers. LOL.
Most of it is just common sense.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1A640B78-E981-4F57-B8A9-AF324C0ECE10@.microsoft.com...
> sql2k sp3
> Based on recent experience Ive got a few questions about data corruption:
> 1> How does it happen?
> 2> What can be done to prevent it?
> 3> If it does happen, what are the proper steps to try to fix it without
> restoring?
> 4> If I do need to restore, how do I know how far back to restore? I
wouldnt
> want to go through a lengthy restore process and then find out I still
have
> the corruption. Is there a way to know the day/time it first started?
> TIA, ChrisR

Data Access layer Advice

I've been following Soctt Mitchell's tutorials on Data Access and in Tutorial 1 (Step 5) he suggests using SQL Subqueries in TableAdapters in order to pick up extra information for display using a datasource.

I have two tables for a gallery system I'm building. One called Photographs and one called MS_Photographs which has extra information about certain images. When reading the MS_Photograph data I also want to include a couple of fields from the related Photographs table. Rather than creating a table adapter just to pull this data I wanted to use the existing MS_Photographs adapter with a query such as...

1SELECT CAR_MAKE, CAR_MODEL,2 (SELECT DATE_TAKEN3FROM PHOTOGRAPHS4WHERE (PHOTOGRAPH_ID = MS_PHOTOGRAPHS.PHOTOGRAPH_ID))AS DATE_TAKEN,5 (SELECT FORMAT6FROM PHOTOGRAPHS7WHERE (PHOTOGRAPH_ID = MS_PHOTOGRAPHS.PHOTOGRAPH_ID))AS FORMAT,8 (SELECT REFERENCE9FROM PHOTOGRAPHS10WHERE (PHOTOGRAPH_ID = MS_PHOTOGRAPHS.PHOTOGRAPH_ID))AS REFERENCE,11 DRIVER1, TEAM, GALLERY_ID, PHOTOGRAPH_ID12FROM MS_PHOTOGRAPHS13WHERE (GALLERY_ID = @.GalleryID)

This works but I wanted to know if there's a way to get all of the fields using one subquery instead of three? I did try it but it gave me errors for everything I could think of.

Is using a subquery like above the best way when you want this many fields from a secondary table or should I be using another approach. I'm using classes for the BLL as well and wondered if there's a way to do it at this stage instead?

Can't you simply right a query containing a join like so:

SELECTA.CAR_MAKE,A.CAR_MODEL,B.DATE_TAKEN, B.FORMAT,B.REFERENCE,A.DRIVER1,A.TEAM,A.GALLERY_ID,A.PHOTOGRAPH_IDFROMMS_PHOTOGRAPHSAS AINNERJOIN PHOTOGRAPHSAS BON B.PHOTOGRAPH_ID = A.PHOTOGRAPH_IDWHEREA.GALLERY_ID = @.GalleryID

|||

You can use a join but the tutorial explains that this affects the auto-generated methods for inserting, updating and deleting data using the table adapter.

|||

If I understand your problem correctly it sounds like what you want is a join (perhaps an outer join):

select a.car_make, a.car_model, b.date_taken, b.format, b.reference., etc
from ms_photographs a,
photographs b
where a.photographs_id = b.photographs_id
and a.gallery_id = @.GalleryID

The only issue is that the above query is an "inner" join which means that it will only return data that have matching rows in each table. If you might have data from ms_photographs that is not in photograps, then you need an "outer" join, which just means a change to the where clause as follows:

where a.photographs_id *= b.photographs_id
and a.gallery_id = @.GalleryID

There is an alternate syntax involving the use of the words LEFT OUTER JOIN (which is what the above example is) or RIGHT OUTER JOIN, but I prefer the *= syntax (only because I'm old and that's how I learned it This alternate syntax is the new standard, but the old way is still quite common. Here is the above rewritten to the new standard:

select a.car_make, a.car_model, b.date_taken, b.format, b.reference., etc
from ms_photographs a left outer join photographs b
on a.photographs_id = b.photographs_id
where a.gallery_id = @.GalleryID

BTW, the 2 syntaxes do not actually produce identical output in all cases, but the differences are very subtle and have to do with what happens if you include a where condition on the outer table -- it's not worth going into at this point

|||

Thanks for your suggestion. I can see how that would work but I'm trying to avoid it as the tutorial states that it will affect the other autogenerated statements in the tableadapter if i use a join.

Is there any sensible way to do this without a join or in the BLL?

Cheers

|||

nevets2001uk2:

Is there any sensible way to do this without a join or in the BLL?

I don't see how. Either you use a join or your do all the lookups yourself.

|||

nevets2001uk2:

I've been following Soctt Mitchell's tutorials on Data Access and in Tutorial 1 (Step 5) he suggests using SQL Subqueries in TableAdapters in order to pick up extra information for display using a datasource.

I have two tables for a gallery system I'm building. One called Photographs and one called MS_Photographs which has extra information about certain images. When reading the MS_Photograph data I also want to include a couple of fields from the related Photographs table. Rather than creating a table adapter just to pull this data I wanted to use the existing MS_Photographs adapter with a query such as......

You can have multiple select methods - for example, the GetAll and GetByID are common. So if you are not dealing with ginormous volumes and millions of hits, just stick the 3 subqueries in and see how it goes. I dont think SQL Server will physically read the second table three times per row. You can another select method without the subqueries for perfomance if you like. You don'tneed another adapter, though youcould have one if you wanted.

|||

Thanks for the advice. I'll stick with what I have for now and see how I go.