Impact of transaction log backups in synchronous DB Mirroring syst

Hello,
We have a site on SQL Server 2005 SP2 EE. They are using synchronous
database mirroring. Both the principal and mirror are on a fast LAN. When
they perform transaction log backups on the principal database, our
application that accesses the principal database freezes for 1 or 2 seconds.
The transaction log backups are being written to the same disk where the
database resides. They have tried reducing the frequency of the transaciton
log backups to every 10 minutes, and that reduced the length of the app
freeze, but did not eliminate it. Has anyone heard of a problem running
transaction log backups when the database is mirrored?
Thanks,
Heather
I would be more concerned as to the placement of the log backups. Why would
you write them to the same disk that hold the data files? This is definitely
a candidate for a performance hit not to mention a problem if the disk
itself crashes. You loose both the data and logs and are probably in line to
update the resume. If you saturate the I/O the db can appear to freeze.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"HeatherK" <HeatherK@.community.nospam> wrote in message
news:E6A9E23D-916B-4CEE-8C3F-8801CD3394EB@.microsoft.com...
> Hello,
> We have a site on SQL Server 2005 SP2 EE. They are using synchronous
> database mirroring. Both the principal and mirror are on a fast LAN. When
> they perform transaction log backups on the principal database, our
> application that accesses the principal database freezes for 1 or 2
> seconds.
> The transaction log backups are being written to the same disk where the
> database resides. They have tried reducing the frequency of the
> transaciton
> log backups to every 10 minutes, and that reduced the length of the app
> freeze, but did not eliminate it. Has anyone heard of a problem running
> transaction log backups when the database is mirrored?
> Thanks,
> Heather
|||Hi Andrew,
Thanks for your post.
Yes, we realize that the placement of the backup files is not optimal. They
are using RAID5 on the drive, so they should be protected from a disk crash.
We realize RAID 5 is not optimal for performance, but that is what they have.
We have not seen a problem with transaction log backups at our other sites
that are not using Database Mirroring. That is why we were wondering if
there were any known issues with taking transaction log backups when you are
using synchronous Database Mirroring.
We will gather more performance metrics to determine if this is strictly
related to I/O or if there is a connection with Database Mirroring.
Thanks,
Heather
"Andrew J. Kelly" wrote:

> I would be more concerned as to the placement of the log backups. Why would
> you write them to the same disk that hold the data files? This is definitely
> a candidate for a performance hit not to mention a problem if the disk
> itself crashes. You loose both the data and logs and are probably in line to
> update the resume. If you saturate the I/O the db can appear to freeze.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "HeatherK" <HeatherK@.community.nospam> wrote in message
> news:E6A9E23D-916B-4CEE-8C3F-8801CD3394EB@.microsoft.com...
>
|||Well mirroring does add overhead and potentially even a little more I/O on
the drive with the logs so I am sure it adds to the whole mess. Doing a log
backup also forces a checkpoint which is I/O intensive as well. What do the
perfmon counters and virtual file statsw say for that drive and the log
files? And by the way you should never count on the fact that it is Raid 5
to avert a crash. They would be better off copying the files to a network
share.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"HeatherK" <HeatherK@.community.nospam> wrote in message
news:C0FB6FB3-9661-4BAA-84D2-17AC2B7C1A6F@.microsoft.com...[vbcol=seagreen]
> Hi Andrew,
> Thanks for your post.
> Yes, we realize that the placement of the backup files is not optimal.
> They
> are using RAID5 on the drive, so they should be protected from a disk
> crash.
> We realize RAID 5 is not optimal for performance, but that is what they
> have.
> We have not seen a problem with transaction log backups at our other sites
> that are not using Database Mirroring. That is why we were wondering if
> there were any known issues with taking transaction log backups when you
> are
> using synchronous Database Mirroring.
> We will gather more performance metrics to determine if this is strictly
> related to I/O or if there is a connection with Database Mirroring.
> Thanks,
> Heather
> "Andrew J. Kelly" wrote:

Impact of transaction log backups in synchronous DB Mirroring syst

