Best technique for Replication

thx Christian and Paul fro previous answers!
I have a SQL2000 server that hosts our accounting database and I want to
replicate it to another SQL2000 server so I can write reports etc, and do not
want to use the production server for these tasks. I tried to setup a
"snapshot" and was somewhat successful. My question is would it be better to
do a snapshot or transactional replication? The snapshot seemed to take along
time to run. I also do log backups every 15 minutes.
ps Paul I ordered your book...hope it will help also.
Rick Rushing
System Administrator
SQL Newbie
D & J Construction
Rick,
snapshot is sometimes OK but it'll lock the tables and you can't really do
this regularly, and unless all your data is changing it is overkill.
Transactional would be more appropriate. Log shipping is sometimes used, but
you have to be out of the standby server when the log is restored, which in
most cases rules it out.
Rgds,
Paul Ibison, SQL Server MVP
BTW the only book I've written is my address book - Hilary's the man for
replication books and it's a very useful resource.
|||Paul,
There will be about 20 users on the SQL production server and only myself
using the other SQL server. My goal is to find a way to have a copy of the
database on the production server to the other server for use in writing
reports etc. So you think transaction would be a better approach?
ps you are right..it was Hilary's bok on replication.
Rick Rushing
System Administrator
D & J Construction
"Paul Ibison" wrote:

> Rick,
> snapshot is sometimes OK but it'll lock the tables and you can't really do
> this regularly, and unless all your data is changing it is overkill.
> Transactional would be more appropriate. Log shipping is sometimes used, but
> you have to be out of the standby server when the log is restored, which in
> most cases rules it out.
> Rgds,
> Paul Ibison, SQL Server MVP
> BTW the only book I've written is my address book - Hilary's the man for
> replication books and it's a very useful resource.
>
>
|||Rick - yes this is quite a common approach, and gives you the control to
decide exactly which tables are selected. BTW the Database snapshots in SQL
Server 2005 will offer an interesting alternative.
Rgds,
Paul Ibison
|||Thanks Paul
I will pursue using transactional when my book arrives. We just installed
these SQL servers back in October so it will be a while before we upgrade.
thanks again for the input.
Rick Rushing
System Administrator
D & J Construction
"Paul Ibison" wrote:

> Rick - yes this is quite a common approach, and gives you the control to
> decide exactly which tables are selected. BTW the Database snapshots in SQL
> Server 2005 will offer an interesting alternative.
> Rgds,
> Paul Ibison
>
>

Best practice for DR/Standby server

We use SQL2000 Standard Edition SP3a. We have a
Production Server and a DR Server that is off site.
Currently I have SPs coded that do log shipping to the DR
Server every hour from the log backups on the production
server. Is this the best way to keep the DR Server up to
date? I've explored Replication, but it seemed much more
complex and structure changes don't get replicated which
means a new shipment of the entire Production DBs anytime
a structure change is made. It seems like Replication
would be tough to support for this. There is also
Hardware Replication with the Production SAN to the SAN at
the DR site. That's not something I'm very familar with
or how it works exactly. Is Hardware Replication a better
way to do it? I've been told that they may need real time
updates to the DR Server in the future and I'm not sure if
that is realistic or not. I still think log shipping is
best. I could take it down to shipping every 15 min if
needed. Could someone post some opinions/suggestions for
me.
Thanks,
Van
Van,
If your DR server is only used for DR purposes this is what I would do - you should have less than 30 secs down time if you try what I propose.
1) Place your SQL Server data and log files into two separate LUNs on the SAN at your primary site. Start SQL Server and make sure it's running normally. You may even want to try putting the OS on the SAN too - up to you.
2) Image the server using an imaging tool and restore the image to your secondary server and attach it to your secondary SAN at your DR site.
3) Stop SQL Server service on the secondary server.
4) Set up replication at the block level using SAN replication software - replicate the LUNs that the log and data files reside on. You will need to do a full mirror to start with, and then stream replication from then on.
5) When the primary site goes down, make sure that replication gets stopped (it will stop anyway), perform a failover on your SAN to the secondary site, then start SQL Server on your secondary server. You will find that SQL Server will start normally and
there will be much, much, much less data loss than log shipping. Make sure that replication cannot go from source --> secondary should the source come back online, otherwise you will get data file corruption.
6) Step 5 can be scripted so that you can just "push a button" to fail over. The entire process to fail over shouldn't take more than 30 seconds - 1 minute.
The failback process once the primary is available again is a reverse of the above but in a more controlled manner.
I have implemented this solution across the atlantic and it works very, very well indeed, as long as the process is scripted with all manual steps removed. Also, make sure a DR test is performed regularly. Say goodbye to log shipping.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk

Best practice for DR/Standby server

We use SQL2000 Standard Edition SP3a. We have a
Production Server and a DR Server that is off site.
Currently I have SPs coded that do log shipping to the DR
Server every hour from the log backups on the production
server. Is this the best way to keep the DR Server up to
date? I've explored Replication, but it seemed much more
complex and structure changes don't get replicated which
means a new shipment of the entire Production DBs anytime
a structure change is made. It seems like Replication
would be tough to support for this. There is also
Hardware Replication with the Production SAN to the SAN at
the DR site. That's not something I'm very familar with
or how it works exactly. Is hardware Replication a better
way to do it? I've been told that they may need real time
updates to the DR Server in the future and I'm not sure if
that is realistic or not. I still think log shipping is
best. I could take it down to shipping every 15 min if
needed. Could someone post some opinions/suggestions for
me.
Thanks,
VanVan,
If your DR server is only used for DR purposes this is what I would do - you
should have less than 30 secs down time if you try what I propose.
1) Place your SQL Server data and log files into two separate LUNs on the SA
N at your primary site. Start SQL Server and make sure it's running normally
. You may even want to try putting the OS on the SAN too - up to you.
2) Image the server using an imaging tool and restore the image to your seco
ndary server and attach it to your secondary SAN at your DR site.
3) Stop SQL Server service on the secondary server.
4) Set up replication at the block level using SAN replication software - re
plicate the LUNs that the log and data files reside on. You will need to do
a full mirror to start with, and then stream replication from then on.
5) When the primary site goes down, make sure that replication gets stopped
(it will stop anyway), perform a failover on your SAN to the secondary site,
then start SQL Server on your secondary server. You will find that SQL Serv
er will start normally and
there will be much, much, much less data loss than log shipping. Make sure t
hat replication cannot go from source --> secondary should the source come b
ack online, otherwise you will get data file corruption.
6) Step 5 can be scripted so that you can just "push a button" to fail over.
The entire process to fail over shouldn't take more than 30 seconds - 1 min
ute.
The failback process once the primary is available again is a reverse of the
above but in a more controlled manner.
I have implemented this solution across the atlantic and it works very, very
well indeed, as long as the process is scripted with all manual steps remov
ed. Also, make sure a DR test is performed regularly. Say goodbye to log shi
pping.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk

Best Performance Strip setting RAID

We are running SQL6.5 and plan to upgrade to SQL2000. I'm wondering what's the best stripe size for the RAID 5 configuration. 8,16,32 or 64 kb.
The database is 90% used for read actions. Only during night complete refill of data and write actions only for statistics. Any advise is welcome on this subjectSince SQL server pages are 64K the raid stripe settings must also be set to 64K|||SQL 6.5 uses 2k pages and SQL 2000 uses 8k pages.

The stripe size of your RAID drives does NOT have to follow the page size, however I would use a RAID stripe size >= to my page size.

Best performance for SQL2000

