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.

moving away from cluster to standalone

We have 2 servers on a SQL 2000 cluster and now we would like to move away
from a clustered enviroment to just a standalone machine. Any idea how I
should proceed?
"nobody" <nobody@.nobody.com> wrote in message
news:%231FAGluoHHA.4424@.TK2MSFTNGP03.phx.gbl...
> We have 2 servers on a SQL 2000 cluster and now we would like to move away
> from a clustered enviroment to just a standalone machine. Any idea how I
> should proceed?
Build a new server, install SQL, and migrate databases.
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
The next ClusterHelp classes are:
July 10-13 in Denver
July 16-19 in New York
|||Agreed, there is no procedure for converting a clustered install to a
standalone install.
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"Russ Kaufmann [MVP]" wrote:

> "nobody" <nobody@.nobody.com> wrote in message
> news:%231FAGluoHHA.4424@.TK2MSFTNGP03.phx.gbl...
> Build a new server, install SQL, and migrate databases.
>
> --
> Russ Kaufmann
> MVP - Windows Server - Clustering
> ClusterHelp.com, a Microsoft Certified Gold Partner
> Web http://www.clusterhelp.com
> Blog http://msmvps.com/clusterhelp
> The next ClusterHelp classes are:
> July 10-13 in Denver
> July 16-19 in New York
>

Moving Average using Select Statement or Cursor based?

ID DATE(dd/mm/yy) TYPE QTYIN COST_IN_AMT COST_OUT_AMT(MOVING AVERAGE)
1 01/01/2007 PURCHASE 10 1000
2 01/01/2007 PURCHAES 5 1100
3 01/01/2007 SALES -5 *TobeCalculated
4 02/01/2007 Purchase 20 9000
5 02/01/2007 SALES -10 *TobeCalculated
5 02/01/2007 purchase 50 8000
6 03/01/2007 Sales -10 *TobeCalculate

7 01/01/2007 Purchase 20 12000

I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here)

In order to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly.

When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly.

The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated.

Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time.

Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?

It is best to learn to avoiding cursors; Transact SQL is a set-based language and works best when used to perform set-based operations rather than use cursors, which are record-based. I am still not sure that I fully understand the problem, but here is my first pass:

Code Snippet

create table dbo.item_txns
( id integer,
date datetime,
type varchar(10),
qtyin integer,
cost_out_amt decimal(12,2)
)
go

declare @.avg table
( id integer,
date datetime,
type varchar(10),
qtyin integer,
cost_out_amt decimal(12,2),
seq integer primary key,
moving_cost decimal(12,2),
moving_qty integer
)

insert into dbo.item_txns
select 1,'01/01/2007','PURCHASE',10,1000 union all
select 2,'01/01/2007','PURCHAES',5,1100 union all
select 3,'01/01/2007','SALES',-5,null union all
select 4,'02/01/2007','Purchase',20,9000 union all
select 5,'02/01/2007','SALES',-10,null union all
select 5,'02/01/2007','purchase',50,8000 union all
select 6,'03/01/2007','Sales',-10,null union all
select 7,'01/01/2007','Purchase',20,12000

;with tranCTE as
( select id,
date,
type,
qtyin,
cost_out_amt,
row_number() over
( order by date, id, type, qtyin )
as Seq
from item_txns
)
insert into @.avg
( id,
date,
type,
qtyin,
cost_out_amt,
seq
)
select id,
date,
type,
qtyin,
cost_out_amt,
seq
from tranCTE

--select * from @.avg

declare @.movingCost decimal(12,2) set @.movingCost = 0
declare @.movingQty integer set @.movingQty = 0

update @.avg
set moving_cost = @.movingCost,
moving_qty = @.movingQty,
cost_out_amt
= case when cost_out_amt is null
and @.movingQty <> 0
then qtyin * (@.movingCost/@.movingQty)
when cost_out_amt is null
and @.movingQty = 0
then 0
else cost_out_amt
end,
@.movingCost = @.movingCost +
case when cost_out_amt is null
and @.movingQty <> 0
then qtyin * (@.movingCost/@.movingQty)
when cost_out_amt is null
then 0
else cost_out_amt
end,
@.movingQty = @.movingQty + isnull(qtyin,0)

