Hi,
I've looked at the online documentation and encountered this piece of
text
"If uniqueness must be enforced to ensure data integrity, create a
UNIQUE or PRIMARY KEY constraint on the column rather than a unique
index"
I'm baffled; I understand a unique contraint, and personally do not
care how it is physically enforced (using an index to speed up the
process) as long as it is enforced. However what is the deal with
unique indexes? The piece of documentation above seems to suggest that
a unique index will not enforce uniqueness.
The reason I ask is that I am working against a legacy system where
unique indexes are used for each table. Each table, besides a PK, has
a column that stores a GUID. Personally I would use a Unique
Contraint, but they have used a unique index on this column. What
would be the advantages and disadvantages of using a unique index vs a
unique constraint.'
regards
Paul SjoerdsmaYes, a unique index enforces uniqueness. A unique index is created in the
background when you specify a PK or Unique constraint.
The difference is more semantic, but also tactical... Placing the constraint
indicates a design requirement. When you create the index directly, how can
you know IF the index was created for performance, or if the index is
intended to enforce some design constraint... The fact is that you can
not... So use PK and Uniques to enforce design requirements...Indexes are
created for 2 main reasons, to enforce integrity and speed up queries.The
indexes used to speed up queries( or other statements) may need to change
over time as business priorities change, but we would still need to maintain
the indexes created to enforce data integrity.
Indexes created as a by-product of PK or unique constraints can NOT be
dropped using DROP index, so you are protected from inadvertently dropping
an index which is used for data integrity. FOr this you must use alter table
drop constraint, alter table add constraint... Any index that goes away
using DROP index, therefore, would have been created for performance, and is
a candidate for re-assessment as needs change
Additionally FKs must be placed on columns with PKs or Unique
constraints.(from BOL). HOwever the actual code allows you to put an FK on
any column which has a unique index ( although I beleive this should NOT be
allowed)..
Hope this helps/
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul Sjoerdsma" <paul@.pasoftware.nl> wrote in message
news:2j8ko0pdll18amito60gcqne5g58en6otj@.
4ax.com...
> Hi,
> I've looked at the online documentation and encountered this piece of
> text
> "If uniqueness must be enforced to ensure data integrity, create a
> UNIQUE or PRIMARY KEY constraint on the column rather than a unique
> index"
> I'm baffled; I understand a unique contraint, and personally do not
> care how it is physically enforced (using an index to speed up the
> process) as long as it is enforced. However what is the deal with
> unique indexes? The piece of documentation above seems to suggest that
> a unique index will not enforce uniqueness.
> The reason I ask is that I am working against a legacy system where
> unique indexes are used for each table. Each table, besides a PK, has
> a column that stores a GUID. Personally I would use a Unique
> Contraint, but they have used a unique index on this column. What
> would be the advantages and disadvantages of using a unique index vs a
> unique constraint.'
>
> regards
> Paul Sjoerdsma
>|||In addition to the other respondents reply, implicit in your question is
really two seperate questions:
What is the difference between the use of PK and Unique Constraints and
Unique Indexes? The was answered by the other respondent. Except, so you
know, there is no such thing as a PK index, only a constraint, which is also
enforced through a Unique Index.
Second, you made mention of PK and GUID, which I am assuming you implied
what is typically used some sort of INT IDENTITY. Both of these are unique
by design, one locally to the table, the other across all tables, databases,
and time. However, these are mearly mechanics. They do not garauntee
uniqueness of the data, which is the sole purpose of KEYS, Primary or
Alternate. Moreover, PKs and Unique Constraints are not limited to IDENTITY
nor GUID. For example:
EmployeeID LastName FirstName
1 Smith John
2 Jones Tom
3 Marshall Eric
4 Smith John
Although EmployeeID is unique, because it is IDENTITY and is so by
construction, for this table, the DATA is NOT unique: John Smith is repeated
.
Therefore, this table is NOT even a table by relational design--it is not in
1NF (First Normal Form).
The real key for this entity is the Logical Name attribute, which is
composite upon FirstName and LastName. Since that is pretty much all of the
data, it is also the Primary Key candidate. However, if this table were
JOINed to some other not only would you have to populate both attributes to
that table, you would have to populate string types (data storage
inefficient) and would have to include both attributes in any Foreign Key or
JOIN declaration, also highly inefficient.
So, oftentimes, one creates the IDENTITY or GUID attributes to simplify
these requirements. What happens, TOO OFTEN, is the PK is defined on
EmployeeID and no Unique Constraint is ever defined on the Candidate Key
combination of First and Last Names, which is the true key of this table, bu
t
should be and MUST be for this implementation to be in at least 1NF.
Moreover, while I am on it, I see far too often that such an IDENTITY or
GUID PK is also defined as the Clustered Index, if there is one at all. Tha
t
is a lousy choice. Not only should every table have a KEY (Primary or
Otherwise)--this is a logical or design requirement--every table should also
have a WELL CHOSEN Clustered Index or Key defined--this is a physical
recommendation.
A well chosen Clustered Index or Key will affect the construction of
statistics and other indexes on the table. It should be nearly unique, if
not a unique key. It should not change often. And, it should be considered
for RANGE type query requirements. In our example, not only do we need to
create a UNIQUE CONSTRAINT for the Name attribute, the Last Name or the Last
Name + First Name combination should be considered for the Clustered Index.
I hope I have not gone to Academic, or Theoretical--some might say, Cultish.
Nevertheless, these point go far too unnoticed by the mass of database
designers and users and needs to be said more often.
Sincerely,
Anthony Thomas
"Paul Sjoerdsma" wrote:
> Hi,
> I've looked at the online documentation and encountered this piece of
> text
> "If uniqueness must be enforced to ensure data integrity, create a
> UNIQUE or PRIMARY KEY constraint on the column rather than a unique
> index"
> I'm baffled; I understand a unique contraint, and personally do not
> care how it is physically enforced (using an index to speed up the
> process) as long as it is enforced. However what is the deal with
> unique indexes? The piece of documentation above seems to suggest that
> a unique index will not enforce uniqueness.
> The reason I ask is that I am working against a legacy system where
> unique indexes are used for each table. Each table, besides a PK, has
> a column that stores a GUID. Personally I would use a Unique
> Contraint, but they have used a unique index on this column. What
> would be the advantages and disadvantages of using a unique index vs a
> unique constraint.'
>
> regards
> Paul Sjoerdsma
>|||Anthony and Wayne,
Thanks for your answers. Although I would consider myself to be
experienced in database design (with the necessary theoretical
background) I still do not grok the full idea behind the concept of a
unique index; even after your elaborate explanations.
To me it seems to have raised it's head out of some murky swamp. There
are nicer places to visit, so a Unique Key constraint is what it will
be.
However if someone could give me a clear example where I would use a
unique index instead of a unique key I would be very happy,
thanks,
regards
Paul
On Thu, 4 Nov 2004 05:34:01 -0800, "AnthonyThomas"
<AnthonyThomas@.discussions.microsoft.com> wrote:
>In addition to the other respondents reply, implicit in your question is
>really two seperate questions:
>|||> However if someone could give me a clear example where I would use a
> unique index instead of a unique key I would be very happy,
Very good question. IMO, there aren't any (*). Some say that you should use
an index directly when
the uniqueness isn't a property of the data model per se, but IMO, I have a
hard time finding such
an example. If I want to enforce uniqueness, I surely want to expose this at
the logical level
(constraint) as well, right?
(*) Here's an exception. There are at least one attribute you can define for
a unique index which
isn't available through a unique constraint. If this index attribute is impo
rtant for you, define an
index directly and not though a unique constraint. In SQL Server 2005, this
will be available
through a constraint...
Be prepared that different people have different viewpoints about this. It i
s a bit like the age-old
discussion "natural keys vs. surrogate keys".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul Sjoerdsma" <paul.nospam.@.pasoftware.nl> wrote in message
news:h9dmo0dce287fe5gdjoo0ro4mvgqrsggb5@.
4ax.com...
> Anthony and Wayne,
> Thanks for your answers. Although I would consider myself to be
> experienced in database design (with the necessary theoretical
> background) I still do not grok the full idea behind the concept of a
> unique index; even after your elaborate explanations.
> To me it seems to have raised it's head out of some murky swamp. There
> are nicer places to visit, so a Unique Key constraint is what it will
> be.
>
> However if someone could give me a clear example where I would use a
> unique index instead of a unique key I would be very happy,
>
> thanks,
> regards
> Paul
>
> On Thu, 4 Nov 2004 05:34:01 -0800, "AnthonyThomas"
> <AnthonyThomas@.discussions.microsoft.com> wrote:
>
>|||I have two real life examples where I would use a unique index as
opposed to a unique constraint.
1. When you have a relation table that contains the keys of two
referenced tables. For example the table StudentCourses which lists the
courses for each student. It might be defined as:
CREATE TABLE StudentCourses
(StudentID int not null REFERENCES Students
,CourseID int not null REFERENCES Courses
,CONSTRAINT PK_StudentCourses PRIMARY KEY (StudentID, CourseID)
)
In this case there already is a unique constraint on (StudentID,
CourseID). For performance purposes I would add a unique index on
(CourseID, StudentID). This would benefit query like:
SELECT Course, COUNT(*) AS StudentCount
FROM Courses C1
INNER JOIN StudentCourses CS1
ON CS1.CourseID = C1.CourseID
GROUP BY Course
2. When a (compound) covering index is needed, and one of its columns is
by itself unique. When I have the choice between a nonunique and a
unique index, I choose a unique index. But the columns of the covering
index do not constitute a new or different "unique relation", so a
unique constraint would be inappropriate.
The rule I use for unique constraints is that you should be able to
remove all user created unique indexes, and the data model would still
be consistent and enforced.
Hope this helps,
Gert-Jan
Paul Sjoerdsma wrote:[vbcol=seagreen]
> Anthony and Wayne,
> Thanks for your answers. Although I would consider myself to be
> experienced in database design (with the necessary theoretical
> background) I still do not grok the full idea behind the concept of a
> unique index; even after your elaborate explanations.
> To me it seems to have raised it's head out of some murky swamp. There
> are nicer places to visit, so a Unique Key constraint is what it will
> be.
> However if someone could give me a clear example where I would use a
> unique index instead of a unique key I would be very happy,
> thanks,
> regards
> Paul
> On Thu, 4 Nov 2004 05:34:01 -0800, "AnthonyThomas"
> <AnthonyThomas@.discussions.microsoft.com> wrote:
>
Showing posts with label creating. Show all posts
Showing posts with label creating. Show all posts
Sunday, March 25, 2012
Difference between creating a Unique Constraint and a Unique Index
Hi,
I've looked at the online documentation and encountered this piece of
text
"If uniqueness must be enforced to ensure data integrity, create a
UNIQUE or PRIMARY KEY constraint on the column rather than a unique
index"
I'm baffled; I understand a unique contraint, and personally do not
care how it is physically enforced (using an index to speed up the
process) as long as it is enforced. However what is the deal with
unique indexes? The piece of documentation above seems to suggest that
a unique index will not enforce uniqueness.
The reason I ask is that I am working against a legacy system where
unique indexes are used for each table. Each table, besides a PK, has
a column that stores a GUID. Personally I would use a Unique
Contraint, but they have used a unique index on this column. What
would be the advantages and disadvantages of using a unique index vs a
unique constraint.'
regards
Paul SjoerdsmaYes, a unique index enforces uniqueness. A unique index is created in the
background when you specify a PK or Unique constraint.
The difference is more semantic, but also tactical... Placing the constraint
indicates a design requirement. When you create the index directly, how can
you know IF the index was created for performance, or if the index is
intended to enforce some design constraint... The fact is that you can
not... So use PK and Uniques to enforce design requirements...Indexes are
created for 2 main reasons, to enforce integrity and speed up queries.The
indexes used to speed up queries( or other statements) may need to change
over time as business priorities change, but we would still need to maintain
the indexes created to enforce data integrity.
Indexes created as a by-product of PK or unique constraints can NOT be
dropped using DROP index, so you are protected from inadvertently dropping
an index which is used for data integrity. FOr this you must use alter table
drop constraint, alter table add constraint... Any index that goes away
using DROP index, therefore, would have been created for performance, and is
a candidate for re-assessment as needs change
Additionally FKs must be placed on columns with PKs or Unique
constraints.(from BOL). HOwever the actual code allows you to put an FK on
any column which has a unique index ( although I beleive this should NOT be
allowed)..
Hope this helps/
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul Sjoerdsma" <paul@.pasoftware.nl> wrote in message
news:2j8ko0pdll18amito60gcqne5g58en6otj@.4ax.com...
> Hi,
> I've looked at the online documentation and encountered this piece of
> text
> "If uniqueness must be enforced to ensure data integrity, create a
> UNIQUE or PRIMARY KEY constraint on the column rather than a unique
> index"
> I'm baffled; I understand a unique contraint, and personally do not
> care how it is physically enforced (using an index to speed up the
> process) as long as it is enforced. However what is the deal with
> unique indexes? The piece of documentation above seems to suggest that
> a unique index will not enforce uniqueness.
> The reason I ask is that I am working against a legacy system where
> unique indexes are used for each table. Each table, besides a PK, has
> a column that stores a GUID. Personally I would use a Unique
> Contraint, but they have used a unique index on this column. What
> would be the advantages and disadvantages of using a unique index vs a
> unique constraint.'
>
> regards
> Paul Sjoerdsma
>|||In addition to the other respondents reply, implicit in your question is
really two seperate questions:
What is the difference between the use of PK and Unique Constraints and
Unique Indexes? The was answered by the other respondent. Except, so you
know, there is no such thing as a PK index, only a constraint, which is also
enforced through a Unique Index.
Second, you made mention of PK and GUID, which I am assuming you implied
what is typically used some sort of INT IDENTITY. Both of these are unique
by design, one locally to the table, the other across all tables, databases,
and time. However, these are mearly mechanics. They do not garauntee
uniqueness of the data, which is the sole purpose of KEYS, Primary or
Alternate. Moreover, PKs and Unique Constraints are not limited to IDENTITY
nor GUID. For example:
EmployeeID LastName FirstName
1 Smith John
2 Jones Tom
3 Marshall Eric
4 Smith John
Although EmployeeID is unique, because it is IDENTITY and is so by
construction, for this table, the DATA is NOT unique: John Smith is repeated.
Therefore, this table is NOT even a table by relational design--it is not in
1NF (First Normal Form).
The real key for this entity is the Logical Name attribute, which is
composite upon FirstName and LastName. Since that is pretty much all of the
data, it is also the Primary Key candidate. However, if this table were
JOINed to some other not only would you have to populate both attributes to
that table, you would have to populate string types (data storage
inefficient) and would have to include both attributes in any Foreign Key or
JOIN declaration, also highly inefficient.
So, oftentimes, one creates the IDENTITY or GUID attributes to simplify
these requirements. What happens, TOO OFTEN, is the PK is defined on
EmployeeID and no Unique Constraint is ever defined on the Candidate Key
combination of First and Last Names, which is the true key of this table, but
should be and MUST be for this implementation to be in at least 1NF.
Moreover, while I am on it, I see far too often that such an IDENTITY or
GUID PK is also defined as the Clustered Index, if there is one at all. That
is a lousy choice. Not only should every table have a KEY (Primary or
Otherwise)--this is a logical or design requirement--every table should also
have a WELL CHOSEN Clustered Index or Key defined--this is a physical
recommendation.
A well chosen Clustered Index or Key will affect the construction of
statistics and other indexes on the table. It should be nearly unique, if
not a unique key. It should not change often. And, it should be considered
for RANGE type query requirements. In our example, not only do we need to
create a UNIQUE CONSTRAINT for the Name attribute, the Last Name or the Last
Name + First Name combination should be considered for the Clustered Index.
I hope I have not gone to Academic, or Theoretical--some might say, Cultish.
Nevertheless, these point go far too unnoticed by the mass of database
designers and users and needs to be said more often.
Sincerely,
Anthony Thomas
"Paul Sjoerdsma" wrote:
> Hi,
> I've looked at the online documentation and encountered this piece of
> text
> "If uniqueness must be enforced to ensure data integrity, create a
> UNIQUE or PRIMARY KEY constraint on the column rather than a unique
> index"
> I'm baffled; I understand a unique contraint, and personally do not
> care how it is physically enforced (using an index to speed up the
> process) as long as it is enforced. However what is the deal with
> unique indexes? The piece of documentation above seems to suggest that
> a unique index will not enforce uniqueness.
> The reason I ask is that I am working against a legacy system where
> unique indexes are used for each table. Each table, besides a PK, has
> a column that stores a GUID. Personally I would use a Unique
> Contraint, but they have used a unique index on this column. What
> would be the advantages and disadvantages of using a unique index vs a
> unique constraint.'
>
> regards
> Paul Sjoerdsma
>|||Anthony and Wayne,
Thanks for your answers. Although I would consider myself to be
experienced in database design (with the necessary theoretical
background) I still do not grok the full idea behind the concept of a
unique index; even after your elaborate explanations.
To me it seems to have raised it's head out of some murky swamp. There
are nicer places to visit, so a Unique Key constraint is what it will
be.
However if someone could give me a clear example where I would use a
unique index instead of a unique key I would be very happy,
thanks,
regards
Paul
On Thu, 4 Nov 2004 05:34:01 -0800, "AnthonyThomas"
<AnthonyThomas@.discussions.microsoft.com> wrote:
>In addition to the other respondents reply, implicit in your question is
>really two seperate questions:
>|||> However if someone could give me a clear example where I would use a
> unique index instead of a unique key I would be very happy,
Very good question. IMO, there aren't any (*). Some say that you should use an index directly when
the uniqueness isn't a property of the data model per se, but IMO, I have a hard time finding such
an example. If I want to enforce uniqueness, I surely want to expose this at the logical level
(constraint) as well, right?
(*) Here's an exception. There are at least one attribute you can define for a unique index which
isn't available through a unique constraint. If this index attribute is important for you, define an
index directly and not though a unique constraint. In SQL Server 2005, this will be available
through a constraint...
Be prepared that different people have different viewpoints about this. It is a bit like the age-old
discussion "natural keys vs. surrogate keys".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul Sjoerdsma" <paul.nospam.@.pasoftware.nl> wrote in message
news:h9dmo0dce287fe5gdjoo0ro4mvgqrsggb5@.4ax.com...
> Anthony and Wayne,
> Thanks for your answers. Although I would consider myself to be
> experienced in database design (with the necessary theoretical
> background) I still do not grok the full idea behind the concept of a
> unique index; even after your elaborate explanations.
> To me it seems to have raised it's head out of some murky swamp. There
> are nicer places to visit, so a Unique Key constraint is what it will
> be.
>
> However if someone could give me a clear example where I would use a
> unique index instead of a unique key I would be very happy,
>
> thanks,
> regards
> Paul
>
> On Thu, 4 Nov 2004 05:34:01 -0800, "AnthonyThomas"
> <AnthonyThomas@.discussions.microsoft.com> wrote:
> >In addition to the other respondents reply, implicit in your question is
> >really two seperate questions:
> >
>|||I have two real life examples where I would use a unique index as
opposed to a unique constraint.
1. When you have a relation table that contains the keys of two
referenced tables. For example the table StudentCourses which lists the
courses for each student. It might be defined as:
CREATE TABLE StudentCourses
(StudentID int not null REFERENCES Students
,CourseID int not null REFERENCES Courses
,CONSTRAINT PK_StudentCourses PRIMARY KEY (StudentID, CourseID)
)
In this case there already is a unique constraint on (StudentID,
CourseID). For performance purposes I would add a unique index on
(CourseID, StudentID). This would benefit query like:
SELECT Course, COUNT(*) AS StudentCount
FROM Courses C1
INNER JOIN StudentCourses CS1
ON CS1.CourseID = C1.CourseID
GROUP BY Course
2. When a (compound) covering index is needed, and one of its columns is
by itself unique. When I have the choice between a nonunique and a
unique index, I choose a unique index. But the columns of the covering
index do not constitute a new or different "unique relation", so a
unique constraint would be inappropriate.
The rule I use for unique constraints is that you should be able to
remove all user created unique indexes, and the data model would still
be consistent and enforced.
Hope this helps,
Gert-Jan
Paul Sjoerdsma wrote:
> Anthony and Wayne,
> Thanks for your answers. Although I would consider myself to be
> experienced in database design (with the necessary theoretical
> background) I still do not grok the full idea behind the concept of a
> unique index; even after your elaborate explanations.
> To me it seems to have raised it's head out of some murky swamp. There
> are nicer places to visit, so a Unique Key constraint is what it will
> be.
> However if someone could give me a clear example where I would use a
> unique index instead of a unique key I would be very happy,
> thanks,
> regards
> Paul
> On Thu, 4 Nov 2004 05:34:01 -0800, "AnthonyThomas"
> <AnthonyThomas@.discussions.microsoft.com> wrote:
> >In addition to the other respondents reply, implicit in your question is
> >really two seperate questions:
> >
I've looked at the online documentation and encountered this piece of
text
"If uniqueness must be enforced to ensure data integrity, create a
UNIQUE or PRIMARY KEY constraint on the column rather than a unique
index"
I'm baffled; I understand a unique contraint, and personally do not
care how it is physically enforced (using an index to speed up the
process) as long as it is enforced. However what is the deal with
unique indexes? The piece of documentation above seems to suggest that
a unique index will not enforce uniqueness.
The reason I ask is that I am working against a legacy system where
unique indexes are used for each table. Each table, besides a PK, has
a column that stores a GUID. Personally I would use a Unique
Contraint, but they have used a unique index on this column. What
would be the advantages and disadvantages of using a unique index vs a
unique constraint.'
regards
Paul SjoerdsmaYes, a unique index enforces uniqueness. A unique index is created in the
background when you specify a PK or Unique constraint.
The difference is more semantic, but also tactical... Placing the constraint
indicates a design requirement. When you create the index directly, how can
you know IF the index was created for performance, or if the index is
intended to enforce some design constraint... The fact is that you can
not... So use PK and Uniques to enforce design requirements...Indexes are
created for 2 main reasons, to enforce integrity and speed up queries.The
indexes used to speed up queries( or other statements) may need to change
over time as business priorities change, but we would still need to maintain
the indexes created to enforce data integrity.
Indexes created as a by-product of PK or unique constraints can NOT be
dropped using DROP index, so you are protected from inadvertently dropping
an index which is used for data integrity. FOr this you must use alter table
drop constraint, alter table add constraint... Any index that goes away
using DROP index, therefore, would have been created for performance, and is
a candidate for re-assessment as needs change
Additionally FKs must be placed on columns with PKs or Unique
constraints.(from BOL). HOwever the actual code allows you to put an FK on
any column which has a unique index ( although I beleive this should NOT be
allowed)..
Hope this helps/
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul Sjoerdsma" <paul@.pasoftware.nl> wrote in message
news:2j8ko0pdll18amito60gcqne5g58en6otj@.4ax.com...
> Hi,
> I've looked at the online documentation and encountered this piece of
> text
> "If uniqueness must be enforced to ensure data integrity, create a
> UNIQUE or PRIMARY KEY constraint on the column rather than a unique
> index"
> I'm baffled; I understand a unique contraint, and personally do not
> care how it is physically enforced (using an index to speed up the
> process) as long as it is enforced. However what is the deal with
> unique indexes? The piece of documentation above seems to suggest that
> a unique index will not enforce uniqueness.
> The reason I ask is that I am working against a legacy system where
> unique indexes are used for each table. Each table, besides a PK, has
> a column that stores a GUID. Personally I would use a Unique
> Contraint, but they have used a unique index on this column. What
> would be the advantages and disadvantages of using a unique index vs a
> unique constraint.'
>
> regards
> Paul Sjoerdsma
>|||In addition to the other respondents reply, implicit in your question is
really two seperate questions:
What is the difference between the use of PK and Unique Constraints and
Unique Indexes? The was answered by the other respondent. Except, so you
know, there is no such thing as a PK index, only a constraint, which is also
enforced through a Unique Index.
Second, you made mention of PK and GUID, which I am assuming you implied
what is typically used some sort of INT IDENTITY. Both of these are unique
by design, one locally to the table, the other across all tables, databases,
and time. However, these are mearly mechanics. They do not garauntee
uniqueness of the data, which is the sole purpose of KEYS, Primary or
Alternate. Moreover, PKs and Unique Constraints are not limited to IDENTITY
nor GUID. For example:
EmployeeID LastName FirstName
1 Smith John
2 Jones Tom
3 Marshall Eric
4 Smith John
Although EmployeeID is unique, because it is IDENTITY and is so by
construction, for this table, the DATA is NOT unique: John Smith is repeated.
Therefore, this table is NOT even a table by relational design--it is not in
1NF (First Normal Form).
The real key for this entity is the Logical Name attribute, which is
composite upon FirstName and LastName. Since that is pretty much all of the
data, it is also the Primary Key candidate. However, if this table were
JOINed to some other not only would you have to populate both attributes to
that table, you would have to populate string types (data storage
inefficient) and would have to include both attributes in any Foreign Key or
JOIN declaration, also highly inefficient.
So, oftentimes, one creates the IDENTITY or GUID attributes to simplify
these requirements. What happens, TOO OFTEN, is the PK is defined on
EmployeeID and no Unique Constraint is ever defined on the Candidate Key
combination of First and Last Names, which is the true key of this table, but
should be and MUST be for this implementation to be in at least 1NF.
Moreover, while I am on it, I see far too often that such an IDENTITY or
GUID PK is also defined as the Clustered Index, if there is one at all. That
is a lousy choice. Not only should every table have a KEY (Primary or
Otherwise)--this is a logical or design requirement--every table should also
have a WELL CHOSEN Clustered Index or Key defined--this is a physical
recommendation.
A well chosen Clustered Index or Key will affect the construction of
statistics and other indexes on the table. It should be nearly unique, if
not a unique key. It should not change often. And, it should be considered
for RANGE type query requirements. In our example, not only do we need to
create a UNIQUE CONSTRAINT for the Name attribute, the Last Name or the Last
Name + First Name combination should be considered for the Clustered Index.
I hope I have not gone to Academic, or Theoretical--some might say, Cultish.
Nevertheless, these point go far too unnoticed by the mass of database
designers and users and needs to be said more often.
Sincerely,
Anthony Thomas
"Paul Sjoerdsma" wrote:
> Hi,
> I've looked at the online documentation and encountered this piece of
> text
> "If uniqueness must be enforced to ensure data integrity, create a
> UNIQUE or PRIMARY KEY constraint on the column rather than a unique
> index"
> I'm baffled; I understand a unique contraint, and personally do not
> care how it is physically enforced (using an index to speed up the
> process) as long as it is enforced. However what is the deal with
> unique indexes? The piece of documentation above seems to suggest that
> a unique index will not enforce uniqueness.
> The reason I ask is that I am working against a legacy system where
> unique indexes are used for each table. Each table, besides a PK, has
> a column that stores a GUID. Personally I would use a Unique
> Contraint, but they have used a unique index on this column. What
> would be the advantages and disadvantages of using a unique index vs a
> unique constraint.'
>
> regards
> Paul Sjoerdsma
>|||Anthony and Wayne,
Thanks for your answers. Although I would consider myself to be
experienced in database design (with the necessary theoretical
background) I still do not grok the full idea behind the concept of a
unique index; even after your elaborate explanations.
To me it seems to have raised it's head out of some murky swamp. There
are nicer places to visit, so a Unique Key constraint is what it will
be.
However if someone could give me a clear example where I would use a
unique index instead of a unique key I would be very happy,
thanks,
regards
Paul
On Thu, 4 Nov 2004 05:34:01 -0800, "AnthonyThomas"
<AnthonyThomas@.discussions.microsoft.com> wrote:
>In addition to the other respondents reply, implicit in your question is
>really two seperate questions:
>|||> However if someone could give me a clear example where I would use a
> unique index instead of a unique key I would be very happy,
Very good question. IMO, there aren't any (*). Some say that you should use an index directly when
the uniqueness isn't a property of the data model per se, but IMO, I have a hard time finding such
an example. If I want to enforce uniqueness, I surely want to expose this at the logical level
(constraint) as well, right?
(*) Here's an exception. There are at least one attribute you can define for a unique index which
isn't available through a unique constraint. If this index attribute is important for you, define an
index directly and not though a unique constraint. In SQL Server 2005, this will be available
through a constraint...
Be prepared that different people have different viewpoints about this. It is a bit like the age-old
discussion "natural keys vs. surrogate keys".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul Sjoerdsma" <paul.nospam.@.pasoftware.nl> wrote in message
news:h9dmo0dce287fe5gdjoo0ro4mvgqrsggb5@.4ax.com...
> Anthony and Wayne,
> Thanks for your answers. Although I would consider myself to be
> experienced in database design (with the necessary theoretical
> background) I still do not grok the full idea behind the concept of a
> unique index; even after your elaborate explanations.
> To me it seems to have raised it's head out of some murky swamp. There
> are nicer places to visit, so a Unique Key constraint is what it will
> be.
>
> However if someone could give me a clear example where I would use a
> unique index instead of a unique key I would be very happy,
>
> thanks,
> regards
> Paul
>
> On Thu, 4 Nov 2004 05:34:01 -0800, "AnthonyThomas"
> <AnthonyThomas@.discussions.microsoft.com> wrote:
> >In addition to the other respondents reply, implicit in your question is
> >really two seperate questions:
> >
>|||I have two real life examples where I would use a unique index as
opposed to a unique constraint.
1. When you have a relation table that contains the keys of two
referenced tables. For example the table StudentCourses which lists the
courses for each student. It might be defined as:
CREATE TABLE StudentCourses
(StudentID int not null REFERENCES Students
,CourseID int not null REFERENCES Courses
,CONSTRAINT PK_StudentCourses PRIMARY KEY (StudentID, CourseID)
)
In this case there already is a unique constraint on (StudentID,
CourseID). For performance purposes I would add a unique index on
(CourseID, StudentID). This would benefit query like:
SELECT Course, COUNT(*) AS StudentCount
FROM Courses C1
INNER JOIN StudentCourses CS1
ON CS1.CourseID = C1.CourseID
GROUP BY Course
2. When a (compound) covering index is needed, and one of its columns is
by itself unique. When I have the choice between a nonunique and a
unique index, I choose a unique index. But the columns of the covering
index do not constitute a new or different "unique relation", so a
unique constraint would be inappropriate.
The rule I use for unique constraints is that you should be able to
remove all user created unique indexes, and the data model would still
be consistent and enforced.
Hope this helps,
Gert-Jan
Paul Sjoerdsma wrote:
> Anthony and Wayne,
> Thanks for your answers. Although I would consider myself to be
> experienced in database design (with the necessary theoretical
> background) I still do not grok the full idea behind the concept of a
> unique index; even after your elaborate explanations.
> To me it seems to have raised it's head out of some murky swamp. There
> are nicer places to visit, so a Unique Key constraint is what it will
> be.
> However if someone could give me a clear example where I would use a
> unique index instead of a unique key I would be very happy,
> thanks,
> regards
> Paul
> On Thu, 4 Nov 2004 05:34:01 -0800, "AnthonyThomas"
> <AnthonyThomas@.discussions.microsoft.com> wrote:
> >In addition to the other respondents reply, implicit in your question is
> >really two seperate questions:
> >
Thursday, March 22, 2012
Difference
What are the major difference b/w index hint and creating index on column
Thanks
Joh wrote:
> What are the major difference b/w index hint and creating index on
> column
> Thanks
They really can't be compared as you pose the question. You cannot use
an index hint without the index existing and the index can be created on
one or more columns. An index hint tells the optimizer to use a
particular index on a table when executing a query. It's not recommended
users use hints because it prevents the optimizer from generating what
it believes is the best execution plan. Keeping your statistics up to
date can help prevent underperforming execution plans from being used.
In some rare cases, SQL Server may make not make an optimal decision for
a query and you can use a hint to keep SQL Server in line. However, if
you do this, it's extremely important to document the hint and revisit
the query periodically to make sure it continues to work as expected and
to re-test the query without the hint when new service packs are
installed.
David Gugick
Imceda Software
www.imceda.com
|||Thanks David...I really appreciate
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uuNcCLIXFHA.612@.TK2MSFTNGP12.phx.gbl...
> Joh wrote:
> They really can't be compared as you pose the question. You cannot use
> an index hint without the index existing and the index can be created on
> one or more columns. An index hint tells the optimizer to use a
> particular index on a table when executing a query. It's not recommended
> users use hints because it prevents the optimizer from generating what
> it believes is the best execution plan. Keeping your statistics up to
> date can help prevent underperforming execution plans from being used.
> In some rare cases, SQL Server may make not make an optimal decision for
> a query and you can use a hint to keep SQL Server in line. However, if
> you do this, it's extremely important to document the hint and revisit
> the query periodically to make sure it continues to work as expected and
> to re-test the query without the hint when new service packs are
> installed.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Index hints forces the optimizer to utilize an index. This restricts the
optimizer from doing what it is good at i.e. finding the optimized plan /
index for the given query based on the data and clauses specified.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Joh" <joh@.mailcity.com> wrote in message
news:evDIu%23HXFHA.1044@.TK2MSFTNGP10.phx.gbl...
> What are the major difference b/w index hint and creating index on column
> Thanks
>
Thanks
Joh wrote:
> What are the major difference b/w index hint and creating index on
> column
> Thanks
They really can't be compared as you pose the question. You cannot use
an index hint without the index existing and the index can be created on
one or more columns. An index hint tells the optimizer to use a
particular index on a table when executing a query. It's not recommended
users use hints because it prevents the optimizer from generating what
it believes is the best execution plan. Keeping your statistics up to
date can help prevent underperforming execution plans from being used.
In some rare cases, SQL Server may make not make an optimal decision for
a query and you can use a hint to keep SQL Server in line. However, if
you do this, it's extremely important to document the hint and revisit
the query periodically to make sure it continues to work as expected and
to re-test the query without the hint when new service packs are
installed.
David Gugick
Imceda Software
www.imceda.com
|||Thanks David...I really appreciate
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uuNcCLIXFHA.612@.TK2MSFTNGP12.phx.gbl...
> Joh wrote:
> They really can't be compared as you pose the question. You cannot use
> an index hint without the index existing and the index can be created on
> one or more columns. An index hint tells the optimizer to use a
> particular index on a table when executing a query. It's not recommended
> users use hints because it prevents the optimizer from generating what
> it believes is the best execution plan. Keeping your statistics up to
> date can help prevent underperforming execution plans from being used.
> In some rare cases, SQL Server may make not make an optimal decision for
> a query and you can use a hint to keep SQL Server in line. However, if
> you do this, it's extremely important to document the hint and revisit
> the query periodically to make sure it continues to work as expected and
> to re-test the query without the hint when new service packs are
> installed.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Index hints forces the optimizer to utilize an index. This restricts the
optimizer from doing what it is good at i.e. finding the optimized plan /
index for the given query based on the data and clauses specified.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Joh" <joh@.mailcity.com> wrote in message
news:evDIu%23HXFHA.1044@.TK2MSFTNGP10.phx.gbl...
> What are the major difference b/w index hint and creating index on column
> Thanks
>
Sunday, March 11, 2012
Diagram Tool Causes Strange Write Table Behavior
Using SQL Server 2000...
I'm creating a series of database diagrams for a database that has
~ 200 tables. The tables are segmented by module, so instead of
having one massive diagram I am creating a diagram per module.
All I'm doing is creating a new diagram, selecting all the tables
that belong to a module, and letting the software create the
diagram. I do make some format/layout changes to make the
diagram easier to read, but at no time am I making any structural
changes to the table.
For three of the four modules this approach has worked perfectly.
However, there is one set of tables that the tool thinks has been
altered, and it insists on writing the tables back to the database
before it will save it. The first time this happened I just said "No"
to any writing/saving and started over from scratch (I assumed I
had accidentally made a change to a table). The next time I added
all the module's tables, I simply moved one table two or three
inches to the left, and then tried to save the daigram (having 100%
confidence there were no accidental structural changes). Once
again, it wanted to write a few of the tables back to the database
before saving.
I have no idea why it thinks any structural changes occured to the
referenced tables. I certainly didn't make any, and on the rare
occasion I have accidentally made a change, I was able to
successfully start over by not writing/saving the change and
closing/opening the diagram.
Can anyone let me know what possible changes could have been
applied to a table(s) outside the diagram tool, that could affect the
diagram tool and make it think it needs to write the tables to the
database before saving?
ThanksHi Garth,
I don't know the answer to your question because I don't like, or use the
SQL Server diagramming tool - for the exact reason you're experiencing ...
changes to the diagram make changes to the database.
While I used to use ER/win (which is a fantastic tool), I've shifted to
Visio. Now, without the developer edition (I think that's the one) you can't
do a forward engineer, but you can reverse engineer any DB you can connect
to (ODBC, OLE DB, etc), it provides at least 80% of what matters in ER/win
(or Rational Rose) and you have the advantage that anyone with Visio can
read the file.
I admit it's going to be hard to learn it (as there is virtually no one on
the boards who know how), but I find it far, far superior to what is
available in SQL Server and more convenient than anything else.
Thanks,
Jay
"Garth Wells" <nobody@.nowhere.com> wrote in message
news:eQq7Mx1FIHA.4712@.TK2MSFTNGP04.phx.gbl...
> Using SQL Server 2000...
> I'm creating a series of database diagrams for a database that has
> ~ 200 tables. The tables are segmented by module, so instead of
> having one massive diagram I am creating a diagram per module.
> All I'm doing is creating a new diagram, selecting all the tables
> that belong to a module, and letting the software create the
> diagram. I do make some format/layout changes to make the
> diagram easier to read, but at no time am I making any structural
> changes to the table.
> For three of the four modules this approach has worked perfectly.
> However, there is one set of tables that the tool thinks has been
> altered, and it insists on writing the tables back to the database
> before it will save it. The first time this happened I just said "No"
> to any writing/saving and started over from scratch (I assumed I
> had accidentally made a change to a table). The next time I added
> all the module's tables, I simply moved one table two or three
> inches to the left, and then tried to save the daigram (having 100%
> confidence there were no accidental structural changes). Once
> again, it wanted to write a few of the tables back to the database
> before saving.
> I have no idea why it thinks any structural changes occured to the
> referenced tables. I certainly didn't make any, and on the rare
> occasion I have accidentally made a change, I was able to
> successfully start over by not writing/saving the change and
> closing/opening the diagram.
> Can anyone let me know what possible changes could have been
> applied to a table(s) outside the diagram tool, that could affect the
> diagram tool and make it think it needs to write the tables to the
> database before saving?
> Thanks
>
>
I'm creating a series of database diagrams for a database that has
~ 200 tables. The tables are segmented by module, so instead of
having one massive diagram I am creating a diagram per module.
All I'm doing is creating a new diagram, selecting all the tables
that belong to a module, and letting the software create the
diagram. I do make some format/layout changes to make the
diagram easier to read, but at no time am I making any structural
changes to the table.
For three of the four modules this approach has worked perfectly.
However, there is one set of tables that the tool thinks has been
altered, and it insists on writing the tables back to the database
before it will save it. The first time this happened I just said "No"
to any writing/saving and started over from scratch (I assumed I
had accidentally made a change to a table). The next time I added
all the module's tables, I simply moved one table two or three
inches to the left, and then tried to save the daigram (having 100%
confidence there were no accidental structural changes). Once
again, it wanted to write a few of the tables back to the database
before saving.
I have no idea why it thinks any structural changes occured to the
referenced tables. I certainly didn't make any, and on the rare
occasion I have accidentally made a change, I was able to
successfully start over by not writing/saving the change and
closing/opening the diagram.
Can anyone let me know what possible changes could have been
applied to a table(s) outside the diagram tool, that could affect the
diagram tool and make it think it needs to write the tables to the
database before saving?
ThanksHi Garth,
I don't know the answer to your question because I don't like, or use the
SQL Server diagramming tool - for the exact reason you're experiencing ...
changes to the diagram make changes to the database.
While I used to use ER/win (which is a fantastic tool), I've shifted to
Visio. Now, without the developer edition (I think that's the one) you can't
do a forward engineer, but you can reverse engineer any DB you can connect
to (ODBC, OLE DB, etc), it provides at least 80% of what matters in ER/win
(or Rational Rose) and you have the advantage that anyone with Visio can
read the file.
I admit it's going to be hard to learn it (as there is virtually no one on
the boards who know how), but I find it far, far superior to what is
available in SQL Server and more convenient than anything else.
Thanks,
Jay
"Garth Wells" <nobody@.nowhere.com> wrote in message
news:eQq7Mx1FIHA.4712@.TK2MSFTNGP04.phx.gbl...
> Using SQL Server 2000...
> I'm creating a series of database diagrams for a database that has
> ~ 200 tables. The tables are segmented by module, so instead of
> having one massive diagram I am creating a diagram per module.
> All I'm doing is creating a new diagram, selecting all the tables
> that belong to a module, and letting the software create the
> diagram. I do make some format/layout changes to make the
> diagram easier to read, but at no time am I making any structural
> changes to the table.
> For three of the four modules this approach has worked perfectly.
> However, there is one set of tables that the tool thinks has been
> altered, and it insists on writing the tables back to the database
> before it will save it. The first time this happened I just said "No"
> to any writing/saving and started over from scratch (I assumed I
> had accidentally made a change to a table). The next time I added
> all the module's tables, I simply moved one table two or three
> inches to the left, and then tried to save the daigram (having 100%
> confidence there were no accidental structural changes). Once
> again, it wanted to write a few of the tables back to the database
> before saving.
> I have no idea why it thinks any structural changes occured to the
> referenced tables. I certainly didn't make any, and on the rare
> occasion I have accidentally made a change, I was able to
> successfully start over by not writing/saving the change and
> closing/opening the diagram.
> Can anyone let me know what possible changes could have been
> applied to a table(s) outside the diagram tool, that could affect the
> diagram tool and make it think it needs to write the tables to the
> database before saving?
> Thanks
>
>
Wednesday, March 7, 2012
Development - Test Environment Questions
Hi,
I've been given the task of creating a dev/test environment. Currently we
have several production applications using databases on a common SQL Server.
If changes are required, the developers are performing the changes on the
production system - yea I know BAD,BAD,BAD - but I didn't set this up but
instead inherited it. I'd like to configure a dev/test environment to move
the devs off of the production system and to faciliate their development of
future projects coming up soon.
How is your dev/test environment configured? I'm looking for a few examples
here that I can work with to implement our dev/test based on our budget and
system capabilities.
I.e., Each dev has their own sandbox or each dev shares a common dev env -
then changes are implemented to test env (and by who - dev or dba - and
how - scripts) etc... then scripted to deploy on prod systems etc... also
we image/ghost systems or use VMs and also we use Visual SourceSafe to...
Trying to come up with a solid game plan here.
Thanks for any input.
Jerry
Jerry Spivey wrote:
> Hi,
> I've been given the task of creating a dev/test environment. Currently
> we have several production applications using databases on
> a common SQL Server. If changes are required, the developers are
> performing the changes on the production system - yea I know
> BAD,BAD,BAD - but I didn't set this up but instead inherited it. I'd
> like to configure a dev/test environment to move the devs off of the
> production system and to faciliate their development of future
> projects coming up soon.
> How is your dev/test environment configured? I'm looking for a few
> examples here that I can work with to implement our dev/test based on
> our budget and system capabilities.
> I.e., Each dev has their own sandbox or each dev shares a common dev
> env - then changes are implemented to test env (and by who - dev or
> dba - and how - scripts) etc... then scripted to deploy on prod
> systems etc... also we image/ghost systems or use VMs and also we use
> Visual
> SourceSafe to...
> Trying to come up with a solid game plan here.
> Thanks for any input.
> Jerry
At most companies where I've worked in the past, we had development
servers that were used strictly for development. They generally
contained stripped down data from the production databases with any
customer sensitive data masked. Lead developers generally had dbo rights
and may even have admin rights depending on the size of the company.
Version control software was used for all object changes. Initial
application testing was done on the dev servers.
QA/Test servers were not managed by development. They were usually owned
by QA group. We would provide detailed scripts to update QA servers with
the necessary changes. Users would test the applications on QA servers.
QA servers have data that more closely mimics production in terms of
data value distribution and quantity and may even be created from
production backups. Sensitive data was not masked back then, as I
recall, but it may have to be today. QA has the ability to reload the
database in case the migration to QA fails. It's important to be able to
always start from an exact copy of the production database schema.
These days, you can use multiple SQL Server Instances to save hardware
(assuming you have the necessary memory). VMs, while convenient, are
probably not ideal for performance testing. But I'm not well informed
about the capabilites of the server VM products.
If everything tested ok, the updates were escalated to production.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Thanks David.
Looking into using named instances with EE to help control costs. Will be
looking into implementing Visual SourceSafe as well.
I noticed you work for Quest. I have a few questions about the Quest
Central if you're open to them. Please email me if so.
Thanks
Jerry
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23YygmKIaFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Jerry Spivey wrote:
> At most companies where I've worked in the past, we had development
> servers that were used strictly for development. They generally contained
> stripped down data from the production databases with any customer
> sensitive data masked. Lead developers generally had dbo rights and may
> even have admin rights depending on the size of the company. Version
> control software was used for all object changes. Initial application
> testing was done on the dev servers.
> QA/Test servers were not managed by development. They were usually owned
> by QA group. We would provide detailed scripts to update QA servers with
> the necessary changes. Users would test the applications on QA servers. QA
> servers have data that more closely mimics production in terms of data
> value distribution and quantity and may even be created from production
> backups. Sensitive data was not masked back then, as I recall, but it may
> have to be today. QA has the ability to reload the database in case the
> migration to QA fails. It's important to be able to always start from an
> exact copy of the production database schema.
> These days, you can use multiple SQL Server Instances to save hardware
> (assuming you have the necessary memory). VMs, while convenient, are
> probably not ideal for performance testing. But I'm not well informed
> about the capabilites of the server VM products.
> If everything tested ok, the updates were escalated to production.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||Jerry Spivey wrote:
> Thanks David.
> Looking into using named instances with EE to help control costs. Will
> be looking into implementing Visual SourceSafe as well.
> I noticed you work for Quest. I have a few questions about the Quest
> Central if you're open to them. Please email me if so.
> Thanks
> Jerry
>
The best way for you to get information and help with the Quest product
line is to contact sales. Our offices and numbers are located here:
http://www.quest.com/company/us_offices.asp
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Change management is the term.
http://www.innovartis.co.uk/pdf/Inno...ange_Mgt. pdf
This is a white paper on the subject using Source Control (Visual Source
Safe) as the back bone of the approach. The application DB Ghost
(www.dbghost.com) was built using this methodology.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Jerry Spivey" wrote:
> Hi,
> I've been given the task of creating a dev/test environment. Currently we
> have several production applications using databases on a common SQL Server.
> If changes are required, the developers are performing the changes on the
> production system - yea I know BAD,BAD,BAD - but I didn't set this up but
> instead inherited it. I'd like to configure a dev/test environment to move
> the devs off of the production system and to faciliate their development of
> future projects coming up soon.
>
> How is your dev/test environment configured? I'm looking for a few examples
> here that I can work with to implement our dev/test based on our budget and
> system capabilities.
> I.e., Each dev has their own sandbox or each dev shares a common dev env -
> then changes are implemented to test env (and by who - dev or dba - and
> how - scripts) etc... then scripted to deploy on prod systems etc... also
> we image/ghost systems or use VMs and also we use Visual SourceSafe to...
> Trying to come up with a solid game plan here.
> Thanks for any input.
> Jerry
>
>
I've been given the task of creating a dev/test environment. Currently we
have several production applications using databases on a common SQL Server.
If changes are required, the developers are performing the changes on the
production system - yea I know BAD,BAD,BAD - but I didn't set this up but
instead inherited it. I'd like to configure a dev/test environment to move
the devs off of the production system and to faciliate their development of
future projects coming up soon.
How is your dev/test environment configured? I'm looking for a few examples
here that I can work with to implement our dev/test based on our budget and
system capabilities.
I.e., Each dev has their own sandbox or each dev shares a common dev env -
then changes are implemented to test env (and by who - dev or dba - and
how - scripts) etc... then scripted to deploy on prod systems etc... also
we image/ghost systems or use VMs and also we use Visual SourceSafe to...
Trying to come up with a solid game plan here.
Thanks for any input.
Jerry
Jerry Spivey wrote:
> Hi,
> I've been given the task of creating a dev/test environment. Currently
> we have several production applications using databases on
> a common SQL Server. If changes are required, the developers are
> performing the changes on the production system - yea I know
> BAD,BAD,BAD - but I didn't set this up but instead inherited it. I'd
> like to configure a dev/test environment to move the devs off of the
> production system and to faciliate their development of future
> projects coming up soon.
> How is your dev/test environment configured? I'm looking for a few
> examples here that I can work with to implement our dev/test based on
> our budget and system capabilities.
> I.e., Each dev has their own sandbox or each dev shares a common dev
> env - then changes are implemented to test env (and by who - dev or
> dba - and how - scripts) etc... then scripted to deploy on prod
> systems etc... also we image/ghost systems or use VMs and also we use
> Visual
> SourceSafe to...
> Trying to come up with a solid game plan here.
> Thanks for any input.
> Jerry
At most companies where I've worked in the past, we had development
servers that were used strictly for development. They generally
contained stripped down data from the production databases with any
customer sensitive data masked. Lead developers generally had dbo rights
and may even have admin rights depending on the size of the company.
Version control software was used for all object changes. Initial
application testing was done on the dev servers.
QA/Test servers were not managed by development. They were usually owned
by QA group. We would provide detailed scripts to update QA servers with
the necessary changes. Users would test the applications on QA servers.
QA servers have data that more closely mimics production in terms of
data value distribution and quantity and may even be created from
production backups. Sensitive data was not masked back then, as I
recall, but it may have to be today. QA has the ability to reload the
database in case the migration to QA fails. It's important to be able to
always start from an exact copy of the production database schema.
These days, you can use multiple SQL Server Instances to save hardware
(assuming you have the necessary memory). VMs, while convenient, are
probably not ideal for performance testing. But I'm not well informed
about the capabilites of the server VM products.
If everything tested ok, the updates were escalated to production.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Thanks David.
Looking into using named instances with EE to help control costs. Will be
looking into implementing Visual SourceSafe as well.
I noticed you work for Quest. I have a few questions about the Quest
Central if you're open to them. Please email me if so.
Thanks
Jerry
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23YygmKIaFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Jerry Spivey wrote:
> At most companies where I've worked in the past, we had development
> servers that were used strictly for development. They generally contained
> stripped down data from the production databases with any customer
> sensitive data masked. Lead developers generally had dbo rights and may
> even have admin rights depending on the size of the company. Version
> control software was used for all object changes. Initial application
> testing was done on the dev servers.
> QA/Test servers were not managed by development. They were usually owned
> by QA group. We would provide detailed scripts to update QA servers with
> the necessary changes. Users would test the applications on QA servers. QA
> servers have data that more closely mimics production in terms of data
> value distribution and quantity and may even be created from production
> backups. Sensitive data was not masked back then, as I recall, but it may
> have to be today. QA has the ability to reload the database in case the
> migration to QA fails. It's important to be able to always start from an
> exact copy of the production database schema.
> These days, you can use multiple SQL Server Instances to save hardware
> (assuming you have the necessary memory). VMs, while convenient, are
> probably not ideal for performance testing. But I'm not well informed
> about the capabilites of the server VM products.
> If everything tested ok, the updates were escalated to production.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||Jerry Spivey wrote:
> Thanks David.
> Looking into using named instances with EE to help control costs. Will
> be looking into implementing Visual SourceSafe as well.
> I noticed you work for Quest. I have a few questions about the Quest
> Central if you're open to them. Please email me if so.
> Thanks
> Jerry
>
The best way for you to get information and help with the Quest product
line is to contact sales. Our offices and numbers are located here:
http://www.quest.com/company/us_offices.asp
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Change management is the term.
http://www.innovartis.co.uk/pdf/Inno...ange_Mgt. pdf
This is a white paper on the subject using Source Control (Visual Source
Safe) as the back bone of the approach. The application DB Ghost
(www.dbghost.com) was built using this methodology.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Jerry Spivey" wrote:
> Hi,
> I've been given the task of creating a dev/test environment. Currently we
> have several production applications using databases on a common SQL Server.
> If changes are required, the developers are performing the changes on the
> production system - yea I know BAD,BAD,BAD - but I didn't set this up but
> instead inherited it. I'd like to configure a dev/test environment to move
> the devs off of the production system and to faciliate their development of
> future projects coming up soon.
>
> How is your dev/test environment configured? I'm looking for a few examples
> here that I can work with to implement our dev/test based on our budget and
> system capabilities.
> I.e., Each dev has their own sandbox or each dev shares a common dev env -
> then changes are implemented to test env (and by who - dev or dba - and
> how - scripts) etc... then scripted to deploy on prod systems etc... also
> we image/ghost systems or use VMs and also we use Visual SourceSafe to...
> Trying to come up with a solid game plan here.
> Thanks for any input.
> Jerry
>
>
Development - Test Environment Questions
Hi,
I've been given the task of creating a dev/test environment. Currently we
have several production applications using databases on a common SQL Server.
If changes are required, the developers are performing the changes on the
production system - yea I know BAD,BAD,BAD - but I didn't set this up but
instead inherited it. I'd like to configure a dev/test environment to move
the devs off of the production system and to faciliate their development of
future projects coming up soon.
How is your dev/test environment configured? I'm looking for a few examples
here that I can work with to implement our dev/test based on our budget and
system capabilities.
I.e., Each dev has their own sandbox or each dev shares a common dev env -
then changes are implemented to test env (and by who - dev or dba - and
how - scripts) etc... then scripted to deploy on prod systems etc... also
we image/ghost systems or use VMs and also we use Visual SourceSafe to...
Trying to come up with a solid game plan here.
Thanks for any input.
JerryJerry Spivey wrote:
> Hi,
> I've been given the task of creating a dev/test environment. Currently
> we have several production applications using databases on
> a common SQL Server. If changes are required, the developers are
> performing the changes on the production system - yea I know
> BAD,BAD,BAD - but I didn't set this up but instead inherited it. I'd
> like to configure a dev/test environment to move the devs off of the
> production system and to faciliate their development of future
> projects coming up soon.
> How is your dev/test environment configured? I'm looking for a few
> examples here that I can work with to implement our dev/test based on
> our budget and system capabilities.
> I.e., Each dev has their own sandbox or each dev shares a common dev
> env - then changes are implemented to test env (and by who - dev or
> dba - and how - scripts) etc... then scripted to deploy on prod
> systems etc... also we image/ghost systems or use VMs and also we use
> Visual
> SourceSafe to...
> Trying to come up with a solid game plan here.
> Thanks for any input.
> Jerry
At most companies where I've worked in the past, we had development
servers that were used strictly for development. They generally
contained stripped down data from the production databases with any
customer sensitive data masked. Lead developers generally had dbo rights
and may even have admin rights depending on the size of the company.
Version control software was used for all object changes. Initial
application testing was done on the dev servers.
QA/Test servers were not managed by development. They were usually owned
by QA group. We would provide detailed scripts to update QA servers with
the necessary changes. Users would test the applications on QA servers.
QA servers have data that more closely mimics production in terms of
data value distribution and quantity and may even be created from
production backups. Sensitive data was not masked back then, as I
recall, but it may have to be today. QA has the ability to reload the
database in case the migration to QA fails. It's important to be able to
always start from an exact copy of the production database schema.
These days, you can use multiple SQL Server Instances to save hardware
(assuming you have the necessary memory). VMs, while convenient, are
probably not ideal for performance testing. But I'm not well informed
about the capabilites of the server VM products.
If everything tested ok, the updates were escalated to production.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks David.
Looking into using named instances with EE to help control costs. Will be
looking into implementing Visual SourceSafe as well.
I noticed you work for Quest. I have a few questions about the Quest
Central if you're open to them. Please email me if so.
Thanks
Jerry
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23YygmKIaFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Jerry Spivey wrote:
>> Hi,
>> I've been given the task of creating a dev/test environment. Currently we
>> have several production applications using databases on
>> a common SQL Server. If changes are required, the developers are
>> performing the changes on the production system - yea I know
>> BAD,BAD,BAD - but I didn't set this up but instead inherited it. I'd
>> like to configure a dev/test environment to move the devs off of the
>> production system and to faciliate their development of future
>> projects coming up soon.
>> How is your dev/test environment configured? I'm looking for a few
>> examples here that I can work with to implement our dev/test based on
>> our budget and system capabilities.
>> I.e., Each dev has their own sandbox or each dev shares a common dev
>> env - then changes are implemented to test env (and by who - dev or
>> dba - and how - scripts) etc... then scripted to deploy on prod systems
>> etc... also we image/ghost systems or use VMs and also we use Visual
>> SourceSafe to...
>> Trying to come up with a solid game plan here.
>> Thanks for any input.
>> Jerry
> At most companies where I've worked in the past, we had development
> servers that were used strictly for development. They generally contained
> stripped down data from the production databases with any customer
> sensitive data masked. Lead developers generally had dbo rights and may
> even have admin rights depending on the size of the company. Version
> control software was used for all object changes. Initial application
> testing was done on the dev servers.
> QA/Test servers were not managed by development. They were usually owned
> by QA group. We would provide detailed scripts to update QA servers with
> the necessary changes. Users would test the applications on QA servers. QA
> servers have data that more closely mimics production in terms of data
> value distribution and quantity and may even be created from production
> backups. Sensitive data was not masked back then, as I recall, but it may
> have to be today. QA has the ability to reload the database in case the
> migration to QA fails. It's important to be able to always start from an
> exact copy of the production database schema.
> These days, you can use multiple SQL Server Instances to save hardware
> (assuming you have the necessary memory). VMs, while convenient, are
> probably not ideal for performance testing. But I'm not well informed
> about the capabilites of the server VM products.
> If everything tested ok, the updates were escalated to production.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Jerry Spivey wrote:
> Thanks David.
> Looking into using named instances with EE to help control costs. Will
> be looking into implementing Visual SourceSafe as well.
> I noticed you work for Quest. I have a few questions about the Quest
> Central if you're open to them. Please email me if so.
> Thanks
> Jerry
>
The best way for you to get information and help with the Quest product
line is to contact sales. Our offices and numbers are located here:
http://www.quest.com/company/us_offices.asp
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Change management is the term.
http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Approach_To_Do_Change_Mgt.pdf
This is a white paper on the subject using Source Control (Visual Source
Safe) as the back bone of the approach. The application DB Ghost
(www.dbghost.com) was built using this methodology.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Jerry Spivey" wrote:
> Hi,
> I've been given the task of creating a dev/test environment. Currently we
> have several production applications using databases on a common SQL Server.
> If changes are required, the developers are performing the changes on the
> production system - yea I know BAD,BAD,BAD - but I didn't set this up but
> instead inherited it. I'd like to configure a dev/test environment to move
> the devs off of the production system and to faciliate their development of
> future projects coming up soon.
>
> How is your dev/test environment configured? I'm looking for a few examples
> here that I can work with to implement our dev/test based on our budget and
> system capabilities.
> I.e., Each dev has their own sandbox or each dev shares a common dev env -
> then changes are implemented to test env (and by who - dev or dba - and
> how - scripts) etc... then scripted to deploy on prod systems etc... also
> we image/ghost systems or use VMs and also we use Visual SourceSafe to...
> Trying to come up with a solid game plan here.
> Thanks for any input.
> Jerry
>
>
I've been given the task of creating a dev/test environment. Currently we
have several production applications using databases on a common SQL Server.
If changes are required, the developers are performing the changes on the
production system - yea I know BAD,BAD,BAD - but I didn't set this up but
instead inherited it. I'd like to configure a dev/test environment to move
the devs off of the production system and to faciliate their development of
future projects coming up soon.
How is your dev/test environment configured? I'm looking for a few examples
here that I can work with to implement our dev/test based on our budget and
system capabilities.
I.e., Each dev has their own sandbox or each dev shares a common dev env -
then changes are implemented to test env (and by who - dev or dba - and
how - scripts) etc... then scripted to deploy on prod systems etc... also
we image/ghost systems or use VMs and also we use Visual SourceSafe to...
Trying to come up with a solid game plan here.
Thanks for any input.
JerryJerry Spivey wrote:
> Hi,
> I've been given the task of creating a dev/test environment. Currently
> we have several production applications using databases on
> a common SQL Server. If changes are required, the developers are
> performing the changes on the production system - yea I know
> BAD,BAD,BAD - but I didn't set this up but instead inherited it. I'd
> like to configure a dev/test environment to move the devs off of the
> production system and to faciliate their development of future
> projects coming up soon.
> How is your dev/test environment configured? I'm looking for a few
> examples here that I can work with to implement our dev/test based on
> our budget and system capabilities.
> I.e., Each dev has their own sandbox or each dev shares a common dev
> env - then changes are implemented to test env (and by who - dev or
> dba - and how - scripts) etc... then scripted to deploy on prod
> systems etc... also we image/ghost systems or use VMs and also we use
> Visual
> SourceSafe to...
> Trying to come up with a solid game plan here.
> Thanks for any input.
> Jerry
At most companies where I've worked in the past, we had development
servers that were used strictly for development. They generally
contained stripped down data from the production databases with any
customer sensitive data masked. Lead developers generally had dbo rights
and may even have admin rights depending on the size of the company.
Version control software was used for all object changes. Initial
application testing was done on the dev servers.
QA/Test servers were not managed by development. They were usually owned
by QA group. We would provide detailed scripts to update QA servers with
the necessary changes. Users would test the applications on QA servers.
QA servers have data that more closely mimics production in terms of
data value distribution and quantity and may even be created from
production backups. Sensitive data was not masked back then, as I
recall, but it may have to be today. QA has the ability to reload the
database in case the migration to QA fails. It's important to be able to
always start from an exact copy of the production database schema.
These days, you can use multiple SQL Server Instances to save hardware
(assuming you have the necessary memory). VMs, while convenient, are
probably not ideal for performance testing. But I'm not well informed
about the capabilites of the server VM products.
If everything tested ok, the updates were escalated to production.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks David.
Looking into using named instances with EE to help control costs. Will be
looking into implementing Visual SourceSafe as well.
I noticed you work for Quest. I have a few questions about the Quest
Central if you're open to them. Please email me if so.
Thanks
Jerry
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23YygmKIaFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Jerry Spivey wrote:
>> Hi,
>> I've been given the task of creating a dev/test environment. Currently we
>> have several production applications using databases on
>> a common SQL Server. If changes are required, the developers are
>> performing the changes on the production system - yea I know
>> BAD,BAD,BAD - but I didn't set this up but instead inherited it. I'd
>> like to configure a dev/test environment to move the devs off of the
>> production system and to faciliate their development of future
>> projects coming up soon.
>> How is your dev/test environment configured? I'm looking for a few
>> examples here that I can work with to implement our dev/test based on
>> our budget and system capabilities.
>> I.e., Each dev has their own sandbox or each dev shares a common dev
>> env - then changes are implemented to test env (and by who - dev or
>> dba - and how - scripts) etc... then scripted to deploy on prod systems
>> etc... also we image/ghost systems or use VMs and also we use Visual
>> SourceSafe to...
>> Trying to come up with a solid game plan here.
>> Thanks for any input.
>> Jerry
> At most companies where I've worked in the past, we had development
> servers that were used strictly for development. They generally contained
> stripped down data from the production databases with any customer
> sensitive data masked. Lead developers generally had dbo rights and may
> even have admin rights depending on the size of the company. Version
> control software was used for all object changes. Initial application
> testing was done on the dev servers.
> QA/Test servers were not managed by development. They were usually owned
> by QA group. We would provide detailed scripts to update QA servers with
> the necessary changes. Users would test the applications on QA servers. QA
> servers have data that more closely mimics production in terms of data
> value distribution and quantity and may even be created from production
> backups. Sensitive data was not masked back then, as I recall, but it may
> have to be today. QA has the ability to reload the database in case the
> migration to QA fails. It's important to be able to always start from an
> exact copy of the production database schema.
> These days, you can use multiple SQL Server Instances to save hardware
> (assuming you have the necessary memory). VMs, while convenient, are
> probably not ideal for performance testing. But I'm not well informed
> about the capabilites of the server VM products.
> If everything tested ok, the updates were escalated to production.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Jerry Spivey wrote:
> Thanks David.
> Looking into using named instances with EE to help control costs. Will
> be looking into implementing Visual SourceSafe as well.
> I noticed you work for Quest. I have a few questions about the Quest
> Central if you're open to them. Please email me if so.
> Thanks
> Jerry
>
The best way for you to get information and help with the Quest product
line is to contact sales. Our offices and numbers are located here:
http://www.quest.com/company/us_offices.asp
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Change management is the term.
http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Approach_To_Do_Change_Mgt.pdf
This is a white paper on the subject using Source Control (Visual Source
Safe) as the back bone of the approach. The application DB Ghost
(www.dbghost.com) was built using this methodology.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Jerry Spivey" wrote:
> Hi,
> I've been given the task of creating a dev/test environment. Currently we
> have several production applications using databases on a common SQL Server.
> If changes are required, the developers are performing the changes on the
> production system - yea I know BAD,BAD,BAD - but I didn't set this up but
> instead inherited it. I'd like to configure a dev/test environment to move
> the devs off of the production system and to faciliate their development of
> future projects coming up soon.
>
> How is your dev/test environment configured? I'm looking for a few examples
> here that I can work with to implement our dev/test based on our budget and
> system capabilities.
> I.e., Each dev has their own sandbox or each dev shares a common dev env -
> then changes are implemented to test env (and by who - dev or dba - and
> how - scripts) etc... then scripted to deploy on prod systems etc... also
> we image/ghost systems or use VMs and also we use Visual SourceSafe to...
> Trying to come up with a solid game plan here.
> Thanks for any input.
> Jerry
>
>
Development - Test Environment Questions
Hi,
I've been given the task of creating a dev/test environment. Currently we
have several production applications using databases on a common SQL Server.
If changes are required, the developers are performing the changes on the
production system - yea I know BAD,BAD,BAD - but I didn't set this up but
instead inherited it. I'd like to configure a dev/test environment to move
the devs off of the production system and to faciliate their development of
future projects coming up soon.
How is your dev/test environment configured? I'm looking for a few examples
here that I can work with to implement our dev/test based on our budget and
system capabilities.
I.e., Each dev has their own sandbox or each dev shares a common dev env -
then changes are implemented to test env (and by who - dev or dba - and
how - scripts) etc... then scripted to deploy on prod systems etc... also
we image/ghost systems or use VMs and also we use Visual SourceSafe to...
Trying to come up with a solid game plan here.
Thanks for any input.
JerryJerry Spivey wrote:
> Hi,
> I've been given the task of creating a dev/test environment. Currently
> we have several production applications using databases on
> a common SQL Server. If changes are required, the developers are
> performing the changes on the production system - yea I know
> BAD,BAD,BAD - but I didn't set this up but instead inherited it. I'd
> like to configure a dev/test environment to move the devs off of the
> production system and to faciliate their development of future
> projects coming up soon.
> How is your dev/test environment configured? I'm looking for a few
> examples here that I can work with to implement our dev/test based on
> our budget and system capabilities.
> I.e., Each dev has their own sandbox or each dev shares a common dev
> env - then changes are implemented to test env (and by who - dev or
> dba - and how - scripts) etc... then scripted to deploy on prod
> systems etc... also we image/ghost systems or use VMs and also we use
> Visual
> SourceSafe to...
> Trying to come up with a solid game plan here.
> Thanks for any input.
> Jerry
At most companies where I've worked in the past, we had development
servers that were used strictly for development. They generally
contained stripped down data from the production databases with any
customer sensitive data masked. Lead developers generally had dbo rights
and may even have admin rights depending on the size of the company.
Version control software was used for all object changes. Initial
application testing was done on the dev servers.
QA/Test servers were not managed by development. They were usually owned
by QA group. We would provide detailed scripts to update QA servers with
the necessary changes. Users would test the applications on QA servers.
QA servers have data that more closely mimics production in terms of
data value distribution and quantity and may even be created from
production backups. Sensitive data was not masked back then, as I
recall, but it may have to be today. QA has the ability to reload the
database in case the migration to QA fails. It's important to be able to
always start from an exact copy of the production database schema.
These days, you can use multiple SQL Server Instances to save hardware
(assuming you have the necessary memory). VMs, while convenient, are
probably not ideal for performance testing. But I'm not well informed
about the capabilites of the server VM products.
If everything tested ok, the updates were escalated to production.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks David.
Looking into using named instances with EE to help control costs. Will be
looking into implementing Visual SourceSafe as well.
I noticed you work for Quest. I have a few questions about the Quest
Central if you're open to them. Please email me if so.
Thanks
Jerry
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23YygmKIaFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Jerry Spivey wrote:
> At most companies where I've worked in the past, we had development
> servers that were used strictly for development. They generally contained
> stripped down data from the production databases with any customer
> sensitive data masked. Lead developers generally had dbo rights and may
> even have admin rights depending on the size of the company. Version
> control software was used for all object changes. Initial application
> testing was done on the dev servers.
> QA/Test servers were not managed by development. They were usually owned
> by QA group. We would provide detailed scripts to update QA servers with
> the necessary changes. Users would test the applications on QA servers. QA
> servers have data that more closely mimics production in terms of data
> value distribution and quantity and may even be created from production
> backups. Sensitive data was not masked back then, as I recall, but it may
> have to be today. QA has the ability to reload the database in case the
> migration to QA fails. It's important to be able to always start from an
> exact copy of the production database schema.
> These days, you can use multiple SQL Server Instances to save hardware
> (assuming you have the necessary memory). VMs, while convenient, are
> probably not ideal for performance testing. But I'm not well informed
> about the capabilites of the server VM products.
> If everything tested ok, the updates were escalated to production.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Jerry Spivey wrote:
> Thanks David.
> Looking into using named instances with EE to help control costs. Will
> be looking into implementing Visual SourceSafe as well.
> I noticed you work for Quest. I have a few questions about the Quest
> Central if you're open to them. Please email me if so.
> Thanks
> Jerry
>
The best way for you to get information and help with the Quest product
line is to contact sales. Our offices and numbers are located here:
http://www.quest.com/company/us_offices.asp
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Change management is the term.
hange_Mgt.
pdf" target="_blank">http://www.innovartis.co.uk/pdf/ In...Mgt.
pdf
This is a white paper on the subject using Source Control (Visual Source
Safe) as the back bone of the approach. The application DB Ghost
(www.dbghost.com) was built using this methodology.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Jerry Spivey" wrote:
> Hi,
> I've been given the task of creating a dev/test environment. Currently we
> have several production applications using databases on a common SQL Serve
r.
> If changes are required, the developers are performing the changes on the
> production system - yea I know BAD,BAD,BAD - but I didn't set this up but
> instead inherited it. I'd like to configure a dev/test environment to mov
e
> the devs off of the production system and to faciliate their development o
f
> future projects coming up soon.
>
> How is your dev/test environment configured? I'm looking for a few exampl
es
> here that I can work with to implement our dev/test based on our budget an
d
> system capabilities.
> I.e., Each dev has their own sandbox or each dev shares a common dev env -
> then changes are implemented to test env (and by who - dev or dba - and
> how - scripts) etc... then scripted to deploy on prod systems etc... also
> we image/ghost systems or use VMs and also we use Visual SourceSafe to...
> Trying to come up with a solid game plan here.
> Thanks for any input.
> Jerry
>
>
I've been given the task of creating a dev/test environment. Currently we
have several production applications using databases on a common SQL Server.
If changes are required, the developers are performing the changes on the
production system - yea I know BAD,BAD,BAD - but I didn't set this up but
instead inherited it. I'd like to configure a dev/test environment to move
the devs off of the production system and to faciliate their development of
future projects coming up soon.
How is your dev/test environment configured? I'm looking for a few examples
here that I can work with to implement our dev/test based on our budget and
system capabilities.
I.e., Each dev has their own sandbox or each dev shares a common dev env -
then changes are implemented to test env (and by who - dev or dba - and
how - scripts) etc... then scripted to deploy on prod systems etc... also
we image/ghost systems or use VMs and also we use Visual SourceSafe to...
Trying to come up with a solid game plan here.
Thanks for any input.
JerryJerry Spivey wrote:
> Hi,
> I've been given the task of creating a dev/test environment. Currently
> we have several production applications using databases on
> a common SQL Server. If changes are required, the developers are
> performing the changes on the production system - yea I know
> BAD,BAD,BAD - but I didn't set this up but instead inherited it. I'd
> like to configure a dev/test environment to move the devs off of the
> production system and to faciliate their development of future
> projects coming up soon.
> How is your dev/test environment configured? I'm looking for a few
> examples here that I can work with to implement our dev/test based on
> our budget and system capabilities.
> I.e., Each dev has their own sandbox or each dev shares a common dev
> env - then changes are implemented to test env (and by who - dev or
> dba - and how - scripts) etc... then scripted to deploy on prod
> systems etc... also we image/ghost systems or use VMs and also we use
> Visual
> SourceSafe to...
> Trying to come up with a solid game plan here.
> Thanks for any input.
> Jerry
At most companies where I've worked in the past, we had development
servers that were used strictly for development. They generally
contained stripped down data from the production databases with any
customer sensitive data masked. Lead developers generally had dbo rights
and may even have admin rights depending on the size of the company.
Version control software was used for all object changes. Initial
application testing was done on the dev servers.
QA/Test servers were not managed by development. They were usually owned
by QA group. We would provide detailed scripts to update QA servers with
the necessary changes. Users would test the applications on QA servers.
QA servers have data that more closely mimics production in terms of
data value distribution and quantity and may even be created from
production backups. Sensitive data was not masked back then, as I
recall, but it may have to be today. QA has the ability to reload the
database in case the migration to QA fails. It's important to be able to
always start from an exact copy of the production database schema.
These days, you can use multiple SQL Server Instances to save hardware
(assuming you have the necessary memory). VMs, while convenient, are
probably not ideal for performance testing. But I'm not well informed
about the capabilites of the server VM products.
If everything tested ok, the updates were escalated to production.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks David.
Looking into using named instances with EE to help control costs. Will be
looking into implementing Visual SourceSafe as well.
I noticed you work for Quest. I have a few questions about the Quest
Central if you're open to them. Please email me if so.
Thanks
Jerry
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23YygmKIaFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Jerry Spivey wrote:
> At most companies where I've worked in the past, we had development
> servers that were used strictly for development. They generally contained
> stripped down data from the production databases with any customer
> sensitive data masked. Lead developers generally had dbo rights and may
> even have admin rights depending on the size of the company. Version
> control software was used for all object changes. Initial application
> testing was done on the dev servers.
> QA/Test servers were not managed by development. They were usually owned
> by QA group. We would provide detailed scripts to update QA servers with
> the necessary changes. Users would test the applications on QA servers. QA
> servers have data that more closely mimics production in terms of data
> value distribution and quantity and may even be created from production
> backups. Sensitive data was not masked back then, as I recall, but it may
> have to be today. QA has the ability to reload the database in case the
> migration to QA fails. It's important to be able to always start from an
> exact copy of the production database schema.
> These days, you can use multiple SQL Server Instances to save hardware
> (assuming you have the necessary memory). VMs, while convenient, are
> probably not ideal for performance testing. But I'm not well informed
> about the capabilites of the server VM products.
> If everything tested ok, the updates were escalated to production.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Jerry Spivey wrote:
> Thanks David.
> Looking into using named instances with EE to help control costs. Will
> be looking into implementing Visual SourceSafe as well.
> I noticed you work for Quest. I have a few questions about the Quest
> Central if you're open to them. Please email me if so.
> Thanks
> Jerry
>
The best way for you to get information and help with the Quest product
line is to contact sales. Our offices and numbers are located here:
http://www.quest.com/company/us_offices.asp
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Change management is the term.
hange_Mgt.
pdf" target="_blank">http://www.innovartis.co.uk/pdf/ In...Mgt.
This is a white paper on the subject using Source Control (Visual Source
Safe) as the back bone of the approach. The application DB Ghost
(www.dbghost.com) was built using this methodology.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Jerry Spivey" wrote:
> Hi,
> I've been given the task of creating a dev/test environment. Currently we
> have several production applications using databases on a common SQL Serve
r.
> If changes are required, the developers are performing the changes on the
> production system - yea I know BAD,BAD,BAD - but I didn't set this up but
> instead inherited it. I'd like to configure a dev/test environment to mov
e
> the devs off of the production system and to faciliate their development o
f
> future projects coming up soon.
>
> How is your dev/test environment configured? I'm looking for a few exampl
es
> here that I can work with to implement our dev/test based on our budget an
d
> system capabilities.
> I.e., Each dev has their own sandbox or each dev shares a common dev env -
> then changes are implemented to test env (and by who - dev or dba - and
> how - scripts) etc... then scripted to deploy on prod systems etc... also
> we image/ghost systems or use VMs and also we use Visual SourceSafe to...
> Trying to come up with a solid game plan here.
> Thanks for any input.
> Jerry
>
>
Sunday, February 19, 2012
developer edition
I'm not sure of my limitations using the dev edition. I want to test my
Access front end across a LAN. Can I do this or is this creating a
production environment that exceeds the limit of the licensing.Hi,
Developer Edition is licensed for use only as a development and test system,
not a production server.
Developer edition is used by programmers for developing applications that
use SQL Server 2000 as their data store. Although the Developer Edition
supports all the features of the Enterprise Edition that allow developers to
write and test applications that can use the features.
Thanks
Hari
SQL Server Mvp
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:4286A62E-4771-4224-835D-E1B13976CDB3@.microsoft.com...
> I'm not sure of my limitations using the dev edition. I want to test my
> Access front end across a LAN. Can I do this or is this creating a
> production environment that exceeds the limit of the licensing.
Access front end across a LAN. Can I do this or is this creating a
production environment that exceeds the limit of the licensing.Hi,
Developer Edition is licensed for use only as a development and test system,
not a production server.
Developer edition is used by programmers for developing applications that
use SQL Server 2000 as their data store. Although the Developer Edition
supports all the features of the Enterprise Edition that allow developers to
write and test applications that can use the features.
Thanks
Hari
SQL Server Mvp
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:4286A62E-4771-4224-835D-E1B13976CDB3@.microsoft.com...
> I'm not sure of my limitations using the dev edition. I want to test my
> Access front end across a LAN. Can I do this or is this creating a
> production environment that exceeds the limit of the licensing.
developer edition
I'm not sure of my limitations using the dev edition. I want to test my
Access front end across a LAN. Can I do this or is this creating a
production environment that exceeds the limit of the licensing.
Hi,
Developer Edition is licensed for use only as a development and test system,
not a production server.
Developer edition is used by programmers for developing applications that
use SQL Server 2000 as their data store. Although the Developer Edition
supports all the features of the Enterprise Edition that allow developers to
write and test applications that can use the features.
Thanks
Hari
SQL Server Mvp
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:4286A62E-4771-4224-835D-E1B13976CDB3@.microsoft.com...
> I'm not sure of my limitations using the dev edition. I want to test my
> Access front end across a LAN. Can I do this or is this creating a
> production environment that exceeds the limit of the licensing.
Access front end across a LAN. Can I do this or is this creating a
production environment that exceeds the limit of the licensing.
Hi,
Developer Edition is licensed for use only as a development and test system,
not a production server.
Developer edition is used by programmers for developing applications that
use SQL Server 2000 as their data store. Although the Developer Edition
supports all the features of the Enterprise Edition that allow developers to
write and test applications that can use the features.
Thanks
Hari
SQL Server Mvp
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:4286A62E-4771-4224-835D-E1B13976CDB3@.microsoft.com...
> I'm not sure of my limitations using the dev edition. I want to test my
> Access front end across a LAN. Can I do this or is this creating a
> production environment that exceeds the limit of the licensing.
developer edition
I'm not sure of my limitations using the dev edition. I want to test my
Access front end across a LAN. Can I do this or is this creating a
production environment that exceeds the limit of the licensing.Hi,
Developer Edition is licensed for use only as a development and test system,
not a production server.
Developer edition is used by programmers for developing applications that
use SQL Server 2000 as their data store. Although the Developer Edition
supports all the features of the Enterprise Edition that allow developers to
write and test applications that can use the features.
Thanks
Hari
SQL Server Mvp
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:4286A62E-4771-4224-835D-E1B13976CDB3@.microsoft.com...
> I'm not sure of my limitations using the dev edition. I want to test my
> Access front end across a LAN. Can I do this or is this creating a
> production environment that exceeds the limit of the licensing.
Access front end across a LAN. Can I do this or is this creating a
production environment that exceeds the limit of the licensing.Hi,
Developer Edition is licensed for use only as a development and test system,
not a production server.
Developer edition is used by programmers for developing applications that
use SQL Server 2000 as their data store. Although the Developer Edition
supports all the features of the Enterprise Edition that allow developers to
write and test applications that can use the features.
Thanks
Hari
SQL Server Mvp
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:4286A62E-4771-4224-835D-E1B13976CDB3@.microsoft.com...
> I'm not sure of my limitations using the dev edition. I want to test my
> Access front end across a LAN. Can I do this or is this creating a
> production environment that exceeds the limit of the licensing.
Subscribe to:
Posts (Atom)