moving clustered indexes

Hi,
I have quite a few clustered indexes that I need to move theminto a new file
created in a new filegroup. How can I move them without losing any data?
regards,
Hi,
Data are contained in the leaf pages of the clustered index, moving the
clustered index moves the data
to the new file group. So you cannot move the clustered index alone.
How to move the index to a new file group
create clustered index idx_clus on Table (Column) with drop existing
on new_file_group
Thanks
Hari
MCDBA
"aoxpsql" <anonymous@.discussion.com> wrote in message
news:#$PdXIGdEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> I have quite a few clustered indexes that I need to move theminto a new
file
> created in a new filegroup. How can I move them without losing any data?
> regards,
>
|||Hari is correct in the how-to... Be aware that this process may take a long
time... It will write to the transaction log, so be backing up the log
during the process to keep the log from growing huge... And users will be
locked out of the tables during the process.
Backup everything both before and after, just to be safe..
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
"aoxpsql" <anonymous@.discussion.com> wrote in message
news:%23$PdXIGdEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> I have quite a few clustered indexes that I need to move theminto a new
file
> created in a new filegroup. How can I move them without losing any data?
> regards,
>

moving clustered indexes

Hi,
I have quite a few clustered indexes that I need to move theminto a new file
created in a new filegroup. How can I move them without losing any data?
regards,Hi,
Data are contained in the leaf pages of the clustered index, moving the
clustered index moves the data
to the new file group. So you cannot move the clustered index alone.
How to move the index to a new file group
---
create clustered index idx_clus on Table (Column) with drop existing
on new_file_group
Thanks
Hari
MCDBA
"aoxpsql" <anonymous@.discussion.com> wrote in message
news:#$PdXIGdEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> I have quite a few clustered indexes that I need to move theminto a new
file
> created in a new filegroup. How can I move them without losing any data?
> regards,
>|||Hari is correct in the how-to... Be aware that this process may take a long
time... It will write to the transaction log, so be backing up the log
during the process to keep the log from growing huge... And users will be
locked out of the tables during the process.
Backup everything both before and after, just to be safe..
--
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
"aoxpsql" <anonymous@.discussion.com> wrote in message
news:%23$PdXIGdEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> I have quite a few clustered indexes that I need to move theminto a new
file
> created in a new filegroup. How can I move them without losing any data?
> regards,
>

moving clustered indexes

Hi,
I have quite a few clustered indexes that I need to move theminto a new file
created in a new filegroup. How can I move them without losing any data?
regards,Hi,
Data are contained in the leaf pages of the clustered index, moving the
clustered index moves the data
to the new file group. So you cannot move the clustered index alone.
How to move the index to a new file group
---
create clustered index idx_clus on Table (Column) with drop existing
on new_file_group
Thanks
Hari
MCDBA
"aoxpsql" <anonymous@.discussion.com> wrote in message
news:#$PdXIGdEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> I have quite a few clustered indexes that I need to move theminto a new
file
> created in a new filegroup. How can I move them without losing any data?
> regards,
>|||Hari is correct in the how-to... Be aware that this process may take a long
time... It will write to the transaction log, so be backing up the log
during the process to keep the log from growing huge... And users will be
locked out of the tables during the process.
Backup everything both before and after, just to be safe..
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
"aoxpsql" <anonymous@.discussion.com> wrote in message
news:%23$PdXIGdEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> I have quite a few clustered indexes that I need to move theminto a new
file
> created in a new filegroup. How can I move them without losing any data?
> regards,
>

Moving cluster server to new location

We are in the process of migrating our data center to a new location. we
have a cluster server running win2k advance server active passive mode with
Sql server 2000 on it. Any one has any ideas or best practice notes on the
best way to do it.Hi
I guess your IP address wil change:
Changing the IP Address of Network Adapters in Cluster Server
http://support.microsoft.com/default.aspx?kbid=230356
How to change the network IP addresses of SQL Server virtual servers
http://support.microsoft.com/defaul...0&Product=sql2k
Test as much as you can beforehand. Make a checklist before starting.
(Credit to Geoff N. Hiten for the original answer in
microsoft.public.sqlserver.clustering)
Regards
Mike
"sumsuk" wrote:

> We are in the process of migrating our data center to a new location. we
> have a cluster server running win2k advance server active passive mode wit
h
> Sql server 2000 on it. Any one has any ideas or best practice notes on the
> best way to do it.|||Is it better to build a new cluster server at the new site. backup system
and user database from the old server then restore the data on the new
server. Any one had done this , will this work.
If we cannot afford down time then what is the best way to sync. the two
server.
If i am physicaly moving the old server to the new location do you think i
will have to break the cluster because the storage is on a san in the
current location.
My planning is in a very primitive stage now. Any input is apprieciated.
Thanx.
"sumsuk" wrote:

> We are in the process of migrating our data center to a new location. we
> have a cluster server running win2k advance server active passive mode wit
h
> Sql server 2000 on it. Any one has any ideas or best practice notes on the
> best way to do it.sql

Moving cluster server to new location

