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 method of doing Connection Strings

I am using SQL 2000 sp3a on Windows 2000 sp3. I have developed an Intranet application using asp.net/vb.net. Currently my connection string is:

data source=intraweb1;initial catalog=ASGWEB;password=blahblah;persist security info=True;user id=justauser;packet size=4096

So all my users are coming in with one SQL database id. Is this the best method for a combination of security and performance?

I do not allow anonymous to the website so I was thinking of setting up an application role and putting the domain users account in it. But from some other threads I was reading this does not work well with connection pooling.> Is this the best method for a combination of security and performance?

yeah, that's fine. I hardly ever do it otherwise - it's not fine-grained security-wise, but do you need it to be?

as for the connection pooling thing, yup - connection polling makes a pollfor the user id, so with multiple users you'd probably lose the beneficial effects, besides needing more CALs|||::besides needing more CALs

Using onedb server is does NOT save you CAL's. Read the licensing condition. You still need one CAL for every user. They say user - NOT user id. This is actually extremely clear, especially in the descriptions and comments.|||I had a discussion about this recently, and the concensus seemed to be one Device Access license for IIS to grab data if you're using one user ID. licencing is a nightmare though, and don't claim to be an expert on it by any means. I usually just ask MS whet the deal is and get multiple answers (!)

Bench Mark on Performance

Hi
I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
I understand that the profiler looks at the logical reads and physical
writes.
What I'm trying to understand is what people use as a guide as to
whether something should be dedicated or if it could be shared on an
active/passive clustered environment.
If I'm recieving a million reads per hour, they are logical, hence in
the buffer cache, but does that mean the server is performing well? I
know that it's not always down to just read and writes but that is the
essence of a db system... so that's where I start.
Your thoughts would be most appreciated.
SimonHi
I'd also look at Performance Monitor to examine PhysicalDisk Object: Avg.
Disk Queue Length
http://www.sql-server-performance.com/performance_monitor_counters_io.asp
Per a query you can use SET STATISTICS IO command
When looking at individual queries ,the Logical Reads is more interesting
because the ibfo is consistent
Pay close attention as well to chache-hit ratio. (>=98%)
<simonaarons@.yahoo.co.uk> wrote in message
news:1129024856.929599.263150@.o13g2000cwo.googlegroups.com...
> Hi
> I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
> I understand that the profiler looks at the logical reads and physical
> writes.
> What I'm trying to understand is what people use as a guide as to
> whether something should be dedicated or if it could be shared on an
> active/passive clustered environment.
> If I'm recieving a million reads per hour, they are logical, hence in
> the buffer cache, but does that mean the server is performing well? I
> know that it's not always down to just read and writes but that is the
> essence of a db system... so that's where I start.
> Your thoughts would be most appreciated.
> Simon
>|||Simon,
This is where a benchmark for your system would help. This would provide
you with how the system operates during normal operations. The performance
values derived from the benchmark can be compared against over time to
establish changes in the environment and to proactively predict future HW
upgrades/additions.
HTH
Jerry
<simonaarons@.yahoo.co.uk> wrote in message
news:1129024856.929599.263150@.o13g2000cwo.googlegroups.com...
> Hi
> I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
> I understand that the profiler looks at the logical reads and physical
> writes.
> What I'm trying to understand is what people use as a guide as to
> whether something should be dedicated or if it could be shared on an
> active/passive clustered environment.
> If I'm recieving a million reads per hour, they are logical, hence in
> the buffer cache, but does that mean the server is performing well? I
> know that it's not always down to just read and writes but that is the
> essence of a db system... so that's where I start.
> Your thoughts would be most appreciated.
> Simon
>

Bench Mark on Performance

