Hi,
I am a relative newcomer to data warehousing and OLAP. We are in the process
of planning a general way forward and an architecture for our warehouse, and
I am seeking advice as to best practice ways forward. We are using SQL
Server 2000, and will extend this with Analysis Services as and when
necessary. The ultimate goal of the project is to provide online reporting
capabilities using the new SQL Server Reporting Services, early next year.
My biggest headache is in designing our dimensions. I have ploughed through
books by Ralph Kimball ( => the authority on dimensional analysis, and
denormalised data warehouse structure with multiple marts making one big
whole) and Claudia Imhoff ( => advocate of a relational 'enterprise' data
warehouse, with dimensional data marts hanging off this). We are likely to
take the Imhoff route and have a relational DW, largely because we want to
have a place to keep historical data in a strcuture similar to the structure
it originated from. But we will use a lot of ideas from Kimball to construct
our data marts.
To the main headache...We have a number of dimensions that originate from
recursive relational tables (namely 'geographic region', 'market sector' and
'business division'). They all have unpredictable numbers of levels in their
hierarchy, and they are type 2 (slowly changing) dimensions - so it's
important to preserve historical relationships. These recursive tables also
are snowflaked - i.e. they form many/many relationships with other dimension
tables.
My question is how we should collapse/explode/denormalize these dimensions?
For our first stage of work we don't really need to do OLAp style analysis -
we can just report on our data as-is - OLAP analysis will come later.
Kimball suggests using 'bridge' tables to descibe the parent/child
hierarchies - these bridge tables are built at the DW load stage - does
anyone have experience doing this?
OR, could we use Analysis Services to handle the recursion and snowflaking -
AS makes big claims about how efficiently it can do this - and then create
cubes from our fact/dimensiosn and report from this?
Any suggestions or criticisms or anything else very gratefully received. If
I am barking up the wrong tree, then please let me know - as I said, I am
new to this.
Thanks, in anticipation.
Chris LewisHi
You may want to ask people of OLAP newsgroup.
"chrislewis@.etsolutions.com" <chrislewis@.etnospamsolutions.com> wrote in
message news:3fe6e368$0$45676$65c69314@.mercury.nildram.net...
> Hi,
> I am a relative newcomer to data warehousing and OLAP. We are in the
process
> of planning a general way forward and an architecture for our warehouse,
and
> I am seeking advice as to best practice ways forward. We are using SQL
> Server 2000, and will extend this with Analysis Services as and when
> necessary. The ultimate goal of the project is to provide online reporting
> capabilities using the new SQL Server Reporting Services, early next year.
> My biggest headache is in designing our dimensions. I have ploughed
through
> books by Ralph Kimball ( => the authority on dimensional analysis, and
> denormalised data warehouse structure with multiple marts making one big
> whole) and Claudia Imhoff ( => advocate of a relational 'enterprise' data
> warehouse, with dimensional data marts hanging off this). We are likely to
> take the Imhoff route and have a relational DW, largely because we want to
> have a place to keep historical data in a strcuture similar to the
structure
> it originated from. But we will use a lot of ideas from Kimball to
construct
> our data marts.
> To the main headache...We have a number of dimensions that originate from
> recursive relational tables (namely 'geographic region', 'market sector'
and
> 'business division'). They all have unpredictable numbers of levels in
their
> hierarchy, and they are type 2 (slowly changing) dimensions - so it's
> important to preserve historical relationships. These recursive tables
also
> are snowflaked - i.e. they form many/many relationships with other
dimension
> tables.
> My question is how we should collapse/explode/denormalize these
dimensions?
> For our first stage of work we don't really need to do OLAp style
analysis -
> we can just report on our data as-is - OLAP analysis will come later.
> Kimball suggests using 'bridge' tables to descibe the parent/child
> hierarchies - these bridge tables are built at the DW load stage - does
> anyone have experience doing this?
> OR, could we use Analysis Services to handle the recursion and
snowflaking -
> AS makes big claims about how efficiently it can do this - and then create
> cubes from our fact/dimensiosn and report from this?
> Any suggestions or criticisms or anything else very gratefully received.
If
> I am barking up the wrong tree, then please let me know - as I said, I am
> new to this.
> Thanks, in anticipation.
> Chris Lewis
>
>
Showing posts with label process. Show all posts
Showing posts with label process. Show all posts
Tuesday, March 20, 2012
Big problem
I have two databases that replicate with merge replication. A couple of days, the replication process failed while trying to do a re-initialization (with upload changes to publisher option enabled) and it seemed that some of the records in the subscriber
were missing. So I did a restore of the subscriber and then I then kicked off another re-initialization (with upload changes to publisher option enabled) and it has completed succesfully. However, now it seems that some of the older data that was created
in the subscriber are no longer in the subscriber but they are in the publisher. In addition to this, some of the records that were created in the subscriber did not upload to the publisher.
Is there any way to manually flag records to force them to replicate (for inserts)?
You could try doing a dummy update on the relevant rows (or all rows if necessary). Something like
Update MyTable set fname = fname
should do it.
HTH,
Paul Ibison
|||Yes, I tried this, but the problem is that I need to know which column has changed for each row. Is there any way to get it to replicate the whole row?
|||In your first post you explain that there are some rows on the publisher which don't exist on the subscriber and vice-versa. For some reason the merge triggers didn't fire and MSmerge_contents doesn't contain the the row value, so it is not replicated. Up
dating these rows - any column - will force an entry to this system table, and this should then be replicated. However your second post explains you need to know which column has changed for each row, which is what confuses me
. Why is this required - d
o you also have rows which which exist on both publisher and subscriber but are not synchronized and contain different data?
Regards,
Paul Ibison
|||to force a row to be replicated again use sp_mergedummyupdate.
When the update is done on the other end SQL Server constructs the parameters for the stored procedure so that only the changed column is updated. So, it is stored somewhere. Figuring out where is not for mere mortals such as I - it is for the likes of su
ch as Mr. Ibison
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Paul,
Yes, sorry, I was not clear in my first description of the problem. There are some instances where there is already a recod there but it is an older version, and there are some instances where the records are not there at all. I was mixing the two problem
s up.
Thank you (and Hilary) for your answers. they have been very helpfull.
were missing. So I did a restore of the subscriber and then I then kicked off another re-initialization (with upload changes to publisher option enabled) and it has completed succesfully. However, now it seems that some of the older data that was created
in the subscriber are no longer in the subscriber but they are in the publisher. In addition to this, some of the records that were created in the subscriber did not upload to the publisher.
Is there any way to manually flag records to force them to replicate (for inserts)?
You could try doing a dummy update on the relevant rows (or all rows if necessary). Something like
Update MyTable set fname = fname
should do it.
HTH,
Paul Ibison
|||Yes, I tried this, but the problem is that I need to know which column has changed for each row. Is there any way to get it to replicate the whole row?
|||In your first post you explain that there are some rows on the publisher which don't exist on the subscriber and vice-versa. For some reason the merge triggers didn't fire and MSmerge_contents doesn't contain the the row value, so it is not replicated. Up
dating these rows - any column - will force an entry to this system table, and this should then be replicated. However your second post explains you need to know which column has changed for each row, which is what confuses me
. Why is this required - do you also have rows which which exist on both publisher and subscriber but are not synchronized and contain different data?
Regards,
Paul Ibison
|||to force a row to be replicated again use sp_mergedummyupdate.
When the update is done on the other end SQL Server constructs the parameters for the stored procedure so that only the changed column is updated. So, it is stored somewhere. Figuring out where is not for mere mortals such as I - it is for the likes of su
ch as Mr. Ibison
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Paul,
Yes, sorry, I was not clear in my first description of the problem. There are some instances where there is already a recod there but it is an older version, and there are some instances where the records are not there at all. I was mixing the two problem
s up.
Thank you (and Hilary) for your answers. they have been very helpfull.
Monday, March 19, 2012
BIDS abort
On several occasions I have experienced crashes while running an SSIS package in debug mode. It happens when I start process on Control Flow table and click on Data Flow tab to monitor progress. I have forwarded the error reports and have searched the boards for similar references. Is this problem going to be fixed?
SSIS Data Sources
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
-
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
Mar 23 2007 16:28:52
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Visiual Studio 2005 Professional SP1 SP.050727-7600
.NET Framework 2.0.50727
Any specific error? That might help us.Saturday, February 25, 2012
better disk config for staging & tempdb files...
Hi,
I want to have your feedback on how to configure my drives to insure a good
performance during loading process & transformation against a staging
database.
Image you have 4 drives available for the staging database & tempdb
database...
loosing these disk is not important, so no redundancy required.
I read from an external database into the staging, I do some updates and
copy into the staging himself, and finally I read the staging to load the
datawarehouse.
I execute more then 70 DTS packages, the sequence of these packages is
optimized. So I could write a fact table in the staging while I read another
to fill the datawarehouse at the same time.
I have 4 "big" fact tables (from 1 millions of rows to 20 millions)
There is "only" 4Gb to 5Gb used by the staging database during the loading
process.
how to configure these disks?
Does it better to setup all disks in raid 0? (the system do everything)
Does it better to keep the 4 disks separatly, and create multiple files &
file groups and dedicating 1 disk by fact table?
Creating 1 filegroup and 4 files in this file group (1 on each disk)? (SQL
Server manage the sharing usage of the disks)
Where to put log files?
thanks for your feedback.
Jerome.Jerome,
What's the cost to the business if your DW is down for several days? Having
nonredundant disk for tempdb is generally unacceptable. Having nonredundant
disk for staging is a bit unusual. Push back to get more disk.
If I were placed in this situation and could not get more disk, I'd mirror
and strip with the controller and say that's all the performance she's got.
If management and the business owners are ready to signoff on the potential
of several days down time. The amount of down time depends on outside
hardware availability and support staff availability. Put your logs for
tempdb and staging on one disk. Use the disk controller to strip the other
three and place one file per processor (create all the same size) within the
file group. Normally I would break it out more but four disks isn't enough
to do much with.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:eT0XnuEfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to have your feedback on how to configure my drives to insure a
> good performance during loading process & transformation against a staging
> database.
> Image you have 4 drives available for the staging database & tempdb
> database...
> loosing these disk is not important, so no redundancy required.
> I read from an external database into the staging, I do some updates and
> copy into the staging himself, and finally I read the staging to load the
> datawarehouse.
> I execute more then 70 DTS packages, the sequence of these packages is
> optimized. So I could write a fact table in the staging while I read
> another to fill the datawarehouse at the same time.
> I have 4 "big" fact tables (from 1 millions of rows to 20 millions)
> There is "only" 4Gb to 5Gb used by the staging database during the loading
> process.
> how to configure these disks?
> Does it better to setup all disks in raid 0? (the system do everything)
> Does it better to keep the 4 disks separatly, and create multiple files &
> file groups and dedicating 1 disk by fact table?
> Creating 1 filegroup and 4 files in this file group (1 on each disk)? (SQL
> Server manage the sharing usage of the disks)
> Where to put log files?
> thanks for your feedback.
> Jerome.
>|||the cost is 0 if we can't load data.
a downtime of 4hour to change a disk if we don't have one in the office is
not an issue.
I don't understand why having the tempdb database on a non-fault taulerant
disk is not a good idea from your point of view!!!
tempdb is the only database recreated when er restart SQL server, so the
downtime in case of a crash is ... 5 minutes?
Thanks for the guide about 1 disk by processor.
I'll plan this config.
"Danny" <someone@.nowhere.com> wrote in message
news:1oRwe.14848$Xr6.6230@.trnddc07...
> Jerome,
> What's the cost to the business if your DW is down for several days?
> Having nonredundant disk for tempdb is generally unacceptable. Having
> nonredundant disk for staging is a bit unusual. Push back to get more
> disk.
> If I were placed in this situation and could not get more disk, I'd mirror
> and strip with the controller and say that's all the performance she's
> got.
> If management and the business owners are ready to signoff on the
> potential of several days down time. The amount of down time depends on
> outside hardware availability and support staff availability. Put your
> logs for tempdb and staging on one disk. Use the disk controller to strip
> the other three and place one file per processor (create all the same
> size) within the file group. Normally I would break it out more but four
> disks isn't enough to do much with.
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:eT0XnuEfFHA.3692@.TK2MSFTNGP09.phx.gbl...
>
I want to have your feedback on how to configure my drives to insure a good
performance during loading process & transformation against a staging
database.
Image you have 4 drives available for the staging database & tempdb
database...
loosing these disk is not important, so no redundancy required.
I read from an external database into the staging, I do some updates and
copy into the staging himself, and finally I read the staging to load the
datawarehouse.
I execute more then 70 DTS packages, the sequence of these packages is
optimized. So I could write a fact table in the staging while I read another
to fill the datawarehouse at the same time.
I have 4 "big" fact tables (from 1 millions of rows to 20 millions)
There is "only" 4Gb to 5Gb used by the staging database during the loading
process.
how to configure these disks?
Does it better to setup all disks in raid 0? (the system do everything)
Does it better to keep the 4 disks separatly, and create multiple files &
file groups and dedicating 1 disk by fact table?
Creating 1 filegroup and 4 files in this file group (1 on each disk)? (SQL
Server manage the sharing usage of the disks)
Where to put log files?
thanks for your feedback.
Jerome.Jerome,
What's the cost to the business if your DW is down for several days? Having
nonredundant disk for tempdb is generally unacceptable. Having nonredundant
disk for staging is a bit unusual. Push back to get more disk.
If I were placed in this situation and could not get more disk, I'd mirror
and strip with the controller and say that's all the performance she's got.
If management and the business owners are ready to signoff on the potential
of several days down time. The amount of down time depends on outside
hardware availability and support staff availability. Put your logs for
tempdb and staging on one disk. Use the disk controller to strip the other
three and place one file per processor (create all the same size) within the
file group. Normally I would break it out more but four disks isn't enough
to do much with.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:eT0XnuEfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to have your feedback on how to configure my drives to insure a
> good performance during loading process & transformation against a staging
> database.
> Image you have 4 drives available for the staging database & tempdb
> database...
> loosing these disk is not important, so no redundancy required.
> I read from an external database into the staging, I do some updates and
> copy into the staging himself, and finally I read the staging to load the
> datawarehouse.
> I execute more then 70 DTS packages, the sequence of these packages is
> optimized. So I could write a fact table in the staging while I read
> another to fill the datawarehouse at the same time.
> I have 4 "big" fact tables (from 1 millions of rows to 20 millions)
> There is "only" 4Gb to 5Gb used by the staging database during the loading
> process.
> how to configure these disks?
> Does it better to setup all disks in raid 0? (the system do everything)
> Does it better to keep the 4 disks separatly, and create multiple files &
> file groups and dedicating 1 disk by fact table?
> Creating 1 filegroup and 4 files in this file group (1 on each disk)? (SQL
> Server manage the sharing usage of the disks)
> Where to put log files?
> thanks for your feedback.
> Jerome.
>|||the cost is 0 if we can't load data.
a downtime of 4hour to change a disk if we don't have one in the office is
not an issue.
I don't understand why having the tempdb database on a non-fault taulerant
disk is not a good idea from your point of view!!!
tempdb is the only database recreated when er restart SQL server, so the
downtime in case of a crash is ... 5 minutes?
Thanks for the guide about 1 disk by processor.
I'll plan this config.
"Danny" <someone@.nowhere.com> wrote in message
news:1oRwe.14848$Xr6.6230@.trnddc07...
> Jerome,
> What's the cost to the business if your DW is down for several days?
> Having nonredundant disk for tempdb is generally unacceptable. Having
> nonredundant disk for staging is a bit unusual. Push back to get more
> disk.
> If I were placed in this situation and could not get more disk, I'd mirror
> and strip with the controller and say that's all the performance she's
> got.
> If management and the business owners are ready to signoff on the
> potential of several days down time. The amount of down time depends on
> outside hardware availability and support staff availability. Put your
> logs for tempdb and staging on one disk. Use the disk controller to strip
> the other three and place one file per processor (create all the same
> size) within the file group. Normally I would break it out more but four
> disks isn't enough to do much with.
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:eT0XnuEfFHA.3692@.TK2MSFTNGP09.phx.gbl...
>
better disk config for staging & tempdb files...
Hi,
I want to have your feedback on how to configure my drives to insure a good
performance during loading process & transformation against a staging
database.
Image you have 4 drives available for the staging database & tempdb
database...
loosing these disk is not important, so no redundancy required.
I read from an external database into the staging, I do some updates and
copy into the staging himself, and finally I read the staging to load the
datawarehouse.
I execute more then 70 DTS packages, the sequence of these packages is
optimized. So I could write a fact table in the staging while I read another
to fill the datawarehouse at the same time.
I have 4 "big" fact tables (from 1 millions of rows to 20 millions)
There is "only" 4Gb to 5Gb used by the staging database during the loading
process.
how to configure these disks?
Does it better to setup all disks in raid 0? (the system do everything)
Does it better to keep the 4 disks separatly, and create multiple files &
file groups and dedicating 1 disk by fact table?
Creating 1 filegroup and 4 files in this file group (1 on each disk)? (SQL
Server manage the sharing usage of the disks)
Where to put log files?
thanks for your feedback.
Jerome.
Jerome,
What's the cost to the business if your DW is down for several days? Having
nonredundant disk for tempdb is generally unacceptable. Having nonredundant
disk for staging is a bit unusual. Push back to get more disk.
If I were placed in this situation and could not get more disk, I'd mirror
and strip with the controller and say that's all the performance she's got.
If management and the business owners are ready to signoff on the potential
of several days down time. The amount of down time depends on outside
hardware availability and support staff availability. Put your logs for
tempdb and staging on one disk. Use the disk controller to strip the other
three and place one file per processor (create all the same size) within the
file group. Normally I would break it out more but four disks isn't enough
to do much with.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:eT0XnuEfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to have your feedback on how to configure my drives to insure a
> good performance during loading process & transformation against a staging
> database.
> Image you have 4 drives available for the staging database & tempdb
> database...
> loosing these disk is not important, so no redundancy required.
> I read from an external database into the staging, I do some updates and
> copy into the staging himself, and finally I read the staging to load the
> datawarehouse.
> I execute more then 70 DTS packages, the sequence of these packages is
> optimized. So I could write a fact table in the staging while I read
> another to fill the datawarehouse at the same time.
> I have 4 "big" fact tables (from 1 millions of rows to 20 millions)
> There is "only" 4Gb to 5Gb used by the staging database during the loading
> process.
> how to configure these disks?
> Does it better to setup all disks in raid 0? (the system do everything)
> Does it better to keep the 4 disks separatly, and create multiple files &
> file groups and dedicating 1 disk by fact table?
> Creating 1 filegroup and 4 files in this file group (1 on each disk)? (SQL
> Server manage the sharing usage of the disks)
> Where to put log files?
> thanks for your feedback.
> Jerome.
>
|||the cost is 0 if we can't load data.
a downtime of 4hour to change a disk if we don't have one in the office is
not an issue.
I don't understand why having the tempdb database on a non-fault taulerant
disk is not a good idea from your point of view!!!
tempdb is the only database recreated when er restart SQL server, so the
downtime in case of a crash is ... 5 minutes?
Thanks for the guide about 1 disk by processor.
I'll plan this config.
"Danny" <someone@.nowhere.com> wrote in message
news:1oRwe.14848$Xr6.6230@.trnddc07...
> Jerome,
> What's the cost to the business if your DW is down for several days?
> Having nonredundant disk for tempdb is generally unacceptable. Having
> nonredundant disk for staging is a bit unusual. Push back to get more
> disk.
> If I were placed in this situation and could not get more disk, I'd mirror
> and strip with the controller and say that's all the performance she's
> got.
> If management and the business owners are ready to signoff on the
> potential of several days down time. The amount of down time depends on
> outside hardware availability and support staff availability. Put your
> logs for tempdb and staging on one disk. Use the disk controller to strip
> the other three and place one file per processor (create all the same
> size) within the file group. Normally I would break it out more but four
> disks isn't enough to do much with.
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:eT0XnuEfFHA.3692@.TK2MSFTNGP09.phx.gbl...
>
I want to have your feedback on how to configure my drives to insure a good
performance during loading process & transformation against a staging
database.
Image you have 4 drives available for the staging database & tempdb
database...
loosing these disk is not important, so no redundancy required.
I read from an external database into the staging, I do some updates and
copy into the staging himself, and finally I read the staging to load the
datawarehouse.
I execute more then 70 DTS packages, the sequence of these packages is
optimized. So I could write a fact table in the staging while I read another
to fill the datawarehouse at the same time.
I have 4 "big" fact tables (from 1 millions of rows to 20 millions)
There is "only" 4Gb to 5Gb used by the staging database during the loading
process.
how to configure these disks?
Does it better to setup all disks in raid 0? (the system do everything)
Does it better to keep the 4 disks separatly, and create multiple files &
file groups and dedicating 1 disk by fact table?
Creating 1 filegroup and 4 files in this file group (1 on each disk)? (SQL
Server manage the sharing usage of the disks)
Where to put log files?
thanks for your feedback.
Jerome.
Jerome,
What's the cost to the business if your DW is down for several days? Having
nonredundant disk for tempdb is generally unacceptable. Having nonredundant
disk for staging is a bit unusual. Push back to get more disk.
If I were placed in this situation and could not get more disk, I'd mirror
and strip with the controller and say that's all the performance she's got.
If management and the business owners are ready to signoff on the potential
of several days down time. The amount of down time depends on outside
hardware availability and support staff availability. Put your logs for
tempdb and staging on one disk. Use the disk controller to strip the other
three and place one file per processor (create all the same size) within the
file group. Normally I would break it out more but four disks isn't enough
to do much with.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:eT0XnuEfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to have your feedback on how to configure my drives to insure a
> good performance during loading process & transformation against a staging
> database.
> Image you have 4 drives available for the staging database & tempdb
> database...
> loosing these disk is not important, so no redundancy required.
> I read from an external database into the staging, I do some updates and
> copy into the staging himself, and finally I read the staging to load the
> datawarehouse.
> I execute more then 70 DTS packages, the sequence of these packages is
> optimized. So I could write a fact table in the staging while I read
> another to fill the datawarehouse at the same time.
> I have 4 "big" fact tables (from 1 millions of rows to 20 millions)
> There is "only" 4Gb to 5Gb used by the staging database during the loading
> process.
> how to configure these disks?
> Does it better to setup all disks in raid 0? (the system do everything)
> Does it better to keep the 4 disks separatly, and create multiple files &
> file groups and dedicating 1 disk by fact table?
> Creating 1 filegroup and 4 files in this file group (1 on each disk)? (SQL
> Server manage the sharing usage of the disks)
> Where to put log files?
> thanks for your feedback.
> Jerome.
>
|||the cost is 0 if we can't load data.
a downtime of 4hour to change a disk if we don't have one in the office is
not an issue.
I don't understand why having the tempdb database on a non-fault taulerant
disk is not a good idea from your point of view!!!
tempdb is the only database recreated when er restart SQL server, so the
downtime in case of a crash is ... 5 minutes?
Thanks for the guide about 1 disk by processor.
I'll plan this config.
"Danny" <someone@.nowhere.com> wrote in message
news:1oRwe.14848$Xr6.6230@.trnddc07...
> Jerome,
> What's the cost to the business if your DW is down for several days?
> Having nonredundant disk for tempdb is generally unacceptable. Having
> nonredundant disk for staging is a bit unusual. Push back to get more
> disk.
> If I were placed in this situation and could not get more disk, I'd mirror
> and strip with the controller and say that's all the performance she's
> got.
> If management and the business owners are ready to signoff on the
> potential of several days down time. The amount of down time depends on
> outside hardware availability and support staff availability. Put your
> logs for tempdb and staging on one disk. Use the disk controller to strip
> the other three and place one file per processor (create all the same
> size) within the file group. Normally I would break it out more but four
> disks isn't enough to do much with.
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:eT0XnuEfFHA.3692@.TK2MSFTNGP09.phx.gbl...
>
Tuesday, February 14, 2012
Best way to install a database at a remote site
Greetings,
I am in the process of installing a SQL database at a customer
location. I have determined that there are 3 ways to do this, and I
wanted to know which is the best of the 3.
1 Install From Script.
In this method I create the database and its objects in scripts that
are run via osql utility on the SQL server machine. For loading any
initial data that I need in the database I also run bcp commands.
2 Install from a backup
In this method I created an empty database on the SQL server, and then
restore over it the database from a backup of the database that I need
to deploy. Then I add or re-attach the users for the database. I
perform all of these operations using osql as well.
3 Install by attaching the data files.
In this method I created an empty database on the SQL server, and then
I attach the data files to the database using the sp_attach procedure.
Then I add or re-attach the users for the database. I perform all of
these operations using osql as well.
Although it is no problem for me to use any of these methods, I wanted
to know from you veterans out there what the best practices are. And
also if there are any unseen hazards for each method above. Or if I
am totally off-the-target, and there is another method that is the
preferred way.
Thanks in Advance,
roger"great_googley_moogley" <pow_pow1476@.yahoo.com> wrote in message
news:dda0ee86.0404021123.46fd0e4e@.posting.google.c om...
> Greetings,
> I am in the process of installing a SQL database at a customer
> location. I have determined that there are 3 ways to do this, and I
> wanted to know which is the best of the 3.
> 1 Install From Script.
> In this method I create the database and its objects in scripts that
> are run via osql utility on the SQL server machine. For loading any
> initial data that I need in the database I also run bcp commands.
> 2 Install from a backup
> In this method I created an empty database on the SQL server, and then
> restore over it the database from a backup of the database that I need
> to deploy. Then I add or re-attach the users for the database. I
> perform all of these operations using osql as well.
> 3 Install by attaching the data files.
> In this method I created an empty database on the SQL server, and then
> I attach the data files to the database using the sp_attach procedure.
> Then I add or re-attach the users for the database. I perform all of
> these operations using osql as well.
> Although it is no problem for me to use any of these methods, I wanted
> to know from you veterans out there what the best practices are. And
> also if there are any unseen hazards for each method above. Or if I
> am totally off-the-target, and there is another method that is the
> preferred way.
> Thanks in Advance,
> roger
All three options can give the same results, as you say, but there are some
differences in functionality.
Option 1 is good if you need to load different data for different clients -
with other methods you'd need a copy of the database for every possible data
set you need to provide, but with scripts the object scripts are the same,
and only the data (BCP) files change. Or from the database object
perspective, you can provide different subsets and/or versions of stored
procedures etc. to different clients. In addition, this option doesn't
require you to have any access to the filesystem, which can be useful in
environments with limited bandwidth or tight security requirements. The
downside - if you consider it one - is that you need to manage the scripts,
but presumably you're using some sort of source control and deployment
scripting already.
Options 2 and 3 are essentially the same, in that you're creating the
database from files which you need to copy to a filesystem first. The major
difference is that you can restore from a network drive, but you can't
attach a database on a network drive (usually). So if you have no access to
the server's local filesystem, but you do have access to a network share
accessible to the server, you could only use the restore option. The big
advantage of these options is obviously simplicity..
Personally, I would consider option 1 the best in the sense that it's the
most flexible - you have complete control over which objects and data are
loaded, and the layout of the files and filegroups can be decided together
with the DBA before you create the database. But if you're always installing
identical databases in very similar environments (eg. inside a large
company), then the simplicity of one of the other options is likely to be
better for you - less time and effort required.
Simon|||On Fri, 2 Apr 2004 22:07:49 +0200, Simon Hayes <sql@.hayes.ch> wrote:
> "great_googley_moogley" <pow_pow1476@.yahoo.com> wrote in message
> news:dda0ee86.0404021123.46fd0e4e@.posting.google.c om...
>> Greetings,
>>
>> I am in the process of installing a SQL database at a customer
>> location. I have determined that there are 3 ways to do this, and I
>> wanted to know which is the best of the 3.
>>
>> 1 Install From Script.
>> In this method I create the database and its objects in scripts that
>> are run via osql utility on the SQL server machine. For loading any
>> initial data that I need in the database I also run bcp commands.
>>
>> 2 Install from a backup
>> In this method I created an empty database on the SQL server, and then
>> restore over it the database from a backup of the database that I need
>> to deploy. Then I add or re-attach the users for the database. I
>> perform all of these operations using osql as well.
>>
>> 3 Install by attaching the data files.
>> In this method I created an empty database on the SQL server, and then
>> I attach the data files to the database using the sp_attach procedure.
>> Then I add or re-attach the users for the database. I perform all of
>> these operations using osql as well.
>>
>> Although it is no problem for me to use any of these methods, I wanted
>> to know from you veterans out there what the best practices are. And
>> also if there are any unseen hazards for each method above. Or if I
>> am totally off-the-target, and there is another method that is the
>> preferred way.
>>
>> Thanks in Advance,
>> roger
> All three options can give the same results, as you say, but there are
> some
> differences in functionality.
> Option 1 is good if you need to load different data for different
> clients -
> with other methods you'd need a copy of the database for every possible
> data
> set you need to provide, but with scripts the object scripts are the
> same,
> and only the data (BCP) files change. Or from the database object
> perspective, you can provide different subsets and/or versions of stored
> procedures etc. to different clients. In addition, this option doesn't
> require you to have any access to the filesystem, which can be useful in
> environments with limited bandwidth or tight security requirements. The
> downside - if you consider it one - is that you need to manage the
> scripts,
> but presumably you're using some sort of source control and deployment
> scripting already.
> Options 2 and 3 are essentially the same, in that you're creating the
> database from files which you need to copy to a filesystem first. The
> major
> difference is that you can restore from a network drive, but you can't
> attach a database on a network drive (usually). So if you have no access
> to
> the server's local filesystem, but you do have access to a network share
> accessible to the server, you could only use the restore option. The big
> advantage of these options is obviously simplicity..
> Personally, I would consider option 1 the best in the sense that it's the
> most flexible - you have complete control over which objects and data are
> loaded, and the layout of the files and filegroups can be decided
> together
> with the DBA before you create the database. But if you're always
> installing
> identical databases in very similar environments (eg. inside a large
> company), then the simplicity of one of the other options is likely to be
> better for you - less time and effort required.
> Simon
I am using option 1 too. But there is one thing I cannot do with it. This
thing is installing custom dll implementing extended stored procedure into
remote computer. Do you know any solution for this problem?
--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||great_googley_moogley (pow_pow1476@.yahoo.com) writes:
> 1 Install From Script.
> In this method I create the database and its objects in scripts that
> are run via osql utility on the SQL server machine. For loading any
> initial data that I need in the database I also run bcp commands.
Be careful to use the -I option to enable the option QUOTED_IDENTIFIERS.
This is paricularly important if you are using indexed views.
> 2 Install from a backup
> In this method I created an empty database on the SQL server, and then
> restore over it the database from a backup of the database that I need
> to deploy. Then I add or re-attach the users for the database. I
> perform all of these operations using osql as well.
> 3 Install by attaching the data files.
> In this method I created an empty database on the SQL server, and then
> I attach the data files to the database using the sp_attach procedure.
> Then I add or re-attach the users for the database. I perform all of
> these operations using osql as well.
This far, all methods may appear to be the same. But then comes the next
step: you need to install fixes, updates and changes. Suddenly option
1 is the only one possible.
In our shop we ship install kits both for installation of new databases,
and upgrades. We have a toolset that includes a tool building an empty
database from SourceSafe. You can do that do step, so that you first
extract the files, and then build from the disk at the remote script.
The toolset also includes a tool that can build update scripts with
changed files, and the same applies to them that you can run in two steps.
If you're curious, it's available at http://www.abaris.se/abaperls/ as
freeware.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Igor Solodovnikov (igor@.helpco.kiev) writes:
> I am using option 1 too. But there is one thing I cannot do with it. This
> thing is installing custom dll implementing extended stored procedure into
> remote computer. Do you know any solution for this problem?
See my reply for the original poster, about the toolset we use.
When our installation staff build new databases or run update scripts at
customer sites, they rarely run them directly. They package everything
in Windows Installer kits. I don't know too much about that part of the
process, but I guess this is the way to package it. Of course, learning
Wise and all that may be too much - packaing in a BAT file could work too.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Fri, 2 Apr 2004 22:15:15 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:
> Igor Solodovnikov (igor@.helpco.kiev) writes:
>> I am using option 1 too. But there is one thing I cannot do with it.
>> This
>> thing is installing custom dll implementing extended stored procedure
>> into
>> remote computer. Do you know any solution for this problem?
> See my reply for the original poster, about the toolset we use.
> When our installation staff build new databases or run update scripts at
> customer sites, they rarely run them directly. They package everything
> in Windows Installer kits. I don't know too much about that part of the
> process, but I guess this is the way to package it. Of course, learning
> Wise and all that may be too much - packaing in a BAT file could work
> too.
Thank you for answer. I must say that I also using Wise and Windows
Installer. My installer copies all required files to local computer and
then executes another utility (written by me) which can install new
instance of msde or create database through SQL script on existing
instance of SQL server/msde. When existing instance is on local computer I
can simply copy dll into sql server directory and my extended stored proc
will work. But I cant do that when existing instance is on another
computer.
Using SQL script to create database is very convenient but it has this
problem which complicates creating extended stored procs.
To solve this problem I must create one more Windows Installer package and
run it on remote computer. This is ugly.
--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
Sunday, February 12, 2012
Best way to export data.
Hello,
The easiest solution that came to my head is to execute DTS package in
command shell. In DTS package you can define whatever format you want.
Create it. Debug it. Play with it. Then just add xp_cmdshell
'dtsrun.exe -S<server-N<dts-package-E -M<dts-password>' to your
procedure.
Straight forward solution is to UNION field names with data and use
BCP -
I have some questions on my options available.
I have to export some tables to csv files to enable another department
to process the files. What I need is a way to do this in ms sql
though a stored proc with quoted identifiers and column names as
heads. I cannot figure out how to do this.
Can anybody give me some options that would be the best options.
I am using ms sql 2000.
Thank you for your time.On Apr 9, 7:27 am, "Designing Solutions WD"
<michael.grass...@.gmail.comwrote:
Quote:
Originally Posted by
Hello,
>
I have some questions on my options available.
>
I have to export some tables to csv files to enable another department
to process the files. What I need is a way to do this in ms sql
though a stored proc with quoted identifiers and column names as
heads. I cannot figure out how to do this.
>
Can anybody give me some options that would be the best options.
>
I am using ms sql 2000.
>
Thank you for your time.
The easiest solution that came to my head is to execute DTS package in
command shell. In DTS package you can define whatever format you want.
Create it. Debug it. Play with it. Then just add xp_cmdshell
'dtsrun.exe -S<server-N<dts-package-E -M<dts-password>' to your
procedure.
- Roman|||On Apr 9, 7:27 am, "Designing Solutions WD"
<michael.grass...@.gmail.comwrote:
Quote:
Originally Posted by
Hello,
>
I have some questions on my options available.
>
I have to export some tables to csv files to enable another department
to process the files. What I need is a way to do this in ms sql
though a stored proc with quoted identifiers and column names as
heads. I cannot figure out how to do this.
>
Can anybody give me some options that would be the best options.
>
I am using ms sql 2000.
>
Thank you for your time.
Straight forward solution is to UNION field names with data and use
BCP -
1. Create a SELECT statement that includes field names -
DECLARE @.names varchar(100), @.delimiter varchar(10)
SET @.delimiter = ','
SELECT @.names = COALESCE(@.names + @.delimiter, '') + '"' + name + '"'
FROM syscolumns where id = (select id from sysobjects where
name='TABLE_TO_EXPORT')
SELECT 'select ' + @.names
2. Concatenate it with UNION SELECT cast(FIELD1 as char), cast(FIELD2
as char), ... From TABLE_TO_EXPORT (which is ugly but it has to be
done to create union)
3. Then using UNION create a VIEW which can be used in BCP to export
data
4. Use BCP from command shell xp_cmdshell "BCP ""select * from
VIEW_TO_EXPORT"" out c:\results.csv -c -t, -T -S<servername>
- Roman
Best way to copy large number of databases to new server?
We're in the process of migrating to a new database server, and have a large
number of databases to move across (approx 100). I found the 'copy database
wizard' which seemed to be exactly what I needed, only it didn't work
because the servers aren't on the same domain (or even the same network).
Is there any similar solution, or am I going to have to use backup/restore
on every single database individually?
Thanks in advance!
ChrisIf they are on the same network and you can´t reach them fromthe one SQL
Server, you should make a backup. You can make a hot backup or depending on
uptime of your database and the size of the databasefiles do a service
shutdown, copy the files and restart the service. This "clone" can be
attached to the other server.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> schrieb im Newsbeitrag
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>|||Thats odd, I have done the same thing and it works on mine ok.
Couple of other solutions for you
Detach the database and copy the data and log file over (you will need to be
careful on Server permissions for the files for this)
Create the structure and copy the data over using DTS.
Have fun
Peter
"Chris Ashley" wrote:
> We're in the process of migrating to a new database server, and have a large
> number of databases to move across (approx 100). I found the 'copy database
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
>|||Hi,
Easy method to copy all the databases (including system DB) is :-
1. Stop active SQL Server service and copy all the MDF , NDF and LDF into a
Tape.
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as existing server)
3. Stop the SQL server
4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
same folders (Same as existing active server).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
Thanks
Hari
SQL Server MVP
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
large
> number of databases to move across (approx 100). I found the 'copy
database
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>|||In article <OsLpXcaUFHA.3176@.TK2MSFTNGP12.phx.gbl>,
hari_prasad_k@.hotmail.com says...
> Hi,
> Easy method to copy all the databases (including system DB) is :-
> 1. Stop active SQL Server service and copy all the MDF , NDF and LDF into a
> Tape.
> 2. Install SQL server and same Service packs (as old) in the identical
> folder (Same as existing server)
> 3. Stop the SQL server
> 4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
> same folders (Same as existing active server).
> 5. Start SQL server
>
> Now login to query analyzer or enterprise manager and confirm all the
> databases are online.
Don't forget about account maintenance - you will have to check all the
logon/permissions - which can be done in a script.
--
--
spam999free@.rrohio.com
remove 999 in order to email me|||Can't you simply write a T-SQL query that lists all databases and backs each
one up, so that on the other end you can restore it in a similar fashion?
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
number of databases to move across (approx 100). I found the 'copy database
wizard' which seemed to be exactly what I needed, only it didn't work
because the servers aren't on the same domain (or even the same network).
Is there any similar solution, or am I going to have to use backup/restore
on every single database individually?
Thanks in advance!
ChrisIf they are on the same network and you can´t reach them fromthe one SQL
Server, you should make a backup. You can make a hot backup or depending on
uptime of your database and the size of the databasefiles do a service
shutdown, copy the files and restart the service. This "clone" can be
attached to the other server.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> schrieb im Newsbeitrag
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>|||Thats odd, I have done the same thing and it works on mine ok.
Couple of other solutions for you
Detach the database and copy the data and log file over (you will need to be
careful on Server permissions for the files for this)
Create the structure and copy the data over using DTS.
Have fun
Peter
"Chris Ashley" wrote:
> We're in the process of migrating to a new database server, and have a large
> number of databases to move across (approx 100). I found the 'copy database
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
>|||Hi,
Easy method to copy all the databases (including system DB) is :-
1. Stop active SQL Server service and copy all the MDF , NDF and LDF into a
Tape.
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as existing server)
3. Stop the SQL server
4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
same folders (Same as existing active server).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
Thanks
Hari
SQL Server MVP
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
large
> number of databases to move across (approx 100). I found the 'copy
database
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>|||In article <OsLpXcaUFHA.3176@.TK2MSFTNGP12.phx.gbl>,
hari_prasad_k@.hotmail.com says...
> Hi,
> Easy method to copy all the databases (including system DB) is :-
> 1. Stop active SQL Server service and copy all the MDF , NDF and LDF into a
> Tape.
> 2. Install SQL server and same Service packs (as old) in the identical
> folder (Same as existing server)
> 3. Stop the SQL server
> 4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
> same folders (Same as existing active server).
> 5. Start SQL server
>
> Now login to query analyzer or enterprise manager and confirm all the
> databases are online.
Don't forget about account maintenance - you will have to check all the
logon/permissions - which can be done in a script.
--
--
spam999free@.rrohio.com
remove 999 in order to email me|||Can't you simply write a T-SQL query that lists all databases and backs each
one up, so that on the other end you can restore it in a similar fashion?
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
Best way to copy large number of databases to new server?
We're in the process of migrating to a new database server, and have a large
number of databases to move across (approx 100). I found the 'copy database
wizard' which seemed to be exactly what I needed, only it didn't work
because the servers aren't on the same domain (or even the same network).
Is there any similar solution, or am I going to have to use backup/restore
on every single database individually?
Thanks in advance!
Chris
If they are on the same network and you cant reach them fromthe one SQL
Server, you should make a backup. You can make a hot backup or depending on
uptime of your database and the size of the databasefiles do a service
shutdown, copy the files and restart the service. This "clone" can be
attached to the other server.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> schrieb im Newsbeitrag
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
|||Thats odd, I have done the same thing and it works on mine ok.
Couple of other solutions for you
Detach the database and copy the data and log file over (you will need to be
careful on Server permissions for the files for this)
Create the structure and copy the data over using DTS.
Have fun
Peter
"Chris Ashley" wrote:
> We're in the process of migrating to a new database server, and have a large
> number of databases to move across (approx 100). I found the 'copy database
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
>
|||Hi,
Easy method to copy all the databases (including system DB) is :-
1. Stop active SQL Server service and copy all the MDF , NDF and LDF into a
Tape.
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as existing server)
3. Stop the SQL server
4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
same folders (Same as existing active server).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
Thanks
Hari
SQL Server MVP
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
large
> number of databases to move across (approx 100). I found the 'copy
database
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
|||In article <OsLpXcaUFHA.3176@.TK2MSFTNGP12.phx.gbl>,
hari_prasad_k@.hotmail.com says...
> Hi,
> Easy method to copy all the databases (including system DB) is :-
> 1. Stop active SQL Server service and copy all the MDF , NDF and LDF into a
> Tape.
> 2. Install SQL server and same Service packs (as old) in the identical
> folder (Same as existing server)
> 3. Stop the SQL server
> 4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
> same folders (Same as existing active server).
> 5. Start SQL server
>
> Now login to query analyzer or enterprise manager and confirm all the
> databases are online.
Don't forget about account maintenance - you will have to check all the
logon/permissions - which can be done in a script.
--
spam999free@.rrohio.com
remove 999 in order to email me
|||Can't you simply write a T-SQL query that lists all databases and backs each
one up, so that on the other end you can restore it in a similar fashion?
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
number of databases to move across (approx 100). I found the 'copy database
wizard' which seemed to be exactly what I needed, only it didn't work
because the servers aren't on the same domain (or even the same network).
Is there any similar solution, or am I going to have to use backup/restore
on every single database individually?
Thanks in advance!
Chris
If they are on the same network and you cant reach them fromthe one SQL
Server, you should make a backup. You can make a hot backup or depending on
uptime of your database and the size of the databasefiles do a service
shutdown, copy the files and restart the service. This "clone" can be
attached to the other server.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> schrieb im Newsbeitrag
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
|||Thats odd, I have done the same thing and it works on mine ok.
Couple of other solutions for you
Detach the database and copy the data and log file over (you will need to be
careful on Server permissions for the files for this)
Create the structure and copy the data over using DTS.
Have fun
Peter
"Chris Ashley" wrote:
> We're in the process of migrating to a new database server, and have a large
> number of databases to move across (approx 100). I found the 'copy database
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
>
|||Hi,
Easy method to copy all the databases (including system DB) is :-
1. Stop active SQL Server service and copy all the MDF , NDF and LDF into a
Tape.
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as existing server)
3. Stop the SQL server
4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
same folders (Same as existing active server).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
Thanks
Hari
SQL Server MVP
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
large
> number of databases to move across (approx 100). I found the 'copy
database
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
|||In article <OsLpXcaUFHA.3176@.TK2MSFTNGP12.phx.gbl>,
hari_prasad_k@.hotmail.com says...
> Hi,
> Easy method to copy all the databases (including system DB) is :-
> 1. Stop active SQL Server service and copy all the MDF , NDF and LDF into a
> Tape.
> 2. Install SQL server and same Service packs (as old) in the identical
> folder (Same as existing server)
> 3. Stop the SQL server
> 4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
> same folders (Same as existing active server).
> 5. Start SQL server
>
> Now login to query analyzer or enterprise manager and confirm all the
> databases are online.
Don't forget about account maintenance - you will have to check all the
logon/permissions - which can be done in a script.
--
spam999free@.rrohio.com
remove 999 in order to email me
|||Can't you simply write a T-SQL query that lists all databases and backs each
one up, so that on the other end you can restore it in a similar fashion?
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
Best way to copy large number of databases to new server?
We're in the process of migrating to a new database server, and have a large
number of databases to move across (approx 100). I found the 'copy database
wizard' which seemed to be exactly what I needed, only it didn't work
because the servers aren't on the same domain (or even the same network).
Is there any similar solution, or am I going to have to use backup/restore
on every single database individually?
Thanks in advance!
ChrisIf they are on the same network and you cant reach them fromthe one SQL
Server, you should make a backup. You can make a hot backup or depending on
uptime of your database and the size of the databasefiles do a service
shutdown, copy the files and restart the service. This "clone" can be
attached to the other server.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> schrieb im Newsbeitrag
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>|||Thats odd, I have done the same thing and it works on mine ok.
Couple of other solutions for you
Detach the database and copy the data and log file over (you will need to be
careful on Server permissions for the files for this)
Create the structure and copy the data over using DTS.
Have fun
Peter
"Chris Ashley" wrote:
> We're in the process of migrating to a new database server, and have a lar
ge
> number of databases to move across (approx 100). I found the 'copy databas
e
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
>|||Hi,
Easy method to copy all the databases (including system DB) is :-
1. Stop active SQL Server service and copy all the MDF , NDF and LDF into a
Tape.
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as existing server)
3. Stop the SQL server
4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
same folders (Same as existing active server).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
Thanks
Hari
SQL Server MVP
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
large
> number of databases to move across (approx 100). I found the 'copy
database
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>|||In article <OsLpXcaUFHA.3176@.TK2MSFTNGP12.phx.gbl>,
hari_prasad_k@.hotmail.com says...
> Hi,
> Easy method to copy all the databases (including system DB) is :-
> 1. Stop active SQL Server service and copy all the MDF , NDF and LDF into
a
> Tape.
> 2. Install SQL server and same Service packs (as old) in the identical
> folder (Same as existing server)
> 3. Stop the SQL server
> 4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
> same folders (Same as existing active server).
> 5. Start SQL server
>
> Now login to query analyzer or enterprise manager and confirm all the
> databases are online.
Don't forget about account maintenance - you will have to check all the
logon/permissions - which can be done in a script.
--
spam999free@.rrohio.com
remove 999 in order to email me|||Can't you simply write a T-SQL query that lists all databases and backs each
one up, so that on the other end you can restore it in a similar fashion?
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
number of databases to move across (approx 100). I found the 'copy database
wizard' which seemed to be exactly what I needed, only it didn't work
because the servers aren't on the same domain (or even the same network).
Is there any similar solution, or am I going to have to use backup/restore
on every single database individually?
Thanks in advance!
ChrisIf they are on the same network and you cant reach them fromthe one SQL
Server, you should make a backup. You can make a hot backup or depending on
uptime of your database and the size of the databasefiles do a service
shutdown, copy the files and restart the service. This "clone" can be
attached to the other server.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> schrieb im Newsbeitrag
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>|||Thats odd, I have done the same thing and it works on mine ok.
Couple of other solutions for you
Detach the database and copy the data and log file over (you will need to be
careful on Server permissions for the files for this)
Create the structure and copy the data over using DTS.
Have fun
Peter
"Chris Ashley" wrote:
> We're in the process of migrating to a new database server, and have a lar
ge
> number of databases to move across (approx 100). I found the 'copy databas
e
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
>|||Hi,
Easy method to copy all the databases (including system DB) is :-
1. Stop active SQL Server service and copy all the MDF , NDF and LDF into a
Tape.
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as existing server)
3. Stop the SQL server
4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
same folders (Same as existing active server).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
Thanks
Hari
SQL Server MVP
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
large
> number of databases to move across (approx 100). I found the 'copy
database
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>|||In article <OsLpXcaUFHA.3176@.TK2MSFTNGP12.phx.gbl>,
hari_prasad_k@.hotmail.com says...
> Hi,
> Easy method to copy all the databases (including system DB) is :-
> 1. Stop active SQL Server service and copy all the MDF , NDF and LDF into
a
> Tape.
> 2. Install SQL server and same Service packs (as old) in the identical
> folder (Same as existing server)
> 3. Stop the SQL server
> 4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
> same folders (Same as existing active server).
> 5. Start SQL server
>
> Now login to query analyzer or enterprise manager and confirm all the
> databases are online.
Don't forget about account maintenance - you will have to check all the
logon/permissions - which can be done in a script.
--
spam999free@.rrohio.com
remove 999 in order to email me|||Can't you simply write a T-SQL query that lists all databases and backs each
one up, so that on the other end you can restore it in a similar fashion?
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
Friday, February 10, 2012
Best Way of data transfer
Hello All,
I am a newbie dba and need some expert advice on one of my development
scenario I am currently struck with. I am in process of designing a
reporting solution for my company. It is going to be a web based
intranet thin client multitiered application using sql server 2000 and
..net platform.
This application basically shows the real time KPI's or statistics in
different forms of reports on an hourly basis to the senior managers to
right on their desktop. Eventually these reports will help them in
decision making for the better performance of the business...
My question here is what is the best way of transferring large chunk of
data (not entire table(s)) from production server (SQL 2000) to a
reporting server or staging database with an hourly refresh without
stressing the production environment?
Is it a) Replication preferably snapshot? Or
b) BCP? or
c) DTS?
Or do you suggest any better way of achieving this task?
Any suggestion or tips would be greatly appreciated.
Looking forward for your responses.
Many Thanks,
AK
I'd recommend using transactional replication - after the snapshot, it'll
just take the changes to the data.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks very much for your reply. Paul,
Is it viable to do hourly refresh through out the day using your
advised approach? Will there be any performance issues?
Thanks very much for your reply.
Ak
Paul Ibison wrote:
> I'd recommend using transactional replication - after the snapshot, it'll
> just take the changes to the data.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||The log-reader will ov course affect the production system, and if you have
a publisher/distributor, there will be disk access required to write and
read from the distribution database. Exactly what this all amounts to is
difficult to say and is more empirically determined, although the section
entitled "Cost of Transactional Replication at the Publisher" in this
article will give you some idea:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx.
The other thing to take into account is the effect on reporting queries to
have the distribution agent aplying transactions, and the consequential
potential blocking issues.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||I would use transactional replication as it offers the lowest latency.
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
"AK" <arshad.khan@.policyadmin.co.uk> wrote in message
news:1163424096.157922.228010@.k70g2000cwa.googlegr oups.com...
> Hello All,
> I am a newbie dba and need some expert advice on one of my development
> scenario I am currently struck with. I am in process of designing a
> reporting solution for my company. It is going to be a web based
> intranet thin client multitiered application using sql server 2000 and
> .net platform.
> This application basically shows the real time KPI's or statistics in
> different forms of reports on an hourly basis to the senior managers to
> right on their desktop. Eventually these reports will help them in
> decision making for the better performance of the business...
>
> My question here is what is the best way of transferring large chunk of
> data (not entire table(s)) from production server (SQL 2000) to a
> reporting server or staging database with an hourly refresh without
> stressing the production environment?
> Is it a) Replication preferably snapshot? Or
> b) BCP? or
> c) DTS?
> Or do you suggest any better way of achieving this task?
> Any suggestion or tips would be greatly appreciated.
> Looking forward for your responses.
>
> Many Thanks,
> AK
>
|||Thanks very much for all your replies guys.
I will give at a go this way then. Will post more queries on this
thread if i get stuck any where.
AK
Hilary Cotter wrote:[vbcol=seagreen]
> I would use transactional replication as it offers the lowest latency.
> --
> 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
>
> "AK" <arshad.khan@.policyadmin.co.uk> wrote in message
> news:1163424096.157922.228010@.k70g2000cwa.googlegr oups.com...
I am a newbie dba and need some expert advice on one of my development
scenario I am currently struck with. I am in process of designing a
reporting solution for my company. It is going to be a web based
intranet thin client multitiered application using sql server 2000 and
..net platform.
This application basically shows the real time KPI's or statistics in
different forms of reports on an hourly basis to the senior managers to
right on their desktop. Eventually these reports will help them in
decision making for the better performance of the business...
My question here is what is the best way of transferring large chunk of
data (not entire table(s)) from production server (SQL 2000) to a
reporting server or staging database with an hourly refresh without
stressing the production environment?
Is it a) Replication preferably snapshot? Or
b) BCP? or
c) DTS?
Or do you suggest any better way of achieving this task?
Any suggestion or tips would be greatly appreciated.
Looking forward for your responses.
Many Thanks,
AK
I'd recommend using transactional replication - after the snapshot, it'll
just take the changes to the data.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks very much for your reply. Paul,
Is it viable to do hourly refresh through out the day using your
advised approach? Will there be any performance issues?
Thanks very much for your reply.
Ak
Paul Ibison wrote:
> I'd recommend using transactional replication - after the snapshot, it'll
> just take the changes to the data.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||The log-reader will ov course affect the production system, and if you have
a publisher/distributor, there will be disk access required to write and
read from the distribution database. Exactly what this all amounts to is
difficult to say and is more empirically determined, although the section
entitled "Cost of Transactional Replication at the Publisher" in this
article will give you some idea:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx.
The other thing to take into account is the effect on reporting queries to
have the distribution agent aplying transactions, and the consequential
potential blocking issues.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||I would use transactional replication as it offers the lowest latency.
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
"AK" <arshad.khan@.policyadmin.co.uk> wrote in message
news:1163424096.157922.228010@.k70g2000cwa.googlegr oups.com...
> Hello All,
> I am a newbie dba and need some expert advice on one of my development
> scenario I am currently struck with. I am in process of designing a
> reporting solution for my company. It is going to be a web based
> intranet thin client multitiered application using sql server 2000 and
> .net platform.
> This application basically shows the real time KPI's or statistics in
> different forms of reports on an hourly basis to the senior managers to
> right on their desktop. Eventually these reports will help them in
> decision making for the better performance of the business...
>
> My question here is what is the best way of transferring large chunk of
> data (not entire table(s)) from production server (SQL 2000) to a
> reporting server or staging database with an hourly refresh without
> stressing the production environment?
> Is it a) Replication preferably snapshot? Or
> b) BCP? or
> c) DTS?
> Or do you suggest any better way of achieving this task?
> Any suggestion or tips would be greatly appreciated.
> Looking forward for your responses.
>
> Many Thanks,
> AK
>
|||Thanks very much for all your replies guys.
I will give at a go this way then. Will post more queries on this
thread if i get stuck any where.
AK
Hilary Cotter wrote:[vbcol=seagreen]
> I would use transactional replication as it offers the lowest latency.
> --
> 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
>
> "AK" <arshad.khan@.policyadmin.co.uk> wrote in message
> news:1163424096.157922.228010@.k70g2000cwa.googlegr oups.com...
Subscribe to:
Posts (Atom)