We are in the process of migrating our data center to a new location. we
have a cluster server running win2k advance server active passive mode with
Sql server 2000 on it. Any one has any ideas or best practice notes on the
best way to do it.Hi
I guess your IP address wil change:
Changing the IP Address of Network Adapters in Cluster Server
http://support.microsoft.com/default.aspx?kbid=230356
How to change the network IP addresses of SQL Server virtual servers
http://support.microsoft.com/default.aspx?scid=kb;en-us;244980&Product=sql2k
Test as much as you can beforehand. Make a checklist before starting.
(Credit to Geoff N. Hiten for the original answer in
microsoft.public.sqlserver.clustering)
Regards
Mike
"sumsuk" wrote:
> We are in the process of migrating our data center to a new location. we
> have a cluster server running win2k advance server active passive mode with
> Sql server 2000 on it. Any one has any ideas or best practice notes on the
> best way to do it.|||Is it better to build a new cluster server at the new site. backup system
and user database from the old server then restore the data on the new
server. Any one had done this , will this work.
If we cannot afford down time then what is the best way to sync. the two
server.
If i am physicaly moving the old server to the new location do you think i
will have to break the cluster because the storage is on a san in the
current location.
My planning is in a very primitive stage now. Any input is apprieciated.
Thanx.
"sumsuk" wrote:
> We are in the process of migrating our data center to a new location. we
> have a cluster server running win2k advance server active passive mode with
> Sql server 2000 on it. Any one has any ideas or best practice notes on the
> best way to do it.

Moving cluster server to new location

We are in the process of migrating our data center to a new location. we
have a cluster server running win2k advance server active passive mode with
Sql server 2000 on it. Any one has any ideas or best practice notes on the
best way to do it.
Hi
I guess your IP address wil change:
Changing the IP Address of Network Adapters in Cluster Server
http://support.microsoft.com/default.aspx?kbid=230356
How to change the network IP addresses of SQL Server virtual servers
http://support.microsoft.com/default...&Product=sql2k
Test as much as you can beforehand. Make a checklist before starting.
(Credit to Geoff N. Hiten for the original answer in
microsoft.public.sqlserver.clustering)
Regards
Mike
"sumsuk" wrote:

> We are in the process of migrating our data center to a new location. we
> have a cluster server running win2k advance server active passive mode with
> Sql server 2000 on it. Any one has any ideas or best practice notes on the
> best way to do it.
|||Is it better to build a new cluster server at the new site. backup system
and user database from the old server then restore the data on the new
server. Any one had done this , will this work.
If we cannot afford down time then what is the best way to sync. the two
server.
If i am physicaly moving the old server to the new location do you think i
will have to break the cluster because the storage is on a san in the
current location.
My planning is in a very primitive stage now. Any input is apprieciated.
Thanx.
"sumsuk" wrote:

> We are in the process of migrating our data center to a new location. we
> have a cluster server running win2k advance server active passive mode with
> Sql server 2000 on it. Any one has any ideas or best practice notes on the
> best way to do it.

Moving Client Network Utility

Hi Folks,
I'm having to move workstations and was wondering if anybody knew a quick way to move all the alias's from your client network utility from one workstation to another.
CheersSave the following key and restore it over the other workstations.
HKEY_LOCAL_MACHINE=>SOFTWARE=>MICROSOFT=>MSSQLSERVER=>CLIENT=>CONNECTTO

Howdy!

Moving changes from test server to production server?

