Sql Ignoring spaces

Friday, March 30, 2012

Impact on replication when a server goes into standby mode

We have the following scenario:

Server A replicates Database A to Server B.

Server C has Database A on it as well, but in standby mode. We are applying the transaction logs generated by Database A on Server A to the database on Server C leaving it in standby mode each time.

Let's say we had planned maintenance for Server Aand dumped the last set of transactions on Server A in standby mode to be applied to to Server C. What happens to the replica on Server B? When I start to use Server C, can I backup its transactions and apply them to Server A, and then have those transactions replicated to Server B? And then what do I do when the maintenance is complete so that I can swithc back to Server A and have the replication continue on as before the maintenance to Server B?

Thanls

In SQL 2000 unless you

1) have configured Server C to also ship the master, msdb, distirbution and publication databases
2) restore the last log with the keep_replication switch
3) take Server A completely offline
4) rename ServerC to ServerA and reboot ServerC(now ServerA)
5) run sp_replrestart
6) use the continue on data consistency error profile

it will not work.

If you also follow the sequence in a failback scenario it will also work.

In SQL 2005 you can configure Server A and C as log shipping failover partners (use a remote distributor and set the publisherfailoverpartner accordingly).

Note that it is possible to do the following in SQL 2000

1) ship logs to ServerC from Server A.
2) on failover restore the tail of A to C with the keep_replication switch
3) use the sp_replrestart command to pick up where you left off

There is more potential exposure to data loss with the above method.

Impact of transaction log backups in synchronous DB Mirroring syst

. 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

Impact of SELECTed colums on the execution plan

Hi guys,

I have something weird that I want to understand.

I have a big table, containing around 17 millions of rows. This table has been progressively indexed over time, mainly by following some of the recommendations of the DB tuning advisor. As a result, we have around ten indexes on this table, some of them are using the new "INCLUDE" feature of SQL2005 indexes with non-key data.

The problem I have is the following : I have a very simple query (SELECT <some columns> FROM that_table WHERE <simple clause conditions>) which should benefit from the indexes built on the table. But depending on the columns I select, the execution plan varies totally !!

With one column selected, the good index is used, and the query is fast.

With two columns selected, the execution plan becomes complicated (several different indexes seeked in parallel). I guess the optimization system is trying to get benefit from some non-key data included in some of the indexes ? But I'm not sure...

With a "SELECT *" no appropriate index is used. The excution plan shows it will do a "clustered index scan" over the primary key (which is not part of the where clause), resulting in a full scan of the table... OK, I know that "SELECT *" is not a good practice, but anyway, this result is very surprising.

(I tried to make it short, I can detail the queries and the construction of the indexes if needed, but the main info is there I think)

How can it be possible to have so many differences, just by changing the list of SELECTed columns ? It's beyond my understanding of what is an index...

Thanks for your help

Mathieu

Hi Mathieu,

There's a few things that could be going on here, and it's hard to make a definitive call without investigating the query plan, but...

If you include columns in the select list that are not served by the index the used in order to serve the where clause, the engine will need to perform what's known as a bookmark lookup in order to retrieve the column values that are not included in the index. It of course gets a bit more complicated if the optimiser chooses parallel indexes.

So, if your query was something like:

SELECT id, name, dob

FROM tblPerson

WHERE id = 993

You can also try execting the query with OPTION (MAXDOP 1) in order to determine if the generation of a parallel query plan is introducing unnecessary overhead.

You would create an index on the id column and list name and dob columns in the new INCLUDE clause. This would be a good starting point, but you'd need to take into account all other queries against this table in order to decide if indeed this. Indexing is a huge subject, so get googling! :)

Cheers,

Rob

|||

To be clearer :

1st case :

SELECT a FROM table WHERE b=constant AND c>constant AND d LIKE 'string%'.

c AND d are covered by an index, and the column a is included (INCLUDE keyword) in that index. b, c, and d are regulars data columns (not involved in PK). The exec plan is fine, the query is fast.

