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!
>
>
Showing posts with label production. Show all posts
Showing posts with label production. Show all posts
Friday, March 30, 2012
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
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!
>
>
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!
>
>
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!
>
>
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!
>
>
Wednesday, March 28, 2012
Moving a Large Table into New datafile vs. New database
Hi,
We have a production database of size 40GB and growing at the rate 1.5
GB/month and one table is occupying about 20GB and all other tables are of
normal size. Till now every table is in the PRIMARY group and it has only on
e
datafile. Now we would like to split that database by moving that large
table out.
I was wondering if anyone could tell me what would be the best option in
terms of performance of SQLServer of the following options:
1. Create a new Filegroup and move the table into the newfile group.
2. Simply add a new datafile to existing PRIMARY group and do nothing.
3. Create a new database and move the table to the new database, and create
a view of the table in the first database pointing to the table in the new
database (Assuming the foreign key constraints are taken care with in
application).
Thankyou,
VMVM wrote:
> Hi,
> We have a production database of size 40GB and growing at the rate 1.5
> GB/month and one table is occupying about 20GB and all other tables
> are of normal size. Till now every table is in the PRIMARY group and
> it has only one datafile. Now we would like to split that database
> by moving that large table out.
> I was wondering if anyone could tell me what would be the best option
> in terms of performance of SQLServer of the following options:
> 1. Create a new Filegroup and move the table into the newfile group.
> 2. Simply add a new datafile to existing PRIMARY group and do nothing.
> 3. Create a new database and move the table to the new database, and
> create a view of the table in the first database pointing to the
> table in the new database (Assuming the foreign key constraints are
> taken care with in application).
> Thankyou,
> VM
First we would want to understand why you feel you need to move this
table out of the primary filegroup. The size is not a concern for me,
but it may be for you. Are you having backup issues? Also, if you create
a second filegroup, it would really need to be on a different array to
provide performance benefits. If the new filegroup is on the same drive
array, it will not help (backup flexibility aside).
If you do decide to move the table to another filegroup, the easiest way
to do this is to recreate the clustered index for the table on the new
filegroup (off hours). That will cause the table to move to the new
location. Non-clustered indexes will stay put, however. You can move
them as well by manually recreating them, but having the non-clustered
indexes on a separate array can help performance if the table is
accessed/updated frequently.
David Gugick
Quest Software
www.quest.com|||Hi David,
Thankyou for the reply. We have been observing some performace degradation
of our database recently including full database backup time taken. After
brainstorming the ways we can split one large database file into multiple
physical files and we came up with below given options.
I think we might create a new filegroup (as you said on a different array)
and move some of the data. Either we would move all the indexes on to the
new group, or, Since one of our table is half of the size of total database
and is frequently inserted and updated, we may move that one table onto new
filegroup. Creation of clustered index is also a great idea.
Thanks again,
VM
"David Gugick" wrote:
> VM wrote:
> First we would want to understand why you feel you need to move this
> table out of the primary filegroup. The size is not a concern for me,
> but it may be for you. Are you having backup issues? Also, if you create
> a second filegroup, it would really need to be on a different array to
> provide performance benefits. If the new filegroup is on the same drive
> array, it will not help (backup flexibility aside).
> If you do decide to move the table to another filegroup, the easiest way
> to do this is to recreate the clustered index for the table on the new
> filegroup (off hours). That will cause the table to move to the new
> location. Non-clustered indexes will stay put, however. You can move
> them as well by manually recreating them, but having the non-clustered
> indexes on a separate array can help performance if the table is
> accessed/updated frequently.
>
> --
> David Gugick
> Quest Software
> www.quest.com
>
We have a production database of size 40GB and growing at the rate 1.5
GB/month and one table is occupying about 20GB and all other tables are of
normal size. Till now every table is in the PRIMARY group and it has only on
e
datafile. Now we would like to split that database by moving that large
table out.
I was wondering if anyone could tell me what would be the best option in
terms of performance of SQLServer of the following options:
1. Create a new Filegroup and move the table into the newfile group.
2. Simply add a new datafile to existing PRIMARY group and do nothing.
3. Create a new database and move the table to the new database, and create
a view of the table in the first database pointing to the table in the new
database (Assuming the foreign key constraints are taken care with in
application).
Thankyou,
VMVM wrote:
> Hi,
> We have a production database of size 40GB and growing at the rate 1.5
> GB/month and one table is occupying about 20GB and all other tables
> are of normal size. Till now every table is in the PRIMARY group and
> it has only one datafile. Now we would like to split that database
> by moving that large table out.
> I was wondering if anyone could tell me what would be the best option
> in terms of performance of SQLServer of the following options:
> 1. Create a new Filegroup and move the table into the newfile group.
> 2. Simply add a new datafile to existing PRIMARY group and do nothing.
> 3. Create a new database and move the table to the new database, and
> create a view of the table in the first database pointing to the
> table in the new database (Assuming the foreign key constraints are
> taken care with in application).
> Thankyou,
> VM
First we would want to understand why you feel you need to move this
table out of the primary filegroup. The size is not a concern for me,
but it may be for you. Are you having backup issues? Also, if you create
a second filegroup, it would really need to be on a different array to
provide performance benefits. If the new filegroup is on the same drive
array, it will not help (backup flexibility aside).
If you do decide to move the table to another filegroup, the easiest way
to do this is to recreate the clustered index for the table on the new
filegroup (off hours). That will cause the table to move to the new
location. Non-clustered indexes will stay put, however. You can move
them as well by manually recreating them, but having the non-clustered
indexes on a separate array can help performance if the table is
accessed/updated frequently.
David Gugick
Quest Software
www.quest.com|||Hi David,
Thankyou for the reply. We have been observing some performace degradation
of our database recently including full database backup time taken. After
brainstorming the ways we can split one large database file into multiple
physical files and we came up with below given options.
I think we might create a new filegroup (as you said on a different array)
and move some of the data. Either we would move all the indexes on to the
new group, or, Since one of our table is half of the size of total database
and is frequently inserted and updated, we may move that one table onto new
filegroup. Creation of clustered index is also a great idea.
Thanks again,
VM
"David Gugick" wrote:
> VM wrote:
> First we would want to understand why you feel you need to move this
> table out of the primary filegroup. The size is not a concern for me,
> but it may be for you. Are you having backup issues? Also, if you create
> a second filegroup, it would really need to be on a different array to
> provide performance benefits. If the new filegroup is on the same drive
> array, it will not help (backup flexibility aside).
> If you do decide to move the table to another filegroup, the easiest way
> to do this is to recreate the clustered index for the table on the new
> filegroup (off hours). That will cause the table to move to the new
> location. Non-clustered indexes will stay put, however. You can move
> them as well by manually recreating them, but having the non-clustered
> indexes on a separate array can help performance if the table is
> accessed/updated frequently.
>
> --
> David Gugick
> Quest Software
> www.quest.com
>
Moving a Large Table into New datafile vs. New database
Hi,
We have a production database of size 40GB and growing at the rate 1.5
GB/month and one table is occupying about 20GB and all other tables are of
normal size. Till now every table is in the PRIMARY group and it has only one
datafile. Now we would like to split that database by moving that large
table out.
I was wondering if anyone could tell me what would be the best option in
terms of performance of SQLServer of the following options:
1. Create a new Filegroup and move the table into the newfile group.
2. Simply add a new datafile to existing PRIMARY group and do nothing.
3. Create a new database and move the table to the new database, and create
a view of the table in the first database pointing to the table in the new
database (Assuming the foreign key constraints are taken care with in
application).
Thankyou,
VMVM wrote:
> Hi,
> We have a production database of size 40GB and growing at the rate 1.5
> GB/month and one table is occupying about 20GB and all other tables
> are of normal size. Till now every table is in the PRIMARY group and
> it has only one datafile. Now we would like to split that database
> by moving that large table out.
> I was wondering if anyone could tell me what would be the best option
> in terms of performance of SQLServer of the following options:
> 1. Create a new Filegroup and move the table into the newfile group.
> 2. Simply add a new datafile to existing PRIMARY group and do nothing.
> 3. Create a new database and move the table to the new database, and
> create a view of the table in the first database pointing to the
> table in the new database (Assuming the foreign key constraints are
> taken care with in application).
> Thankyou,
> VM
First we would want to understand why you feel you need to move this
table out of the primary filegroup. The size is not a concern for me,
but it may be for you. Are you having backup issues? Also, if you create
a second filegroup, it would really need to be on a different array to
provide performance benefits. If the new filegroup is on the same drive
array, it will not help (backup flexibility aside).
If you do decide to move the table to another filegroup, the easiest way
to do this is to recreate the clustered index for the table on the new
filegroup (off hours). That will cause the table to move to the new
location. Non-clustered indexes will stay put, however. You can move
them as well by manually recreating them, but having the non-clustered
indexes on a separate array can help performance if the table is
accessed/updated frequently.
David Gugick
Quest Software
www.quest.com|||Hi David,
Thankyou for the reply. We have been observing some performace degradation
of our database recently including full database backup time taken. After
brainstorming the ways we can split one large database file into multiple
physical files and we came up with below given options.
I think we might create a new filegroup (as you said on a different array)
and move some of the data. Either we would move all the indexes on to the
new group, or, Since one of our table is half of the size of total database
and is frequently inserted and updated, we may move that one table onto new
filegroup. Creation of clustered index is also a great idea.
Thanks again,
VM
"David Gugick" wrote:
> VM wrote:
> > Hi,
> >
> > We have a production database of size 40GB and growing at the rate 1.5
> > GB/month and one table is occupying about 20GB and all other tables
> > are of normal size. Till now every table is in the PRIMARY group and
> > it has only one datafile. Now we would like to split that database
> > by moving that large table out.
> >
> > I was wondering if anyone could tell me what would be the best option
> > in terms of performance of SQLServer of the following options:
> >
> > 1. Create a new Filegroup and move the table into the newfile group.
> > 2. Simply add a new datafile to existing PRIMARY group and do nothing.
> > 3. Create a new database and move the table to the new database, and
> > create a view of the table in the first database pointing to the
> > table in the new database (Assuming the foreign key constraints are
> > taken care with in application).
> >
> > Thankyou,
> > VM
> First we would want to understand why you feel you need to move this
> table out of the primary filegroup. The size is not a concern for me,
> but it may be for you. Are you having backup issues? Also, if you create
> a second filegroup, it would really need to be on a different array to
> provide performance benefits. If the new filegroup is on the same drive
> array, it will not help (backup flexibility aside).
> If you do decide to move the table to another filegroup, the easiest way
> to do this is to recreate the clustered index for the table on the new
> filegroup (off hours). That will cause the table to move to the new
> location. Non-clustered indexes will stay put, however. You can move
> them as well by manually recreating them, but having the non-clustered
> indexes on a separate array can help performance if the table is
> accessed/updated frequently.
>
> --
> David Gugick
> Quest Software
> www.quest.com
>
We have a production database of size 40GB and growing at the rate 1.5
GB/month and one table is occupying about 20GB and all other tables are of
normal size. Till now every table is in the PRIMARY group and it has only one
datafile. Now we would like to split that database by moving that large
table out.
I was wondering if anyone could tell me what would be the best option in
terms of performance of SQLServer of the following options:
1. Create a new Filegroup and move the table into the newfile group.
2. Simply add a new datafile to existing PRIMARY group and do nothing.
3. Create a new database and move the table to the new database, and create
a view of the table in the first database pointing to the table in the new
database (Assuming the foreign key constraints are taken care with in
application).
Thankyou,
VMVM wrote:
> Hi,
> We have a production database of size 40GB and growing at the rate 1.5
> GB/month and one table is occupying about 20GB and all other tables
> are of normal size. Till now every table is in the PRIMARY group and
> it has only one datafile. Now we would like to split that database
> by moving that large table out.
> I was wondering if anyone could tell me what would be the best option
> in terms of performance of SQLServer of the following options:
> 1. Create a new Filegroup and move the table into the newfile group.
> 2. Simply add a new datafile to existing PRIMARY group and do nothing.
> 3. Create a new database and move the table to the new database, and
> create a view of the table in the first database pointing to the
> table in the new database (Assuming the foreign key constraints are
> taken care with in application).
> Thankyou,
> VM
First we would want to understand why you feel you need to move this
table out of the primary filegroup. The size is not a concern for me,
but it may be for you. Are you having backup issues? Also, if you create
a second filegroup, it would really need to be on a different array to
provide performance benefits. If the new filegroup is on the same drive
array, it will not help (backup flexibility aside).
If you do decide to move the table to another filegroup, the easiest way
to do this is to recreate the clustered index for the table on the new
filegroup (off hours). That will cause the table to move to the new
location. Non-clustered indexes will stay put, however. You can move
them as well by manually recreating them, but having the non-clustered
indexes on a separate array can help performance if the table is
accessed/updated frequently.
David Gugick
Quest Software
www.quest.com|||Hi David,
Thankyou for the reply. We have been observing some performace degradation
of our database recently including full database backup time taken. After
brainstorming the ways we can split one large database file into multiple
physical files and we came up with below given options.
I think we might create a new filegroup (as you said on a different array)
and move some of the data. Either we would move all the indexes on to the
new group, or, Since one of our table is half of the size of total database
and is frequently inserted and updated, we may move that one table onto new
filegroup. Creation of clustered index is also a great idea.
Thanks again,
VM
"David Gugick" wrote:
> VM wrote:
> > Hi,
> >
> > We have a production database of size 40GB and growing at the rate 1.5
> > GB/month and one table is occupying about 20GB and all other tables
> > are of normal size. Till now every table is in the PRIMARY group and
> > it has only one datafile. Now we would like to split that database
> > by moving that large table out.
> >
> > I was wondering if anyone could tell me what would be the best option
> > in terms of performance of SQLServer of the following options:
> >
> > 1. Create a new Filegroup and move the table into the newfile group.
> > 2. Simply add a new datafile to existing PRIMARY group and do nothing.
> > 3. Create a new database and move the table to the new database, and
> > create a view of the table in the first database pointing to the
> > table in the new database (Assuming the foreign key constraints are
> > taken care with in application).
> >
> > Thankyou,
> > VM
> First we would want to understand why you feel you need to move this
> table out of the primary filegroup. The size is not a concern for me,
> but it may be for you. Are you having backup issues? Also, if you create
> a second filegroup, it would really need to be on a different array to
> provide performance benefits. If the new filegroup is on the same drive
> array, it will not help (backup flexibility aside).
> If you do decide to move the table to another filegroup, the easiest way
> to do this is to recreate the clustered index for the table on the new
> filegroup (off hours). That will cause the table to move to the new
> location. Non-clustered indexes will stay put, however. You can move
> them as well by manually recreating them, but having the non-clustered
> indexes on a separate array can help performance if the table is
> accessed/updated frequently.
>
> --
> David Gugick
> Quest Software
> www.quest.com
>
Moving a Large Table into New datafile vs. New database
Hi,
We have a production database of size 40GB and growing at the rate 1.5
GB/month and one table is occupying about 20GB and all other tables are of
normal size. Till now every table is in the PRIMARY group and it has only one
datafile. Now we would like to split that database by moving that large
table out.
I was wondering if anyone could tell me what would be the best option in
terms of performance of SQLServer of the following options:
1. Create a new Filegroup and move the table into the newfile group.
2. Simply add a new datafile to existing PRIMARY group and do nothing.
3. Create a new database and move the table to the new database, and create
a view of the table in the first database pointing to the table in the new
database (Assuming the foreign key constraints are taken care with in
application).
Thankyou,
VM
VM wrote:
> Hi,
> We have a production database of size 40GB and growing at the rate 1.5
> GB/month and one table is occupying about 20GB and all other tables
> are of normal size. Till now every table is in the PRIMARY group and
> it has only one datafile. Now we would like to split that database
> by moving that large table out.
> I was wondering if anyone could tell me what would be the best option
> in terms of performance of SQLServer of the following options:
> 1. Create a new Filegroup and move the table into the newfile group.
> 2. Simply add a new datafile to existing PRIMARY group and do nothing.
> 3. Create a new database and move the table to the new database, and
> create a view of the table in the first database pointing to the
> table in the new database (Assuming the foreign key constraints are
> taken care with in application).
> Thankyou,
> VM
First we would want to understand why you feel you need to move this
table out of the primary filegroup. The size is not a concern for me,
but it may be for you. Are you having backup issues? Also, if you create
a second filegroup, it would really need to be on a different array to
provide performance benefits. If the new filegroup is on the same drive
array, it will not help (backup flexibility aside).
If you do decide to move the table to another filegroup, the easiest way
to do this is to recreate the clustered index for the table on the new
filegroup (off hours). That will cause the table to move to the new
location. Non-clustered indexes will stay put, however. You can move
them as well by manually recreating them, but having the non-clustered
indexes on a separate array can help performance if the table is
accessed/updated frequently.
David Gugick
Quest Software
www.quest.com
|||Hi David,
Thankyou for the reply. We have been observing some performace degradation
of our database recently including full database backup time taken. After
brainstorming the ways we can split one large database file into multiple
physical files and we came up with below given options.
I think we might create a new filegroup (as you said on a different array)
and move some of the data. Either we would move all the indexes on to the
new group, or, Since one of our table is half of the size of total database
and is frequently inserted and updated, we may move that one table onto new
filegroup. Creation of clustered index is also a great idea.
Thanks again,
VM
"David Gugick" wrote:
> VM wrote:
> First we would want to understand why you feel you need to move this
> table out of the primary filegroup. The size is not a concern for me,
> but it may be for you. Are you having backup issues? Also, if you create
> a second filegroup, it would really need to be on a different array to
> provide performance benefits. If the new filegroup is on the same drive
> array, it will not help (backup flexibility aside).
> If you do decide to move the table to another filegroup, the easiest way
> to do this is to recreate the clustered index for the table on the new
> filegroup (off hours). That will cause the table to move to the new
> location. Non-clustered indexes will stay put, however. You can move
> them as well by manually recreating them, but having the non-clustered
> indexes on a separate array can help performance if the table is
> accessed/updated frequently.
>
> --
> David Gugick
> Quest Software
> www.quest.com
>
We have a production database of size 40GB and growing at the rate 1.5
GB/month and one table is occupying about 20GB and all other tables are of
normal size. Till now every table is in the PRIMARY group and it has only one
datafile. Now we would like to split that database by moving that large
table out.
I was wondering if anyone could tell me what would be the best option in
terms of performance of SQLServer of the following options:
1. Create a new Filegroup and move the table into the newfile group.
2. Simply add a new datafile to existing PRIMARY group and do nothing.
3. Create a new database and move the table to the new database, and create
a view of the table in the first database pointing to the table in the new
database (Assuming the foreign key constraints are taken care with in
application).
Thankyou,
VM
VM wrote:
> Hi,
> We have a production database of size 40GB and growing at the rate 1.5
> GB/month and one table is occupying about 20GB and all other tables
> are of normal size. Till now every table is in the PRIMARY group and
> it has only one datafile. Now we would like to split that database
> by moving that large table out.
> I was wondering if anyone could tell me what would be the best option
> in terms of performance of SQLServer of the following options:
> 1. Create a new Filegroup and move the table into the newfile group.
> 2. Simply add a new datafile to existing PRIMARY group and do nothing.
> 3. Create a new database and move the table to the new database, and
> create a view of the table in the first database pointing to the
> table in the new database (Assuming the foreign key constraints are
> taken care with in application).
> Thankyou,
> VM
First we would want to understand why you feel you need to move this
table out of the primary filegroup. The size is not a concern for me,
but it may be for you. Are you having backup issues? Also, if you create
a second filegroup, it would really need to be on a different array to
provide performance benefits. If the new filegroup is on the same drive
array, it will not help (backup flexibility aside).
If you do decide to move the table to another filegroup, the easiest way
to do this is to recreate the clustered index for the table on the new
filegroup (off hours). That will cause the table to move to the new
location. Non-clustered indexes will stay put, however. You can move
them as well by manually recreating them, but having the non-clustered
indexes on a separate array can help performance if the table is
accessed/updated frequently.
David Gugick
Quest Software
www.quest.com
|||Hi David,
Thankyou for the reply. We have been observing some performace degradation
of our database recently including full database backup time taken. After
brainstorming the ways we can split one large database file into multiple
physical files and we came up with below given options.
I think we might create a new filegroup (as you said on a different array)
and move some of the data. Either we would move all the indexes on to the
new group, or, Since one of our table is half of the size of total database
and is frequently inserted and updated, we may move that one table onto new
filegroup. Creation of clustered index is also a great idea.
Thanks again,
VM
"David Gugick" wrote:
> VM wrote:
> First we would want to understand why you feel you need to move this
> table out of the primary filegroup. The size is not a concern for me,
> but it may be for you. Are you having backup issues? Also, if you create
> a second filegroup, it would really need to be on a different array to
> provide performance benefits. If the new filegroup is on the same drive
> array, it will not help (backup flexibility aside).
> If you do decide to move the table to another filegroup, the easiest way
> to do this is to recreate the clustered index for the table on the new
> filegroup (off hours). That will cause the table to move to the new
> location. Non-clustered indexes will stay put, however. You can move
> them as well by manually recreating them, but having the non-clustered
> indexes on a separate array can help performance if the table is
> accessed/updated frequently.
>
> --
> David Gugick
> Quest Software
> www.quest.com
>
Friday, March 23, 2012
Moving 6.5 database
HI
I have to move a production 6.5 database to another server (also 6.5) in wich other dbs already exist.
1. Can I just backup and restore on the other server ?
2. Do I have to check for the same service packs on both?
3. Are there other ways to do it?
Any help will be apreciated, since I can't find much info about this version.
Thank youIt's been awhile since I've used 6.5, however I know in the pass I've moved a dump over from one machine to another, a few notes:[list=a]
Make sure both machines where setup with the same CodePage
You'll have to resync any UserIDs in the database sysusers, to those in Master..syslogins, I believe Microsoft have a stored proc that does this
Recompile all of your procs, we use to have a problem with this. Use a simple cursor to loop through user tables issuing a 'SP_RECOMPILE' on each table
[/list=a]
I have to move a production 6.5 database to another server (also 6.5) in wich other dbs already exist.
1. Can I just backup and restore on the other server ?
2. Do I have to check for the same service packs on both?
3. Are there other ways to do it?
Any help will be apreciated, since I can't find much info about this version.
Thank youIt's been awhile since I've used 6.5, however I know in the pass I've moved a dump over from one machine to another, a few notes:[list=a]
Make sure both machines where setup with the same CodePage
You'll have to resync any UserIDs in the database sysusers, to those in Master..syslogins, I believe Microsoft have a stored proc that does this
Recompile all of your procs, we use to have a problem with this. Use a simple cursor to loop through user tables issuing a 'SP_RECOMPILE' on each table
[/list=a]
Moving 5 databases to new server
In a few hours I am going to move 5 user databases from the existing product
ion server to a new one. I have a plan in place, but I would like to verify
that its the correct approach.
Here's my plan:
1. Stop SQL Server on production using Services Manager
2. Start SQL Server on production in Single User Mode
-using Services Manager, type "-m" in startup parameters
3. Perform a manual Full Backup of each user database to be migrated
4. Stop the SQL Server on production
5. Restore each database on the new server from the backup files
-check "force restore over existing database"
6. Link all orphaned logins on each database
7. Point the application to the new server
My main question is: Do I need to restore transaction logs also?
No users will be connected at the time of the full backup. I am currectly u
sing the Full recovery model on each database. I have already recreated all
the DTS jobs and Database Maintenance Plans on the new server. The new ser
ver is not yet in use, so I
was able to test the process by restoring each database from the production
backup files to the new server.
The production SQL Server is 2000 version 8.00.194(RTM). The new server is
2000 version 8.00.760(SP3).
Am I overlooking anything?
Thanks,
ScottI am placing the production database in single user mode to ensure there are
no other users.
I have actually performed the restores and they worked OK. I am not restori
ng the system databases only user databases.
The link you provided did not work.
"DeeJay Puar" wrote:
> Hi,
> Here are some comments:
> 1. Production server does have to in 'Single User Mode',
> since no one is going to be connected.
> 2. If you do a fullbackup and restore without further
> activity on the database, you do not have to restore any
> transaction log backups.
> 3. I do not think, restores will work with different
> service packs (production is different level than new
> server).
> 4. Here is an nice article to review:
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;314546&Product=sql
> hth
> DeeJay
> the existing production server to a new one. I have a
> plan in place, but I would like to verify that its the
> correct approach.
> parameters
> be migrated
> backup files
> logs also?
> backup. I am currectly using the Full recovery model on
> each database. I have already recreated all the DTS jobs
> and Database Maintenance Plans on the new server. The new
> server is not yet in use, so I was able to test the
> process by restoring each database from the production
> backup files to the new server.
> The new server is 2000 version 8.00.760(SP3).
>|||Some of below might help:
Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
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 users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Server
s
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"scott" <scott@.discussions.microsoft.com> wrote in message
news:D4DB1784-FA39-4FD7-A9DC-F557759A333A@.microsoft.com...
> In a few hours I am going to move 5 user databases from the existing production se
rver to a new
one. I have a plan in place, but I would like to verify that its the correct approach.[vbco
l=seagreen]
> Here's my plan:
> 1. Stop SQL Server on production using Services Manager
> 2. Start SQL Server on production in Single User Mode
> -using Services Manager, type "-m" in startup parameters
> 3. Perform a manual Full Backup of each user database to be migrated
> 4. Stop the SQL Server on production
> 5. Restore each database on the new server from the backup files
> -check "force restore over existing database"
> 6. Link all orphaned logins on each database
> 7. Point the application to the new server
> My main question is: Do I need to restore transaction logs also?
> No users will be connected at the time of the full backup. I am currectly using t
he Full recovery[/vbcol]
model on each database. I have already recreated all the DTS jobs and Datab
ase Maintenance Plans on
the new server. The new server is not yet in use, so I was able to test the
process by restoring
each database from the production backup files to the new server.
> The production SQL Server is 2000 version 8.00.194(RTM). The new server is 2000 v
ersion
8.00.760(SP3).
> Am I overlooking anything?
> Thanks,
> Scott|||also,...
Restore or Detach\Reattach will not move\migrate SQL Agent Jobs, System
Messages, DTS Packages, etc.
Greg Jackson
PDX, Oregon
ion server to a new one. I have a plan in place, but I would like to verify
that its the correct approach.
Here's my plan:
1. Stop SQL Server on production using Services Manager
2. Start SQL Server on production in Single User Mode
-using Services Manager, type "-m" in startup parameters
3. Perform a manual Full Backup of each user database to be migrated
4. Stop the SQL Server on production
5. Restore each database on the new server from the backup files
-check "force restore over existing database"
6. Link all orphaned logins on each database
7. Point the application to the new server
My main question is: Do I need to restore transaction logs also?
No users will be connected at the time of the full backup. I am currectly u
sing the Full recovery model on each database. I have already recreated all
the DTS jobs and Database Maintenance Plans on the new server. The new ser
ver is not yet in use, so I
was able to test the process by restoring each database from the production
backup files to the new server.
The production SQL Server is 2000 version 8.00.194(RTM). The new server is
2000 version 8.00.760(SP3).
Am I overlooking anything?
Thanks,
ScottI am placing the production database in single user mode to ensure there are
no other users.
I have actually performed the restores and they worked OK. I am not restori
ng the system databases only user databases.
The link you provided did not work.
"DeeJay Puar" wrote:
> Hi,
> Here are some comments:
> 1. Production server does have to in 'Single User Mode',
> since no one is going to be connected.
> 2. If you do a fullbackup and restore without further
> activity on the database, you do not have to restore any
> transaction log backups.
> 3. I do not think, restores will work with different
> service packs (production is different level than new
> server).
> 4. Here is an nice article to review:
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;314546&Product=sql
> hth
> DeeJay
> the existing production server to a new one. I have a
> plan in place, but I would like to verify that its the
> correct approach.
> parameters
> be migrated
> backup files
> logs also?
> backup. I am currectly using the Full recovery model on
> each database. I have already recreated all the DTS jobs
> and Database Maintenance Plans on the new server. The new
> server is not yet in use, so I was able to test the
> process by restoring each database from the production
> backup files to the new server.
> The new server is 2000 version 8.00.760(SP3).
>|||Some of below might help:
Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
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 users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Server
s
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"scott" <scott@.discussions.microsoft.com> wrote in message
news:D4DB1784-FA39-4FD7-A9DC-F557759A333A@.microsoft.com...
> In a few hours I am going to move 5 user databases from the existing production se
rver to a new
one. I have a plan in place, but I would like to verify that its the correct approach.[vbco
l=seagreen]
> Here's my plan:
> 1. Stop SQL Server on production using Services Manager
> 2. Start SQL Server on production in Single User Mode
> -using Services Manager, type "-m" in startup parameters
> 3. Perform a manual Full Backup of each user database to be migrated
> 4. Stop the SQL Server on production
> 5. Restore each database on the new server from the backup files
> -check "force restore over existing database"
> 6. Link all orphaned logins on each database
> 7. Point the application to the new server
> My main question is: Do I need to restore transaction logs also?
> No users will be connected at the time of the full backup. I am currectly using t
he Full recovery[/vbcol]
model on each database. I have already recreated all the DTS jobs and Datab
ase Maintenance Plans on
the new server. The new server is not yet in use, so I was able to test the
process by restoring
each database from the production backup files to the new server.
> The production SQL Server is 2000 version 8.00.194(RTM). The new server is 2000 v
ersion
8.00.760(SP3).
> Am I overlooking anything?
> Thanks,
> Scott|||also,...
Restore or Detach\Reattach will not move\migrate SQL Agent Jobs, System
Messages, DTS Packages, etc.
Greg Jackson
PDX, Oregon
Moving 5 databases to new server
In a few hours I am going to move 5 user databases from the existing production server to a new one. I have a plan in place, but I would like to verify that its the correct approach.
Here's my plan:
1. Stop SQL Server on production using Services Manager
2. Start SQL Server on production in Single User Mode
-using Services Manager, type "-m" in startup parameters
3. Perform a manual Full Backup of each user database to be migrated
4. Stop the SQL Server on production
5. Restore each database on the new server from the backup files
-check "force restore over existing database"
6. Link all orphaned logins on each database
7. Point the application to the new server
My main question is: Do I need to restore transaction logs also?
No users will be connected at the time of the full backup. I am currectly using the Full recovery model on each database. I have already recreated all the DTS jobs and Database Maintenance Plans on the new server. The new server is not yet in use, so I
was able to test the process by restoring each database from the production backup files to the new server.
The production SQL Server is 2000 version 8.00.194(RTM). The new server is 2000 version 8.00.760(SP3).
Am I overlooking anything?
Thanks,
Scott
I am placing the production database in single user mode to ensure there are no other users.
I have actually performed the restores and they worked OK. I am not restoring the system databases only user databases.
The link you provided did not work.
"DeeJay Puar" wrote:
> Hi,
> Here are some comments:
> 1. Production server does have to in 'Single User Mode',
> since no one is going to be connected.
> 2. If you do a fullbackup and restore without further
> activity on the database, you do not have to restore any
> transaction log backups.
> 3. I do not think, restores will work with different
> service packs (production is different level than new
> server).
> 4. Here is an nice article to review:
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;314546&Product=sql
> hth
> DeeJay
> the existing production server to a new one. I have a
> plan in place, but I would like to verify that its the
> correct approach.
> parameters
> be migrated
> backup files
> logs also?
> backup. I am currectly using the Full recovery model on
> each database. I have already recreated all the DTS jobs
> and Database Maintenance Plans on the new server. The new
> server is not yet in use, so I was able to test the
> process by restoring each database from the production
> backup files to the new server.
> The new server is 2000 version 8.00.760(SP3).
>
|||Some of below might help:
Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
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 users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Servers
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"scott" <scott@.discussions.microsoft.com> wrote in message
news:D4DB1784-FA39-4FD7-A9DC-F557759A333A@.microsoft.com...
> In a few hours I am going to move 5 user databases from the existing production server to a new
one. I have a plan in place, but I would like to verify that its the correct approach.
> Here's my plan:
> 1. Stop SQL Server on production using Services Manager
> 2. Start SQL Server on production in Single User Mode
> -using Services Manager, type "-m" in startup parameters
> 3. Perform a manual Full Backup of each user database to be migrated
> 4. Stop the SQL Server on production
> 5. Restore each database on the new server from the backup files
> -check "force restore over existing database"
> 6. Link all orphaned logins on each database
> 7. Point the application to the new server
> My main question is: Do I need to restore transaction logs also?
> No users will be connected at the time of the full backup. I am currectly using the Full recovery
model on each database. I have already recreated all the DTS jobs and Database Maintenance Plans on
the new server. The new server is not yet in use, so I was able to test the process by restoring
each database from the production backup files to the new server.
> The production SQL Server is 2000 version 8.00.194(RTM). The new server is 2000 version
8.00.760(SP3).
> Am I overlooking anything?
> Thanks,
> Scott
|||also,...
Restore or Detach\Reattach will not move\migrate SQL Agent Jobs, System
Messages, DTS Packages, etc.
Greg Jackson
PDX, Oregon
Here's my plan:
1. Stop SQL Server on production using Services Manager
2. Start SQL Server on production in Single User Mode
-using Services Manager, type "-m" in startup parameters
3. Perform a manual Full Backup of each user database to be migrated
4. Stop the SQL Server on production
5. Restore each database on the new server from the backup files
-check "force restore over existing database"
6. Link all orphaned logins on each database
7. Point the application to the new server
My main question is: Do I need to restore transaction logs also?
No users will be connected at the time of the full backup. I am currectly using the Full recovery model on each database. I have already recreated all the DTS jobs and Database Maintenance Plans on the new server. The new server is not yet in use, so I
was able to test the process by restoring each database from the production backup files to the new server.
The production SQL Server is 2000 version 8.00.194(RTM). The new server is 2000 version 8.00.760(SP3).
Am I overlooking anything?
Thanks,
Scott
I am placing the production database in single user mode to ensure there are no other users.
I have actually performed the restores and they worked OK. I am not restoring the system databases only user databases.
The link you provided did not work.
"DeeJay Puar" wrote:
> Hi,
> Here are some comments:
> 1. Production server does have to in 'Single User Mode',
> since no one is going to be connected.
> 2. If you do a fullbackup and restore without further
> activity on the database, you do not have to restore any
> transaction log backups.
> 3. I do not think, restores will work with different
> service packs (production is different level than new
> server).
> 4. Here is an nice article to review:
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;314546&Product=sql
> hth
> DeeJay
> the existing production server to a new one. I have a
> plan in place, but I would like to verify that its the
> correct approach.
> parameters
> be migrated
> backup files
> logs also?
> backup. I am currectly using the Full recovery model on
> each database. I have already recreated all the DTS jobs
> and Database Maintenance Plans on the new server. The new
> server is not yet in use, so I was able to test the
> process by restoring each database from the production
> backup files to the new server.
> The new server is 2000 version 8.00.760(SP3).
>
|||Some of below might help:
Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
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 users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Servers
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"scott" <scott@.discussions.microsoft.com> wrote in message
news:D4DB1784-FA39-4FD7-A9DC-F557759A333A@.microsoft.com...
> In a few hours I am going to move 5 user databases from the existing production server to a new
one. I have a plan in place, but I would like to verify that its the correct approach.
> Here's my plan:
> 1. Stop SQL Server on production using Services Manager
> 2. Start SQL Server on production in Single User Mode
> -using Services Manager, type "-m" in startup parameters
> 3. Perform a manual Full Backup of each user database to be migrated
> 4. Stop the SQL Server on production
> 5. Restore each database on the new server from the backup files
> -check "force restore over existing database"
> 6. Link all orphaned logins on each database
> 7. Point the application to the new server
> My main question is: Do I need to restore transaction logs also?
> No users will be connected at the time of the full backup. I am currectly using the Full recovery
model on each database. I have already recreated all the DTS jobs and Database Maintenance Plans on
the new server. The new server is not yet in use, so I was able to test the process by restoring
each database from the production backup files to the new server.
> The production SQL Server is 2000 version 8.00.194(RTM). The new server is 2000 version
8.00.760(SP3).
> Am I overlooking anything?
> Thanks,
> Scott
|||also,...
Restore or Detach\Reattach will not move\migrate SQL Agent Jobs, System
Messages, DTS Packages, etc.
Greg Jackson
PDX, Oregon
Wednesday, March 21, 2012
Move test data to production SQL Server
Hi All,
Could someone point me to a white paper/best practices on how to move/update
data from test sql server to a production sql server?
Both of the server have the same schema.
I would like to know if there is a way to synchronize the two databases.
Would SQL Server Replication be a good idea?
Many thanks,
See http://www.red-gate.com for the SQL Data Compare product. We use Red
Gate's SQL Compare and SQL Data Compare (mostly in development, testing, and
production prep -- not transitioning to production). SQL Data Compare
quickly compares and allows you to synchronize two databases.
A more robust solution that accounts for "production data" being preserved
while merging in updated lookup tables, et. al., simply requires analyzing
your needs (which takes _time_) and then, in our case, maintaining a central
script for the lookup tables (which also takes _time_). Our script is
simply a 1) disable constraints 2) delete, repopulate lookup tables, 3)
re-enable constraints (and run DBCC CHECKCONSTRAINTS to make sure nothing
unexpected happened!)
"Amine" <Amine@.discussions.microsoft.com> wrote in message
news:D92C4BF2-4EE9-4B64-BA32-D39D4BC21D15@.microsoft.com...
> Hi All,
> Could someone point me to a white paper/best practices on how to
move/update
> data from test sql server to a production sql server?
> Both of the server have the same schema.
> I would like to know if there is a way to synchronize the two databases.
> Would SQL Server Replication be a good idea?
> Many thanks,
>
>
|||Mike:
What do you mean by lookup tables?
"Mike Jansen" wrote:
> See http://www.red-gate.com for the SQL Data Compare product. We use Red
> Gate's SQL Compare and SQL Data Compare (mostly in development, testing, and
> production prep -- not transitioning to production). SQL Data Compare
> quickly compares and allows you to synchronize two databases.
> A more robust solution that accounts for "production data" being preserved
> while merging in updated lookup tables, et. al., simply requires analyzing
> your needs (which takes _time_) and then, in our case, maintaining a central
> script for the lookup tables (which also takes _time_). Our script is
> simply a 1) disable constraints 2) delete, repopulate lookup tables, 3)
> re-enable constraints (and run DBCC CHECKCONSTRAINTS to make sure nothing
> unexpected happened!)
> "Amine" <Amine@.discussions.microsoft.com> wrote in message
> news:D92C4BF2-4EE9-4B64-BA32-D39D4BC21D15@.microsoft.com...
> move/update
>
>
|||By lookup tables I simply meant tables that are read-only, that the
application doesn't update.
I've got to run, but if you need more info on what I mean, let me know and
I'll try to be more detailed.
"Amine" <Amine@.discussions.microsoft.com> wrote in message
news:6C29CC4B-9B2C-4930-8269-2D3B371D28B9@.microsoft.com...[vbcol=seagreen]
> Mike:
> What do you mean by lookup tables?
>
> "Mike Jansen" wrote:
Red[vbcol=seagreen]
and[vbcol=seagreen]
preserved[vbcol=seagreen]
analyzing[vbcol=seagreen]
central[vbcol=seagreen]
nothing[vbcol=seagreen]
databases.[vbcol=seagreen]
Could someone point me to a white paper/best practices on how to move/update
data from test sql server to a production sql server?
Both of the server have the same schema.
I would like to know if there is a way to synchronize the two databases.
Would SQL Server Replication be a good idea?
Many thanks,
See http://www.red-gate.com for the SQL Data Compare product. We use Red
Gate's SQL Compare and SQL Data Compare (mostly in development, testing, and
production prep -- not transitioning to production). SQL Data Compare
quickly compares and allows you to synchronize two databases.
A more robust solution that accounts for "production data" being preserved
while merging in updated lookup tables, et. al., simply requires analyzing
your needs (which takes _time_) and then, in our case, maintaining a central
script for the lookup tables (which also takes _time_). Our script is
simply a 1) disable constraints 2) delete, repopulate lookup tables, 3)
re-enable constraints (and run DBCC CHECKCONSTRAINTS to make sure nothing
unexpected happened!)
"Amine" <Amine@.discussions.microsoft.com> wrote in message
news:D92C4BF2-4EE9-4B64-BA32-D39D4BC21D15@.microsoft.com...
> Hi All,
> Could someone point me to a white paper/best practices on how to
move/update
> data from test sql server to a production sql server?
> Both of the server have the same schema.
> I would like to know if there is a way to synchronize the two databases.
> Would SQL Server Replication be a good idea?
> Many thanks,
>
>
|||Mike:
What do you mean by lookup tables?
"Mike Jansen" wrote:
> See http://www.red-gate.com for the SQL Data Compare product. We use Red
> Gate's SQL Compare and SQL Data Compare (mostly in development, testing, and
> production prep -- not transitioning to production). SQL Data Compare
> quickly compares and allows you to synchronize two databases.
> A more robust solution that accounts for "production data" being preserved
> while merging in updated lookup tables, et. al., simply requires analyzing
> your needs (which takes _time_) and then, in our case, maintaining a central
> script for the lookup tables (which also takes _time_). Our script is
> simply a 1) disable constraints 2) delete, repopulate lookup tables, 3)
> re-enable constraints (and run DBCC CHECKCONSTRAINTS to make sure nothing
> unexpected happened!)
> "Amine" <Amine@.discussions.microsoft.com> wrote in message
> news:D92C4BF2-4EE9-4B64-BA32-D39D4BC21D15@.microsoft.com...
> move/update
>
>
|||By lookup tables I simply meant tables that are read-only, that the
application doesn't update.
I've got to run, but if you need more info on what I mean, let me know and
I'll try to be more detailed.
"Amine" <Amine@.discussions.microsoft.com> wrote in message
news:6C29CC4B-9B2C-4930-8269-2D3B371D28B9@.microsoft.com...[vbcol=seagreen]
> Mike:
> What do you mean by lookup tables?
>
> "Mike Jansen" wrote:
Red[vbcol=seagreen]
and[vbcol=seagreen]
preserved[vbcol=seagreen]
analyzing[vbcol=seagreen]
central[vbcol=seagreen]
nothing[vbcol=seagreen]
databases.[vbcol=seagreen]
Move test data to production SQL Server
Hi All,
Could someone point me to a white paper/best practices on how to move/update
data from test sql server to a production sql server?
Both of the server have the same schema.
I would like to know if there is a way to synchronize the two databases.
Would SQL Server Replication be a good idea?
Many thanks,See http://www.red-gate.com for the SQL Data Compare product. We use Red
Gate's SQL Compare and SQL Data Compare (mostly in development, testing, and
production prep -- not transitioning to production). SQL Data Compare
quickly compares and allows you to synchronize two databases.
A more robust solution that accounts for "production data" being preserved
while merging in updated lookup tables, et. al., simply requires analyzing
your needs (which takes _time_) and then, in our case, maintaining a central
script for the lookup tables (which also takes _time_). Our script is
simply a 1) disable constraints 2) delete, repopulate lookup tables, 3)
re-enable constraints (and run DBCC CHECKCONSTRAINTS to make sure nothing
unexpected happened!)
"Amine" <Amine@.discussions.microsoft.com> wrote in message
news:D92C4BF2-4EE9-4B64-BA32-D39D4BC21D15@.microsoft.com...
> Hi All,
> Could someone point me to a white paper/best practices on how to
move/update
> data from test sql server to a production sql server?
> Both of the server have the same schema.
> I would like to know if there is a way to synchronize the two databases.
> Would SQL Server Replication be a good idea?
> Many thanks,
>
>|||Mike:
What do you mean by lookup tables?
"Mike Jansen" wrote:
> See http://www.red-gate.com for the SQL Data Compare product. We use Red
> Gate's SQL Compare and SQL Data Compare (mostly in development, testing, and
> production prep -- not transitioning to production). SQL Data Compare
> quickly compares and allows you to synchronize two databases.
> A more robust solution that accounts for "production data" being preserved
> while merging in updated lookup tables, et. al., simply requires analyzing
> your needs (which takes _time_) and then, in our case, maintaining a central
> script for the lookup tables (which also takes _time_). Our script is
> simply a 1) disable constraints 2) delete, repopulate lookup tables, 3)
> re-enable constraints (and run DBCC CHECKCONSTRAINTS to make sure nothing
> unexpected happened!)
> "Amine" <Amine@.discussions.microsoft.com> wrote in message
> news:D92C4BF2-4EE9-4B64-BA32-D39D4BC21D15@.microsoft.com...
> > Hi All,
> > Could someone point me to a white paper/best practices on how to
> move/update
> > data from test sql server to a production sql server?
> >
> > Both of the server have the same schema.
> > I would like to know if there is a way to synchronize the two databases.
> > Would SQL Server Replication be a good idea?
> > Many thanks,
> >
> >
> >
> >
>
>|||By lookup tables I simply meant tables that are read-only, that the
application doesn't update.
I've got to run, but if you need more info on what I mean, let me know and
I'll try to be more detailed.
"Amine" <Amine@.discussions.microsoft.com> wrote in message
news:6C29CC4B-9B2C-4930-8269-2D3B371D28B9@.microsoft.com...
> Mike:
> What do you mean by lookup tables?
>
> "Mike Jansen" wrote:
> > See http://www.red-gate.com for the SQL Data Compare product. We use
Red
> > Gate's SQL Compare and SQL Data Compare (mostly in development, testing,
and
> > production prep -- not transitioning to production). SQL Data Compare
> > quickly compares and allows you to synchronize two databases.
> >
> > A more robust solution that accounts for "production data" being
preserved
> > while merging in updated lookup tables, et. al., simply requires
analyzing
> > your needs (which takes _time_) and then, in our case, maintaining a
central
> > script for the lookup tables (which also takes _time_). Our script is
> > simply a 1) disable constraints 2) delete, repopulate lookup tables, 3)
> > re-enable constraints (and run DBCC CHECKCONSTRAINTS to make sure
nothing
> > unexpected happened!)
> >
> > "Amine" <Amine@.discussions.microsoft.com> wrote in message
> > news:D92C4BF2-4EE9-4B64-BA32-D39D4BC21D15@.microsoft.com...
> > > Hi All,
> > > Could someone point me to a white paper/best practices on how to
> > move/update
> > > data from test sql server to a production sql server?
> > >
> > > Both of the server have the same schema.
> > > I would like to know if there is a way to synchronize the two
databases.
> > > Would SQL Server Replication be a good idea?
> > > Many thanks,
> > >
> > >
> > >
> > >
> >
> >
> >sql
Could someone point me to a white paper/best practices on how to move/update
data from test sql server to a production sql server?
Both of the server have the same schema.
I would like to know if there is a way to synchronize the two databases.
Would SQL Server Replication be a good idea?
Many thanks,See http://www.red-gate.com for the SQL Data Compare product. We use Red
Gate's SQL Compare and SQL Data Compare (mostly in development, testing, and
production prep -- not transitioning to production). SQL Data Compare
quickly compares and allows you to synchronize two databases.
A more robust solution that accounts for "production data" being preserved
while merging in updated lookup tables, et. al., simply requires analyzing
your needs (which takes _time_) and then, in our case, maintaining a central
script for the lookup tables (which also takes _time_). Our script is
simply a 1) disable constraints 2) delete, repopulate lookup tables, 3)
re-enable constraints (and run DBCC CHECKCONSTRAINTS to make sure nothing
unexpected happened!)
"Amine" <Amine@.discussions.microsoft.com> wrote in message
news:D92C4BF2-4EE9-4B64-BA32-D39D4BC21D15@.microsoft.com...
> Hi All,
> Could someone point me to a white paper/best practices on how to
move/update
> data from test sql server to a production sql server?
> Both of the server have the same schema.
> I would like to know if there is a way to synchronize the two databases.
> Would SQL Server Replication be a good idea?
> Many thanks,
>
>|||Mike:
What do you mean by lookup tables?
"Mike Jansen" wrote:
> See http://www.red-gate.com for the SQL Data Compare product. We use Red
> Gate's SQL Compare and SQL Data Compare (mostly in development, testing, and
> production prep -- not transitioning to production). SQL Data Compare
> quickly compares and allows you to synchronize two databases.
> A more robust solution that accounts for "production data" being preserved
> while merging in updated lookup tables, et. al., simply requires analyzing
> your needs (which takes _time_) and then, in our case, maintaining a central
> script for the lookup tables (which also takes _time_). Our script is
> simply a 1) disable constraints 2) delete, repopulate lookup tables, 3)
> re-enable constraints (and run DBCC CHECKCONSTRAINTS to make sure nothing
> unexpected happened!)
> "Amine" <Amine@.discussions.microsoft.com> wrote in message
> news:D92C4BF2-4EE9-4B64-BA32-D39D4BC21D15@.microsoft.com...
> > Hi All,
> > Could someone point me to a white paper/best practices on how to
> move/update
> > data from test sql server to a production sql server?
> >
> > Both of the server have the same schema.
> > I would like to know if there is a way to synchronize the two databases.
> > Would SQL Server Replication be a good idea?
> > Many thanks,
> >
> >
> >
> >
>
>|||By lookup tables I simply meant tables that are read-only, that the
application doesn't update.
I've got to run, but if you need more info on what I mean, let me know and
I'll try to be more detailed.
"Amine" <Amine@.discussions.microsoft.com> wrote in message
news:6C29CC4B-9B2C-4930-8269-2D3B371D28B9@.microsoft.com...
> Mike:
> What do you mean by lookup tables?
>
> "Mike Jansen" wrote:
> > See http://www.red-gate.com for the SQL Data Compare product. We use
Red
> > Gate's SQL Compare and SQL Data Compare (mostly in development, testing,
and
> > production prep -- not transitioning to production). SQL Data Compare
> > quickly compares and allows you to synchronize two databases.
> >
> > A more robust solution that accounts for "production data" being
preserved
> > while merging in updated lookup tables, et. al., simply requires
analyzing
> > your needs (which takes _time_) and then, in our case, maintaining a
central
> > script for the lookup tables (which also takes _time_). Our script is
> > simply a 1) disable constraints 2) delete, repopulate lookup tables, 3)
> > re-enable constraints (and run DBCC CHECKCONSTRAINTS to make sure
nothing
> > unexpected happened!)
> >
> > "Amine" <Amine@.discussions.microsoft.com> wrote in message
> > news:D92C4BF2-4EE9-4B64-BA32-D39D4BC21D15@.microsoft.com...
> > > Hi All,
> > > Could someone point me to a white paper/best practices on how to
> > move/update
> > > data from test sql server to a production sql server?
> > >
> > > Both of the server have the same schema.
> > > I would like to know if there is a way to synchronize the two
databases.
> > > Would SQL Server Replication be a good idea?
> > > Many thanks,
> > >
> > >
> > >
> > >
> >
> >
> >sql
Move test data to production SQL Server
Hi All,
Could someone point me to a white paper/best practices on how to move/update
data from test sql server to a production sql server?
Both of the server have the same schema.
I would like to know if there is a way to synchronize the two databases.
Would SQL Server Replication be a good idea?
Many thanks,
Setting up replication between test server and production server is a very
bad idea. Don't even think about it.
"Amine" wrote:
> Hi All,
> Could someone point me to a white paper/best practices on how to move/update
> data from test sql server to a production sql server?
> Both of the server have the same schema.
> I would like to know if there is a way to synchronize the two databases.
> Would SQL Server Replication be a good idea?
> Many thanks,
|||I concur with Jack, however if you are going in the opposite direction, it
could be a good idea.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:3AE6B9EF-089F-4D1D-8BEB-967CC32C0004@.microsoft.com...[vbcol=seagreen]
> Setting up replication between test server and production server is a very
> bad idea. Don't even think about it.
> "Amine" wrote:
move/update[vbcol=seagreen]
|||Amine,
like Jack and Hilary I'm struggling to see why you would want replication
from test to proiduction. However, perhaps you are loading (cleansed) data
as part of a build? In this case you could use DTS to transfer the data as a
one-off. If the environments are disconnected, then there are 3rd party
tools to take data and generate insert statements from it.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Could someone point me to a white paper/best practices on how to move/update
data from test sql server to a production sql server?
Both of the server have the same schema.
I would like to know if there is a way to synchronize the two databases.
Would SQL Server Replication be a good idea?
Many thanks,
Setting up replication between test server and production server is a very
bad idea. Don't even think about it.
"Amine" wrote:
> Hi All,
> Could someone point me to a white paper/best practices on how to move/update
> data from test sql server to a production sql server?
> Both of the server have the same schema.
> I would like to know if there is a way to synchronize the two databases.
> Would SQL Server Replication be a good idea?
> Many thanks,
|||I concur with Jack, however if you are going in the opposite direction, it
could be a good idea.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:3AE6B9EF-089F-4D1D-8BEB-967CC32C0004@.microsoft.com...[vbcol=seagreen]
> Setting up replication between test server and production server is a very
> bad idea. Don't even think about it.
> "Amine" wrote:
move/update[vbcol=seagreen]
|||Amine,
like Jack and Hilary I'm struggling to see why you would want replication
from test to proiduction. However, perhaps you are loading (cleansed) data
as part of a build? In this case you could use DTS to transfer the data as a
one-off. If the environments are disconnected, then there are 3rd party
tools to take data and generate insert statements from it.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Move test data to production SQL Server
Hi All,
Could someone point me to a white paper/best practices on how to move/update
data from test sql server to a production sql server?
Both of the server have the same schema.
I would like to know if there is a way to synchronize the two databases.
Would SQL Server Replication be a good idea?
Many thanks,See http://www.red-gate.com for the SQL Data Compare product. We use Red
Gate's SQL Compare and SQL Data Compare (mostly in development, testing, and
production prep -- not transitioning to production). SQL Data Compare
quickly compares and allows you to synchronize two databases.
A more robust solution that accounts for "production data" being preserved
while merging in updated lookup tables, et. al., simply requires analyzing
your needs (which takes _time_) and then, in our case, maintaining a central
script for the lookup tables (which also takes _time_). Our script is
simply a 1) disable constraints 2) delete, repopulate lookup tables, 3)
re-enable constraints (and run DBCC CHECKCONSTRAINTS to make sure nothing
unexpected happened!)
"Amine" <Amine@.discussions.microsoft.com> wrote in message
news:D92C4BF2-4EE9-4B64-BA32-D39D4BC21D15@.microsoft.com...
> Hi All,
> Could someone point me to a white paper/best practices on how to
move/update
> data from test sql server to a production sql server?
> Both of the server have the same schema.
> I would like to know if there is a way to synchronize the two databases.
> Would SQL Server Replication be a good idea?
> Many thanks,
>
>|||Mike:
What do you mean by lookup tables?
"Mike Jansen" wrote:
> See http://www.red-gate.com for the SQL Data Compare product. We use Red
> Gate's SQL Compare and SQL Data Compare (mostly in development, testing, a
nd
> production prep -- not transitioning to production). SQL Data Compare
> quickly compares and allows you to synchronize two databases.
> A more robust solution that accounts for "production data" being preserved
> while merging in updated lookup tables, et. al., simply requires analyzing
> your needs (which takes _time_) and then, in our case, maintaining a centr
al
> script for the lookup tables (which also takes _time_). Our script is
> simply a 1) disable constraints 2) delete, repopulate lookup tables, 3)
> re-enable constraints (and run DBCC CHECKCONSTRAINTS to make sure nothing
> unexpected happened!)
> "Amine" <Amine@.discussions.microsoft.com> wrote in message
> news:D92C4BF2-4EE9-4B64-BA32-D39D4BC21D15@.microsoft.com...
> move/update
>
>|||By lookup tables I simply meant tables that are read-only, that the
application doesn't update.
I've got to run, but if you need more info on what I mean, let me know and
I'll try to be more detailed.
"Amine" <Amine@.discussions.microsoft.com> wrote in message
news:6C29CC4B-9B2C-4930-8269-2D3B371D28B9@.microsoft.com...[vbcol=seagreen]
> Mike:
> What do you mean by lookup tables?
>
> "Mike Jansen" wrote:
>
Red[vbcol=seagreen]
and[vbcol=seagreen]
preserved[vbcol=seagreen]
analyzing[vbcol=seagreen]
central[vbcol=seagreen]
nothing[vbcol=seagreen]
databases.[vbcol=seagreen]
Could someone point me to a white paper/best practices on how to move/update
data from test sql server to a production sql server?
Both of the server have the same schema.
I would like to know if there is a way to synchronize the two databases.
Would SQL Server Replication be a good idea?
Many thanks,See http://www.red-gate.com for the SQL Data Compare product. We use Red
Gate's SQL Compare and SQL Data Compare (mostly in development, testing, and
production prep -- not transitioning to production). SQL Data Compare
quickly compares and allows you to synchronize two databases.
A more robust solution that accounts for "production data" being preserved
while merging in updated lookup tables, et. al., simply requires analyzing
your needs (which takes _time_) and then, in our case, maintaining a central
script for the lookup tables (which also takes _time_). Our script is
simply a 1) disable constraints 2) delete, repopulate lookup tables, 3)
re-enable constraints (and run DBCC CHECKCONSTRAINTS to make sure nothing
unexpected happened!)
"Amine" <Amine@.discussions.microsoft.com> wrote in message
news:D92C4BF2-4EE9-4B64-BA32-D39D4BC21D15@.microsoft.com...
> Hi All,
> Could someone point me to a white paper/best practices on how to
move/update
> data from test sql server to a production sql server?
> Both of the server have the same schema.
> I would like to know if there is a way to synchronize the two databases.
> Would SQL Server Replication be a good idea?
> Many thanks,
>
>|||Mike:
What do you mean by lookup tables?
"Mike Jansen" wrote:
> See http://www.red-gate.com for the SQL Data Compare product. We use Red
> Gate's SQL Compare and SQL Data Compare (mostly in development, testing, a
nd
> production prep -- not transitioning to production). SQL Data Compare
> quickly compares and allows you to synchronize two databases.
> A more robust solution that accounts for "production data" being preserved
> while merging in updated lookup tables, et. al., simply requires analyzing
> your needs (which takes _time_) and then, in our case, maintaining a centr
al
> script for the lookup tables (which also takes _time_). Our script is
> simply a 1) disable constraints 2) delete, repopulate lookup tables, 3)
> re-enable constraints (and run DBCC CHECKCONSTRAINTS to make sure nothing
> unexpected happened!)
> "Amine" <Amine@.discussions.microsoft.com> wrote in message
> news:D92C4BF2-4EE9-4B64-BA32-D39D4BC21D15@.microsoft.com...
> move/update
>
>|||By lookup tables I simply meant tables that are read-only, that the
application doesn't update.
I've got to run, but if you need more info on what I mean, let me know and
I'll try to be more detailed.
"Amine" <Amine@.discussions.microsoft.com> wrote in message
news:6C29CC4B-9B2C-4930-8269-2D3B371D28B9@.microsoft.com...[vbcol=seagreen]
> Mike:
> What do you mean by lookup tables?
>
> "Mike Jansen" wrote:
>
Red[vbcol=seagreen]
and[vbcol=seagreen]
preserved[vbcol=seagreen]
analyzing[vbcol=seagreen]
central[vbcol=seagreen]
nothing[vbcol=seagreen]
databases.[vbcol=seagreen]
Move Table to different filegroup.
Hi everyone,
We have a production database holding time based manufacturing data. A new
table is created on the 1st day of each month and data is saved there until
the 1st day of the next month.
Each new table holds one month's worth of data - table size is between 2.5
GB and 4 GB. For the purpose of more efficient backup strategy we want to
move tables older than 1 year to a different
filegroup. Moving a table table using Enterpise Manager takes a long time
and causes the transaction log to grow by about 7-8 GB even if I set the
Recovery model to Simple.
What can I do to be able to move the tables more quickly and efficiently ?
Best regards.
Hi
If you the table has a clustered index , just re-create an index on
different file group . It is just because a CI in leaf level has actual
data and as result all table will be moved as well
--Create a table via QA and not via EM
CREATE TABLE mywind..t1 (id int) ON new_customers
CREATE TABLE mywind..t2 (id int) ON sales
GO
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:u8ma5BATGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi everyone,
> We have a production database holding time based manufacturing data. A new
> table is created on the 1st day of each month and data is saved there
> until the 1st day of the next month.
> Each new table holds one month's worth of data - table size is between 2.5
> GB and 4 GB. For the purpose of more efficient backup strategy we want to
> move tables older than 1 year to a different
> filegroup. Moving a table table using Enterpise Manager takes a long time
> and causes the transaction log to grow by about 7-8 GB even if I set the
> Recovery model to Simple.
> What can I do to be able to move the tables more quickly and efficiently ?
>
> Best regards.
>
>
sql
We have a production database holding time based manufacturing data. A new
table is created on the 1st day of each month and data is saved there until
the 1st day of the next month.
Each new table holds one month's worth of data - table size is between 2.5
GB and 4 GB. For the purpose of more efficient backup strategy we want to
move tables older than 1 year to a different
filegroup. Moving a table table using Enterpise Manager takes a long time
and causes the transaction log to grow by about 7-8 GB even if I set the
Recovery model to Simple.
What can I do to be able to move the tables more quickly and efficiently ?
Best regards.
Hi
If you the table has a clustered index , just re-create an index on
different file group . It is just because a CI in leaf level has actual
data and as result all table will be moved as well
--Create a table via QA and not via EM
CREATE TABLE mywind..t1 (id int) ON new_customers
CREATE TABLE mywind..t2 (id int) ON sales
GO
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:u8ma5BATGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi everyone,
> We have a production database holding time based manufacturing data. A new
> table is created on the 1st day of each month and data is saved there
> until the 1st day of the next month.
> Each new table holds one month's worth of data - table size is between 2.5
> GB and 4 GB. For the purpose of more efficient backup strategy we want to
> move tables older than 1 year to a different
> filegroup. Moving a table table using Enterpise Manager takes a long time
> and causes the transaction log to grow by about 7-8 GB even if I set the
> Recovery model to Simple.
> What can I do to be able to move the tables more quickly and efficiently ?
>
> Best regards.
>
>
sql
Move Table to different filegroup.
Hi everyone,
We have a production database holding time based manufacturing data. A new
table is created on the 1st day of each month and data is saved there until
the 1st day of the next month.
Each new table holds one month's worth of data - table size is between 2.5
GB and 4 GB. For the purpose of more efficient backup strategy we want to
move tables older than 1 year to a different
filegroup. Moving a table table using Enterpise Manager takes a long time
and causes the transaction log to grow by about 7-8 GB even if I set the
Recovery model to Simple.
What can I do to be able to move the tables more quickly and efficiently ?
Best regards.Hi
If you the table has a clustered index , just re-create an index on
different file group . It is just because a CI in leaf level has actual
data and as result all table will be moved as well
--Create a table via QA and not via EM
CREATE TABLE mywind..t1 (id int) ON new_customers
CREATE TABLE mywind..t2 (id int) ON sales
GO
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:u8ma5BATGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi everyone,
> We have a production database holding time based manufacturing data. A new
> table is created on the 1st day of each month and data is saved there
> until the 1st day of the next month.
> Each new table holds one month's worth of data - table size is between 2.5
> GB and 4 GB. For the purpose of more efficient backup strategy we want to
> move tables older than 1 year to a different
> filegroup. Moving a table table using Enterpise Manager takes a long time
> and causes the transaction log to grow by about 7-8 GB even if I set the
> Recovery model to Simple.
> What can I do to be able to move the tables more quickly and efficiently ?
>
> Best regards.
>
>
We have a production database holding time based manufacturing data. A new
table is created on the 1st day of each month and data is saved there until
the 1st day of the next month.
Each new table holds one month's worth of data - table size is between 2.5
GB and 4 GB. For the purpose of more efficient backup strategy we want to
move tables older than 1 year to a different
filegroup. Moving a table table using Enterpise Manager takes a long time
and causes the transaction log to grow by about 7-8 GB even if I set the
Recovery model to Simple.
What can I do to be able to move the tables more quickly and efficiently ?
Best regards.Hi
If you the table has a clustered index , just re-create an index on
different file group . It is just because a CI in leaf level has actual
data and as result all table will be moved as well
--Create a table via QA and not via EM
CREATE TABLE mywind..t1 (id int) ON new_customers
CREATE TABLE mywind..t2 (id int) ON sales
GO
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:u8ma5BATGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi everyone,
> We have a production database holding time based manufacturing data. A new
> table is created on the 1st day of each month and data is saved there
> until the 1st day of the next month.
> Each new table holds one month's worth of data - table size is between 2.5
> GB and 4 GB. For the purpose of more efficient backup strategy we want to
> move tables older than 1 year to a different
> filegroup. Moving a table table using Enterpise Manager takes a long time
> and causes the transaction log to grow by about 7-8 GB even if I set the
> Recovery model to Simple.
> What can I do to be able to move the tables more quickly and efficiently ?
>
> Best regards.
>
>
Monday, March 19, 2012
Move Table to different filegroup.
Hi everyone,
We have a production database holding time based manufacturing data. A new
table is created on the 1st day of each month and data is saved there until
the 1st day of the next month.
Each new table holds one month's worth of data - table size is between 2.5
GB and 4 GB. For the purpose of more efficient backup strategy we want to
move tables older than 1 year to a different
filegroup. Moving a table table using Enterpise Manager takes a long time
and causes the transaction log to grow by about 7-8 GB even if I set the
Recovery model to Simple.
What can I do to be able to move the tables more quickly and efficiently ?
Best regards.Hi
If you the table has a clustered index , just re-create an index on
different file group . It is just because a CI in leaf level has actual
data and as result all table will be moved as well
--Create a table via QA and not via EM
CREATE TABLE mywind..t1 (id int) ON new_customers
CREATE TABLE mywind..t2 (id int) ON sales
GO
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:u8ma5BATGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi everyone,
> We have a production database holding time based manufacturing data. A new
> table is created on the 1st day of each month and data is saved there
> until the 1st day of the next month.
> Each new table holds one month's worth of data - table size is between 2.5
> GB and 4 GB. For the purpose of more efficient backup strategy we want to
> move tables older than 1 year to a different
> filegroup. Moving a table table using Enterpise Manager takes a long time
> and causes the transaction log to grow by about 7-8 GB even if I set the
> Recovery model to Simple.
> What can I do to be able to move the tables more quickly and efficiently ?
>
> Best regards.
>
>
We have a production database holding time based manufacturing data. A new
table is created on the 1st day of each month and data is saved there until
the 1st day of the next month.
Each new table holds one month's worth of data - table size is between 2.5
GB and 4 GB. For the purpose of more efficient backup strategy we want to
move tables older than 1 year to a different
filegroup. Moving a table table using Enterpise Manager takes a long time
and causes the transaction log to grow by about 7-8 GB even if I set the
Recovery model to Simple.
What can I do to be able to move the tables more quickly and efficiently ?
Best regards.Hi
If you the table has a clustered index , just re-create an index on
different file group . It is just because a CI in leaf level has actual
data and as result all table will be moved as well
--Create a table via QA and not via EM
CREATE TABLE mywind..t1 (id int) ON new_customers
CREATE TABLE mywind..t2 (id int) ON sales
GO
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:u8ma5BATGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi everyone,
> We have a production database holding time based manufacturing data. A new
> table is created on the 1st day of each month and data is saved there
> until the 1st day of the next month.
> Each new table holds one month's worth of data - table size is between 2.5
> GB and 4 GB. For the purpose of more efficient backup strategy we want to
> move tables older than 1 year to a different
> filegroup. Moving a table table using Enterpise Manager takes a long time
> and causes the transaction log to grow by about 7-8 GB even if I set the
> Recovery model to Simple.
> What can I do to be able to move the tables more quickly and efficiently ?
>
> Best regards.
>
>
Move SQL Server Virtual Server Instance to new Server
What is the Best Practices way and quickest way to move a SQL Server 2000
Virtual Server database to a new server.
This a production server with numerous users and the server can be down for
only several hours. The server has Windows 2000 Advanced Server with SQL
Server 2000 (Active/Active) Cluster with two nodes and a 400GB database.
We purchased a new HP server.
We would like to install Windows 2003 Server and SQL Server 2000 Virtual
Server on this cluster server.
We would like the new server to have the same server name, IP Addresses, and
database instance name as the old system.
How can we complete this task with minimum down time and with Microsoft Best
Practices.
Thanks,
It's not really practical to have 2 servers in the same domain with the same
IP & name (in fact it's not possible). I had a similar situation recently
where I wanted to replace our production cluster with a new production
cluster and I wanted to keep all the config the same and I figured out that
the best way to do it was to use DNS to point at the SQL server that was to
be the production box.
What I did was I kept SQL01 (our production SQL instance) up & running as
normal while I built up SQL02. Different IP & name but I needed it to
co-exist in our domain while the DBs were transferred from one to the other.
I had a DNS alias (CNAME record) called SQLProduction that initially pointed
at SQL01. Leading up to our regular weekly outage period I'd copied full DB
backups over to SQL02 and restored them (WITH NORECOVERY) and then during
the outage period we put SQL01 into single user mode (so nobody could
connect to change data) and did transaction log backups of all the DB
(substantially smaller than the full DB backups). I then copied those tlog
backups over to SQL02, restored them (WITH RECOVERY) and changed the
SQLProduction DNS alias to point at SQL02.
This added a level of abstraction to the client/server model we use, which
has been very useful (the users don't need to know what server the
production server really is, all they know is they get to it by connecting
to SQLProduction). We can switch the DNS alias to whichever box we like
and, assuming the DBs are synched, nobody every knows the difference (we set
the DNS TTL very small, like 5 minutes, before the change so it gets out to
the client machines almost immediately). We did this for all our major
servers (SQLProduction, SQLStaging, SQLDevelopment). We now have a few
production servers (all running on different clusters) but the users have no
idea which servers actually host the DBs (as that detail shouldn't matter to
them).
The only ugly thing about this is that leading up to the change we had to
make sure all the client apps (and we had thousands running on a couple
thousand computers) we're connecting to the DNS aliases rather than the
actual server names so that when we turned off SQL01 they wouldn't still be
trying to connect to it.
It was slightly more complicated for us because we were using named
instances, which don't translate very well with DNS (ie. DNS can't cater for
them at all). What I did to solve that was use the MDAC aliases to say
"SQLProduction" is a TCP connecting to a box called SQLProduction (ie. the
DNS alias) and it should use port 1434 or 1435 or 1436 or whichever. The
MDAC aliases are just registry values so I got the appropriate aliases
together into a reg file and we used SMS to push them out to every client
desktop (and we updated our 100 or so servers manually with the reg file).
This works well with a disaster recovery scenario too - we just set up our
DR SQL servers (say SQL03) to listen on the same port as the production
instance. Then in a disaster (which we had in August this
year...<sigh>...dumb SAN engineers!) we simply change the DNS alias for
SQLProduction to point to SQL03. After that, every new connecting to
SQLProduction goes off to SQL03 on port 1434 (or whichever one we use for
our "production" box). We keep the production & DR servers synchronised
with log shipping and it works a treat - we can be up and running on our DR
server (with no changes to any middle-tier or client app) in about 1 hour;
all we have to do is log shipping role changes for all the DBs (which takes
a little while because we have about 150 of the dam things, but I wrote a
SQL batch to perform the role changes, so we just kick it off and 30-45
minutes later everyone is pointing at our DR server and nobody is the wiser
(except for the 1 hour outage)).
Anyway, sorry about my verbal diarrhoea. HTH.
Cheers,
Mike
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:019A8672-58C8-4A98-9A12-5C7D762F8C27@.microsoft.com...
> What is the Best Practices way and quickest way to move a SQL Server 2000
> Virtual Server database to a new server.
> This a production server with numerous users and the server can be down
> for
> only several hours. The server has Windows 2000 Advanced Server with SQL
> Server 2000 (Active/Active) Cluster with two nodes and a 400GB database.
> We purchased a new HP server.
> We would like to install Windows 2003 Server and SQL Server 2000 Virtual
> Server on this cluster server.
> We would like the new server to have the same server name, IP Addresses,
> and
> database instance name as the old system.
> How can we complete this task with minimum down time and with Microsoft
> Best
> Practices.
> Thanks,
Virtual Server database to a new server.
This a production server with numerous users and the server can be down for
only several hours. The server has Windows 2000 Advanced Server with SQL
Server 2000 (Active/Active) Cluster with two nodes and a 400GB database.
We purchased a new HP server.
We would like to install Windows 2003 Server and SQL Server 2000 Virtual
Server on this cluster server.
We would like the new server to have the same server name, IP Addresses, and
database instance name as the old system.
How can we complete this task with minimum down time and with Microsoft Best
Practices.
Thanks,
It's not really practical to have 2 servers in the same domain with the same
IP & name (in fact it's not possible). I had a similar situation recently
where I wanted to replace our production cluster with a new production
cluster and I wanted to keep all the config the same and I figured out that
the best way to do it was to use DNS to point at the SQL server that was to
be the production box.
What I did was I kept SQL01 (our production SQL instance) up & running as
normal while I built up SQL02. Different IP & name but I needed it to
co-exist in our domain while the DBs were transferred from one to the other.
I had a DNS alias (CNAME record) called SQLProduction that initially pointed
at SQL01. Leading up to our regular weekly outage period I'd copied full DB
backups over to SQL02 and restored them (WITH NORECOVERY) and then during
the outage period we put SQL01 into single user mode (so nobody could
connect to change data) and did transaction log backups of all the DB
(substantially smaller than the full DB backups). I then copied those tlog
backups over to SQL02, restored them (WITH RECOVERY) and changed the
SQLProduction DNS alias to point at SQL02.
This added a level of abstraction to the client/server model we use, which
has been very useful (the users don't need to know what server the
production server really is, all they know is they get to it by connecting
to SQLProduction). We can switch the DNS alias to whichever box we like
and, assuming the DBs are synched, nobody every knows the difference (we set
the DNS TTL very small, like 5 minutes, before the change so it gets out to
the client machines almost immediately). We did this for all our major
servers (SQLProduction, SQLStaging, SQLDevelopment). We now have a few
production servers (all running on different clusters) but the users have no
idea which servers actually host the DBs (as that detail shouldn't matter to
them).
The only ugly thing about this is that leading up to the change we had to
make sure all the client apps (and we had thousands running on a couple
thousand computers) we're connecting to the DNS aliases rather than the
actual server names so that when we turned off SQL01 they wouldn't still be
trying to connect to it.
It was slightly more complicated for us because we were using named
instances, which don't translate very well with DNS (ie. DNS can't cater for
them at all). What I did to solve that was use the MDAC aliases to say
"SQLProduction" is a TCP connecting to a box called SQLProduction (ie. the
DNS alias) and it should use port 1434 or 1435 or 1436 or whichever. The
MDAC aliases are just registry values so I got the appropriate aliases
together into a reg file and we used SMS to push them out to every client
desktop (and we updated our 100 or so servers manually with the reg file).
This works well with a disaster recovery scenario too - we just set up our
DR SQL servers (say SQL03) to listen on the same port as the production
instance. Then in a disaster (which we had in August this
year...<sigh>...dumb SAN engineers!) we simply change the DNS alias for
SQLProduction to point to SQL03. After that, every new connecting to
SQLProduction goes off to SQL03 on port 1434 (or whichever one we use for
our "production" box). We keep the production & DR servers synchronised
with log shipping and it works a treat - we can be up and running on our DR
server (with no changes to any middle-tier or client app) in about 1 hour;
all we have to do is log shipping role changes for all the DBs (which takes
a little while because we have about 150 of the dam things, but I wrote a
SQL batch to perform the role changes, so we just kick it off and 30-45
minutes later everyone is pointing at our DR server and nobody is the wiser
(except for the 1 hour outage)).
Anyway, sorry about my verbal diarrhoea. HTH.
Cheers,
Mike
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:019A8672-58C8-4A98-9A12-5C7D762F8C27@.microsoft.com...
> What is the Best Practices way and quickest way to move a SQL Server 2000
> Virtual Server database to a new server.
> This a production server with numerous users and the server can be down
> for
> only several hours. The server has Windows 2000 Advanced Server with SQL
> Server 2000 (Active/Active) Cluster with two nodes and a 400GB database.
> We purchased a new HP server.
> We would like to install Windows 2003 Server and SQL Server 2000 Virtual
> Server on this cluster server.
> We would like the new server to have the same server name, IP Addresses,
> and
> database instance name as the old system.
> How can we complete this task with minimum down time and with Microsoft
> Best
> Practices.
> Thanks,
Friday, March 9, 2012
Move large amounts of data across Data Centers for DR
As you set up disaster recovery(DR)of your existing production data say in
DataCenter 1 to your DR site that may on the other side of the continent,
how do you move all the data acorss the first time? It may be Terrabytes of
data or for that matter even 100s of GBs. It takes so long to move this
backup files for eg: on the network.
We intend to use Log shipping once the backups are restored, but moving
those initial full database backup files is a nightmare.
Can you share how you move your initial data set across ?
FedEx is your friend.
Removable USB disk drives and overnight shipping are still the fastest way
to get very large data sets initialized from one site to another.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Hassan" <hassan@.hotmail.com> wrote in message
news:OejIIuYwHHA.4916@.TK2MSFTNGP04.phx.gbl...
> As you set up disaster recovery(DR)of your existing production data say in
> DataCenter 1 to your DR site that may on the other side of the continent,
> how do you move all the data acorss the first time? It may be Terrabytes
> of data or for that matter even 100s of GBs. It takes so long to move this
> backup files for eg: on the network.
> We intend to use Log shipping once the backups are restored, but moving
> those initial full database backup files is a nightmare.
> Can you share how you move your initial data set across ?
>
|||Can I say DHL ;-)
What I do if the database files are quite big is run a full and transaction
log backup and store it in a backup device. Compress the backup before
sending it over the network using ROBOCOPY. When you configure log
shipping, restore the full and transaction log backups on the DR site and
use an existing database in your option. When it's bigger than 5GB over a
4MBps network, then I would recommend courier
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OlPBX4YwHHA.4640@.TK2MSFTNGP03.phx.gbl...
> FedEx is your friend.
> Removable USB disk drives and overnight shipping are still the fastest way
> to get very large data sets initialized from one site to another.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:OejIIuYwHHA.4916@.TK2MSFTNGP04.phx.gbl...
>
|||Overnighting an external drive is indeed best way to go, imho.
BE SURE TO ENCRYPT THE DATA!!! I would hate to read about yet another
company losing sensitive data! :-0
TheSQLGuru
President
Indicium Resources, Inc.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OlPBX4YwHHA.4640@.TK2MSFTNGP03.phx.gbl...
> FedEx is your friend.
> Removable USB disk drives and overnight shipping are still the fastest way
> to get very large data sets initialized from one site to another.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:OejIIuYwHHA.4916@.TK2MSFTNGP04.phx.gbl...
>
DataCenter 1 to your DR site that may on the other side of the continent,
how do you move all the data acorss the first time? It may be Terrabytes of
data or for that matter even 100s of GBs. It takes so long to move this
backup files for eg: on the network.
We intend to use Log shipping once the backups are restored, but moving
those initial full database backup files is a nightmare.
Can you share how you move your initial data set across ?
FedEx is your friend.
Removable USB disk drives and overnight shipping are still the fastest way
to get very large data sets initialized from one site to another.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Hassan" <hassan@.hotmail.com> wrote in message
news:OejIIuYwHHA.4916@.TK2MSFTNGP04.phx.gbl...
> As you set up disaster recovery(DR)of your existing production data say in
> DataCenter 1 to your DR site that may on the other side of the continent,
> how do you move all the data acorss the first time? It may be Terrabytes
> of data or for that matter even 100s of GBs. It takes so long to move this
> backup files for eg: on the network.
> We intend to use Log shipping once the backups are restored, but moving
> those initial full database backup files is a nightmare.
> Can you share how you move your initial data set across ?
>
|||Can I say DHL ;-)
What I do if the database files are quite big is run a full and transaction
log backup and store it in a backup device. Compress the backup before
sending it over the network using ROBOCOPY. When you configure log
shipping, restore the full and transaction log backups on the DR site and
use an existing database in your option. When it's bigger than 5GB over a
4MBps network, then I would recommend courier
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OlPBX4YwHHA.4640@.TK2MSFTNGP03.phx.gbl...
> FedEx is your friend.
> Removable USB disk drives and overnight shipping are still the fastest way
> to get very large data sets initialized from one site to another.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:OejIIuYwHHA.4916@.TK2MSFTNGP04.phx.gbl...
>
|||Overnighting an external drive is indeed best way to go, imho.
BE SURE TO ENCRYPT THE DATA!!! I would hate to read about yet another
company losing sensitive data! :-0
TheSQLGuru
President
Indicium Resources, Inc.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OlPBX4YwHHA.4640@.TK2MSFTNGP03.phx.gbl...
> FedEx is your friend.
> Removable USB disk drives and overnight shipping are still the fastest way
> to get very large data sets initialized from one site to another.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:OejIIuYwHHA.4916@.TK2MSFTNGP04.phx.gbl...
>
Subscribe to:
Posts (Atom)