We're starting out some development efforts here at work (with SQL Server
2000) and I'd like to know where I can find some best practices regarding
migrating changes from your test sql server to your production server. The
basic iteration as I see it is:
1) Create initial database on production server (at the time, this is
probably the datbase from your test server - after testing is complete).
2) Next phase of development starts where you will alter the database in
response to bug fixes, additional requirements, etc.
3) That development effort is finished, complete with testing the test
datbase. So, now you want to move those "changes" to the production server.
4) Repeat steps 2-3 as required.
As most companies new to SQL Server, we are learning the product via
Enterprise Manager for the most part. I believe we'll have to take more of
a script-based approach (meaning creating sql statements to alter the
database as opposed to using the EM GUI) to properly address this issue.
I see a couple of issues here that need I am not quite clear on:
A) The physical steps of updating your test server incrementally (as you
discover changes that need to be made) and then upgrading a set of those
incremental updates to your production server once you're ready. I assume
the best way to do this is through sql statements (that alter the database)
that you could store in files. I'm not sure if Enterprise Manager has the
concept of "versioning" with regards to changes in the database and offers a
way to automate this type of migration.
B) Keeping up with these changes in some organized manner like you would
with versioning a software product. My assumption is that I could keep a
file (or maybe a table) that would contain each logical/incremental change
to the database and a date or a version to identify the change. That
version/date could be stored somewhere in the database (maybe another table)
so you could determine the "version" of the database and know what updates
you might need to apply.
I'm sure I'm missing many things and that this has been thought through many
times. I just don't want to reinvent the wheel here, so any help would be
much appreciated.
Thanks!SQL Server Central has a group of 4 articles on this process which are
pretty good.
Here is a link for the 4th article. You can see the other 3 articles
(parts) at the bottom of the article.
http://www.sqlservercentral.com/col...proachpart4.asp
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||First off, don't cross post. Pick a group, post it there, right now, five
other people are answering your question, in different groups. If you don't
get an acceptable answer, try again for sure in a different newsgroups, but
one at a time.
You are totally on the right track. Use enterprise manager only as a tool
to generate scripts for changes where it can. Usually the process for table
changes is:
1. On a development server, create your database. Create your tables and
stuff without regard to versioning (the first time only)
2. Script the database, apply to a testing database so that people can test
that it works (usually not the dev server.) Find bugs, then the fun begins:
3. Change your development server with change scripts, such that all of
your drops and creates of table objects change the dev server from what it
was, to what you want it to be. If you have to drop a table, drop it, and
keep a script. Update data? Make a script. Script everything before you
do it, and use it to apply the changes.
4. Apply the changes to test check for bugs, if bugs, go back to step 3 and
repeat until happy.
5. Run these scripts against the production server, and let er' rip.
Afterwards, you make changes to dev via scripts, apply to QA, apply to prod.
For procedures, this is a bit more tricky. Most people keep a script file
per stored procedure in some sort of version control system and apply these
changes at the same time, in the same way as the table changes. SQL Server
does not do this for you, but just applying the same kind of process as you
do with functional code will go a long way.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"epigram" <nospam@.spammy.com> wrote in message
news:1112195019. 3fabb7bc41be3dd34667b3019bd3d7b5@.bubbane
ws...
> We're starting out some development efforts here at work (with SQL Server
> 2000) and I'd like to know where I can find some best practices regarding
> migrating changes from your test sql server to your production server.
> The basic iteration as I see it is:
> 1) Create initial database on production server (at the time, this is
> probably the datbase from your test server - after testing is complete).
> 2) Next phase of development starts where you will alter the database in
> response to bug fixes, additional requirements, etc.
> 3) That development effort is finished, complete with testing the test
> datbase. So, now you want to move those "changes" to the production
> server.
> 4) Repeat steps 2-3 as required.
> As most companies new to SQL Server, we are learning the product via
> Enterprise Manager for the most part. I believe we'll have to take more
> of a script-based approach (meaning creating sql statements to alter the
> database as opposed to using the EM GUI) to properly address this issue.
> I see a couple of issues here that need I am not quite clear on:
> A) The physical steps of updating your test server incrementally (as you
> discover changes that need to be made) and then upgrading a set of those
> incremental updates to your production server once you're ready. I assume
> the best way to do this is through sql statements (that alter the
> database) that you could store in files. I'm not sure if Enterprise
> Manager has the concept of "versioning" with regards to changes in the
> database and offers a way to automate this type of migration.
> B) Keeping up with these changes in some organized manner like you would
> with versioning a software product. My assumption is that I could keep a
> file (or maybe a table) that would contain each logical/incremental change
> to the database and a date or a version to identify the change. That
> version/date could be stored somewhere in the database (maybe another
> table) so you could determine the "version" of the database and know what
> updates you might need to apply.
> I'm sure I'm missing many things and that this has been thought through
> many times. I just don't want to reinvent the wheel here, so any help
> would be much appreciated.
> Thanks!
>
>|||Eeep.
Sorry about the xpost on the response.
My bad.
Rick|||It happens :)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:e%23AOnOUNFHA.2716@.TK2MSFTNGP10.phx.gbl...
> Eeep.
> Sorry about the xpost on the response.
> My bad.
>
> Rick
>|||like every shop I've ever worked in, you require a change management process
.
Everyone has such a process even if you consider that not having a process i
s
a process in itself. Most however get bogged down in the complexity and spen
d
an age making their processes work and this is where I created the tool DB
Ghost which is a database change management utility designed to rid the IT
staff of the complexities of having a robust process to develop and deploy
changes quickly, reliably and with complete control and auditability.
The articles Rick Sawtell mentions are in fact the architectural theory
behind the DB Ghost software. If you bother to investigate you may well find
what you are looking for. It may however mean you have to change the way in
which you think about change management as the concepts portrayed seem to be
very rare although no-one has ever been able to disprove them and evidence i
s
bountiful in regards to disproving every other method.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"epigram" wrote:

> We're starting out some development efforts here at work (with SQL Server
> 2000) and I'd like to know where I can find some best practices regarding
> migrating changes from your test sql server to your production server. Th
e
> basic iteration as I see it is:
> 1) Create initial database on production server (at the time, this is
> probably the datbase from your test server - after testing is complete).
> 2) Next phase of development starts where you will alter the database in
> response to bug fixes, additional requirements, etc.
> 3) That development effort is finished, complete with testing the test
> datbase. So, now you want to move those "changes" to the production serve
r.
> 4) Repeat steps 2-3 as required.
> As most companies new to SQL Server, we are learning the product via
> Enterprise Manager for the most part. I believe we'll have to take more o
f
> a script-based approach (meaning creating sql statements to alter the
> database as opposed to using the EM GUI) to properly address this issue.
> I see a couple of issues here that need I am not quite clear on:
> A) The physical steps of updating your test server incrementally (as you
> discover changes that need to be made) and then upgrading a set of those
> incremental updates to your production server once you're ready. I assume
> the best way to do this is through sql statements (that alter the database
)
> that you could store in files. I'm not sure if Enterprise Manager has the
> concept of "versioning" with regards to changes in the database and offers
a
> way to automate this type of migration.
> B) Keeping up with these changes in some organized manner like you would
> with versioning a software product. My assumption is that I could keep a
> file (or maybe a table) that would contain each logical/incremental change
> to the database and a date or a version to identify the change. That
> version/date could be stored somewhere in the database (maybe another tabl
e)
> so you could determine the "version" of the database and know what updates
> you might need to apply.
> I'm sure I'm missing many things and that this has been thought through ma
ny
> times. I just don't want to reinvent the wheel here, so any help would be
> much appreciated.
> Thanks!
>
>

Moving changes from test server to production server?