Hi
I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
I understand that the profiler looks at the logical reads and physical
writes.
What I'm trying to understand is what people use as a guide as to
whether something should be dedicated or if it could be shared on an
active/passive clustered environment.
If I'm recieving a million reads per hour, they are logical, hence in
the buffer cache, but does that mean the server is performing well? I
know that it's not always down to just read and writes but that is the
essence of a db system... so that's where I start.
Your thoughts would be most appreciated.
Simon
Hi
I'd also look at Performance Monitor to examine PhysicalDisk Object: Avg.
Disk Queue Length
http://www.sql-server-performance.co...ounters_io.asp
Per a query you can use SET STATISTICS IO command
When looking at individual queries ,the Logical Reads is more interesting
because the ibfo is consistent
Pay close attention as well to chache-hit ratio. (>=98%)
<simonaarons@.yahoo.co.uk> wrote in message
news:1129024856.929599.263150@.o13g2000cwo.googlegr oups.com...
> Hi
> I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
> I understand that the profiler looks at the logical reads and physical
> writes.
> What I'm trying to understand is what people use as a guide as to
> whether something should be dedicated or if it could be shared on an
> active/passive clustered environment.
> If I'm recieving a million reads per hour, they are logical, hence in
> the buffer cache, but does that mean the server is performing well? I
> know that it's not always down to just read and writes but that is the
> essence of a db system... so that's where I start.
> Your thoughts would be most appreciated.
> Simon
>
|||Simon,
This is where a benchmark for your system would help. This would provide
you with how the system operates during normal operations. The performance
values derived from the benchmark can be compared against over time to
establish changes in the environment and to proactively predict future HW
upgrades/additions.
HTH
Jerry
<simonaarons@.yahoo.co.uk> wrote in message
news:1129024856.929599.263150@.o13g2000cwo.googlegr oups.com...
> Hi
> I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
> I understand that the profiler looks at the logical reads and physical
> writes.
> What I'm trying to understand is what people use as a guide as to
> whether something should be dedicated or if it could be shared on an
> active/passive clustered environment.
> If I'm recieving a million reads per hour, they are logical, hence in
> the buffer cache, but does that mean the server is performing well? I
> know that it's not always down to just read and writes but that is the
> essence of a db system... so that's where I start.
> Your thoughts would be most appreciated.
> Simon
>

Bench Mark on Performance

Hi
I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
I understand that the profiler looks at the logical reads and physical
writes.
What I'm trying to understand is what people use as a guide as to
whether something should be dedicated or if it could be shared on an
active/passive clustered environment.
If I'm recieving a million reads per hour, they are logical, hence in
the buffer cache, but does that mean the server is performing well? I
know that it's not always down to just read and writes but that is the
essence of a db system... so that's where I start.
Your thoughts would be most appreciated.
SimonHi
I'd also look at Performance Monitor to examine PhysicalDisk Object: Avg.
Disk Queue Length
http://www.sql-server-performance.c...counters_io.asp
Per a query you can use SET STATISTICS IO command
When looking at individual queries ,the Logical Reads is more interesting
because the ibfo is consistent
Pay close attention as well to chache-hit ratio. (>=98%)
<simonaarons@.yahoo.co.uk> wrote in message
news:1129024856.929599.263150@.o13g2000cwo.googlegroups.com...
> Hi
> I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
> I understand that the profiler looks at the logical reads and physical
> writes.
> What I'm trying to understand is what people use as a guide as to
> whether something should be dedicated or if it could be shared on an
> active/passive clustered environment.
> If I'm recieving a million reads per hour, they are logical, hence in
> the buffer cache, but does that mean the server is performing well? I
> know that it's not always down to just read and writes but that is the
> essence of a db system... so that's where I start.
> Your thoughts would be most appreciated.
> Simon
>|||Simon,
This is where a benchmark for your system would help. This would provide
you with how the system operates during normal operations. The performance
values derived from the benchmark can be compared against over time to
establish changes in the environment and to proactively predict future HW
upgrades/additions.
HTH
Jerry
<simonaarons@.yahoo.co.uk> wrote in message
news:1129024856.929599.263150@.o13g2000cwo.googlegroups.com...
> Hi
> I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
> I understand that the profiler looks at the logical reads and physical
> writes.
> What I'm trying to understand is what people use as a guide as to
> whether something should be dedicated or if it could be shared on an
> active/passive clustered environment.
> If I'm recieving a million reads per hour, they are logical, hence in
> the buffer cache, but does that mean the server is performing well? I
> know that it's not always down to just read and writes but that is the
> essence of a db system... so that's where I start.
> Your thoughts would be most appreciated.
> Simon
>