select * from @.avg

/*
id date type qtyin cost_out_amt seq moving_cost moving_qty
-- -- - - -- --
1 2007-01-01 00:00:00.000 PURCHASE 10 1000.00 1 1000.00 10
2 2007-01-01 00:00:00.000 PURCHAES 5 1100.00 2 2100.00 15
3 2007-01-01 00:00:00.000 SALES -5 -700.00 3 1400.00 10
7 2007-01-01 00:00:00.000 Purchase 20 12000.00 4 13400.00 30
4 2007-02-01 00:00:00.000 Purchase 20 9000.00 5 22400.00 50
5 2007-02-01 00:00:00.000 purchase 50 8000.00 6 30400.00 100
5 2007-02-01 00:00:00.000 SALES -10 -3040.00 7 27360.00 90
6 2007-03-01 00:00:00.000 Sales -10 -3040.00 8 24320.00 80
*/

|||

WOW!!! Marvelous. I have been thinking and figuring out this for a long long time. How To do it without using cursor and looping the cursor row by row. THANK YOU VERY MUCH !!! I never thought it was so straightforward. The reason I can not figure it out because I never thought you can declare variable at update statement to store the previous calculations.

Can I just use the Select Statement from the CTE directly in the Insert statement to insert the record without using the CTE?
Why do you use CTE anyway?
Why do use the row_number()? I have read the help file, but i dont understand. I believe the purpose is to build the result set in sequential order.
What is the purpose to use seq while you can just order the result set?
What do you think about the performance ? Because each time user add new or adjust sales data I need to be able determine the cost average on the fly

And If I want to adopt the FIFO costing method do I need to add another table or Field? I know this more about db design issues but please help me

|||

Hi Kent,

Is this solution based on an ordered update?

If so, then it is not a reliable one.

For this kind of calculation (ordered calculations), a cursor based solution uses to perform better than a set one. That is the reason why Itzik Ben-Gan and Sujata Mehta sent an enhancement request to Microsoft, to improve the OVER clause. Here is the link to the document, It is worth to read it. Inside the document, you will find the links to the entries of the suggestions in Microsoft connect, I encourage everyone that read the document, please, to vote.

OVER Clause and Orderes Calculations - Feature Enhancements Request by Itzik Ben-Gan and Sujata Mehta

(first link after Whitepapers)

http://www.sql.co.il/books/insidetsql2005/resources.htm

Here Is the cursor based solution, notice that the table will be scanned just once, contrary to the set based one.

Code Snippet

use tempdb

go

create table dbo.item_txns

( id integer,

date datetime,

type varchar(10),

qtyin integer,

cost_out_amt decimal(12,2)

)

go

declare @.avg table

( id integer,

date datetime,

type varchar(10),

qtyin integer,

cost_out_amt decimal(12,2),

seq integer primary key,

moving_cost decimal(12,2),

moving_qty integer

)

insert into dbo.item_txns

select 1,'01/01/2007','PURCHASE',10,1000 union all

select 2,'01/01/2007','PURCHAES',5,1100 union all

select 3,'01/01/2007','SALES',-5,null union all

select 4,'02/01/2007','Purchase',20,9000 union all

select 5,'02/01/2007','SALES',-10,null union all

select 5,'02/01/2007','purchase',50,8000 union all

select 6,'03/01/2007','Sales',-10,null union all

select 7,'01/01/2007','Purchase',20,12000

declare @.id integer

declare @.date datetime

declare @.type varchar(10)

declare @.qtyin integer

declare @.cost_out_amt decimal(12,2)

declare @.Seq int

declare @.movingCost decimal(12,2)

declare @.movingQty integer

declare c cursor fast_forward read_only

for

select

id,

date,

[type],

qtyin,

cost_out_amt

from

item_txns

order by

date, id, [type], qtyin

set @.Seq = 0

set @.movingCost = 0