We're starting out some development efforts here at work (with SQL Server
2000) and I'd like to know where I can find some best practices regarding
migrating changes from your test sql server to your production server. The
basic iteration as I see it is:
1) Create initial database on production server (at the time, this is
probably the datbase from your test server - after testing is complete).
2) Next phase of development starts where you will alter the database in
response to bug fixes, additional requirements, etc.
3) That development effort is finished, complete with testing the test
datbase. So, now you want to move those "changes" to the production server.
4) Repeat steps 2-3 as required.
As most companies new to SQL Server, we are learning the product via
Enterprise Manager for the most part. I believe we'll have to take more of
a script-based approach (meaning creating sql statements to alter the
database as opposed to using the EM GUI) to properly address this issue.
I see a couple of issues here that need I am not quite clear on:
A) The physical steps of updating your test server incrementally (as you
discover changes that need to be made) and then upgrading a set of those
incremental updates to your production server once you're ready. I assume
the best way to do this is through sql statements (that alter the database)
that you could store in files. I'm not sure if Enterprise Manager has the
concept of "versioning" with regards to changes in the database and offers a
way to automate this type of migration.
B) Keeping up with these changes in some organized manner like you would
with versioning a software product. My assumption is that I could keep a
file (or maybe a table) that would contain each logical/incremental change
to the database and a date or a version to identify the change. That
version/date could be stored somewhere in the database (maybe another table)
so you could determine the "version" of the database and know what updates
you might need to apply.
I'm sure I'm missing many things and that this has been thought through many
times. I just don't want to reinvent the wheel here, so any help would be
much appreciated.
Thanks!
SQL Server Central has a group of 4 articles on this process which are
pretty good.
Here is a link for the 4th article. You can see the other 3 articles
(parts) at the bottom of the article.
http://www.sqlservercentral.com/colu...achpart4. asp
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||like every shop I've ever worked in, you require a change management process.
Everyone has such a process even if you consider that not having a process is
a process in itself. Most however get bogged down in the complexity and spend
an age making their processes work and this is where I created the tool DB
Ghost which is a database change management utility designed to rid the IT
staff of the complexities of having a robust process to develop and deploy
changes quickly, reliably and with complete control and auditability.
The articles Rick Sawtell mentions are in fact the architectural theory
behind the DB Ghost software. If you bother to investigate you may well find
what you are looking for. It may however mean you have to change the way in
which you think about change management as the concepts portrayed seem to be
very rare although no-one has ever been able to disprove them and evidence is
bountiful in regards to disproving every other method.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"epigram" wrote:

> We're starting out some development efforts here at work (with SQL Server
> 2000) and I'd like to know where I can find some best practices regarding
> migrating changes from your test sql server to your production server. The
> basic iteration as I see it is:
> 1) Create initial database on production server (at the time, this is
> probably the datbase from your test server - after testing is complete).
> 2) Next phase of development starts where you will alter the database in
> response to bug fixes, additional requirements, etc.
> 3) That development effort is finished, complete with testing the test
> datbase. So, now you want to move those "changes" to the production server.
> 4) Repeat steps 2-3 as required.
> As most companies new to SQL Server, we are learning the product via
> Enterprise Manager for the most part. I believe we'll have to take more of
> a script-based approach (meaning creating sql statements to alter the
> database as opposed to using the EM GUI) to properly address this issue.
> I see a couple of issues here that need I am not quite clear on:
> A) The physical steps of updating your test server incrementally (as you
> discover changes that need to be made) and then upgrading a set of those
> incremental updates to your production server once you're ready. I assume
> the best way to do this is through sql statements (that alter the database)
> that you could store in files. I'm not sure if Enterprise Manager has the
> concept of "versioning" with regards to changes in the database and offers a
> way to automate this type of migration.
> B) Keeping up with these changes in some organized manner like you would
> with versioning a software product. My assumption is that I could keep a
> file (or maybe a table) that would contain each logical/incremental change
> to the database and a date or a version to identify the change. That
> version/date could be stored somewhere in the database (maybe another table)
> so you could determine the "version" of the database and know what updates
> you might need to apply.
> I'm sure I'm missing many things and that this has been thought through many
> times. I just don't want to reinvent the wheel here, so any help would be
> much appreciated.
> Thanks!
>
>
sql

Moving changes from test server to production server?

We're starting out some development efforts here at work (with SQL Server
2000) and I'd like to know where I can find some best practices regarding
migrating changes from your test sql server to your production server. The
basic iteration as I see it is:
1) Create initial database on production server (at the time, this is
probably the datbase from your test server - after testing is complete).
2) Next phase of development starts where you will alter the database in
response to bug fixes, additional requirements, etc.
3) That development effort is finished, complete with testing the test
datbase. So, now you want to move those "changes" to the production server.
4) Repeat steps 2-3 as required.
As most companies new to SQL Server, we are learning the product via
Enterprise Manager for the most part. I believe we'll have to take more of
a script-based approach (meaning creating sql statements to alter the
database as opposed to using the EM GUI) to properly address this issue.
I see a couple of issues here that need I am not quite clear on:
A) The physical steps of updating your test server incrementally (as you
discover changes that need to be made) and then upgrading a set of those
incremental updates to your production server once you're ready. I assume
the best way to do this is through sql statements (that alter the database)
that you could store in files. I'm not sure if Enterprise Manager has the
concept of "versioning" with regards to changes in the database and offers a
way to automate this type of migration.
B) Keeping up with these changes in some organized manner like you would
with versioning a software product. My assumption is that I could keep a
file (or maybe a table) that would contain each logical/incremental change
to the database and a date or a version to identify the change. That
version/date could be stored somewhere in the database (maybe another table)
so you could determine the "version" of the database and know what updates
you might need to apply.
I'm sure I'm missing many things and that this has been thought through many
times. I just don't want to reinvent the wheel here, so any help would be
much appreciated.
Thanks!
SQL Server Central has a group of 4 articles on this process which are
pretty good.
Here is a link for the 4th article. You can see the other 3 articles
(parts) at the bottom of the article.
http://www.sqlservercentral.com/colu...achpart4. asp
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||like every shop I've ever worked in, you require a change management process.
Everyone has such a process even if you consider that not having a process is
a process in itself. Most however get bogged down in the complexity and spend
an age making their processes work and this is where I created the tool DB
Ghost which is a database change management utility designed to rid the IT
staff of the complexities of having a robust process to develop and deploy
changes quickly, reliably and with complete control and auditability.
The articles Rick Sawtell mentions are in fact the architectural theory
behind the DB Ghost software. If you bother to investigate you may well find
what you are looking for. It may however mean you have to change the way in
which you think about change management as the concepts portrayed seem to be
very rare although no-one has ever been able to disprove them and evidence is
bountiful in regards to disproving every other method.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"epigram" wrote:

