moving a heap to another filegroup

I have a big table (heap)... well, not so big, I have a small server
and I want to spread access to it across several new disks dedicated
only to that table.

I known its possible to do that creating a clustered index with "ON
filegroup" option but I want to maintain it as a heap, is there any
way to do this without dropping indexes/references - bulk unload -
create table - bulk load - create indexes?."el emperador" <1492a2001@.terra.es> wrote in message
news:dc979468.0407291227.11f42272@.posting.google.c om...
> I have a big table (heap)... well, not so big, I have a small server
> and I want to spread access to it across several new disks dedicated
> only to that table.
> I known its possible to do that creating a clustered index with "ON
> filegroup" option but I want to maintain it as a heap, is there any
> way to do this without dropping indexes/references - bulk unload -
> create table - bulk load - create indexes?.

As far as I know, you have to drop everything and recreate it, as you have
described. But in general, in MSSQL it's a good idea to have a clustered
index on all tables, so it would be interesting to know why you prefer to
maintain a heap table.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<410999b5$1_3@.news.bluewin.ch>...
> "el emperador" <1492a2001@.terra.es> wrote in message
> news:dc979468.0407291227.11f42272@.posting.google.c om...
> > I have a big table (heap)... well, not so big, I have a small server
> > and I want to spread access to it across several new disks dedicated
> > only to that table.
> > I known its possible to do that creating a clustered index with "ON
> > filegroup" option but I want to maintain it as a heap, is there any
> > way to do this without dropping indexes/references - bulk unload -
> > create table - bulk load - create indexes?.
> As far as I know, you have to drop everything and recreate it, as you have
> described. But in general, in MSSQL it's a good idea to have a clustered
> index on all tables, so it would be interesting to know why you prefer to

I agree.

> maintain a heap table.

