I just read the follwing statement in the help file.
Important:
Microsoft support policies do not apply to the database mirroring feature in
SQL Server 2005. Database mirroring is currently disabled by default, but may
be enabled for evaluation purposes only by using trace flag 1400 as a startup
parameter. (For more information about trace flags, see Trace Flags
(Transact-SQL).) Database mirroring should not be used in production
environments, and Microsoft support services will not support databases or
applications that use database mirroring. Database mirroring documentation is
included in SQL Server 2005 for evaluation purposes only, and the
Documentation Policy for SQL Server 2005 Support and Upgrade does not apply
to the database mirroring documentation.
I know that mirroring wasn't available in the original release, but was
available after installing sp1, which I have installed. Is it supported and
safe to use?
Another question I have is, If a snapshot is created off the mirror, which
is on a seperate server from production, and mirroring with high protection
is being used, is it possible for a long running report to cause performance
issues on the prod server? Or could the report prevent the mirror from
getting updated?
Thanks
--
MG>I just read the follwing statement in the help file.
When you upgraded your server to SP1, did you also install the updated Books
Online documentation? The help files are updated frequently. The most
recent official release is July 2006:
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
But if you want the most recent copy (including some information on SP2) you
can get it here:
http://www.microsoft.com/downloads/details.aspx?familyid=19db0b42-a5b2-456f-9c5c-f295cdd58d7a
A|||Thanks
I only installed sp1, but not the updated documentation.
--
MG
"Aaron Bertrand [SQL Server MVP]" wrote:
> >I just read the follwing statement in the help file.
> When you upgraded your server to SP1, did you also install the updated Books
> Online documentation? The help files are updated frequently. The most
> recent official release is July 2006:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> But if you want the most recent copy (including some information on SP2) you
> can get it here:
> http://www.microsoft.com/downloads/details.aspx?familyid=19db0b42-a5b2-456f-9c5c-f295cdd58d7a
> A
>
>
Showing posts with label mirroring. Show all posts
Showing posts with label mirroring. Show all posts
Friday, February 24, 2012
Is DB Mirroring supported in SQL 2005?
I just read the follwing statement in the help file.
Important:
Microsoft support policies do not apply to the database mirroring feature in
SQL Server 2005. Database mirroring is currently disabled by default, but may
be enabled for evaluation purposes only by using trace flag 1400 as a startup
parameter. (For more information about trace flags, see Trace Flags
(Transact-SQL).) Database mirroring should not be used in production
environments, and Microsoft support services will not support databases or
applications that use database mirroring. Database mirroring documentation is
included in SQL Server 2005 for evaluation purposes only, and the
Documentation Policy for SQL Server 2005 Support and Upgrade does not apply
to the database mirroring documentation.
I know that mirroring wasn't available in the original release, but was
available after installing sp1, which I have installed. Is it supported and
safe to use?
Another question I have is, If a snapshot is created off the mirror, which
is on a seperate server from production, and mirroring with high protection
is being used, is it possible for a long running report to cause performance
issues on the prod server? Or could the report prevent the mirror from
getting updated?
Thanks
MG
>I just read the follwing statement in the help file.
When you upgraded your server to SP1, did you also install the updated Books
Online documentation? The help files are updated frequently. The most
recent official release is July 2006:
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
But if you want the most recent copy (including some information on SP2) you
can get it here:
http://www.microsoft.com/downloads/details.aspx?familyid=19db0b42-a5b2-456f-9c5c-f295cdd58d7a
A
|||Thanks
I only installed sp1, but not the updated documentation.
MG
"Aaron Bertrand [SQL Server MVP]" wrote:
> When you upgraded your server to SP1, did you also install the updated Books
> Online documentation? The help files are updated frequently. The most
> recent official release is July 2006:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> But if you want the most recent copy (including some information on SP2) you
> can get it here:
> http://www.microsoft.com/downloads/details.aspx?familyid=19db0b42-a5b2-456f-9c5c-f295cdd58d7a
> A
>
>
Important:
Microsoft support policies do not apply to the database mirroring feature in
SQL Server 2005. Database mirroring is currently disabled by default, but may
be enabled for evaluation purposes only by using trace flag 1400 as a startup
parameter. (For more information about trace flags, see Trace Flags
(Transact-SQL).) Database mirroring should not be used in production
environments, and Microsoft support services will not support databases or
applications that use database mirroring. Database mirroring documentation is
included in SQL Server 2005 for evaluation purposes only, and the
Documentation Policy for SQL Server 2005 Support and Upgrade does not apply
to the database mirroring documentation.
I know that mirroring wasn't available in the original release, but was
available after installing sp1, which I have installed. Is it supported and
safe to use?
Another question I have is, If a snapshot is created off the mirror, which
is on a seperate server from production, and mirroring with high protection
is being used, is it possible for a long running report to cause performance
issues on the prod server? Or could the report prevent the mirror from
getting updated?
Thanks
MG
>I just read the follwing statement in the help file.
When you upgraded your server to SP1, did you also install the updated Books
Online documentation? The help files are updated frequently. The most
recent official release is July 2006:
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
But if you want the most recent copy (including some information on SP2) you
can get it here:
http://www.microsoft.com/downloads/details.aspx?familyid=19db0b42-a5b2-456f-9c5c-f295cdd58d7a
A
|||Thanks
I only installed sp1, but not the updated documentation.
MG
"Aaron Bertrand [SQL Server MVP]" wrote:
> When you upgraded your server to SP1, did you also install the updated Books
> Online documentation? The help files are updated frequently. The most
> recent official release is July 2006:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> But if you want the most recent copy (including some information on SP2) you
> can get it here:
> http://www.microsoft.com/downloads/details.aspx?familyid=19db0b42-a5b2-456f-9c5c-f295cdd58d7a
> A
>
>
Is DB Mirroring supported in SQL 2005?
I just read the follwing statement in the help file.
Important:
Microsoft support policies do not apply to the database mirroring feature in
SQL Server 2005. Database mirroring is currently disabled by default, but ma
y
be enabled for evaluation purposes only by using trace flag 1400 as a startu
p
parameter. (For more information about trace flags, see Trace Flags
(Transact-SQL).) Database mirroring should not be used in production
environments, and Microsoft support services will not support databases or
applications that use database mirroring. Database mirroring documentation i
s
included in SQL Server 2005 for evaluation purposes only, and the
Documentation Policy for SQL Server 2005 Support and Upgrade does not apply
to the database mirroring documentation.
I know that mirroring wasn't available in the original release, but was
available after installing sp1, which I have installed. Is it supported and
safe to use?
Another question I have is, If a snapshot is created off the mirror, which
is on a seperate server from production, and mirroring with high protection
is being used, is it possible for a long running report to cause performance
issues on the prod server? Or could the report prevent the mirror from
getting updated?
Thanks
--
MG>I just read the follwing statement in the help file.
When you upgraded your server to SP1, did you also install the updated Books
Online documentation? The help files are updated frequently. The most
recent official release is July 2006:
http://www.microsoft.com/technet/pr...oads/books.mspx
But if you want the most recent copy (including some information on SP2) you
can get it here:
http://www.microsoft.com/downloads/.../>
295cdd58d7a
A|||Thanks
I only installed sp1, but not the updated documentation.
--
MG
"Aaron Bertrand [SQL Server MVP]" wrote:
> When you upgraded your server to SP1, did you also install the updated Boo
ks
> Online documentation? The help files are updated frequently. The most
> recent official release is July 2006:
> http://www.microsoft.com/technet/pr...oads/books.mspx
> But if you want the most recent copy (including some information on SP2) y
ou
> can get it here:
> http://www.microsoft.com/downloads/...
-f295cdd58d7a
> A
>
>
Important:
Microsoft support policies do not apply to the database mirroring feature in
SQL Server 2005. Database mirroring is currently disabled by default, but ma
y
be enabled for evaluation purposes only by using trace flag 1400 as a startu
p
parameter. (For more information about trace flags, see Trace Flags
(Transact-SQL).) Database mirroring should not be used in production
environments, and Microsoft support services will not support databases or
applications that use database mirroring. Database mirroring documentation i
s
included in SQL Server 2005 for evaluation purposes only, and the
Documentation Policy for SQL Server 2005 Support and Upgrade does not apply
to the database mirroring documentation.
I know that mirroring wasn't available in the original release, but was
available after installing sp1, which I have installed. Is it supported and
safe to use?
Another question I have is, If a snapshot is created off the mirror, which
is on a seperate server from production, and mirroring with high protection
is being used, is it possible for a long running report to cause performance
issues on the prod server? Or could the report prevent the mirror from
getting updated?
Thanks
--
MG>I just read the follwing statement in the help file.
When you upgraded your server to SP1, did you also install the updated Books
Online documentation? The help files are updated frequently. The most
recent official release is July 2006:
http://www.microsoft.com/technet/pr...oads/books.mspx
But if you want the most recent copy (including some information on SP2) you
can get it here:
http://www.microsoft.com/downloads/.../>
295cdd58d7a
A|||Thanks
I only installed sp1, but not the updated documentation.
--
MG
"Aaron Bertrand [SQL Server MVP]" wrote:
> When you upgraded your server to SP1, did you also install the updated Boo
ks
> Online documentation? The help files are updated frequently. The most
> recent official release is July 2006:
> http://www.microsoft.com/technet/pr...oads/books.mspx
> But if you want the most recent copy (including some information on SP2) y
ou
> can get it here:
> http://www.microsoft.com/downloads/...
-f295cdd58d7a
> A
>
>
Is database mirroring the solution for this scenario?
We have an OLTP database handling our business production operations.
Our reporting team wants to extract from the OLTP db every 15 min, but
that will hammer the OLTP db I/O-wise, breaking the system. The
reporting team wants a copy of the OLTP system to do their extractions.
The copy should be kept up-to-date with the OLTP system on a near
continuous basis and be fully readable for extraction (At no time should
the copy ever become unavailable during sync activities). We do not
want to use SQL replication, and are looking at various alternatives
involving SAN snapshot technologies. Due to our requirements, several
of the SAN vendors have recommended using the built-in database
mirroring feature in 2005. I was under the impression that the mirror
was not available for use while it was being kept in sync and once the
mirror was made available, it would have to be reinitialized to resume
syncing. Am I correct, or is there a way to have the mirrored copy
available but still syncing?
You are right that the mirror database itself isn't available for reporting.
However, a snapshot from the mirror can provide a read-only view of the
database at a particular point in time. My guess is that this is what the
SAN vendors are suggesting.
Hope this helps.
Dan Guzman
SQL Server MVP
"DotComDBA" <noreply@.nospam.com> wrote in message
news:uFMrCrtKHHA.536@.TK2MSFTNGP02.phx.gbl...
> We have an OLTP database handling our business production operations. Our
> reporting team wants to extract from the OLTP db every 15 min, but that
> will hammer the OLTP db I/O-wise, breaking the system. The reporting team
> wants a copy of the OLTP system to do their extractions. The copy should
> be kept up-to-date with the OLTP system on a near continuous basis and be
> fully readable for extraction (At no time should the copy ever become
> unavailable during sync activities). We do not want to use SQL
> replication, and are looking at various alternatives involving SAN
> snapshot technologies. Due to our requirements, several of the SAN
> vendors have recommended using the built-in database mirroring feature in
> 2005. I was under the impression that the mirror was not available for
> use while it was being kept in sync and once the mirror was made
> available, it would have to be reinitialized to resume syncing. Am I
> correct, or is there a way to have the mirrored copy available but still
> syncing?
|||I was the DBA on an OLTP system for 10 years (yes a dotcom) and I must
question this "poll every 15 minutes that hammers the system" statement.
There should be NOTHING you should be pulling every 15 minutes that could
take more than a few seconds (if that) that is not going to be a waste.
What are they asking for? All sales? All hits? Promo pages? Are you dumping
everything every time? Put an index on the dattime field of the parent table
and dump the activity since the last dump.
I'm guessing they stuck you with RAID 5, or 6 too - write penalty and all.
RAID 0 is my prefered, 1 01 & 10 are OK. And keep the database files away
from everything else - like the temp directory, spooler and transaction logs.
Make a list of their needs, not what they say they want, but find out what
they are using and actually need. Then, if necessarg, build a seperate
database (call it a markting system) and dump data to it incremently.
Seperate the databases by transfering data via files, or perhaps dedicate a
crssover tcp/ip cable.
Whatever you do, keep them off the production server. They have a budget,
have them pay for what they need.
"DotComDBA" wrote:
> We have an OLTP database handling our business production operations.
> Our reporting team wants to extract from the OLTP db every 15 min, but
> that will hammer the OLTP db I/O-wise, breaking the system. The
> reporting team wants a copy of the OLTP system to do their extractions.
> The copy should be kept up-to-date with the OLTP system on a near
> continuous basis and be fully readable for extraction (At no time should
> the copy ever become unavailable during sync activities). We do not
> want to use SQL replication, and are looking at various alternatives
> involving SAN snapshot technologies. Due to our requirements, several
> of the SAN vendors have recommended using the built-in database
> mirroring feature in 2005. I was under the impression that the mirror
> was not available for use while it was being kept in sync and once the
> mirror was made available, it would have to be reinitialized to resume
> syncing. Am I correct, or is there a way to have the mirrored copy
> available but still syncing?
>
|||We have a similar need & setup (i.e. OLTP + operational reporting / MIS
needs), and do get significant performance gains for both OLTP and
reporting by mirroring - have found that running reports on the OLTP
server can esp hammer counters like the buffer hit ratios etc
Mirroring (e.g. Asynch in SQL 2005) works fine without loading the OLTP
server
If you do "use" your read-only slave, you might need to consider
licensing issues / costs.
You might also look at using regular log shipping
e.g. we roll our OLTP Enterprise Ed SQL logs forward onto a Std Ed
reporting server at 30 minute intervals, and at the same time copy the
logs off site as a DR safety precaution.
We do have a few "real time" reports which we do allow on the live OLTP
server (but you need to set stringent criteria on such reports as per
other poster). The rest (~500 reports) are run off the slave.
The one 'draw back' of log shipping is that you will need to boot all
connections off your slave reporting database slave every time you roll
forward.
The other alternative considered was replication - on the upside it
allowed creation of different indices (mirroring / log shipping
duplicates indices, meaning you have to create your MIS indices on the
OLTP server as well ... ). On the downside, there seemed no real
'guarantees' on the data synchronisation (i.e. for Disaster recovery we
sleep better if using Mirroring or Log Shipping to the slave).
We didn't meet with much success on the SAN Snapshots (unless you e.g.
use a permanent 'mirror') - depending on the size of the disks you need
to snapshot, the SAN still has a finite bandwidth - (our 5 yo SAN can
data transfer was at only around 500GB per hour), and doing the
snapshot does chew up the SAN resources.
Regards
Stuart
Our reporting team wants to extract from the OLTP db every 15 min, but
that will hammer the OLTP db I/O-wise, breaking the system. The
reporting team wants a copy of the OLTP system to do their extractions.
The copy should be kept up-to-date with the OLTP system on a near
continuous basis and be fully readable for extraction (At no time should
the copy ever become unavailable during sync activities). We do not
want to use SQL replication, and are looking at various alternatives
involving SAN snapshot technologies. Due to our requirements, several
of the SAN vendors have recommended using the built-in database
mirroring feature in 2005. I was under the impression that the mirror
was not available for use while it was being kept in sync and once the
mirror was made available, it would have to be reinitialized to resume
syncing. Am I correct, or is there a way to have the mirrored copy
available but still syncing?
You are right that the mirror database itself isn't available for reporting.
However, a snapshot from the mirror can provide a read-only view of the
database at a particular point in time. My guess is that this is what the
SAN vendors are suggesting.
Hope this helps.
Dan Guzman
SQL Server MVP
"DotComDBA" <noreply@.nospam.com> wrote in message
news:uFMrCrtKHHA.536@.TK2MSFTNGP02.phx.gbl...
> We have an OLTP database handling our business production operations. Our
> reporting team wants to extract from the OLTP db every 15 min, but that
> will hammer the OLTP db I/O-wise, breaking the system. The reporting team
> wants a copy of the OLTP system to do their extractions. The copy should
> be kept up-to-date with the OLTP system on a near continuous basis and be
> fully readable for extraction (At no time should the copy ever become
> unavailable during sync activities). We do not want to use SQL
> replication, and are looking at various alternatives involving SAN
> snapshot technologies. Due to our requirements, several of the SAN
> vendors have recommended using the built-in database mirroring feature in
> 2005. I was under the impression that the mirror was not available for
> use while it was being kept in sync and once the mirror was made
> available, it would have to be reinitialized to resume syncing. Am I
> correct, or is there a way to have the mirrored copy available but still
> syncing?
|||I was the DBA on an OLTP system for 10 years (yes a dotcom) and I must
question this "poll every 15 minutes that hammers the system" statement.
There should be NOTHING you should be pulling every 15 minutes that could
take more than a few seconds (if that) that is not going to be a waste.
What are they asking for? All sales? All hits? Promo pages? Are you dumping
everything every time? Put an index on the dattime field of the parent table
and dump the activity since the last dump.
I'm guessing they stuck you with RAID 5, or 6 too - write penalty and all.
RAID 0 is my prefered, 1 01 & 10 are OK. And keep the database files away
from everything else - like the temp directory, spooler and transaction logs.
Make a list of their needs, not what they say they want, but find out what
they are using and actually need. Then, if necessarg, build a seperate
database (call it a markting system) and dump data to it incremently.
Seperate the databases by transfering data via files, or perhaps dedicate a
crssover tcp/ip cable.
Whatever you do, keep them off the production server. They have a budget,
have them pay for what they need.
"DotComDBA" wrote:
> We have an OLTP database handling our business production operations.
> Our reporting team wants to extract from the OLTP db every 15 min, but
> that will hammer the OLTP db I/O-wise, breaking the system. The
> reporting team wants a copy of the OLTP system to do their extractions.
> The copy should be kept up-to-date with the OLTP system on a near
> continuous basis and be fully readable for extraction (At no time should
> the copy ever become unavailable during sync activities). We do not
> want to use SQL replication, and are looking at various alternatives
> involving SAN snapshot technologies. Due to our requirements, several
> of the SAN vendors have recommended using the built-in database
> mirroring feature in 2005. I was under the impression that the mirror
> was not available for use while it was being kept in sync and once the
> mirror was made available, it would have to be reinitialized to resume
> syncing. Am I correct, or is there a way to have the mirrored copy
> available but still syncing?
>
|||We have a similar need & setup (i.e. OLTP + operational reporting / MIS
needs), and do get significant performance gains for both OLTP and
reporting by mirroring - have found that running reports on the OLTP
server can esp hammer counters like the buffer hit ratios etc
Mirroring (e.g. Asynch in SQL 2005) works fine without loading the OLTP
server
If you do "use" your read-only slave, you might need to consider
licensing issues / costs.
You might also look at using regular log shipping
e.g. we roll our OLTP Enterprise Ed SQL logs forward onto a Std Ed
reporting server at 30 minute intervals, and at the same time copy the
logs off site as a DR safety precaution.
We do have a few "real time" reports which we do allow on the live OLTP
server (but you need to set stringent criteria on such reports as per
other poster). The rest (~500 reports) are run off the slave.
The one 'draw back' of log shipping is that you will need to boot all
connections off your slave reporting database slave every time you roll
forward.
The other alternative considered was replication - on the upside it
allowed creation of different indices (mirroring / log shipping
duplicates indices, meaning you have to create your MIS indices on the
OLTP server as well ... ). On the downside, there seemed no real
'guarantees' on the data synchronisation (i.e. for Disaster recovery we
sleep better if using Mirroring or Log Shipping to the slave).
We didn't meet with much success on the SAN Snapshots (unless you e.g.
use a permanent 'mirror') - depending on the size of the disks you need
to snapshot, the SAN still has a finite bandwidth - (our 5 yo SAN can
data transfer was at only around 500GB per hour), and doing the
snapshot does chew up the SAN resources.
Regards
Stuart
Monday, February 20, 2012
Is database mirroring the solution for this scenario?
We have an OLTP database handling our business production operations.
Our reporting team wants to extract from the OLTP db every 15 min, but
that will hammer the OLTP db I/O-wise, breaking the system. The
reporting team wants a copy of the OLTP system to do their extractions.
The copy should be kept up-to-date with the OLTP system on a near
continuous basis and be fully readable for extraction (At no time should
the copy ever become unavailable during sync activities). We do not
want to use SQL replication, and are looking at various alternatives
involving SAN snapshot technologies. Due to our requirements, several
of the SAN vendors have recommended using the built-in database
mirroring feature in 2005. I was under the impression that the mirror
was not available for use while it was being kept in sync and once the
mirror was made available, it would have to be reinitialized to resume
syncing. Am I correct, or is there a way to have the mirrored copy
available but still syncing?You are right that the mirror database itself isn't available for reporting.
However, a snapshot from the mirror can provide a read-only view of the
database at a particular point in time. My guess is that this is what the
SAN vendors are suggesting.
Hope this helps.
Dan Guzman
SQL Server MVP
"DotComDBA" <noreply@.nospam.com> wrote in message
news:uFMrCrtKHHA.536@.TK2MSFTNGP02.phx.gbl...
> We have an OLTP database handling our business production operations. Our
> reporting team wants to extract from the OLTP db every 15 min, but that
> will hammer the OLTP db I/O-wise, breaking the system. The reporting team
> wants a copy of the OLTP system to do their extractions. The copy should
> be kept up-to-date with the OLTP system on a near continuous basis and be
> fully readable for extraction (At no time should the copy ever become
> unavailable during sync activities). We do not want to use SQL
> replication, and are looking at various alternatives involving SAN
> snapshot technologies. Due to our requirements, several of the SAN
> vendors have recommended using the built-in database mirroring feature in
> 2005. I was under the impression that the mirror was not available for
> use while it was being kept in sync and once the mirror was made
> available, it would have to be reinitialized to resume syncing. Am I
> correct, or is there a way to have the mirrored copy available but still
> syncing?|||I was the DBA on an OLTP system for 10 years (yes a dotcom) and I must
question this "poll every 15 minutes that hammers the system" statement.
There should be NOTHING you should be pulling every 15 minutes that could
take more than a few seconds (if that) that is not going to be a waste.
What are they asking for? All sales? All hits? Promo pages? Are you dumping
everything every time? Put an index on the dattime field of the parent table
and dump the activity since the last dump.
I'm guessing they stuck you with RAID 5, or 6 too - write penalty and all.
RAID 0 is my prefered, 1 01 & 10 are OK. And keep the database files away
from everything else - like the temp directory, spooler and transaction logs
.
Make a list of their needs, not what they say they want, but find out what
they are using and actually need. Then, if necessarg, build a seperate
database (call it a markting system) and dump data to it incremently.
Seperate the databases by transfering data via files, or perhaps dedicate a
crssover tcp/ip cable.
Whatever you do, keep them off the production server. They have a budget,
have them pay for what they need.
"DotComDBA" wrote:
> We have an OLTP database handling our business production operations.
> Our reporting team wants to extract from the OLTP db every 15 min, but
> that will hammer the OLTP db I/O-wise, breaking the system. The
> reporting team wants a copy of the OLTP system to do their extractions.
> The copy should be kept up-to-date with the OLTP system on a near
> continuous basis and be fully readable for extraction (At no time should
> the copy ever become unavailable during sync activities). We do not
> want to use SQL replication, and are looking at various alternatives
> involving SAN snapshot technologies. Due to our requirements, several
> of the SAN vendors have recommended using the built-in database
> mirroring feature in 2005. I was under the impression that the mirror
> was not available for use while it was being kept in sync and once the
> mirror was made available, it would have to be reinitialized to resume
> syncing. Am I correct, or is there a way to have the mirrored copy
> available but still syncing?
>|||We have a similar need & setup (i.e. OLTP + operational reporting / MIS
needs), and do get significant performance gains for both OLTP and
reporting by mirroring - have found that running reports on the OLTP
server can esp hammer counters like the buffer hit ratios etc
Mirroring (e.g. Asynch in SQL 2005) works fine without loading the OLTP
server
If you do "use" your read-only slave, you might need to consider
licensing issues / costs.
You might also look at using regular log shipping
e.g. we roll our OLTP Enterprise Ed SQL logs forward onto a Std Ed
reporting server at 30 minute intervals, and at the same time copy the
logs off site as a DR safety precaution.
We do have a few "real time" reports which we do allow on the live OLTP
server (but you need to set stringent criteria on such reports as per
other poster). The rest (~500 reports) are run off the slave.
The one 'draw back' of log shipping is that you will need to boot all
connections off your slave reporting database slave every time you roll
forward.
The other alternative considered was replication - on the upside it
allowed creation of different indices (mirroring / log shipping
duplicates indices, meaning you have to create your MIS indices on the
OLTP server as well ... ). On the downside, there seemed no real
'guarantees' on the data synchronisation (i.e. for Disaster recovery we
sleep better if using Mirroring or Log Shipping to the slave).
We didn't meet with much success on the SAN Snapshots (unless you e.g.
use a permanent 'mirror') - depending on the size of the disks you need
to snapshot, the SAN still has a finite bandwidth - (our 5 yo SAN can
data transfer was at only around 500GB per hour), and doing the
snapshot does chew up the SAN resources.
Regards
Stuart
Our reporting team wants to extract from the OLTP db every 15 min, but
that will hammer the OLTP db I/O-wise, breaking the system. The
reporting team wants a copy of the OLTP system to do their extractions.
The copy should be kept up-to-date with the OLTP system on a near
continuous basis and be fully readable for extraction (At no time should
the copy ever become unavailable during sync activities). We do not
want to use SQL replication, and are looking at various alternatives
involving SAN snapshot technologies. Due to our requirements, several
of the SAN vendors have recommended using the built-in database
mirroring feature in 2005. I was under the impression that the mirror
was not available for use while it was being kept in sync and once the
mirror was made available, it would have to be reinitialized to resume
syncing. Am I correct, or is there a way to have the mirrored copy
available but still syncing?You are right that the mirror database itself isn't available for reporting.
However, a snapshot from the mirror can provide a read-only view of the
database at a particular point in time. My guess is that this is what the
SAN vendors are suggesting.
Hope this helps.
Dan Guzman
SQL Server MVP
"DotComDBA" <noreply@.nospam.com> wrote in message
news:uFMrCrtKHHA.536@.TK2MSFTNGP02.phx.gbl...
> We have an OLTP database handling our business production operations. Our
> reporting team wants to extract from the OLTP db every 15 min, but that
> will hammer the OLTP db I/O-wise, breaking the system. The reporting team
> wants a copy of the OLTP system to do their extractions. The copy should
> be kept up-to-date with the OLTP system on a near continuous basis and be
> fully readable for extraction (At no time should the copy ever become
> unavailable during sync activities). We do not want to use SQL
> replication, and are looking at various alternatives involving SAN
> snapshot technologies. Due to our requirements, several of the SAN
> vendors have recommended using the built-in database mirroring feature in
> 2005. I was under the impression that the mirror was not available for
> use while it was being kept in sync and once the mirror was made
> available, it would have to be reinitialized to resume syncing. Am I
> correct, or is there a way to have the mirrored copy available but still
> syncing?|||I was the DBA on an OLTP system for 10 years (yes a dotcom) and I must
question this "poll every 15 minutes that hammers the system" statement.
There should be NOTHING you should be pulling every 15 minutes that could
take more than a few seconds (if that) that is not going to be a waste.
What are they asking for? All sales? All hits? Promo pages? Are you dumping
everything every time? Put an index on the dattime field of the parent table
and dump the activity since the last dump.
I'm guessing they stuck you with RAID 5, or 6 too - write penalty and all.
RAID 0 is my prefered, 1 01 & 10 are OK. And keep the database files away
from everything else - like the temp directory, spooler and transaction logs
.
Make a list of their needs, not what they say they want, but find out what
they are using and actually need. Then, if necessarg, build a seperate
database (call it a markting system) and dump data to it incremently.
Seperate the databases by transfering data via files, or perhaps dedicate a
crssover tcp/ip cable.
Whatever you do, keep them off the production server. They have a budget,
have them pay for what they need.
"DotComDBA" wrote:
> We have an OLTP database handling our business production operations.
> Our reporting team wants to extract from the OLTP db every 15 min, but
> that will hammer the OLTP db I/O-wise, breaking the system. The
> reporting team wants a copy of the OLTP system to do their extractions.
> The copy should be kept up-to-date with the OLTP system on a near
> continuous basis and be fully readable for extraction (At no time should
> the copy ever become unavailable during sync activities). We do not
> want to use SQL replication, and are looking at various alternatives
> involving SAN snapshot technologies. Due to our requirements, several
> of the SAN vendors have recommended using the built-in database
> mirroring feature in 2005. I was under the impression that the mirror
> was not available for use while it was being kept in sync and once the
> mirror was made available, it would have to be reinitialized to resume
> syncing. Am I correct, or is there a way to have the mirrored copy
> available but still syncing?
>|||We have a similar need & setup (i.e. OLTP + operational reporting / MIS
needs), and do get significant performance gains for both OLTP and
reporting by mirroring - have found that running reports on the OLTP
server can esp hammer counters like the buffer hit ratios etc
Mirroring (e.g. Asynch in SQL 2005) works fine without loading the OLTP
server
If you do "use" your read-only slave, you might need to consider
licensing issues / costs.
You might also look at using regular log shipping
e.g. we roll our OLTP Enterprise Ed SQL logs forward onto a Std Ed
reporting server at 30 minute intervals, and at the same time copy the
logs off site as a DR safety precaution.
We do have a few "real time" reports which we do allow on the live OLTP
server (but you need to set stringent criteria on such reports as per
other poster). The rest (~500 reports) are run off the slave.
The one 'draw back' of log shipping is that you will need to boot all
connections off your slave reporting database slave every time you roll
forward.
The other alternative considered was replication - on the upside it
allowed creation of different indices (mirroring / log shipping
duplicates indices, meaning you have to create your MIS indices on the
OLTP server as well ... ). On the downside, there seemed no real
'guarantees' on the data synchronisation (i.e. for Disaster recovery we
sleep better if using Mirroring or Log Shipping to the slave).
We didn't meet with much success on the SAN Snapshots (unless you e.g.
use a permanent 'mirror') - depending on the size of the disks you need
to snapshot, the SAN still has a finite bandwidth - (our 5 yo SAN can
data transfer was at only around 500GB per hour), and doing the
snapshot does chew up the SAN resources.
Regards
Stuart
Is database mirroring the solution for this scenario?
We have an OLTP database handling our business production operations.
Our reporting team wants to extract from the OLTP db every 15 min, but
that will hammer the OLTP db I/O-wise, breaking the system. The
reporting team wants a copy of the OLTP system to do their extractions.
The copy should be kept up-to-date with the OLTP system on a near
continuous basis and be fully readable for extraction (At no time should
the copy ever become unavailable during sync activities). We do not
want to use SQL replication, and are looking at various alternatives
involving SAN snapshot technologies. Due to our requirements, several
of the SAN vendors have recommended using the built-in database
mirroring feature in 2005. I was under the impression that the mirror
was not available for use while it was being kept in sync and once the
mirror was made available, it would have to be reinitialized to resume
syncing. Am I correct, or is there a way to have the mirrored copy
available but still syncing?You are right that the mirror database itself isn't available for reporting.
However, a snapshot from the mirror can provide a read-only view of the
database at a particular point in time. My guess is that this is what the
SAN vendors are suggesting.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DotComDBA" <noreply@.nospam.com> wrote in message
news:uFMrCrtKHHA.536@.TK2MSFTNGP02.phx.gbl...
> We have an OLTP database handling our business production operations. Our
> reporting team wants to extract from the OLTP db every 15 min, but that
> will hammer the OLTP db I/O-wise, breaking the system. The reporting team
> wants a copy of the OLTP system to do their extractions. The copy should
> be kept up-to-date with the OLTP system on a near continuous basis and be
> fully readable for extraction (At no time should the copy ever become
> unavailable during sync activities). We do not want to use SQL
> replication, and are looking at various alternatives involving SAN
> snapshot technologies. Due to our requirements, several of the SAN
> vendors have recommended using the built-in database mirroring feature in
> 2005. I was under the impression that the mirror was not available for
> use while it was being kept in sync and once the mirror was made
> available, it would have to be reinitialized to resume syncing. Am I
> correct, or is there a way to have the mirrored copy available but still
> syncing?|||I was the DBA on an OLTP system for 10 years (yes a dotcom) and I must
question this "poll every 15 minutes that hammers the system" statement.
There should be NOTHING you should be pulling every 15 minutes that could
take more than a few seconds (if that) that is not going to be a waste.
What are they asking for? All sales? All hits? Promo pages? Are you dumping
everything every time? Put an index on the dattime field of the parent table
and dump the activity since the last dump.
I'm guessing they stuck you with RAID 5, or 6 too - write penalty and all.
RAID 0 is my prefered, 1 01 & 10 are OK. And keep the database files away
from everything else - like the temp directory, spooler and transaction logs.
Make a list of their needs, not what they say they want, but find out what
they are using and actually need. Then, if necessarg, build a seperate
database (call it a markting system) and dump data to it incremently.
Seperate the databases by transfering data via files, or perhaps dedicate a
crssover tcp/ip cable.
Whatever you do, keep them off the production server. They have a budget,
have them pay for what they need.
"DotComDBA" wrote:
> We have an OLTP database handling our business production operations.
> Our reporting team wants to extract from the OLTP db every 15 min, but
> that will hammer the OLTP db I/O-wise, breaking the system. The
> reporting team wants a copy of the OLTP system to do their extractions.
> The copy should be kept up-to-date with the OLTP system on a near
> continuous basis and be fully readable for extraction (At no time should
> the copy ever become unavailable during sync activities). We do not
> want to use SQL replication, and are looking at various alternatives
> involving SAN snapshot technologies. Due to our requirements, several
> of the SAN vendors have recommended using the built-in database
> mirroring feature in 2005. I was under the impression that the mirror
> was not available for use while it was being kept in sync and once the
> mirror was made available, it would have to be reinitialized to resume
> syncing. Am I correct, or is there a way to have the mirrored copy
> available but still syncing?
>|||We have a similar need & setup (i.e. OLTP + operational reporting / MIS
needs), and do get significant performance gains for both OLTP and
reporting by mirroring - have found that running reports on the OLTP
server can esp hammer counters like the buffer hit ratios etc
Mirroring (e.g. Asynch in SQL 2005) works fine without loading the OLTP
server
If you do "use" your read-only slave, you might need to consider
licensing issues / costs.
You might also look at using regular log shipping
e.g. we roll our OLTP Enterprise Ed SQL logs forward onto a Std Ed
reporting server at 30 minute intervals, and at the same time copy the
logs off site as a DR safety precaution.
We do have a few "real time" reports which we do allow on the live OLTP
server (but you need to set stringent criteria on such reports as per
other poster). The rest (~500 reports) are run off the slave.
The one 'draw back' of log shipping is that you will need to boot all
connections off your slave reporting database slave every time you roll
forward.
The other alternative considered was replication - on the upside it
allowed creation of different indices (mirroring / log shipping
duplicates indices, meaning you have to create your MIS indices on the
OLTP server as well ... ). On the downside, there seemed no real
'guarantees' on the data synchronisation (i.e. for Disaster recovery we
sleep better if using Mirroring or Log Shipping to the slave).
We didn't meet with much success on the SAN Snapshots (unless you e.g.
use a permanent 'mirror') - depending on the size of the disks you need
to snapshot, the SAN still has a finite bandwidth - (our 5 yo SAN can
data transfer was at only around 500GB per hour), and doing the
snapshot does chew up the SAN resources.
Regards
Stuart
Our reporting team wants to extract from the OLTP db every 15 min, but
that will hammer the OLTP db I/O-wise, breaking the system. The
reporting team wants a copy of the OLTP system to do their extractions.
The copy should be kept up-to-date with the OLTP system on a near
continuous basis and be fully readable for extraction (At no time should
the copy ever become unavailable during sync activities). We do not
want to use SQL replication, and are looking at various alternatives
involving SAN snapshot technologies. Due to our requirements, several
of the SAN vendors have recommended using the built-in database
mirroring feature in 2005. I was under the impression that the mirror
was not available for use while it was being kept in sync and once the
mirror was made available, it would have to be reinitialized to resume
syncing. Am I correct, or is there a way to have the mirrored copy
available but still syncing?You are right that the mirror database itself isn't available for reporting.
However, a snapshot from the mirror can provide a read-only view of the
database at a particular point in time. My guess is that this is what the
SAN vendors are suggesting.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DotComDBA" <noreply@.nospam.com> wrote in message
news:uFMrCrtKHHA.536@.TK2MSFTNGP02.phx.gbl...
> We have an OLTP database handling our business production operations. Our
> reporting team wants to extract from the OLTP db every 15 min, but that
> will hammer the OLTP db I/O-wise, breaking the system. The reporting team
> wants a copy of the OLTP system to do their extractions. The copy should
> be kept up-to-date with the OLTP system on a near continuous basis and be
> fully readable for extraction (At no time should the copy ever become
> unavailable during sync activities). We do not want to use SQL
> replication, and are looking at various alternatives involving SAN
> snapshot technologies. Due to our requirements, several of the SAN
> vendors have recommended using the built-in database mirroring feature in
> 2005. I was under the impression that the mirror was not available for
> use while it was being kept in sync and once the mirror was made
> available, it would have to be reinitialized to resume syncing. Am I
> correct, or is there a way to have the mirrored copy available but still
> syncing?|||I was the DBA on an OLTP system for 10 years (yes a dotcom) and I must
question this "poll every 15 minutes that hammers the system" statement.
There should be NOTHING you should be pulling every 15 minutes that could
take more than a few seconds (if that) that is not going to be a waste.
What are they asking for? All sales? All hits? Promo pages? Are you dumping
everything every time? Put an index on the dattime field of the parent table
and dump the activity since the last dump.
I'm guessing they stuck you with RAID 5, or 6 too - write penalty and all.
RAID 0 is my prefered, 1 01 & 10 are OK. And keep the database files away
from everything else - like the temp directory, spooler and transaction logs.
Make a list of their needs, not what they say they want, but find out what
they are using and actually need. Then, if necessarg, build a seperate
database (call it a markting system) and dump data to it incremently.
Seperate the databases by transfering data via files, or perhaps dedicate a
crssover tcp/ip cable.
Whatever you do, keep them off the production server. They have a budget,
have them pay for what they need.
"DotComDBA" wrote:
> We have an OLTP database handling our business production operations.
> Our reporting team wants to extract from the OLTP db every 15 min, but
> that will hammer the OLTP db I/O-wise, breaking the system. The
> reporting team wants a copy of the OLTP system to do their extractions.
> The copy should be kept up-to-date with the OLTP system on a near
> continuous basis and be fully readable for extraction (At no time should
> the copy ever become unavailable during sync activities). We do not
> want to use SQL replication, and are looking at various alternatives
> involving SAN snapshot technologies. Due to our requirements, several
> of the SAN vendors have recommended using the built-in database
> mirroring feature in 2005. I was under the impression that the mirror
> was not available for use while it was being kept in sync and once the
> mirror was made available, it would have to be reinitialized to resume
> syncing. Am I correct, or is there a way to have the mirrored copy
> available but still syncing?
>|||We have a similar need & setup (i.e. OLTP + operational reporting / MIS
needs), and do get significant performance gains for both OLTP and
reporting by mirroring - have found that running reports on the OLTP
server can esp hammer counters like the buffer hit ratios etc
Mirroring (e.g. Asynch in SQL 2005) works fine without loading the OLTP
server
If you do "use" your read-only slave, you might need to consider
licensing issues / costs.
You might also look at using regular log shipping
e.g. we roll our OLTP Enterprise Ed SQL logs forward onto a Std Ed
reporting server at 30 minute intervals, and at the same time copy the
logs off site as a DR safety precaution.
We do have a few "real time" reports which we do allow on the live OLTP
server (but you need to set stringent criteria on such reports as per
other poster). The rest (~500 reports) are run off the slave.
The one 'draw back' of log shipping is that you will need to boot all
connections off your slave reporting database slave every time you roll
forward.
The other alternative considered was replication - on the upside it
allowed creation of different indices (mirroring / log shipping
duplicates indices, meaning you have to create your MIS indices on the
OLTP server as well ... ). On the downside, there seemed no real
'guarantees' on the data synchronisation (i.e. for Disaster recovery we
sleep better if using Mirroring or Log Shipping to the slave).
We didn't meet with much success on the SAN Snapshots (unless you e.g.
use a permanent 'mirror') - depending on the size of the disks you need
to snapshot, the SAN still has a finite bandwidth - (our 5 yo SAN can
data transfer was at only around 500GB per hour), and doing the
snapshot does chew up the SAN resources.
Regards
Stuart
Subscribe to:
Posts (Atom)