> We're starting out some development efforts here at work (with SQL Server
> 2000) and I'd like to know where I can find some best practices regarding
> migrating changes from your test sql server to your production server. The
> basic iteration as I see it is:
> 1) Create initial database on production server (at the time, this is
> probably the datbase from your test server - after testing is complete).
> 2) Next phase of development starts where you will alter the database in
> response to bug fixes, additional requirements, etc.
> 3) That development effort is finished, complete with testing the test
> datbase. So, now you want to move those "changes" to the production server.
> 4) Repeat steps 2-3 as required.
> As most companies new to SQL Server, we are learning the product via
> Enterprise Manager for the most part. I believe we'll have to take more of
> a script-based approach (meaning creating sql statements to alter the
> database as opposed to using the EM GUI) to properly address this issue.
> I see a couple of issues here that need I am not quite clear on:
> A) The physical steps of updating your test server incrementally (as you
> discover changes that need to be made) and then upgrading a set of those
> incremental updates to your production server once you're ready. I assume
> the best way to do this is through sql statements (that alter the database)
> that you could store in files. I'm not sure if Enterprise Manager has the
> concept of "versioning" with regards to changes in the database and offers a
> way to automate this type of migration.
> B) Keeping up with these changes in some organized manner like you would
> with versioning a software product. My assumption is that I could keep a
> file (or maybe a table) that would contain each logical/incremental change
> to the database and a date or a version to identify the change. That
> version/date could be stored somewhere in the database (maybe another table)
> so you could determine the "version" of the database and know what updates
> you might need to apply.
> I'm sure I'm missing many things and that this has been thought through many
> times. I just don't want to reinvent the wheel here, so any help would be
> much appreciated.
> Thanks!
>
>

Moving changes from test server to production server?

We're starting out some development efforts here at work (with SQL Server
2000) and I'd like to know where I can find some best practices regarding
migrating changes from your test sql server to your production server. The
basic iteration as I see it is:
1) Create initial database on production server (at the time, this is
probably the datbase from your test server - after testing is complete).
2) Next phase of development starts where you will alter the database in
response to bug fixes, additional requirements, etc.
3) That development effort is finished, complete with testing the test
datbase. So, now you want to move those "changes" to the production server.
4) Repeat steps 2-3 as required.
As most companies new to SQL Server, we are learning the product via
Enterprise Manager for the most part. I believe we'll have to take more of
a script-based approach (meaning creating sql statements to alter the
database as opposed to using the EM GUI) to properly address this issue.
I see a couple of issues here that need I am not quite clear on:
A) The physical steps of updating your test server incrementally (as you
discover changes that need to be made) and then upgrading a set of those
incremental updates to your production server once you're ready. I assume
the best way to do this is through sql statements (that alter the database)
that you could store in files. I'm not sure if Enterprise Manager has the
concept of "versioning" with regards to changes in the database and offers a
way to automate this type of migration.
B) Keeping up with these changes in some organized manner like you would
with versioning a software product. My assumption is that I could keep a
file (or maybe a table) that would contain each logical/incremental change
to the database and a date or a version to identify the change. That
version/date could be stored somewhere in the database (maybe another table)
so you could determine the "version" of the database and know what updates
you might need to apply.
I'm sure I'm missing many things and that this has been thought through many
times. I just don't want to reinvent the wheel here, so any help would be
much appreciated.
Thanks!SQL Server Central has a group of 4 articles on this process which are
pretty good.
Here is a link for the 4th article. You can see the other 3 articles
(parts) at the bottom of the article.
http://www.sqlservercentral.com/col...proachpart4.asp
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||like every shop I've ever worked in, you require a change management process
.
Everyone has such a process even if you consider that not having a process i
s
a process in itself. Most however get bogged down in the complexity and spen
d
an age making their processes work and this is where I created the tool DB
Ghost which is a database change management utility designed to rid the IT
staff of the complexities of having a robust process to develop and deploy
changes quickly, reliably and with complete control and auditability.
The articles Rick Sawtell mentions are in fact the architectural theory
behind the DB Ghost software. If you bother to investigate you may well find
what you are looking for. It may however mean you have to change the way in
which you think about change management as the concepts portrayed seem to be
very rare although no-one has ever been able to disprove them and evidence i
s
bountiful in regards to disproving every other method.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"epigram" wrote:

> We're starting out some development efforts here at work (with SQL Server
> 2000) and I'd like to know where I can find some best practices regarding
> migrating changes from your test sql server to your production server. Th
e
> basic iteration as I see it is:
> 1) Create initial database on production server (at the time, this is
> probably the datbase from your test server - after testing is complete).
> 2) Next phase of development starts where you will alter the database in
> response to bug fixes, additional requirements, etc.
> 3) That development effort is finished, complete with testing the test
> datbase. So, now you want to move those "changes" to the production serve
r.
> 4) Repeat steps 2-3 as required.
> As most companies new to SQL Server, we are learning the product via
> Enterprise Manager for the most part. I believe we'll have to take more o
f
> a script-based approach (meaning creating sql statements to alter the
> database as opposed to using the EM GUI) to properly address this issue.
> I see a couple of issues here that need I am not quite clear on:
> A) The physical steps of updating your test server incrementally (as you
> discover changes that need to be made) and then upgrading a set of those
> incremental updates to your production server once you're ready. I assume
> the best way to do this is through sql statements (that alter the database
)
> that you could store in files. I'm not sure if Enterprise Manager has the
> concept of "versioning" with regards to changes in the database and offers
a
> way to automate this type of migration.
> B) Keeping up with these changes in some organized manner like you would
> with versioning a software product. My assumption is that I could keep a
> file (or maybe a table) that would contain each logical/incremental change
> to the database and a date or a version to identify the change. That
> version/date could be stored somewhere in the database (maybe another tabl
e)
> so you could determine the "version" of the database and know what updates
> you might need to apply.
> I'm sure I'm missing many things and that this has been thought through ma
ny
> times. I just don't want to reinvent the wheel here, so any help would be
> much appreciated.
> Thanks!
>
>

Moving changes from test server to production server?

We're starting out some development efforts here at work (with SQL Server
2000) and I'd like to know where I can find some best practices regarding
migrating changes from your test sql server to your production server. The
basic iteration as I see it is:
1) Create initial database on production server (at the time, this is
probably the datbase from your test server - after testing is complete).
2) Next phase of development starts where you will alter the database in
response to bug fixes, additional requirements, etc.
3) That development effort is finished, complete with testing the test
datbase. So, now you want to move those "changes" to the production server.
4) Repeat steps 2-3 as required.
As most companies new to SQL Server, we are learning the product via
Enterprise Manager for the most part. I believe we'll have to take more of
a script-based approach (meaning creating sql statements to alter the
database as opposed to using the EM GUI) to properly address this issue.
I see a couple of issues here that need I am not quite clear on:
A) The physical steps of updating your test server incrementally (as you
discover changes that need to be made) and then upgrading a set of those
incremental updates to your production server once you're ready. I assume
the best way to do this is through sql statements (that alter the database)
that you could store in files. I'm not sure if Enterprise Manager has the
concept of "versioning" with regards to changes in the database and offers a
way to automate this type of migration.
B) Keeping up with these changes in some organized manner like you would
with versioning a software product. My assumption is that I could keep a
file (or maybe a table) that would contain each logical/incremental change
to the database and a date or a version to identify the change. That
version/date could be stored somewhere in the database (maybe another table)
so you could determine the "version" of the database and know what updates
you might need to apply.
I'm sure I'm missing many things and that this has been thought through many
times. I just don't want to reinvent the wheel here, so any help would be
much appreciated.
Thanks!SQL Server Central has a group of 4 articles on this process which are
pretty good.
Here is a link for the 4th article. You can see the other 3 articles
(parts) at the bottom of the article.
http://www.sqlservercentral.com/columnists/DFuller/changemanagementachievinganautomatedapproachpart4.asp
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||like every shop I've ever worked in, you require a change management process.
Everyone has such a process even if you consider that not having a process is
a process in itself. Most however get bogged down in the complexity and spend
an age making their processes work and this is where I created the tool DB
Ghost which is a database change management utility designed to rid the IT
staff of the complexities of having a robust process to develop and deploy
changes quickly, reliably and with complete control and auditability.
The articles Rick Sawtell mentions are in fact the architectural theory
behind the DB Ghost software. If you bother to investigate you may well find
what you are looking for. It may however mean you have to change the way in
which you think about change management as the concepts portrayed seem to be
very rare although no-one has ever been able to disprove them and evidence is
bountiful in regards to disproving every other method.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"epigram" wrote:
> We're starting out some development efforts here at work (with SQL Server
> 2000) and I'd like to know where I can find some best practices regarding
> migrating changes from your test sql server to your production server. The
> basic iteration as I see it is:
> 1) Create initial database on production server (at the time, this is
> probably the datbase from your test server - after testing is complete).
> 2) Next phase of development starts where you will alter the database in
> response to bug fixes, additional requirements, etc.
> 3) That development effort is finished, complete with testing the test
> datbase. So, now you want to move those "changes" to the production server.
> 4) Repeat steps 2-3 as required.
> As most companies new to SQL Server, we are learning the product via
> Enterprise Manager for the most part. I believe we'll have to take more of
> a script-based approach (meaning creating sql statements to alter the
> database as opposed to using the EM GUI) to properly address this issue.
> I see a couple of issues here that need I am not quite clear on:
> A) The physical steps of updating your test server incrementally (as you
> discover changes that need to be made) and then upgrading a set of those
> incremental updates to your production server once you're ready. I assume
> the best way to do this is through sql statements (that alter the database)
> that you could store in files. I'm not sure if Enterprise Manager has the
> concept of "versioning" with regards to changes in the database and offers a
> way to automate this type of migration.
> B) Keeping up with these changes in some organized manner like you would
> with versioning a software product. My assumption is that I could keep a
> file (or maybe a table) that would contain each logical/incremental change
> to the database and a date or a version to identify the change. That
> version/date could be stored somewhere in the database (maybe another table)
> so you could determine the "version" of the database and know what updates
> you might need to apply.
> I'm sure I'm missing many things and that this has been thought through many
> times. I just don't want to reinvent the wheel here, so any help would be
> much appreciated.
> Thanks!
>
>