2nd case : I just add a column in the SELECT, the where clause is left untouched

SELECT a, e FROM table WHERE b=constant AND c>constant AND d LIKE 'string%'.

Here, the column e is not included in the index mentionned hereunder. The exec plan becomes complicated, involving others indexes in parallel. The query becomes slow.

3rd case : retrieving all columns, the where clause is still left untouched

SELECT * FROM table WHERE b=constant AND c>constant AND d LIKE 'string%'.

This time, the exec plan is "clustered index scan" over the PK. But the PK does not appear in the where clause ! Result is a full scan of the 17millions of rows... catastrophic !

While writing this post, I'm getting convinced that SQL Server is perturbed by the INCLUDEd columns in the indexes... What's your opinion ?

|||

Hi Mathieu,

As mentioned, if a column in the select list is not included in the index, a bookmark lookup is used. So, in the 2nd case above, you would either include column e in the index used for the operation either as a key value or in the INCLUDE list.

In the 3rd case, it would appear the optimiser has decided that rather then performing lookups for all the columns not servicable by the index (ie the SELECT *), it has chosen to perform a full scan. I'd need to see the full query plan and the schema to provide more info, but it sounds like you need to revisit your indexing strategy from the ground up.

Although INCLUDE is new to 2005, I've never had nor heard of an issue directly related to its use (yet).

Cheers,

Rob

impact of polling interval

Hi,
We have a setup with merge replication and polling interval of 10
seconds.
Frequently merge replication agent stops with deadlocks.
On another system we have default polling interval of 60 seconds. this
system does not
have the above issue.
Is it because of low value of polling interval ? what should be the
value of this parameter and waht is the impact of this parameter
change?
rgds,
amit
Basically it means it reads the msmerge_contents, and msmerge_tombstone
tables (among others) every 10 seconds looking for changes. It does sound
like this setting is not optimal for your topology. I would use the default
of 60 seconds.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"mitsql" <amit.nimje@.tcs.com> wrote in message
news:1141010206.296301.156060@.j33g2000cwa.googlegr oups.com...
> Hi,
> We have a setup with merge replication and polling interval of 10
> seconds.
> Frequently merge replication agent stops with deadlocks.
> On another system we have default polling interval of 60 seconds. this
> system does not
> have the above issue.
> Is it because of low value of polling interval ? what should be the
> value of this parameter and waht is the impact of this parameter
> change?
> rgds,
> amit
>
|||Thanks a lot for the reply can you tell me if setting expiry as 14 days
can also have similar impact?

Impact of migration on Windows authenticated SQL user account

Yes, I am trying to migrate a few SQL servers from NT4
domain to the new AD domain. These SQL servers have many
Windows authenticated SQL user account. As I understand,
once the logins have been defined as <NT4
domain>\<username>, we cant change it to <AD
domain>\<username>, unless we delete the logins and
recreate it again?
The article (http://support.microsoft.com/default.aspx?
scid=kb;EN-US;240872) seems to mention the moving of
database from one server to another. However in my case,
the database will remain in the same server, just that
the domain will change...hence affecting all the
previously created Windows authenticated SQL user
accounts.
Is there any step-by-step document on how to do security
translation for these SQL user accounts after the
migration to the new domain?

>--Original Message--
>Message unavailableSomething like this ... it's been awhile ... SQL Server maps SIDS to SQL Se
rver id's. There's a technique/procedure that help to remap these when like
your self domain SIDS don't match up with SQL Server id's. Sorry, but I do
nt' do it often enough to r
emember exactly.

Friday, March 30, 2012

Impact on replication when a server goes into standby mode

Impact of transaction log backups in synchronous DB Mirroring syst

Impact of transaction log backups in synchronous DB Mirroring syst

Impact of transaction log backups in synchronous DB Mirroring syst

Impact of SELECTed colums on the execution plan

impact of polling interval

Impact of migration on Windows authenticated SQL user account

Sql Ignoring spaces

Blog Archive

About Me