Hello,
We have a site on SQL Server 2005 SP2 EE. They are using synchronous
database mirroring. Both the principal and mirror are on a fast LAN. When
they perform transaction log backups on the principal database, our
application that accesses the principal database freezes for 1 or 2 seconds.
The transaction log backups are being written to the same disk where the
database resides. They have tried reducing the frequency of the transaciton
log backups to every 10 minutes, and that reduced the length of the app
freeze, but did not eliminate it. Has anyone heard of a problem running
transaction log backups when the database is mirrored?
Thanks,
HeatherI would be more concerned as to the placement of the log backups. Why would
you write them to the same disk that hold the data files? This is definitely
a candidate for a performance hit not to mention a problem if the disk
itself crashes. You loose both the data and logs and are probably in line to
update the resume:). If you saturate the I/O the db can appear to freeze.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"HeatherK" <HeatherK@.community.nospam> wrote in message
news:E6A9E23D-916B-4CEE-8C3F-8801CD3394EB@.microsoft.com...
> Hello,
> We have a site on SQL Server 2005 SP2 EE. They are using synchronous
> database mirroring. Both the principal and mirror are on a fast LAN. When
> they perform transaction log backups on the principal database, our
> application that accesses the principal database freezes for 1 or 2
> seconds.
> The transaction log backups are being written to the same disk where the
> database resides. They have tried reducing the frequency of the
> transaciton
> log backups to every 10 minutes, and that reduced the length of the app
> freeze, but did not eliminate it. Has anyone heard of a problem running
> transaction log backups when the database is mirrored?
> Thanks,
> Heather|||Hi Andrew,
Thanks for your post.
Yes, we realize that the placement of the backup files is not optimal. They
are using RAID5 on the drive, so they should be protected from a disk crash.
We realize RAID 5 is not optimal for performance, but that is what they have.
We have not seen a problem with transaction log backups at our other sites
that are not using Database Mirroring. That is why we were wondering if
there were any known issues with taking transaction log backups when you are
using synchronous Database Mirroring.
We will gather more performance metrics to determine if this is strictly
related to I/O or if there is a connection with Database Mirroring.
Thanks,
Heather
"Andrew J. Kelly" wrote:
> I would be more concerned as to the placement of the log backups. Why would
> you write them to the same disk that hold the data files? This is definitely
> a candidate for a performance hit not to mention a problem if the disk
> itself crashes. You loose both the data and logs and are probably in line to
> update the resume:). If you saturate the I/O the db can appear to freeze.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "HeatherK" <HeatherK@.community.nospam> wrote in message
> news:E6A9E23D-916B-4CEE-8C3F-8801CD3394EB@.microsoft.com...
> > Hello,
> >
> > We have a site on SQL Server 2005 SP2 EE. They are using synchronous
> > database mirroring. Both the principal and mirror are on a fast LAN. When
> > they perform transaction log backups on the principal database, our
> > application that accesses the principal database freezes for 1 or 2
> > seconds.
> >
> > The transaction log backups are being written to the same disk where the
> > database resides. They have tried reducing the frequency of the
> > transaciton
> > log backups to every 10 minutes, and that reduced the length of the app
> > freeze, but did not eliminate it. Has anyone heard of a problem running
> > transaction log backups when the database is mirrored?
> >
> > Thanks,
> > Heather
>|||Well mirroring does add overhead and potentially even a little more I/O on
the drive with the logs so I am sure it adds to the whole mess. Doing a log
backup also forces a checkpoint which is I/O intensive as well. What do the
perfmon counters and virtual file statsw say for that drive and the log
files? And by the way you should never count on the fact that it is Raid 5
to avert a crash. They would be better off copying the files to a network
share.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"HeatherK" <HeatherK@.community.nospam> wrote in message
news:C0FB6FB3-9661-4BAA-84D2-17AC2B7C1A6F@.microsoft.com...
> Hi Andrew,
> Thanks for your post.
> Yes, we realize that the placement of the backup files is not optimal.
> They
> are using RAID5 on the drive, so they should be protected from a disk
> crash.
> We realize RAID 5 is not optimal for performance, but that is what they
> have.
> We have not seen a problem with transaction log backups at our other sites
> that are not using Database Mirroring. That is why we were wondering if
> there were any known issues with taking transaction log backups when you
> are
> using synchronous Database Mirroring.
> We will gather more performance metrics to determine if this is strictly
> related to I/O or if there is a connection with Database Mirroring.
> Thanks,
> Heather
> "Andrew J. Kelly" wrote:
>> I would be more concerned as to the placement of the log backups. Why
>> would
>> you write them to the same disk that hold the data files? This is
>> definitely
>> a candidate for a performance hit not to mention a problem if the disk
>> itself crashes. You loose both the data and logs and are probably in line
>> to
>> update the resume:). If you saturate the I/O the db can appear to
>> freeze.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "HeatherK" <HeatherK@.community.nospam> wrote in message
>> news:E6A9E23D-916B-4CEE-8C3F-8801CD3394EB@.microsoft.com...
>> > Hello,
>> >
>> > We have a site on SQL Server 2005 SP2 EE. They are using synchronous
>> > database mirroring. Both the principal and mirror are on a fast LAN.
>> > When
>> > they perform transaction log backups on the principal database, our
>> > application that accesses the principal database freezes for 1 or 2
>> > seconds.
>> >
>> > The transaction log backups are being written to the same disk where
>> > the
>> > database resides. They have tried reducing the frequency of the
>> > transaciton
>> > log backups to every 10 minutes, and that reduced the length of the app
>> > freeze, but did not eliminate it. Has anyone heard of a problem
>> > running
>> > transaction log backups when the database is mirrored?
>> >
>> > Thanks,
>> > Heather
>>sql