Well, I have found that after define a clustered index some queries
changed their plan (from a "Index Seek"-->"Nested Loops"-->"Bookmark
Lookup" to a "Clustered Index Seek") and surprisingly got a little
slower, that's suggar for the query optimizer so it thinks its better
to use it, I have to investigate why this happens before define "the
correct" clustered index, a not easy task as you know. Right now I
only want to improve "bookmark lookup" on this table that is 60%-80%
of the weigth of the queries.

> Simon

Thanks|||Hi

Adding the clustered index in the new filegroup and then dropping it will
leave the data in the new filegroup. Your other indexes will be rebuilt
twice though, so it may be quicker to drop and re-create them yourself.

John

"el emperador" <1492a2001@.terra.es> wrote in message
news:dc979468.0407301415.28c2c4@.posting.google.com ...
> "Simon Hayes" <sql@.hayes.ch> wrote in message
news:<410999b5$1_3@.news.bluewin.ch>...
> > "el emperador" <1492a2001@.terra.es> wrote in message
> > news:dc979468.0407291227.11f42272@.posting.google.c om...
> > > I have a big table (heap)... well, not so big, I have a small server
> > > and I want to spread access to it across several new disks dedicated
> > > only to that table.
> > > > I known its possible to do that creating a clustered index with "ON
> > > filegroup" option but I want to maintain it as a heap, is there any
> > > way to do this without dropping indexes/references - bulk unload -
> > > create table - bulk load - create indexes?.
> > As far as I know, you have to drop everything and recreate it, as you
have
> > described. But in general, in MSSQL it's a good idea to have a clustered
> > index on all tables, so it would be interesting to know why you prefer
to
> I agree.
> > maintain a heap table.
> Well, I have found that after define a clustered index some queries
> changed their plan (from a "Index Seek"-->"Nested Loops"-->"Bookmark
> Lookup" to a "Clustered Index Seek") and surprisingly got a little
> slower, that's suggar for the query optimizer so it thinks its better
> to use it, I have to investigate why this happens before define "the
> correct" clustered index, a not easy task as you know. Right now I
> only want to improve "bookmark lookup" on this table that is 60%-80%
> of the weigth of the queries.
> > Simon
> Thanks|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<dBPOc.12029$vm4.122553507@.news-text.cableinet.net>...
> Hi
> Adding the clustered index in the new filegroup and then dropping it will
> leave the data in the new filegroup. Your other indexes will be rebuilt
> twice though, so it may be quicker to drop and re-create them yourself.
> John

Thanks John, that's perfect.

move terrabytes of data to new data center

How does one go about moving terrabyes of data to new data center to say
establish log shipping across a WAN with huge latency ? I can manage moving
the logs , but I am concerned about the first time transfer of all the data
to the new data center to be used for disaster recovery. The first time
transfer would be all the backup files. We have a lot of databases and
cannot wish to move all the backup files one at a time that would take us a
month to get all the data across, but want to push say around 50TB of data
in a couple of days. We are already compressing our backups and still have a
lot of data.
Does anyone use any tools,etc to push the files faster ? Please let me knowThere is no free ride here. You will have to take the full backups and get
them to the other site, even if you copy to tape and drive them over.
Meanwhile, you keep taking your regular log backups at the origin. Ship
your logs until you are in synch.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23vAmMstfHHA.4872@.TK2MSFTNGP03.phx.gbl...
How does one go about moving terrabyes of data to new data center to say
establish log shipping across a WAN with huge latency ? I can manage moving
the logs , but I am concerned about the first time transfer of all the data
to the new data center to be used for disaster recovery. The first time
transfer would be all the backup files. We have a lot of databases and
cannot wish to move all the backup files one at a time that would take us a
month to get all the data across, but want to push say around 50TB of data
in a couple of days. We are already compressing our backups and still have a
lot of data.
Does anyone use any tools,etc to push the files faster ? Please let me know|||Jim Gray used to do this with his astronomy databases and he said by far the
best bandwidth was putting the data on a hard drive and shipping it to the
destination. He used to say FedEx was the best high-bandwidth carrier. You
can get a terabyte of cheap disk for under $500 these days.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:unAG8ztfHHA.4032@.TK2MSFTNGP02.phx.gbl...
> There is no free ride here. You will have to take the full backups and
> get
> them to the other site, even if you copy to tape and drive them over.
> Meanwhile, you keep taking your regular log backups at the origin. Ship
> your logs until you are in synch.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:%23vAmMstfHHA.4872@.TK2MSFTNGP03.phx.gbl...
> How does one go about moving terrabyes of data to new data center to say
> establish log shipping across a WAN with huge latency ? I can manage
> moving
> the logs , but I am concerned about the first time transfer of all the
> data
> to the new data center to be used for disaster recovery. The first time
> transfer would be all the backup files. We have a lot of databases and
> cannot wish to move all the backup files one at a time that would take us
> a
> month to get all the data across, but want to push say around 50TB of data
> in a couple of days. We are already compressing our backups and still have
> a
> lot of data.
> Does anyone use any tools,etc to push the files faster ? Please let me
> know
>|||Y'know, I think he said that at PASS in Dallas in 2005. We'll miss him.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:1B33E8DD-E88C-41A0-8123-629A2E50266D@.microsoft.com...
Jim Gray used to do this with his astronomy databases and he said by far the
best bandwidth was putting the data on a hard drive and shipping it to the
destination. He used to say FedEx was the best high-bandwidth carrier. You
can get a terabyte of cheap disk for under $500 these days.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:unAG8ztfHHA.4032@.TK2MSFTNGP02.phx.gbl...
> There is no free ride here. You will have to take the full backups and
> get
> them to the other site, even if you copy to tape and drive them over.
> Meanwhile, you keep taking your regular log backups at the origin. Ship
> your logs until you are in synch.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:%23vAmMstfHHA.4872@.TK2MSFTNGP03.phx.gbl...
> How does one go about moving terrabyes of data to new data center to say
> establish log shipping across a WAN with huge latency ? I can manage
> moving
> the logs , but I am concerned about the first time transfer of all the
> data
> to the new data center to be used for disaster recovery. The first time
> transfer would be all the backup files. We have a lot of databases and
> cannot wish to move all the backup files one at a time that would take us
> a
> month to get all the data across, but want to push say around 50TB of data
> in a couple of days. We are already compressing our backups and still have
> a
> lot of data.
> Does anyone use any tools,etc to push the files faster ? Please let me
> know
>|||On Sat, 14 Apr 2007 16:00:41 -0700, "Roger Wolter[MSFT]"
<rwolter@.online.microsoft.com> wrote:

>Jim Gray used to do this with his astronomy databases and he said by far th
e
>best bandwidth was putting the data on a hard drive and shipping it to the
>destination. He used to say FedEx was the best high-bandwidth carrier. Yo
u
>can get a terabyte of cheap disk for under $500 these days.
Maybe the best thing is (a) move the new SAN to the source network,
(b) detach and copy all files direct to SAN over local gigabit
backbone, (c) ship entire SAN to new site.
If I compute correctly that's 100 hours to copy even at gigabit, with
no backup/restore, no compress/decompress - and no encrypt/decrypt.
Fun for all!
Here's hoping there's a clear division between current and historical
data, or you're in real trouble!
J.|||"Hassan" <hassan@.hotmail.com> wrote in message
news:%23vAmMstfHHA.4872@.TK2MSFTNGP03.phx.gbl...
> How does one go about moving terrabyes of data to new data center to say
> establish log shipping across a WAN with huge latency ? I can manage
> moving the logs , but I am concerned about the first time transfer of all
> the data to the new data center to be used for disaster recovery. The
> first time transfer would be all the backup files. We have a lot of
> databases and cannot wish to move all the backup files one at a time that
> would take us a month to get all the data across, but want to push say
> around 50TB of data in a couple of days. We are already compressing our
> backups and still have a lot of data.
> Does anyone use any tools,etc to push the files faster ? Please let me
> know
>
A truck full of harddrives has high latency, but low bandwidth.
Quite seriously, you made find it easiest to buy, borrow, rent a disk array
of some sort, copy stuff to it, drive to the new datacenter, load it up and
copy over.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

move terrabytes of data to new data center

How does one go about moving terrabyes of data to new data center to say
establish log shipping across a WAN with huge latency ? I can manage moving
the logs , but I am concerned about the first time transfer of all the data
to the new data center to be used for disaster recovery. The first time
transfer would be all the backup files. We have a lot of databases and
cannot wish to move all the backup files one at a time that would take us a
month to get all the data across, but want to push say around 50TB of data
in a couple of days. We are already compressing our backups and still have a
lot of data.
Does anyone use any tools,etc to push the files faster ? Please let me know
There is no free ride here. You will have to take the full backups and get
them to the other site, even if you copy to tape and drive them over.
Meanwhile, you keep taking your regular log backups at the origin. Ship
your logs until you are in synch.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23vAmMstfHHA.4872@.TK2MSFTNGP03.phx.gbl...
How does one go about moving terrabyes of data to new data center to say
establish log shipping across a WAN with huge latency ? I can manage moving
the logs , but I am concerned about the first time transfer of all the data
to the new data center to be used for disaster recovery. The first time
transfer would be all the backup files. We have a lot of databases and
cannot wish to move all the backup files one at a time that would take us a
month to get all the data across, but want to push say around 50TB of data
in a couple of days. We are already compressing our backups and still have a
lot of data.
Does anyone use any tools,etc to push the files faster ? Please let me know
|||Jim Gray used to do this with his astronomy databases and he said by far the
best bandwidth was putting the data on a hard drive and shipping it to the
destination. He used to say FedEx was the best high-bandwidth carrier. You
can get a terabyte of cheap disk for under $500 these days.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:unAG8ztfHHA.4032@.TK2MSFTNGP02.phx.gbl...
> There is no free ride here. You will have to take the full backups and
> get
> them to the other site, even if you copy to tape and drive them over.
> Meanwhile, you keep taking your regular log backups at the origin. Ship
> your logs until you are in synch.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:%23vAmMstfHHA.4872@.TK2MSFTNGP03.phx.gbl...
> How does one go about moving terrabyes of data to new data center to say
> establish log shipping across a WAN with huge latency ? I can manage
> moving
> the logs , but I am concerned about the first time transfer of all the
> data
> to the new data center to be used for disaster recovery. The first time
> transfer would be all the backup files. We have a lot of databases and
> cannot wish to move all the backup files one at a time that would take us
> a
> month to get all the data across, but want to push say around 50TB of data
> in a couple of days. We are already compressing our backups and still have
> a
> lot of data.
> Does anyone use any tools,etc to push the files faster ? Please let me
> know
>
|||Y'know, I think he said that at PASS in Dallas in 2005. We'll miss him.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:1B33E8DD-E88C-41A0-8123-629A2E50266D@.microsoft.com...
Jim Gray used to do this with his astronomy databases and he said by far the
best bandwidth was putting the data on a hard drive and shipping it to the
destination. He used to say FedEx was the best high-bandwidth carrier. You
can get a terabyte of cheap disk for under $500 these days.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:unAG8ztfHHA.4032@.TK2MSFTNGP02.phx.gbl...
> There is no free ride here. You will have to take the full backups and
> get
> them to the other site, even if you copy to tape and drive them over.
> Meanwhile, you keep taking your regular log backups at the origin. Ship
> your logs until you are in synch.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:%23vAmMstfHHA.4872@.TK2MSFTNGP03.phx.gbl...
> How does one go about moving terrabyes of data to new data center to say
> establish log shipping across a WAN with huge latency ? I can manage
> moving
> the logs , but I am concerned about the first time transfer of all the
> data
> to the new data center to be used for disaster recovery. The first time
> transfer would be all the backup files. We have a lot of databases and
> cannot wish to move all the backup files one at a time that would take us
> a
> month to get all the data across, but want to push say around 50TB of data
> in a couple of days. We are already compressing our backups and still have
> a
> lot of data.
> Does anyone use any tools,etc to push the files faster ? Please let me
> know
>
|||On Sat, 14 Apr 2007 16:00:41 -0700, "Roger Wolter[MSFT]"
<rwolter@.online.microsoft.com> wrote:

>Jim Gray used to do this with his astronomy databases and he said by far the
>best bandwidth was putting the data on a hard drive and shipping it to the
>destination. He used to say FedEx was the best high-bandwidth carrier. You
>can get a terabyte of cheap disk for under $500 these days.
Maybe the best thing is (a) move the new SAN to the source network,
(b) detach and copy all files direct to SAN over local gigabit
backbone, (c) ship entire SAN to new site.
If I compute correctly that's 100 hours to copy even at gigabit, with
no backup/restore, no compress/decompress - and no encrypt/decrypt.
Fun for all!
Here's hoping there's a clear division between current and historical
data, or you're in real trouble!
J.
|||"Hassan" <hassan@.hotmail.com> wrote in message
news:%23vAmMstfHHA.4872@.TK2MSFTNGP03.phx.gbl...
> How does one go about moving terrabyes of data to new data center to say
> establish log shipping across a WAN with huge latency ? I can manage
> moving the logs , but I am concerned about the first time transfer of all
> the data to the new data center to be used for disaster recovery. The
> first time transfer would be all the backup files. We have a lot of
> databases and cannot wish to move all the backup files one at a time that
> would take us a month to get all the data across, but want to push say
> around 50TB of data in a couple of days. We are already compressing our
> backups and still have a lot of data.
> Does anyone use any tools,etc to push the files faster ? Please let me
> know
>
A truck full of harddrives has high latency, but low bandwidth.
Quite seriously, you made find it easiest to buy, borrow, rent a disk array
of some sort, copy stuff to it, drive to the new datacenter, load it up and
copy over.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

move terrabytes of data to new data center

How does one go about moving terrabyes of data to new data center to say
establish log shipping across a WAN with huge latency ? I can manage moving
the logs , but I am concerned about the first time transfer of all the data
to the new data center to be used for disaster recovery. The first time
transfer would be all the backup files. We have a lot of databases and
cannot wish to move all the backup files one at a time that would take us a
month to get all the data across, but want to push say around 50TB of data
in a couple of days. We are already compressing our backups and still have a
lot of data.
Does anyone use any tools,etc to push the files faster ? Please let me knowThere is no free ride here. You will have to take the full backups and get
them to the other site, even if you copy to tape and drive them over.
Meanwhile, you keep taking your regular log backups at the origin. Ship
your logs until you are in synch.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23vAmMstfHHA.4872@.TK2MSFTNGP03.phx.gbl...
How does one go about moving terrabyes of data to new data center to say
establish log shipping across a WAN with huge latency ? I can manage moving
the logs , but I am concerned about the first time transfer of all the data
to the new data center to be used for disaster recovery. The first time
transfer would be all the backup files. We have a lot of databases and
cannot wish to move all the backup files one at a time that would take us a
month to get all the data across, but want to push say around 50TB of data
in a couple of days. We are already compressing our backups and still have a
lot of data.
Does anyone use any tools,etc to push the files faster ? Please let me know|||Jim Gray used to do this with his astronomy databases and he said by far the
best bandwidth was putting the data on a hard drive and shipping it to the
destination. He used to say FedEx was the best high-bandwidth carrier. You
can get a terabyte of cheap disk for under $500 these days.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:unAG8ztfHHA.4032@.TK2MSFTNGP02.phx.gbl...
> There is no free ride here. You will have to take the full backups and
> get
> them to the other site, even if you copy to tape and drive them over.
> Meanwhile, you keep taking your regular log backups at the origin. Ship
> your logs until you are in synch.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:%23vAmMstfHHA.4872@.TK2MSFTNGP03.phx.gbl...
> How does one go about moving terrabyes of data to new data center to say
> establish log shipping across a WAN with huge latency ? I can manage
> moving
> the logs , but I am concerned about the first time transfer of all the
> data
> to the new data center to be used for disaster recovery. The first time
> transfer would be all the backup files. We have a lot of databases and
> cannot wish to move all the backup files one at a time that would take us
> a
> month to get all the data across, but want to push say around 50TB of data
> in a couple of days. We are already compressing our backups and still have
> a
> lot of data.
> Does anyone use any tools,etc to push the files faster ? Please let me
> know
>|||Y'know, I think he said that at PASS in Dallas in 2005. We'll miss him.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:1B33E8DD-E88C-41A0-8123-629A2E50266D@.microsoft.com...
Jim Gray used to do this with his astronomy databases and he said by far the
best bandwidth was putting the data on a hard drive and shipping it to the
destination. He used to say FedEx was the best high-bandwidth carrier. You
can get a terabyte of cheap disk for under $500 these days.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:unAG8ztfHHA.4032@.TK2MSFTNGP02.phx.gbl...
> There is no free ride here. You will have to take the full backups and
> get
> them to the other site, even if you copy to tape and drive them over.
> Meanwhile, you keep taking your regular log backups at the origin. Ship
> your logs until you are in synch.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:%23vAmMstfHHA.4872@.TK2MSFTNGP03.phx.gbl...
> How does one go about moving terrabyes of data to new data center to say
> establish log shipping across a WAN with huge latency ? I can manage
> moving
> the logs , but I am concerned about the first time transfer of all the
> data
> to the new data center to be used for disaster recovery. The first time
> transfer would be all the backup files. We have a lot of databases and
> cannot wish to move all the backup files one at a time that would take us
> a
> month to get all the data across, but want to push say around 50TB of data
> in a couple of days. We are already compressing our backups and still have
> a
> lot of data.
> Does anyone use any tools,etc to push the files faster ? Please let me
> know
>|||On Sat, 14 Apr 2007 16:00:41 -0700, "Roger Wolter[MSFT]"
<rwolter@.online.microsoft.com> wrote:
>Jim Gray used to do this with his astronomy databases and he said by far the
>best bandwidth was putting the data on a hard drive and shipping it to the
>destination. He used to say FedEx was the best high-bandwidth carrier. You
>can get a terabyte of cheap disk for under $500 these days.
Maybe the best thing is (a) move the new SAN to the source network,
(b) detach and copy all files direct to SAN over local gigabit
backbone, (c) ship entire SAN to new site.
If I compute correctly that's 100 hours to copy even at gigabit, with
no backup/restore, no compress/decompress - and no encrypt/decrypt.
Fun for all!
Here's hoping there's a clear division between current and historical
data, or you're in real trouble!
J.|||"Hassan" <hassan@.hotmail.com> wrote in message
news:%23vAmMstfHHA.4872@.TK2MSFTNGP03.phx.gbl...
> How does one go about moving terrabyes of data to new data center to say
> establish log shipping across a WAN with huge latency ? I can manage
> moving the logs , but I am concerned about the first time transfer of all
> the data to the new data center to be used for disaster recovery. The
> first time transfer would be all the backup files. We have a lot of
> databases and cannot wish to move all the backup files one at a time that
> would take us a month to get all the data across, but want to push say
> around 50TB of data in a couple of days. We are already compressing our
> backups and still have a lot of data.
> Does anyone use any tools,etc to push the files faster ? Please let me
> know
>
A truck full of harddrives has high latency, but low bandwidth.
Quite seriously, you made find it easiest to buy, borrow, rent a disk array
of some sort, copy stuff to it, drive to the new datacenter, load it up and
copy over.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.htmlsql

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...
>

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...
>> 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 ?
>|||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...
>> 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 ?
>

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...
>

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...
>