Moving catalog from one drive to another

I have asked (and had a reply - thanks!) this question before, but i'm in a
hurry and cant seem to find the solution even by googling myself.
I have a database which had a FT catalog on E: on one machine and i've moved
the database to a machine with only a C:
I remember the solution has something to do with accessing a system table
and changing the file path from E: to C: but I cant remember which one. I
cant just delete the catalog because it tells me to repopulate it which I
cant do because it doesnt exist.
Can someone point me in the right direction away from this chicken and egg
scenario
Many thanks,
Simon DBA Andy (because it is alphabetically superior, yet Aaaandy would be
pushing it I feel)
have a look at this kbarticle
http://support.microsoft.com/default...b;en-us;240867
The update you are looking for it towards the end of the article.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Andy Gilman" <andygilman2@.hotmail.com> wrote in message
news:OTmDI4faEHA.3352@.TK2MSFTNGP12.phx.gbl...

> I have asked (and had a reply - thanks!) this question before, but i'm in
a
> hurry and cant seem to find the solution even by googling myself.
> I have a database which had a FT catalog on E: on one machine and i've
moved
> the database to a machine with only a C:
> I remember the solution has something to do with accessing a system table
> and changing the file path from E: to C: but I cant remember which one. I
> cant just delete the catalog because it tells me to repopulate it which I
> cant do because it doesnt exist.
> Can someone point me in the right direction away from this chicken and egg
> scenario
> Many thanks,
> Simon DBA Andy (because it is alphabetically superior, yet Aaaandy would
be
> pushing it I feel)
>

Moving C2 Audits

I'm not seeing or finding how to move the location of the C2 Audit files. I
used the pretty standard script to enable C2 Auditing:
EXEC sp_configure 'show advanced option', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'c2 audit mode', 1
RECONFIGURE WITH OVERRIDE
However, it would be nice if I could move them from the C:\ drive to a
folder already being backed up since we need these audit trails for at least
one year (Government thing). Any ideas? I'll keep looking, but I know we
get pretty quick responses in here also!
Thanks
AllenSorry , what is C2 Audit files ?
"A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
news:e4aqFxLtGHA.2260@.TK2MSFTNGP03.phx.gbl...
> I'm not seeing or finding how to move the location of the C2 Audit files.
> I used the pretty standard script to enable C2 Auditing:
> EXEC sp_configure 'show advanced option', 1
> RECONFIGURE WITH OVERRIDE
> EXEC sp_configure 'c2 audit mode', 1
> RECONFIGURE WITH OVERRIDE
> However, it would be nice if I could move them from the C:\ drive to a
> folder already being backed up since we need these audit trails for at
> least one year (Government thing). Any ideas? I'll keep looking, but I
> know we get pretty quick responses in here also!
> Thanks
> Allen
>|||Hi,
By default SQL Server logs the C2 trace into data folder which you mentioned
during SQL Server installation.
The file can be copied to a different location by writing a batch file and
scheduled using SQL Server Agent every 1 hour.
Thanks
Hari
SQL Server MVP
"A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
news:e4aqFxLtGHA.2260@.TK2MSFTNGP03.phx.gbl...
> I'm not seeing or finding how to move the location of the C2 Audit files.
> I used the pretty standard script to enable C2 Auditing:
> EXEC sp_configure 'show advanced option', 1
> RECONFIGURE WITH OVERRIDE
> EXEC sp_configure 'c2 audit mode', 1
> RECONFIGURE WITH OVERRIDE
> However, it would be nice if I could move them from the C:\ drive to a
> folder already being backed up since we need these audit trails for at
> least one year (Government thing). Any ideas? I'll keep looking, but I
> know we get pretty quick responses in here also!
> Thanks
> Allen
>|||C2 audits are predefined audits you can run to trace specific events on a
SQL Server. It is compliant with Government auditing policies and
procedures.
http://www.microsoft.com/technet/se...r/sql2kaud.mspx
A Google search will produce more results for you.
In SQL 2005 it is a standard checkbox you can turn on by checking the
Properties of a Server, then clicking on Security. In SQL 2000, you have to
run the script I show below.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uX%23l0rStGHA.4748@.TK2MSFTNGP03.phx.gbl...
> Sorry , what is C2 Audit files ?
>
>
> "A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
> news:e4aqFxLtGHA.2260@.TK2MSFTNGP03.phx.gbl...
>|||Yeah, not really what I had in mind though. Just like I can change the
location of my data files and log files, I want my traces to go to another
location as well. This should be something we can run a script to configure
IMHO. I don't want to write batch files for each of my 70 SQL Servers ;-)
The default size of an audit file is 200 MB, and with C2 auditing, that will
be reached daily for some servers - no way around it. Furthermore, you
can't copy an active trace file, so I would have to continually check back
to see if the thing is 200 MB yet, then copy it to another location on the
same computer - awaiting pickup by our backup system. That is way too much
disk activity for me.
I would like to see the ability to write them directly to another location -
other than the original /data folder.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uY92JSUtGHA.4968@.TK2MSFTNGP03.phx.gbl...
> Hi,
> By default SQL Server logs the C2 trace into data folder which you
> mentioned during SQL Server installation.
> The file can be copied to a different location by writing a batch file and
> scheduled using SQL Server Agent every 1 hour.
> Thanks
> Hari
> SQL Server MVP
>
> "A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
> news:e4aqFxLtGHA.2260@.TK2MSFTNGP03.phx.gbl...
>|||Ok - I've been told that audits get written to the default data directory.
However, we have dedicated drives for log and data files (E: and F:, for
example). What I had to do to get the C2 audits (trace files) to write to
our data directory was:
1) launch Enterprise Manager
2) right-click and Properties
3) Database Settings tab
4) Change 'default data directory' to F:\<location>
5) Stop and restart the services
Good to go. The traces will begin writing to the drive you specified as
your default data directory.
"A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
news:e4aqFxLtGHA.2260@.TK2MSFTNGP03.phx.gbl...
> I'm not seeing or finding how to move the location of the C2 Audit files.
> I used the pretty standard script to enable C2 Auditing:
> EXEC sp_configure 'show advanced option', 1
> RECONFIGURE WITH OVERRIDE
> EXEC sp_configure 'c2 audit mode', 1
> RECONFIGURE WITH OVERRIDE
> However, it would be nice if I could move them from the C:\ drive to a
> folder already being backed up since we need these audit trails for at
> least one year (Government thing). Any ideas? I'll keep looking, but I
> know we get pretty quick responses in here also!
> Thanks
> Allen
>sql