Impact of transaction log backups in synchronous DB Mirroring syst

Hello,
We have a site on SQL Server 2005 SP2 EE. They are using synchronous
database mirroring. Both the principal and mirror are on a fast LAN. When
they perform transaction log backups on the principal database, our
application that accesses the principal database freezes for 1 or 2 seconds.
The transaction log backups are being written to the same disk where the
database resides. They have tried reducing the frequency of the transaciton
log backups to every 10 minutes, and that reduced the length of the app
freeze, but did not eliminate it. Has anyone heard of a problem running
transaction log backups when the database is mirrored?
Thanks,
HeatherI would be more concerned as to the placement of the log backups. Why would
you write them to the same disk that hold the data files? This is definitely
a candidate for a performance hit not to mention a problem if the disk
itself crashes. You loose both the data and logs and are probably in line to
update the resume. If you saturate the I/O the db can appear to freeze.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"HeatherK" <HeatherK@.community.nospam> wrote in message
news:E6A9E23D-916B-4CEE-8C3F-8801CD3394EB@.microsoft.com...
> Hello,
> We have a site on SQL Server 2005 SP2 EE. They are using synchronous
> database mirroring. Both the principal and mirror are on a fast LAN. When
> they perform transaction log backups on the principal database, our
> application that accesses the principal database freezes for 1 or 2
> seconds.
> The transaction log backups are being written to the same disk where the
> database resides. They have tried reducing the frequency of the
> transaciton
> log backups to every 10 minutes, and that reduced the length of the app
> freeze, but did not eliminate it. Has anyone heard of a problem running
> transaction log backups when the database is mirrored?
> Thanks,
> Heather

Images (jpgs) and Replication

