Database Role & Application

What is the basic difference between these roles..
I know as per books but how it can be used in real life. We have all roles
defined as database roles.. where we can use app roleDave,
here are some good articles:
http://www.databasejournal.com/feat...cle.php/3363521
http://www.sqlteam.com/item.asp?ItemID=864
http://vyaskn.tripod.com/sql_server...t_practices.htm
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:1D3ECF9A-E3C5-403A-8F26-96733B267561@.microsoft.com...
> What is the basic difference between these roles..
> I know as per books but how it can be used in real life. We have all roles
> defined as database roles.. where we can use app role

Database Renaming

Can anyone suggest a quick and easy way to rename a database AND .MDF/.LDF file in SQL Server?
I can not, for the life of me, figure out a way to do this.
Help?You could try to backup database and restore it with another name like this (before restoring you may delete old database).

Old db name is rDB, new will be rrDB.

RESTORE DATABASE rrDB
FROM DISK = 'w:\rdb.bak'
WITH MOVE 'rDB_Data' TO 'w:\rrDB.mdf',
MOVE 'rDB_Log' TO 'w:\rrDB.ldf'|||Originally posted by JBoyce
Can anyone suggest a quick and easy way to rename a database AND .MDF/.LDF file in SQL Server?
I can not, for the life of me, figure out a way to do this.
Help?
You can also use sp_renamedb|||Originally posted by smasanam
You can also use sp_renamedb

But file names will be the same...|||"Alter database" has a way to rename logical files, but the physical files will remain the same. If the DB name and the filenames all have to change, then Snail has the right way to go.

Database promotion life cycle

I need to review schema (table definition, constraints, etc.) inconsistencies
between development, test and production environments.
As part of our migration process to dot net, I've identified a number of
inconsistencies that are not typical of the promotion life-cycle. These
issues appear to be the result of an inadequate promotion process, during
which many schema changes have not been propagated. Conversely, there seem
to be many schema elements in production or QA that have not gone through the
life-cycle, instead having been implemented directly, rather than promoted.
Is there a document out there that can at least be used as a template to get
my review started? I'm certain this isn't anything new to businesses across
this country.SQL WHEEL wrote:
> I need to review schema (table definition, constraints, etc.) inconsistencies
> between development, test and production environments.
> As part of our migration process to dot net, I've identified a number of
> inconsistencies that are not typical of the promotion life-cycle. These
> issues appear to be the result of an inadequate promotion process, during
> which many schema changes have not been propagated. Conversely, there seem
> to be many schema elements in production or QA that have not gone through the
> life-cycle, instead having been implemented directly, rather than promoted.
> Is there a document out there that can at least be used as a template to get
> my review started? I'm certain this isn't anything new to businesses across
> this country.
>
Couple of suggestions:
1. Use a product like SQL Compare to determine what the differences are
between your environments
2. Starting immediately, do not allow direct modification of database
objects. Every object (table, sproc, trigger, etc) should be scripted
and stored in a version control system. Future modifications and new
objects MUST be done ONLY through version control. To modify an object,
you would first "check out" the script from version control, make the
necessary modifications, check the new script in, THEN deploy to the
development environment. Scripts for new objects are checked in, then
deployed. You can then use the capabilities of your version control
system to generate a changelog, making it easier to "release"
enhancements to testing, and finally production.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I use SQL Compare already to compare DTS, schema a data. We don't have MS
Source Safe in house at present. What is done is the scripts are sent to me
and deployed in DEV. The real problem is that b/c we're on a rapid
development track to get our legacy apps on dot net, I receive scripts both
for DEV and TEST...sometimes PROD as well. That, plus our DEV/TEST
environments do not match PROD. Main reason there is b/c PROD has cross
server calls whereas DEV/TEST are self contained.
I want to put together a best practices doc at least the framework of one to
move forward from this mess. Is there a link out there where someone has
already done some leg work that I can take and modify to make it applicable
to our environment?
"Tracy McKibben" wrote:
> SQL WHEEL wrote:
> > I need to review schema (table definition, constraints, etc.) inconsistencies
> > between development, test and production environments.
> > As part of our migration process to dot net, I've identified a number of
> > inconsistencies that are not typical of the promotion life-cycle. These
> > issues appear to be the result of an inadequate promotion process, during
> > which many schema changes have not been propagated. Conversely, there seem
> > to be many schema elements in production or QA that have not gone through the
> > life-cycle, instead having been implemented directly, rather than promoted.
> >
> > Is there a document out there that can at least be used as a template to get
> > my review started? I'm certain this isn't anything new to businesses across
> > this country.
> >
> Couple of suggestions:
> 1. Use a product like SQL Compare to determine what the differences are
> between your environments
> 2. Starting immediately, do not allow direct modification of database
> objects. Every object (table, sproc, trigger, etc) should be scripted
> and stored in a version control system. Future modifications and new
> objects MUST be done ONLY through version control. To modify an object,
> you would first "check out" the script from version control, make the
> necessary modifications, check the new script in, THEN deploy to the
> development environment. Scripts for new objects are checked in, then
> deployed. You can then use the capabilities of your version control
> system to generate a changelog, making it easier to "release"
> enhancements to testing, and finally production.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||SQL WHEEL wrote:
> I use SQL Compare already to compare DTS, schema a data. We don't have MS
> Source Safe in house at present. What is done is the scripts are sent to me
> and deployed in DEV. The real problem is that b/c we're on a rapid
> development track to get our legacy apps on dot net, I receive scripts both
> for DEV and TEST...sometimes PROD as well. That, plus our DEV/TEST
> environments do not match PROD. Main reason there is b/c PROD has cross
> server calls whereas DEV/TEST are self contained.
> I want to put together a best practices doc at least the framework of one to
> move forward from this mess. Is there a link out there where someone has
> already done some leg work that I can take and modify to make it applicable
> to our environment?
>
Your first step needs to get the environments configured the same so
that you can deploy script X to dev, confirm it works, then deploy
UNMODIFIED script X to test, test it, then deploy UNMODIFIED script X to
production. Create self-referencing linked servers in Dev and Test with
the same names that Prod uses, then your scripts don't have to be modified.
Your second step needs to be to establish a FIRM rule that NOTHING goes
directly into production, the process needs to be DEV -> TEST -> PROD,
no exceptions. Until you do that, you don't have a snowball's chance of
keeping things under control.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Database promotion life cycle