set @.movingQty = 0

open c

while 1 = 1

begin

fetch next from c into @.id, @.date, @.type, @.qtyin, @.cost_out_amt

if @.@.error != 0 or @.@.fetch_status != 0 break -- please, use better error handle

set @.Seq = @.Seq + 1

set @.cost_out_amt = coalesce(@.cost_out_amt, @.qtyin * isnull((@.movingCost/nullif(@.movingQty, 0)), 0))

set @.movingCost = @.movingCost + coalesce(@.cost_out_amt, @.qtyin * isnull((@.movingCost/nullif(@.movingQty, 0)), 0))

set @.movingQty = @.movingQty + isnull(@.qtyin,0)

insert into @.avg (

id,

date,

type,

qtyin,

cost_out_amt,

seq,

moving_cost,

moving_qty

)

values(@.id, @.date, @.type, @.qtyin, @.cost_out_amt, @.Seq, @.movingCost, @.movingQty)

end

close c

deallocate c

select * from @.avg order by Seq

drop table dbo.item_txns

go

AMB

|||

Thanks for picking me up!

|||

Hi Kent,

I do not understand what you try to tell me. I just wanted to point that this is one of the cases where cursor based solution can outperform set based one. I also wanted to let you know about this document and the possibility to help ourself voting in connect.

I had no other intention.

Regards,

Alejandro Mesa

|||

I mean thank you for helping me, Alejandro. This is what I love about this forum; I get "picked up" when I fall without getting grief over an "honest mistake".

I wandered about the first pass through the table in this case and I wandered about the use of the update extensions. However, to emphasize for those who are following.

I created a table variable with a primary key based on sequence number

The primary key has a default attribute of being clustered (an assumption, is this correct?)

I inserted the records into the table variable

When I process the table variable, the order processed will NOT be according to the primary key -- despite the assumption of clustering

Because of the ordering problem, the update will not be reliable

|||

Hi Kent,

Now I understand, sometimes I have problem with the language.

You explained it very well, except for the two-pass process. If we want to use a set based solution, then we can use a correlated subquery, this will be used per each row, or we can use "update ... from " joining the table with it self. Let us see how we can calculate the moving quantity, for the sake of simplicity.

update @.avg

set moving_qty = (select sum(qtyin) from @.avg as a where a.seq <= [@.avg].seq)

can you see how many passes?

You can read more in the document sent by Itzik and Sujata, they included a comparison between different approach.

AMB

|||

Alejandro:

Really, since I can't pin any hopes on the update being ordered I see nothing redeaming about the process I outlined. Man, I don't know what the number of posts I made which turn out to be just wrong -- and the previous posts were not corrected!

I'm worried about the damage I've done.

|||

Hi Kent,

Do not feel bad, I have been there too. Learning from our mistakes make us better. There is no doubt that you have done a very good job helping others in this newsgroup.

Sincerely,

Alejandro Mesa

|||

Thanks Alejandro. I need some advice as to what action is best to take. I figure I got 7-15 previous posts in which I might had outlined a similar routine and problem was not pointed out. Do I need to go back to those previous posts and straighten them out as best as I can? Most of them are easy enough to find.

I think I remember one man in particular -- Jake. He was working with the orange alert program; trying to do something about protecting kids. I really think I ought to re-establish contact with Jake and let him know of the potential problem.

There was also a guy a year ago named William who had a query that took between 40 and 60 hours to process; I had a process that cut it down to a few minutes. But another guy had a process similar to the one I outlined that was somewhat faster using ordered updates. It wasn't my solution, but should I contact William?

Again, looking for advice.

Kent