Moving BD to new schema...

Good morning to everyone!

I have a 3Gig SQL database that currently has all Indexes, Logs, and Data on one drive. We have a new server that will be put in place on Wednesday and thankfully we will be putting the Indexes and TransLogs on different drives from the actual Data. Does anyone have a recommendation on his/her preferred way of doing this and what are some of the advantages/disadvantages some of you may have encountered? This isn't homework. I am finally getting the hang of manipulating and working with our database and will need to accomplish the server switch in about two weeks. Or if you have a recommended reading on this - please point me to it and I'll get to practicing!

Thanks to everyone!
Tiffanieput indices on different drives than the data. you can create seperate filegroups and put indices for one set of tables on one drive and data on the other and do the vice versa for another set of tales. put T-Logs on a completely seperate drive from the data and indices. I do not want to start the RAID debate.|||Thanks Sean,

We are putting everything on their own drives, but I was wondering how everyone goes about pointing the tables to the new locations of the I&Ts? No raid discussion from me - server is already built I just get to play with moving everything and making sure it works. Basically we're planning to fully backup on the old server and restore on the new. Somehow I need to adjust the paths though?

Tiffanie|||Monday... erased.|||tables that are frequently joined, you might to keep oon seperate filegroups.

I do not think you can use ALTER TABLE to change filegroups.

I am not sure you are going tobe able to do a backup and restore. You may have to script your whole database in the EM (easy to do). and adjust your create table statements with ON PRIMARY or SECONDARY etc... specified. Then you will have to move the data over without using a backup.

The indices are easy. Just drop and recreate. You can use the EM or write some code that writes some code against sysindexes to do the job.|||Thanks Sean - I'll play around with that.
Have a terrific day!
Tiffanie

Moving backup devices

Is there a way to copy backup devices between 2 servers with a script or
something else ? I actually need to move my current installation on a new
server and I don't want to recreate 200 backup devices manually.
Thanks in advance
script out the sysdevices table... eg
USE master
GO
SELECT *
FROM dbo.sysdevices

Moving backup devices

Is there a way to copy backup devices between 2 servers with a script or
something else ? I actually need to move my current installation on a new
server and I don't want to recreate 200 backup devices manually.
Thanks in advancescript out the sysdevices table... eg
USE master
GO
SELECT *
FROM dbo.sysdevices

Moving backup devices

Is there a way to copy backup devices between 2 servers with a script or
something else ? I actually need to move my current installation on a new
server and I don't want to recreate 200 backup devices manually.
Thanks in advancescript out the sysdevices table... eg
USE master
GO
SELECT *
FROM dbo.sysdevices

Moving back to MSDE 2000 after removing SQLServer 2005 Express

Apparently you cannot go backwards. Once 2005 Express is installed, even removing it does not allow you to setup MSDE 2000 again. So there is no "trying it out" option.

Unfortunately, our software does not use 2005 Express. I tried it out, and now have a useless testing workstation that can't have MSDE 2000 installed again.

Any ideas on how to break the chain here are welcome.

Hi,

If the workstation is just for testing, can you not just rebuild it?

Failing that have you tried removing all of the SQL Server registry keys/values before trying MSDE 2000 reinstall?

Regards,

Gary.

sql

Moving back to MSDE 2000 after removing SQLServer 2005 Express

Apparently you cannot go backwards. Once 2005 Express is installed, even removing it does not allow you to setup MSDE 2000 again. So there is no "trying it out" option.

Unfortunately, our software does not use 2005 Express. I tried it out, and now have a useless testing workstation that can't have MSDE 2000 installed again.

Any ideas on how to break the chain here are welcome.

Hi,

If the workstation is just for testing, can you not just rebuild it?

Failing that have you tried removing all of the SQL Server registry keys/values before trying MSDE 2000 reinstall?

Regards,

Gary.