Hi,
At the moment we have apx 15 images per item (apx 5000/client).
Currently, these are sent to us from the client to our lan so they can be
QC'd.
They go through the QC process and a database pointer is allocated to the
database item (in tblItemImages ( itemID INT, ImageFileName varchar(255),
ImageFolderID INT )
tblItemImages is uploaded to the webserver on a scheduled basis (noon &
midnight) and the images are FTP sync'd at the same time.
We may have a requirement to upload the images every hour (or every few
minutes?), in which case the ftp sync becomes a large overhead.
My question is, how feasable is it to store images in SQL Server and have
them replicated to a remote server (dedicated 1kbps line).
The images are apx 25-50KB each and only 3 of the 15 images are needed on
the webserver.
Getting the images in and out of the database shouldn't be so much of an
issue.Rebecca,
it shouldn't be a problem. I've done that on a database with loads of
image columns running merge replication with >20 subscribers, some of
them connected by a standard ISDN connection. So in your case it should
work without too much problems. But then again in my experience
replication always creates some (un)expected problems.
Markus|||Moving from storing the images as files to storing them in image columns
would require modifications to the database model and website programming.
Why is the FTP sync becomming an overhead; does the FTP utility support a
feature for only transferring files that don't exist on the destination or
have an updated timestamp?
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:434e3f98$0$136$7b0f0fd3@.mistral.news.newnet.co.uk...
> Hi,
> At the moment we have apx 15 images per item (apx 5000/client).
> Currently, these are sent to us from the client to our lan so they can be
> QC'd.
> They go through the QC process and a database pointer is allocated to the
> database item (in tblItemImages ( itemID INT, ImageFileName varchar(255),
> ImageFolderID INT )
> tblItemImages is uploaded to the webserver on a scheduled basis (noon &
> midnight) and the images are FTP sync'd at the same time.
> We may have a requirement to upload the images every hour (or every few
> minutes?), in which case the ftp sync becomes a large overhead.
> --
> My question is, how feasable is it to store images in SQL Server and have
> them replicated to a remote server (dedicated 1kbps line).
> The images are apx 25-50KB each and only 3 of the 15 images are needed on
> the webserver.
>
> Getting the images in and out of the database shouldn't be so much of an
> issue.
>
>|||Yes it does, but the number of images is enormous and it takes ages just to
bring down the image file names and timestamps to work out which ones need
to be uploaded.
images*items*clients
3*5000*5 = 75000
Altho we do it per client at the moment, so it's only 15000 at a time, but
it's still alot of data to bring down.
"JT" <someone@.microsoft.com> wrote in message
news:uu39a8%23zFHA.3812@.TK2MSFTNGP09.phx.gbl...
> Moving from storing the images as files to storing them in image columns
> would require modifications to the database model and website programming.
> Why is the FTP sync becomming an overhead; does the FTP utility support a
> feature for only transferring files that don't exist on the destination or
> have an updated timestamp?

IIS6, VPN, SQL Server 2000

If we have a SQL Server 2000 database on our internal LAN and an
external IIS6 Server with ASP.NET pages using SQL Authentication talking
to the SQL Server through the Internet "cloud" via VPN/Firewall Gateway
devices on either end of the connection (with port 1433 enabled), would
the traffic over the VPN be transparent to SQL Server or do we need to
configure SQL Server to handle it? We are running Windows 2003 server.
Thanks
TimoSQL-- IIS --VPN--Internet--VPN
I'm asuming this is your configuration..
So if your configuration is like this, then once the VPN tunnel is made,
then whatever protocols you're using will be sent across the VPN.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Kevin,
Thanks for the reply. Actually, we will have two IIS Servers, IIS#1 on
our internal LAN used for intranet apps, IIS#2 external for internet
apps visible to the public. The configuration would be like this:
{LAN: IIS#1--SQL } --VPN--{tunnel}--VPN--IIS#2--{Internet}
If I understand you correctly, when SQL gets the request from the IIS#
2, the traffic will look to SQL no different from any other traffic over
port 1433, even though it has come through the tunnel.
Apart from making sure the VPN/Firewall has port 1433 open, is there
anything else required of the VPN to support traffic between SQL and
IIS#2?
Timo
In article <X3QoXmmJEHA.3088@.cpmsftngxa10.phx.gbl>,
kevmc@.online.microsoft.com says...
> SQL-- IIS --VPN--Internet--VPN
> I'm asuming this is your configuration..
> So if your configuration is like this, then once the VPN tunnel is made,
> then whatever protocols you're using will be sent across the VPN.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.|||From what I can see your analysis is correct.
Cheers
Ken
"Timo" <t@.anonymous.com> wrote in message
news:MPG.1aeed52797260409896be@.msnews.microsoft.com...
: Kevin,
: Thanks for the reply. Actually, we will have two IIS Servers, IIS#1 on
: our internal LAN used for intranet apps, IIS#2 external for internet
: apps visible to the public. The configuration would be like this:
:
: {LAN: IIS#1--SQL } --VPN--{tunnel}--VPN--IIS#2--{Internet}
:
: If I understand you correctly, when SQL gets the request from the IIS#
: 2, the traffic will look to SQL no different from any other traffic over
: port 1433, even though it has come through the tunnel.
:
: Apart from making sure the VPN/Firewall has port 1433 open, is there
: anything else required of the VPN to support traffic between SQL and
: IIS#2?
: Timo
:
: In article <X3QoXmmJEHA.3088@.cpmsftngxa10.phx.gbl>,
: kevmc@.online.microsoft.com says...
: >
: > SQL-- IIS --VPN--Internet--VPN
: >
: > I'm asuming this is your configuration..
: > So if your configuration is like this, then once the VPN tunnel is made,
: > then whatever protocols you're using will be sent across the VPN.
: >
: >
: >
: > Thanks,
: >
: > Kevin McDonnell
: > Microsoft Corporation
: >
: > This posting is provided AS IS with no warranties, and confers no
rights.|||previous post;
"If I understand you correctly, when SQL gets the request from the IIS#
2, the traffic will look to SQL no different from any other traffic over
port 1433, even though it has come through the tunnel.
"
Correct.
"Apart from making sure the VPN/Firewall has port 1433 open, is there
anything else required of the VPN to support traffic between SQL and
IIS#2?"
No.
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Another question has arisen and we don't have a system in place
yet to determine the answer by trial and error.
When IIS6 on somedomain.com communicates with SQLServer2000 on
someotherdomain.net via a VPN, should the Connection String
specify the SQLServer by name with Standard Security:
"Data Source=OurSQLServer;Initial Catalog=pubs;User
Id=sa;Password=foo;"
Or should the Connection String use the IP address mode:
Data Source=xxx.xxx.xxx.xxx,1433;Network Library=DBMSSOCN;Initial
Catalog=pubs;User ID=sa;Password=foo;"
If the Connection String should use the IP address mode with
DBMSSOCN, should it specify the IP address of the firewall (and
let the firewall translate 1433 traffic to the SQLServer by NAT)
or should it specify the IP address of the actual SQL Server?
Thanks!
Timo|||So, if you're on the outside of the firewall, you're client machine has no
knowledge of how to resolve a netbios name on the
inside of the firewall. You should be able to use the IP address of the
firewall, and allow it to NAT to the internal server.
Otherwise, if you want to use the fqdn name, add an entry to your host
file, or lmhost file if you want to use the netbios name.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.