(I am going to split this off from the original post because I feel this is a separate issue and doesn't contribute to the original post. )

Also, would I be better off moving this portion to the REPORTING forum?

|||

Hi Kent,

This shows us how much you care for the members of the ng, it is really kind. I do not know what to tell you, because I have no idea where to knock. If you can find those post and contact the OP, for sure they will apreciate it.

Sorry I can not give you a better advice.

Regards,

Alejandro Mesa

|||

Alejandro:

That is an excellent answer. I will give it a go. Thank you for helping me.

Kent

|||

" Is this solution based on an ordered update? "

Could you explain more in detail please?

|||

Please use the solution supplied by Alejandro (hunchback); apparently, the solution I provided is not correct. Sorry for the inconvenience.

Moving Average template

Hi,

I have to include a calculated member in my cube as follows:

I have two figures (X, y) for each month in the year , i need to calculate Sum(X) / Sum(Y) as a % for the previous 12 months.

Can i use the moving average template in the calulations tab?

Assuming that the fact table has one record per month with fields "X" and "Y", and that there is an appropriate time dimension to base the calculations on, then you could try the following approach:

Create cube "sum" measures on each of "X" and "Y" fields.

Moving and renaming SSIS Projects

Say I created a new ssis Project and allowed the project to created a subfolder .. say both the project and subfolder are called x123.

I now want to rename the project to "Mycompany.MyProject" and want to remove the subfolder.

Possible?

Trying to avoid this, I made a copy of the parent folder, and manually tried to copy and paste all objects .. but am getting errors with connection manager entries.

Thanks for any help or information!

I think you need to edit the .sln and .dtproj files to reflect the new folder structure and names.

May be it is easier if you create a new project, and the use add existing package/item option to add the packages and other objects you want to preserve.

|||

Thanks. Copy and pasting finally worked. One quirk, if you paste in connections from another project it don't like that , but if you paste and then copy from new project and paste again and rename and then reset all all objects using the connections that seemed to work.

I'll try changing content of those files, but do suspect it will not be clean. It would be great if there were a utility or something.

sql

moving and renaming a file

I have a DTS package that I currently execute manually. This DTS package
reads a text file and imports it into a table.
I would like to have it run automaticly.
For that to work correctly, it would need to check to see if the txt file is
there (the name is always teh same), if it is not there, then to quit. If
it is there, to read and import it as normal, then once completed, for it to
rename teh file and move it to another location on teh computer.
How do I do that?I'd use ActiveXScipt and the FileSystemObject. Here is some sample code:
Function Main()
dim fso, source, dest, oldFile
set fso = CreateObject("Scripting.FileSystemObject")
source = "\\myBox\FAAIVR\BENEFIT_GROUP_INFO.txt"
oldFile = replace(source, ".txt", "_" & replace(date - 7, "/","_") & ".txt")
if (fso.FileExists(source)) then
dest = replace(source, ".txt", "_" & replace(date, "/","_") & ".txt")
fso.MoveFile source, dest
end if
if (fso.FileExists(oldFile)) then
fso.DeleteFile (oldFile)
"Johnfli" wrote:
> I have a DTS package that I currently execute manually. This DTS package
> reads a text file and imports it into a table.
> I would like to have it run automaticly.
> For that to work correctly, it would need to check to see if the txt file is
> there (the name is always teh same), if it is not there, then to quit. If
> it is there, to read and import it as normal, then once completed, for it to
> rename teh file and move it to another location on teh computer.
> How do I do that?
>
>

moving an sql server 2005 database to a sql server 2000 database

I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database.

I'm not sure the best way to do this......

Can anyone enlighten me?....

this article explains it.

http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1149585_tax301536,00.html

hth,
mcm

|||

pizzamaker74:

I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database.

I'm not sure the best way to do this......

Can anyone enlighten me?....

If both are in the same network or box just register the 2005 with the 2000 create a blank database in 2000 and use DTS to copy everything to the 2000. You may still need to move permissions that is covered in the FAQ below. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms190631.aspx

http://forums.asp.net/thread/1454694.aspx

|||

Caddre;

Thanks heaps.

This document is also very useful:

How to Downgrade a Database from SQL Server 2005 to SQL Server 2000

As you may all know, SQL Server 2005 request a minimum of 8GB RAM to work… let say satisfactorily. I first didn't knew that and after a while from the upgrade I did from SQL Server 2000 to 2005 my SQL Services were starting to crash three or four times per DAY!!!

At first I thought I was being attacked, but soon I realized it was nothing like that. I then decided to downgrade to an SQL Server 2000 edition. Though I looked around the internet to find some information on how to do that, I got very disappointed when I realized that no actual documentation of any kind could be found for that. So I am posting this thread to inform you on the procedures I had to follow for this action.

Before beginning I must assume, firstly that the user, who will attempt such thing, has a basic knowledge of SQL Environment, secondly that he has the two versions already installed (both 2000 and 2005), that a basic backup of the databases has been created and finally that all the 2005 SQL Server Users have been created at the SQL Server 2000 environment as well.

Step 1 Generating Scripts for the Database Elements and Structures

1) Right-click over the desired Database at 2005, Choose Tasks and the Generate Scripts (Option).
2) At the pop-up Dialog Box click at the Script All Objects in the selected Databases check box, to activate it and then Click the Next Button.
3) Set the following Elements to the following Values
a. Script Collation , set to TRUE
b. Script Database Create, set to TRUE
c. Script of SQL Version, set to SQL SERVER 2000
d. Script foreign keys, set to FALSE
e. Script Triggers, set to FALSE
Then Hit the Next button
4) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.
5) Click Finish