Hi
i must configure a RAID on external storage disk array for my SQL 2000
cluster ad have think this configuration :
1- DataFile on separate RAID5 LUN
2- LogFile on another separate RAID5 LUN
3- Quorum on another separate RAID1 LUN
This configugation is good for performace (datafile e logfile separated) and
security (Quorum on Mirror) !'!
Thanks in advanceMake the Log on a RAID 1 or a 10 and not a 5. Raid 5 has too many writes
for peak performance of logs.Put the extra disks into the data raid 5 or
make it a raid 10 for better performance.
--
Andrew J. Kelly SQL MVP
<io.com> wrote in message news:OfbXh0YoEHA.1800@.TK2MSFTNGP15.phx.gbl...
> Hi
> i must configure a RAID on external storage disk array for my SQL 2000
> cluster ad have think this configuration :
> 1- DataFile on separate RAID5 LUN
> 2- LogFile on another separate RAID5 LUN
> 3- Quorum on another separate RAID1 LUN
> This configugation is good for performace (datafile e logfile separated)
and
> security (Quorum on Mirror) !'!
> Thanks in advance
>|||Ok therefore :
datafile RAID5
logfile RAID1
quorum RAID1
it's ok ?
thanks
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uFE6MNaoEHA.324@.TK2MSFTNGP11.phx.gbl...
> Make the Log on a RAID 1 or a 10 and not a 5. Raid 5 has too many writes
> for peak performance of logs.Put the extra disks into the data raid 5 or
> make it a raid 10 for better performance.
> --
> Andrew J. Kelly SQL MVP
>
> <io.com> wrote in message news:OfbXh0YoEHA.1800@.TK2MSFTNGP15.phx.gbl...
> > Hi
> >
> > i must configure a RAID on external storage disk array for my SQL 2000
> > cluster ad have think this configuration :
> >
> > 1- DataFile on separate RAID5 LUN
> > 2- LogFile on another separate RAID5 LUN
> > 3- Quorum on another separate RAID1 LUN
> >
> > This configugation is good for performace (datafile e logfile separated)
> and
> > security (Quorum on Mirror) !'!
> >
> > Thanks in advance
> >
> >
>|||Yes
--
Andrew J. Kelly SQL MVP
<io.com> wrote in message news:ORYQO1aoEHA.1608@.TK2MSFTNGP15.phx.gbl...
> Ok therefore :
> datafile RAID5
> logfile RAID1
> quorum RAID1
> it's ok ?
> thanks
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uFE6MNaoEHA.324@.TK2MSFTNGP11.phx.gbl...
> > Make the Log on a RAID 1 or a 10 and not a 5. Raid 5 has too many
writes
> > for peak performance of logs.Put the extra disks into the data raid 5 or
> > make it a raid 10 for better performance.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > <io.com> wrote in message news:OfbXh0YoEHA.1800@.TK2MSFTNGP15.phx.gbl...
> > > Hi
> > >
> > > i must configure a RAID on external storage disk array for my SQL 2000
> > > cluster ad have think this configuration :
> > >
> > > 1- DataFile on separate RAID5 LUN
> > > 2- LogFile on another separate RAID5 LUN
> > > 3- Quorum on another separate RAID1 LUN
> > >
> > > This configugation is good for performace (datafile e logfile
separated)
> > and
> > > security (Quorum on Mirror) !'!
> > >
> > > Thanks in advance
> > >
> > >
> >
> >
>|||Even better:
datafile RAID10
logfile RAID1
quorum RAID1
Regards
Mike
"io.com" wrote:
> Ok therefore :
> datafile RAID5
> logfile RAID1
> quorum RAID1
> it's ok ?
> thanks
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uFE6MNaoEHA.324@.TK2MSFTNGP11.phx.gbl...
> > Make the Log on a RAID 1 or a 10 and not a 5. Raid 5 has too many writes
> > for peak performance of logs.Put the extra disks into the data raid 5 or
> > make it a raid 10 for better performance.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > <io.com> wrote in message news:OfbXh0YoEHA.1800@.TK2MSFTNGP15.phx.gbl...
> > > Hi
> > >
> > > i must configure a RAID on external storage disk array for my SQL 2000
> > > cluster ad have think this configuration :
> > >
> > > 1- DataFile on separate RAID5 LUN
> > > 2- LogFile on another separate RAID5 LUN
> > > 3- Quorum on another separate RAID1 LUN
> > >
> > > This configugation is good for performace (datafile e logfile separated)
> > and
> > > security (Quorum on Mirror) !'!
> > >
> > > Thanks in advance
> > >
> > >
> >
> >
>
>

Best backup plan for 24*7 running production database

Hi:
ENVIRONMENT:WIN2000/SQL2000
We need best backup plan for one our 24*7 available
production database growing by 1.2 gb per day..
Any one can tell me the best plan or can recommend any
best site for this?
Thanks in advance
PuruPuru
For large databases, which yours soon will be, most people
would take advantage of differential backups to reduce
impact on the system.
Something like a full backup once a week, and
differentials the other days and regular transaction log
backups as required.
Early in the life of your database you may find towards
the end of the week your differentials are nearly a large
and take as much time as a full backup, due to volume of
changes, as the database gets bigger that will change.
Try to put your backups on seperate disks to your database
and transaction logs to reduce the impact of them running.
Try to schedule them for the quietest time of the day,
even with a 24 by 7 installation you will still have some
times that are quieter than others. (usually when peak
business hours are in the paciffic ocean).
If your database gets into the terra-bytes you may need to
come up with a different strategy.
Hope this helps
Regards
John