I need to review schema (table definition, constraints, etc.) inconsistencie
s
between development, test and production environments.
As part of our migration process to dot net, I've identified a number of
inconsistencies that are not typical of the promotion life-cycle. These
issues appear to be the result of an inadequate promotion process, during
which many schema changes have not been propagated. Conversely, there seem
to be many schema elements in production or QA that have not gone through th
e
life-cycle, instead having been implemented directly, rather than promoted.
Is there a document out there that can at least be used as a template to get
my review started? I'm certain this isn't anything new to businesses across
this country.SQL WHEEL wrote:
> I need to review schema (table definition, constraints, etc.) inconsistenc
ies
> between development, test and production environments.
> As part of our migration process to dot net, I've identified a number of
> inconsistencies that are not typical of the promotion life-cycle. These
> issues appear to be the result of an inadequate promotion process, during
> which many schema changes have not been propagated. Conversely, there see
m
> to be many schema elements in production or QA that have not gone through
the
> life-cycle, instead having been implemented directly, rather than promoted
.
> Is there a document out there that can at least be used as a template to g
et
> my review started? I'm certain this isn't anything new to businesses acro
ss
> this country.
>
Couple of suggestions:
1. Use a product like SQL Compare to determine what the differences are
between your environments
2. Starting immediately, do not allow direct modification of database
objects. Every object (table, sproc, trigger, etc) should be scripted
and stored in a version control system. Future modifications and new
objects MUST be done ONLY through version control. To modify an object,
you would first "check out" the script from version control, make the
necessary modifications, check the new script in, THEN deploy to the
development environment. Scripts for new objects are checked in, then
deployed. You can then use the capabilities of your version control
system to generate a changelog, making it easier to "release"
enhancements to testing, and finally production.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I use SQL Compare already to compare DTS, schema a data. We don't have MS
Source Safe in house at present. What is done is the scripts are sent to me
and deployed in DEV. The real problem is that b/c we're on a rapid
development track to get our legacy apps on dot net, I receive scripts both
for DEV and TEST...sometimes PROD as well. That, plus our DEV/TEST
environments do not match PROD. Main reason there is b/c PROD has cross
server calls whereas DEV/TEST are self contained.
I want to put together a best practices doc at least the framework of one to
move forward from this mess. Is there a link out there where someone has
already done some leg work that I can take and modify to make it applicable
to our environment?
"Tracy McKibben" wrote:

> SQL WHEEL wrote:
> Couple of suggestions:
> 1. Use a product like SQL Compare to determine what the differences are
> between your environments
> 2. Starting immediately, do not allow direct modification of database
> objects. Every object (table, sproc, trigger, etc) should be scripted
> and stored in a version control system. Future modifications and new
> objects MUST be done ONLY through version control. To modify an object,
> you would first "check out" the script from version control, make the
> necessary modifications, check the new script in, THEN deploy to the
> development environment. Scripts for new objects are checked in, then
> deployed. You can then use the capabilities of your version control
> system to generate a changelog, making it easier to "release"
> enhancements to testing, and finally production.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||SQL WHEEL wrote:
> I use SQL Compare already to compare DTS, schema a data. We don't have MS
> Source Safe in house at present. What is done is the scripts are sent to
me
> and deployed in DEV. The real problem is that b/c we're on a rapid
> development track to get our legacy apps on dot net, I receive scripts bot
h
> for DEV and TEST...sometimes PROD as well. That, plus our DEV/TEST
> environments do not match PROD. Main reason there is b/c PROD has cross
> server calls whereas DEV/TEST are self contained.
> I want to put together a best practices doc at least the framework of one
to
> move forward from this mess. Is there a link out there where someone has
> already done some leg work that I can take and modify to make it applicabl
e
> to our environment?
>
Your first step needs to get the environments configured the same so
that you can deploy script X to dev, confirm it works, then deploy
UNMODIFIED script X to test, test it, then deploy UNMODIFIED script X to
production. Create self-referencing linked servers in Dev and Test with
the same names that Prod uses, then your scripts don't have to be modified.
Your second step needs to be to establish a FIRM rule that NOTHING goes
directly into production, the process needs to be DEV -> TEST -> PROD,
no exceptions. Until you do that, you don't have a snowball's chance of
keeping things under control.
Tracy McKibben
MCDBA
http://www.realsqlguy.com