After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Structure of the Database will be created.

Be careful, the SQL Server 2005 Edition inserts the Views in a random place through the script. Therefore, all the scripts that are referred to the Views MUST be moved to the end of the script. If the Query Analyzer shows some errors do not be bothered. Delete all the elements created from the script and after you fix the code run it again.


Step2 Moving the data from 2005 to 2000

1) After completing the previous step successfully, moving the data follows. Right-click at the 2005 database you used to run the previous step and select Tasks and then choose the Export Data (option).
2) From the pop-up Dialog Box, select the Source Db and Click at the Next Button.
3) At the next step you will have to choose the destination server and the destination Database for the Data to be exported. Then Click Next.
4) A List of all the Source Database's Elements will appear in the screen. Select one by one all the Elements you wish to move and for each one click at the button Edit Mappings (Located at the bottom right corner of the Dialog Box just under the Elements list). A new Dialog box will pop-up. Select the Delete rows in Destination Tables option and activate the Enable Identity Insert Option. (Remember to repeat this action for each of the selected Element from the list that will be moved.

CAUTION!!! A malfunction of the SQL Server 2005 has been found. Not sure why, after multiple tries I have observed that when I tried to move more than twelve Elements at once, the Export Data Wizard of SQL Server 2005 seemed to disable the Enable Identity Insert Option that was activated over the Edit Mappings Dialog Box. But if the number of the selected Elements is smaller than 12 no problem seemed to appear.

Step 3 Generating Scripts for the Database Foreign Keys and Triggers

Finally, to successfully finish the downgrade of the Database, the Triggers and the Foreign Keys of the DB must be produced. The procedure that should be followed is the one stated next:

1) Right-Click at the SQL 2005 Database and Select from Tasks Menu the Generate Scripts Option.
2) Using the pop-up Dialog Box make sure that the check box Script All Objects in the selected Databases is not enabled and hit the Next Button.
3) Set all the Elements on the List to a False Value except the ones that follow:
a. Include IF NOT EXISTS , set to TRUE
b. Script Owner, set to TRUE
c. Script of SQL Version, set to SQL SERVER 2000
d. Script foreign keys, set to TRUE
e. Script Triggers, set to TRUE
Then Hit the Next button
4) After finishing reading the Elements of the Database, a new list will appear at the Dialog Box. Make sure that you select ONLY THE TABLES of the Database and hit the Next Button.
5) At the screen that follows hit the Select All button and the Next.
6) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.
7) Click Finish Button.

After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Foreign Keys and the Triggers of the Database will be created.

After these steps the database should be fully functional under the SQL Server 2000 edition.

www.hellasinternet.com
Panos Tzirakis & George Dounavis

|||I am glad I could help.

Moving an SQL Server 2000 database

When I crated my first SQL Server 2000 database I overlooked the database
lcoation on the harddisk - which by default points to the C: drive. I need
to move it all over to the d: drive and place in the appropriate directory.
What is the best way to accomplish this? Can I use DTS for this or is there
a better way to do this?
Once moved, is there any thing else that I need to do manually to make sure
that everything related to the database did get moved propery and all the
privs are set etc... etc..
Thanks, Brad
If it is going to remain on the same SQL Instance then you have two very
easy options.
1. Detach the files, move them and reattach them
2. Restore a full backup using the WITH MOVE option to change the file
locations.
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
Andrew J. Kelly SQL MVP
"Brad Pears" <bradp@.truenorthloghomes.com> wrote in message
news:eCiaDuosHHA.4916@.TK2MSFTNGP04.phx.gbl...
> When I crated my first SQL Server 2000 database I overlooked the database
> lcoation on the harddisk - which by default points to the C: drive. I need
> to move it all over to the d: drive and place in the appropriate
> directory.
> What is the best way to accomplish this? Can I use DTS for this or is
> there a better way to do this?
> Once moved, is there any thing else that I need to do manually to make
> sure that everything related to the database did get moved propery and all
> the privs are set etc... etc..
> Thanks, Brad
>
|||Brad Pears (bradp@.truenorthloghomes.com) writes:
> When I crated my first SQL Server 2000 database I overlooked the
> database lcoation on the harddisk - which by default points to the C:
> drive. I need to move it all over to the d: drive and place in the
> appropriate directory.
> What is the best way to accomplish this? Can I use DTS for this or is
> there a better way to do this?
Definitely. Run sp_detach_db, use Explorer to move the file to the desired
location, and then use sp_attach_db to make SQL Server aware of the new
location. (When you detach it, the database is gone as far as SQL Server
is concerned).
See Books Online for exact details on parameters etc.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Thanks for the comments. I used the detach / attach and it worked like a
top. Although I did hear that using the detach/attach option can sometimes
corrup the DB? Have either of you ever had this problem?
Thanks, Brad
"Brad Pears" <bradp@.truenorthloghomes.com> wrote in message
news:eCiaDuosHHA.4916@.TK2MSFTNGP04.phx.gbl...
> When I crated my first SQL Server 2000 database I overlooked the database
> lcoation on the harddisk - which by default points to the C: drive. I need
> to move it all over to the d: drive and place in the appropriate
> directory.
> What is the best way to accomplish this? Can I use DTS for this or is
> there a better way to do this?
> Once moved, is there any thing else that I need to do manually to make
> sure that everything related to the database did get moved propery and all
> the privs are set etc... etc..
> Thanks, Brad
>
|||Not if you do it correctly. If you simply copy over the files without first
detaching them you can have issues.
Andrew J. Kelly SQL MVP
"Brad Pears" <bradp@.truenorthloghomes.com> wrote in message
news:O36bt81sHHA.3504@.TK2MSFTNGP05.phx.gbl...
> Thanks for the comments. I used the detach / attach and it worked like a
> top. Although I did hear that using the detach/attach option can sometimes
> corrup the DB? Have either of you ever had this problem?
> Thanks, Brad
> "Brad Pears" <bradp@.truenorthloghomes.com> wrote in message
> news:eCiaDuosHHA.4916@.TK2MSFTNGP04.phx.gbl...
>

Moving an SQL 2000 database

I am going to be performing an upgrade on a 2000 server. I
will be adding two drives (mirrored). The server is
running 'ACT! Web' which uses SQL. I would like to move
the SQL database to the newly created volume, I am very
green when it comes to SQL, so I don't really know where
to begin.
If anyone hase some suggestions please post.Look up sp_detach_db and sp_attach_db in BOL.
"Steve" <deviller@.starbeam.ca> wrote in message
news:7b9001c402e7$cc7cd310$a001280a@.phx.gbl...
> I am going to be performing an upgrade on a 2000 server. I
> will be adding two drives (mirrored). The server is
> running 'ACT! Web' which uses SQL. I would like to move
> the SQL database to the newly created volume, I am very
> green when it comes to SQL, so I don't really know where
> to begin.
> If anyone hase some suggestions please post.|||And for some more reading, KB article 224071.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:O2yXqhuAEHA.3184@.TK2MSFTNGP09.phx.gbl...
> Look up sp_detach_db and sp_attach_db in BOL.
> "Steve" <deviller@.starbeam.ca> wrote in message
> news:7b9001c402e7$cc7cd310$a001280a@.phx.gbl...
> > I am going to be performing an upgrade on a 2000 server. I
> > will be adding two drives (mirrored). The server is
> > running 'ACT! Web' which uses SQL. I would like to move
> > the SQL database to the newly created volume, I am very
> > green when it comes to SQL, so I don't really know where
> > to begin.
> >
> > If anyone hase some suggestions please post.
>|||Steve wrote:
> I am going to be performing an upgrade on a 2000 server. I
> will be adding two drives (mirrored). The server is
> running 'ACT! Web' which uses SQL. I would like to move
> the SQL database to the newly created volume, I am very
> green when it comes to SQL, so I don't really know where
> to begin.
> If anyone hase some suggestions please post.
Look into Detach/Attach. See:
Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=224071
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map logins to users
http://www.dbmaint.com/SyncSqlLogins.asp
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
Restoring a .mdf
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Moving an SQL 2000 database

I am going to be performing an upgrade on a 2000 server. I
will be adding two drives (mirrored). The server is
running 'ACT! Web' which uses SQL. I would like to move
the SQL database to the newly created volume, I am very
green when it comes to SQL, so I don't really know where
to begin.
If anyone hase some suggestions please post.Look up sp_detach_db and sp_attach_db in BOL.
"Steve" <deviller@.starbeam.ca> wrote in message
news:7b9001c402e7$cc7cd310$a001280a@.phx.gbl...
> I am going to be performing an upgrade on a 2000 server. I
> will be adding two drives (mirrored). The server is
> running 'ACT! Web' which uses SQL. I would like to move
> the SQL database to the newly created volume, I am very
> green when it comes to SQL, so I don't really know where
> to begin.
> If anyone hase some suggestions please post.|||And for some more reading, KB article 224071.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:O2yXqhuAEHA.3184@.TK2MSFTNGP09.phx.gbl...
> Look up sp_detach_db and sp_attach_db in BOL.
> "Steve" <deviller@.starbeam.ca> wrote in message
> news:7b9001c402e7$cc7cd310$a001280a@.phx.gbl...
>|||Steve wrote:
> I am going to be performing an upgrade on a 2000 server. I
> will be adding two drives (mirrored). The server is
> running 'ACT! Web' which uses SQL. I would like to move
> the SQL database to the newly created volume, I am very
> green when it comes to SQL, so I don't really know where
> to begin.
> If anyone hase some suggestions please post.
Look into Detach/Attach. See:
Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=224071
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map logins to users
http://www.dbmaint.com/SyncSqlLogins.asp
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
Restoring a .mdf
http://www.sqlservercentral.com/scr...sp?scriptid=599
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.sql

Moving an SBS SharePoint Database

I wanted to setup SharePoint services but there are a couple issues
that I need some help with. I need to have the full-text search turned
on and I also have a space issue on the DC.
So, for the full-text search I need to upgrade the WMSDE to SQL Server.
But because I don't have the space on my DC I need to move the
database to another server. Fortunatly the other server already has
SQL 2000 installed on it.
My questions are then, can I infact move the databse to the second
server and if I can do I need to upgrade the DC to SQL 2000 before I
move the database so the full-text feature is supported?
EvanNot using the SBS SQL license - they have to be on the same machine..
FWIW - there are much better search engines then the full text search.
There are a lot of performance trade-offs for having the FTS engine
enabled on a database (or WSS site) of any size. Watch the CPU usage
when the MS Search Service starts up and you'll see what I mean.|||Thanks you for responding, sorry, I've been away for a little while.
We have SQL 2000 installed on the other server to run some accounting
software. Could we not use an instance of that for SharePoint, and
have SharePoint point to the other server?
Evan