Sql Ignoring spaces: March 2012

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.

Impact of HD (de)fragmentation on SQL Server performance

It seems logical to me, that defragmented HDs would yield better performance
than fragmented ones.
Are there any published metrics that demonstrate this for Transactional
Databases?
(e.g., RAID 5, 200GB Drive with 50% disk fragmentation and 65% file
fragmentation vs. no fragmentation -- using the same load in both scenarios
-- in terms of any performance indicators: disk seek time, R/W Queue, etc).
Thanks,
Tea C."Tea C" <smece1972@.yahoo.com.nospam> wrote in message
news:C0FF7B34-58B8-4BDF-8360-261ADABEEA94@.microsoft.com...
> It seems logical to me, that defragmented HDs would yield better
> performance
> than fragmented ones.
> Are there any published metrics that demonstrate this for Transactional
> Databases?
> (e.g., RAID 5, 200GB Drive with 50% disk fragmentation and 65% file
> fragmentation vs. no fragmentation -- using the same load in both
> scenarios
> -- in terms of any performance indicators: disk seek time, R/W Queue,
> etc).
>
The fragmentation percentage is almost meaningless for large databases. A
100g database in 2 fragments would equate to 50% disk fragmentation, but has
0.00000001% affect on performance. On the other hand, a small, hot database
in many fragments could adversly affect performance without causing alarming
disk fragmentation percentage.
David|||Hi
Having the Logs on a RAID-5 affects performance more than fragmentation at
OS level.
Auto grow and Auto shrink have an effect as it causes your DB to become
fragmented.
Sometime s with RAID-5, having file fragmentation is actually good as it
might then involve a different spindle than if the data was contiguous.
Test the performance differences in your environment, setup may play a big
role.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tea C" <smece1972@.yahoo.com.nospam> wrote in message
news:C0FF7B34-58B8-4BDF-8360-261ADABEEA94@.microsoft.com...
> It seems logical to me, that defragmented HDs would yield better
performance
> than fragmented ones.
> Are there any published metrics that demonstrate this for Transactional
> Databases?
> (e.g., RAID 5, 200GB Drive with 50% disk fragmentation and 65% file
> fragmentation vs. no fragmentation -- using the same load in both
scenarios
> -- in terms of any performance indicators: disk seek time, R/W Queue,
etc).
> Thanks,
> --
> Tea C.sql

Impact of HD (de)fragmentation on SQL Server performance

It seems logical to me, that defragmented HDs would yield better performance
than fragmented ones.
Are there any published metrics that demonstrate this for Transactional
Databases?
(e.g., RAID 5, 200GB Drive with 50% disk fragmentation and 65% file
fragmentation vs. no fragmentation -- using the same load in both scenarios
-- in terms of any performance indicators: disk seek time, R/W Queue, etc).
Thanks,
--
Tea C."Tea C" <smece1972@.yahoo.com.nospam> wrote in message
news:C0FF7B34-58B8-4BDF-8360-261ADABEEA94@.microsoft.com...
> It seems logical to me, that defragmented HDs would yield better
> performance
> than fragmented ones.
> Are there any published metrics that demonstrate this for Transactional
> Databases?
> (e.g., RAID 5, 200GB Drive with 50% disk fragmentation and 65% file
> fragmentation vs. no fragmentation -- using the same load in both
> scenarios
> -- in terms of any performance indicators: disk seek time, R/W Queue,
> etc).
>
The fragmentation percentage is almost meaningless for large databases. A
100g database in 2 fragments would equate to 50% disk fragmentation, but has
0.00000001% affect on performance. On the other hand, a small, hot database
in many fragments could adversly affect performance without causing alarming
disk fragmentation percentage.
David|||Hi
Having the Logs on a RAID-5 affects performance more than fragmentation at
OS level.
Auto grow and Auto shrink have an effect as it causes your DB to become
fragmented.
Sometime s with RAID-5, having file fragmentation is actually good as it
might then involve a different spindle than if the data was contiguous.
Test the performance differences in your environment, setup may play a big
role.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tea C" <smece1972@.yahoo.com.nospam> wrote in message
news:C0FF7B34-58B8-4BDF-8360-261ADABEEA94@.microsoft.com...
> It seems logical to me, that defragmented HDs would yield better
performance
> than fragmented ones.
> Are there any published metrics that demonstrate this for Transactional
> Databases?
> (e.g., RAID 5, 200GB Drive with 50% disk fragmentation and 65% file
> fragmentation vs. no fragmentation -- using the same load in both
scenarios
> -- in terms of any performance indicators: disk seek time, R/W Queue,
etc).
> Thanks,
> --
> Tea C.

Impact of HD (de)fragmentation on SQL Server performance

It seems logical to me, that defragmented HDs would yield better performance
than fragmented ones.
Are there any published metrics that demonstrate this for Transactional
Databases?
(e.g., RAID 5, 200GB Drive with 50% disk fragmentation and 65% file
fragmentation vs. no fragmentation -- using the same load in both scenarios
-- in terms of any performance indicators: disk seek time, R/W Queue, etc).
Thanks,
Tea C.
"Tea C" <smece1972@.yahoo.com.nospam> wrote in message
news:C0FF7B34-58B8-4BDF-8360-261ADABEEA94@.microsoft.com...
> It seems logical to me, that defragmented HDs would yield better
> performance
> than fragmented ones.
> Are there any published metrics that demonstrate this for Transactional
> Databases?
> (e.g., RAID 5, 200GB Drive with 50% disk fragmentation and 65% file
> fragmentation vs. no fragmentation -- using the same load in both
> scenarios
> -- in terms of any performance indicators: disk seek time, R/W Queue,
> etc).
>
The fragmentation percentage is almost meaningless for large databases. A
100g database in 2 fragments would equate to 50% disk fragmentation, but has
0.00000001% affect on performance. On the other hand, a small, hot database
in many fragments could adversly affect performance without causing alarming
disk fragmentation percentage.
David
|||Hi
Having the Logs on a RAID-5 affects performance more than fragmentation at
OS level.
Auto grow and Auto shrink have an effect as it causes your DB to become
fragmented.
Sometime s with RAID-5, having file fragmentation is actually good as it
might then involve a different spindle than if the data was contiguous.
Test the performance differences in your environment, setup may play a big
role.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tea C" <smece1972@.yahoo.com.nospam> wrote in message
news:C0FF7B34-58B8-4BDF-8360-261ADABEEA94@.microsoft.com...
> It seems logical to me, that defragmented HDs would yield better
performance
> than fragmented ones.
> Are there any published metrics that demonstrate this for Transactional
> Databases?
> (e.g., RAID 5, 200GB Drive with 50% disk fragmentation and 65% file
> fragmentation vs. no fragmentation -- using the same load in both
scenarios
> -- in terms of any performance indicators: disk seek time, R/W Queue,
etc).
> Thanks,
> --
> Tea C.

Impact of Existing replication

Scenario.
A db published for transactional replication will be backed up and restored
on a server in a different facility. There, all new replication will be
defined (completely different articles, completely different targets)
Question.
Will pre-existing replication prevent correct functioning of the db at the
time of restore and new replication being defined? Dropping replication
prior to backing up the db is not an option.
Thank you for your help
Oscar
Once restored, execute sp_removedbreplication @.dbname = 'MyPubDB', then
reconfigure replication...
ChrisB
MCDBA OCP
www.MyDatabaseAdmin.com
"Oscar" wrote:

> Scenario.
> A db published for transactional replication will be backed up and restored
> on a server in a different facility. There, all new replication will be
> defined (completely different articles, completely different targets)
> Question.
> Will pre-existing replication prevent correct functioning of the db at the
> time of restore and new replication being defined? Dropping replication
> prior to backing up the db is not an option.
> Thank you for your help
> Oscar
>
|||Actually you want to execute:
-- REMOVE TRANSACTION IN LOG TO ALLOW FOR TRUNCATE -
EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0, @.time =
0, @.reset = 1
ChrisB
MCDBA OCP
www.MyDatabaseAdmin.com
"Chris" wrote:
[vbcol=seagreen]
> Once restored, execute sp_removedbreplication @.dbname = 'MyPubDB', then
> reconfigure replication...
> ChrisB
> MCDBA OCP
> www.MyDatabaseAdmin.com
> "Oscar" wrote:

Impact of empty tables on database size and performance

Hello All,

When creating my database I have modeled some of the tables after the Adventureworks sample database.

There are some fields or entire tables in Adventureworks that I do not see an imediate use for, however; I would hate to ommit them to find out later they would have been benificial. (.eg territory table).

In general terms what would the impact be on size and performance of a database which contains tables or fields that do not contain data.

Thanks for your help!

Blair:

There will be little impact from tables that are not used provided these tables are small and do not grow. If the tables are large then they can impact your storage requirements and the size and speed of your backups.

Fields that are not used are a different story -- especially if the fields are populated with large amounts of "spectator" data. These "spectator" fields will then impact the amount of space required for their tables and will have an impact on the amount of time required for a "table scan". This becomes amplified to some extent if these fields participate in any indexes. If your fields are all null the amount of bloating will not be as great.

Another problem that can occur if you leave in fields that have at the moment no use is that in the future it can become tempting for somebody to start "using" these fields in ways that are not planned. Eliminating unused fields can stop this kind of "cobbling" before it takes place.

I would suggest that you are better off with a well designed, well thought out database in which each column has a specific meaning with a specific intended use.

impact of changing recovery model on a database that is currently"live"

I'm running sql server 2005 standard edition. We have given up on sql
server replication for various reasons and are gonig to implement
transaction log shipping. I need to switch the database to "Full"
recovery model but owing to a fascinating combination of circumstances
and indecisiveness the server has had replication disabled before the
new backup stratgey is in place. I don't want to wait another day
before I get a replication strategy going to I want to go to FULl
model right NOW. However the databse is in use and I don't want things
to be disrupted. The database server is only lightly loaded (8 cores,
only one running at any one time above 10% and disk queue about 0.1)
and a few seconds of latency is OK..
is it safe to switch recovery model to FULL during working hours on a
production database?
thanks,
sam
sam,
Switching to full mode will not add appreciably to the load on your server,
but it will keep the logs until they are backed up, so you may see grown in
your log (.ldf) files.
When switching to full mode, the first thing to do is a full backup. This
may cause about a 10% hit on the throughput of that server during the time
of the backup. Again, a small price to pay.
Then be sure to schedule regular transaction log backups so that your
transactions are saved and the log files do not grow excessively. (At least
once a day, but depending on your work and its critical nature it could be
as often as every 5 minutes.)
RLF
"sam.m.gardiner" <sam.m.gardiner@.gmail.com> wrote in message
news:6ad20370-72c2-4fec-b623-81e35b63bc9d@.u69g2000hse.googlegroups.com...
> I'm running sql server 2005 standard edition. We have given up on sql
> server replication for various reasons and are gonig to implement
> transaction log shipping. I need to switch the database to "Full"
> recovery model but owing to a fascinating combination of circumstances
> and indecisiveness the server has had replication disabled before the
> new backup stratgey is in place. I don't want to wait another day
> before I get a replication strategy going to I want to go to FULl
> model right NOW. However the databse is in use and I don't want things
> to be disrupted. The database server is only lightly loaded (8 cores,
> only one running at any one time above 10% and disk queue about 0.1)
> and a few seconds of latency is OK..
> is it safe to switch recovery model to FULL during working hours on a
> production database?
> thanks,
> sam
|||ah thanks for that russell, the backup was also a worry.
but i was wondering about the actual moment of switch over, will that
cause any freeze or hang ups?
|||No, basically I understand it does two things.
1. Stops truncating the log
2. Increases the amount of logging done for BULK INSERTs. (If you don't
want this behaviour, the Bulk-Logged recovery model is similar to FULL, but
gives reduced coverage for the bulk inserts.)
RLF
"sam.m.gardiner" <sam.m.gardiner@.gmail.com> wrote in message
news:dde0ef2e-c103-48fd-a5f7-df76652da702@.b1g2000hsg.googlegroups.com...
> ah thanks for that russell, the backup was also a worry.
> but i was wondering about the actual moment of switch over, will that
> cause any freeze or hang ups?
|||thanks for your advice. the switch over was completed with no fuss and
took only a few seconds..
sam
sql

impact of changing recovery model on a database that is currently

I'm running sql server 2005 standard edition. We have given up on sql
server replication for various reasons and are gonig to implement
transaction log shipping. I need to switch the database to "Full"
recovery model but owing to a fascinating combination of circumstances
and indecisiveness the server has had replication disabled before the
new backup stratgey is in place. I don't want to wait another day
before I get a replication strategy going to I want to go to FULl
model right NOW. However the databse is in use and I don't want things
to be disrupted. The database server is only lightly loaded (8 cores,
only one running at any one time above 10% and disk queue about 0.1)
and a few seconds of latency is OK..
is it safe to switch recovery model to FULL during working hours on a
production database?
thanks,
samsam,
Switching to full mode will not add appreciably to the load on your server,
but it will keep the logs until they are backed up, so you may see grown in
your log (.ldf) files.
When switching to full mode, the first thing to do is a full backup. This
may cause about a 10% hit on the throughput of that server during the time
of the backup. Again, a small price to pay.
Then be sure to schedule regular transaction log backups so that your
transactions are saved and the log files do not grow excessively. (At least
once a day, but depending on your work and its critical nature it could be
as often as every 5 minutes.)
RLF
"sam.m.gardiner" <sam.m.gardiner@.gmail.com> wrote in message
news:6ad20370-72c2-4fec-b623-81e35b63bc9d@.u69g2000hse.googlegroups.com...
> I'm running sql server 2005 standard edition. We have given up on sql
> server replication for various reasons and are gonig to implement
> transaction log shipping. I need to switch the database to "Full"
> recovery model but owing to a fascinating combination of circumstances
> and indecisiveness the server has had replication disabled before the
> new backup stratgey is in place. I don't want to wait another day
> before I get a replication strategy going to I want to go to FULl
> model right NOW. However the databse is in use and I don't want things
> to be disrupted. The database server is only lightly loaded (8 cores,
> only one running at any one time above 10% and disk queue about 0.1)
> and a few seconds of latency is OK..
> is it safe to switch recovery model to FULL during working hours on a
> production database?
> thanks,
> sam|||ah thanks for that russell, the backup was also a worry.
but i was wondering about the actual moment of switch over, will that
cause any freeze or hang ups?|||No, basically I understand it does two things.
1. Stops truncating the log
2. Increases the amount of logging done for BULK INSERTs. (If you don't
want this behaviour, the Bulk-Logged recovery model is similar to FULL, but
gives reduced coverage for the bulk inserts.)
RLF
"sam.m.gardiner" <sam.m.gardiner@.gmail.com> wrote in message
news:dde0ef2e-c103-48fd-a5f7-df76652da702@.b1g2000hsg.googlegroups.com...
> ah thanks for that russell, the backup was also a worry.
> but i was wondering about the actual moment of switch over, will that
> cause any freeze or hang ups?|||thanks for your advice. the switch over was completed with no fuss and
took only a few seconds..
sam

Impact of Changing Authentication Mode

Hi,
Our application using an SQL 2000 database, which is currently using Windows
Authentication. For some reason, we need to change the access mode to mixed
mode. Could someone please throw some light on the impact of this change.
Thanks in advance
SuhasOne extra door to guard?
Not sure what you mean by impact. From a performance or resource overhead
perspective? Nothing there. From a security perspective, you now need to
figure out how to manage the SQL Server logins to ensure they follow your
password policies (complexity, length, expiration, re-use, etc...). Not much
help built into SQL Server 2000 but in 2005, life gets a LOT better.
joe.
"Suhas Vengilat" <SuhasVengilat@.discussions.microsoft.com> wrote in message
news:34F882D9-448F-4EDC-A62C-BB87CCAF9307@.microsoft.com...
> Hi,
> Our application using an SQL 2000 database, which is currently using
> Windows
> Authentication. For some reason, we need to change the access mode to
> mixed
> mode. Could someone please throw some light on the impact of this change.
> Thanks in advance
> Suhas|||With either SQL Server 2000 or SQL Server 2005, the process is virtually
identical as follows:
Run SQL Enterprise Manager (or Start > Run > SQLwb.exe if using 2005)
Connect to the server
Launch Server Properties dialog
Switch to the Security tab
Select the Authentication option (Mixed or Windows only) > OK
Dismiss the "restart the server to implement changes" message box
"Restart" the MSSQLServer service (Start > Run > Compmgmt.msc >
services.msc)
Regards,
Keith
This posting is provided "as is" without inferring rights or warranties.
"Joe Yong" <NO_jyong_SPAM@.scalabilityexperts.com> wrote in message
news:OVSeLmxvFHA.1032@.TK2MSFTNGP12.phx.gbl...
> One extra door to guard?
> Not sure what you mean by impact. From a performance or resource overhead
> perspective? Nothing there. From a security perspective, you now need to
> figure out how to manage the SQL Server logins to ensure they follow your
> password policies (complexity, length, expiration, re-use, etc...). Not
> much help built into SQL Server 2000 but in 2005, life gets a LOT better.
>
> joe.
>
> "Suhas Vengilat" <SuhasVengilat@.discussions.microsoft.com> wrote in
> message news:34F882D9-448F-4EDC-A62C-BB87CCAF9307@.microsoft.com...
>

Impact of AD Migration on Windows authenticated SQL user account

We have SQL applications in which some of the SQL user
accounts are integrated with Windows authentication.
We would like to find out whether any of you have
migrated any SQL based applications of which the SQL user
accounts were configured to make use of Windows
authentication instead of SQL authentication. Will the
Windows accounts in SQL be automatically translated to
the new AD account (with all necessary SQL
rights/permissions) or do we have to manually recreate &
reconfigure all Windows accounts in SQL and its
rights/permissions?
Appreciate some feedback. Thanks.
.Hi
If you are using the AD migration tool then you should keep the same SIDs
and therefore there should not be a problem.
If you do change SIDS then the amount of rework would be reduced if you used
NT groups when granting premissions rather than individual users.
John
"Teo Chee Yang" <anonymous@.discussions.microsoft.com> wrote in message
news:c11b01c40893$25655870$a301280a@.phx.gbl...
> We have SQL applications in which some of the SQL user
> accounts are integrated with Windows authentication.
> We would like to find out whether any of you have
> migrated any SQL based applications of which the SQL user
> accounts were configured to make use of Windows
> authentication instead of SQL authentication. Will the
> Windows accounts in SQL be automatically translated to
> the new AD account (with all necessary SQL
> rights/permissions) or do we have to manually recreate &
> reconfigure all Windows accounts in SQL and its
> rights/permissions?
> Appreciate some feedback. Thanks.
> .
>|||Personally I doubt that ADMT does translate any SQL
permissions and rights assigned to Windows-authenticated
SQL user accounts.
Besides, I realized that from the Security, Logins folder
that once the logins are defined as <NT4
domain>\<username>, we cant changed it, even before the
migration of the SQL server computer account.
Any more feedback?

>--Original Message--
>Hi
>If you are using the AD migration tool then you should
keep the same SIDs
>and therefore there should not be a problem.
>If you do change SIDS then the amount of rework would be
reduced if you used
>NT groups when granting premissions rather than
individual users.
>John
>"Teo Chee Yang" <anonymous@.discussions.microsoft.com>
wrote in message
>news:c11b01c40893$25655870$a301280a@.phx.gbl...
user
&
>
>.
>|||Hi
This sounds like you are changing the domain as well during the migration.
In which case look at:
http://support.microsoft.com/defaul...kb;EN-US;240872
I believe in the documentation that SIDs are retained by ADMT, if it did not
all sorts of other privilege related problems would occur.
John
"Teo Chee Yang" <anonymous@.discussions.microsoft.com> wrote in message
news:c64d01c40915$7d155610$a401280a@.phx.gbl...
> Personally I doubt that ADMT does translate any SQL
> permissions and rights assigned to Windows-authenticated
> SQL user accounts.
> Besides, I realized that from the Security, Logins folder
> that once the logins are defined as <NT4
> domain>\<username>, we cant changed it, even before the
> migration of the SQL server computer account.
> Any more feedback?
>
> keep the same SIDs
> reduced if you used
> individual users.
> wrote in message
> user
> &

Impact of "Rows per batch" on OLE DB Destination?

OLE DB Destination has a "Rows per batch" parameter.
Are there best practices/guidelines around what it should be set to (for better performance etc)? For example, if each row is of size 100 bytes, how does performance change when I set a batchsize of 0, 100, 1000, 10,000 etc?

I guess the default value for this parameter is zero.
Does that mean, internally it is implemented as:

For each row:

begin transaction
insert row
commit transaction
thanks,
Nitesh

Nitesh Ambastha wrote:

OLE DB Destination has a "Rows per batch" parameter.
Are there best practices/guidelines around what it should be set to (for better performance etc)? For example, if each row is of size 100 bytes, how does performance change when I set a batchsize of 0, 100, 1000, 10,000 etc?
I guess the default value for this parameter is zero.
Does that mean, internally it is implemented as:
For each row:
begin transaction
insert row
commit transaction
thanks,
Nitesh

Hi Nitesh.
Why not do some benchmarking for it? Find out the answers. It'd be really useful info for the community.

-Jamie|||Mmmmmhmmmmm, I'd like to see that! :)|||OK I thought I'd have a go at this. Where is this "rows per batch" parameter/property?

I can't see it. Am I being really dumb?|||

Jamie,

It is on "OLE DB Destination". When you double click on OLE DB Destination, Editor pops up to configure the destination. It is on the first screen / tab (Connection Manager), Second one from bottom.

Default value is not 0. It is -1, which indicates that no value has been assigned (this is according to BOL).

Thanks
Sutha

|||

Sutha Thiru wrote:

Jamie,
It is on "OLE DB Destination". When you double click on OLE DB Destination, Editor pops up to configure the destination. It is on the first screen / tab (Connection Manager), Second one from bottom.
Default value is not 0. It is -1, which indicates that no value has been assigned (this is according to BOL).
Thanks
Sutha

OK, I'm glad I'm not heading for the loony bin. I was looking at an IDW15 instance which doesn't have it. I've checked my IDW16 VM and its there.

I wonder why it (and many other properties) were removed on IWD15?

And here's another question. Why are they (Rows per batch, keep nulls, table lock, check constraints, keep ID, max insert commit size) not available via the preoperties pane?
cheers Sutha

-Jamie|||

Good question. Only reason I could think of is as they are new options, they forgot to add it into properties.

Can anyone from MS respond that these are going to be available via properties when the product is released in Nov?

Thanks
Sutha

|||

Sutha Thiru wrote:

Good question. Only reason I could think of is as they are new options, they forgot to add it into properties.
Can anyone from MS respond that these are going to be available via properties when the product is released in Nov?
Thanks
Sutha

As an aside...I don't think they are new properties. I'm *sure* I remember seeing them way back when. It seems to me like they disappeared for IDW15 and now they've reappeared again. Weird!!

-Jamie|||

I did test way back when on the perf of loading batches per transaction. I generally found that 2000 was a good figure. However I supsect this depends greatly on,
the wdith of the rows being inserted,
the io configuration
memory in the system

Interestingly, why is there no batch rows setting for the SQL destination?

|||I'm guessing cos it uses bulk insert which doesn't allow you to specify batch sizes (does it?)

-Jamie|||

In SQL server 2005, it is our decision to remove properties "RowsPerBatch" and "BatchSize" from SQL server destination adapter.

Reason is SQL server dest adapter is not written to utilize the functionalities the two properties provide - e.g. query plan optimization.

We will consider whether to enable them in the next version.

|||

"Rows per batch" is a Fast Load Option. In the OLE DB Destination, it is only visible (along with some other options like Keep Identity, Keep Nulls etc.) if you choose the Data Access Mode "Table or View - Fast Load". If you choose the data access mode "Table or view", they won't show up. They never disappeared from IDW15, my guess is you are looking at it with the non fast load option, and therefore are not finding it there.

They are available from the properties window, but in a different way. You can specify them in the FastLoadOptions. For example,

FastLoadOptions : TABLOCK,CHECK_CONSTRAINTS,ROWS_PER_BATCH = 1000

sql

Impact if Change Server ame

I built SQL Server 2k ent sp3a on Windows 2003 ent and completed the testing
and ready to go. But we need to change the Windows server name, which means
the default instance name will be changed automatically. What's the impace
on database, or database setting or SQL server settings?
Thanks
Shouldn't be any, unless you have remote logins (e.g. for replication), or
have hard-coded the old server name in your code, or have linked servers
pointing at aliases that reference the server by its old name as opposed to
IP.
Just make sure you do:
EXEC sp_dropserver 'old name'
GO
EXEC sp_addserver 'new name', local
http://www.aspfaq.com/
(Reverse address to reply.)
"Maggie" <Maggie@.discussions.microsoft.com> wrote in message
news:6CCC918E-573C-44E5-B3C1-7733CC680A75@.microsoft.com...
> I built SQL Server 2k ent sp3a on Windows 2003 ent and completed the
testing
> and ready to go. But we need to change the Windows server name, which
means
> the default instance name will be changed automatically. What's the
impace
> on database, or database setting or SQL server settings?
> Thanks
|||also watch out for the problem with jobs as per below
http://support.microsoft.com/default...b;en-us;281642
Andy.
"Maggie" <Maggie@.discussions.microsoft.com> wrote in message
news:6CCC918E-573C-44E5-B3C1-7733CC680A75@.microsoft.com...
>I built SQL Server 2k ent sp3a on Windows 2003 ent and completed the
>testing
> and ready to go. But we need to change the Windows server name, which
> means
> the default instance name will be changed automatically. What's the
> impace
> on database, or database setting or SQL server settings?
> Thanks

Impact if Change Server ame

I built SQL Server 2k ent sp3a on Windows 2003 ent and completed the testing
and ready to go. But we need to change the Windows server name, which means
the default instance name will be changed automatically. What's the impace
on database, or database setting or SQL server settings?
ThanksShouldn't be any, unless you have remote logins (e.g. for replication), or
have hard-coded the old server name in your code, or have linked servers
pointing at aliases that reference the server by its old name as opposed to
IP.
Just make sure you do:
EXEC sp_dropserver 'old name'
GO
EXEC sp_addserver 'new name', local
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Maggie" <Maggie@.discussions.microsoft.com> wrote in message
news:6CCC918E-573C-44E5-B3C1-7733CC680A75@.microsoft.com...
> I built SQL Server 2k ent sp3a on Windows 2003 ent and completed the
testing
> and ready to go. But we need to change the Windows server name, which
means
> the default instance name will be changed automatically. What's the
impace
> on database, or database setting or SQL server settings?
> Thanks|||also watch out for the problem with jobs as per below
http://support.microsoft.com/default.aspx?scid=kb;en-us;281642
Andy.
"Maggie" <Maggie@.discussions.microsoft.com> wrote in message
news:6CCC918E-573C-44E5-B3C1-7733CC680A75@.microsoft.com...
>I built SQL Server 2k ent sp3a on Windows 2003 ent and completed the
>testing
> and ready to go. But we need to change the Windows server name, which
> means
> the default instance name will be changed automatically. What's the
> impace
> on database, or database setting or SQL server settings?
> Thanks

Imorting Excel 07 data into SQL Server 2005

I have been trying to use the dtswizard.exe in SQL Server 2005 to import Excel 07 spreadsheets with no success.

Any idea how this can be done?

As a work around I have been importing the Excel 07 data into Access 07 and saving the database as the older mdb extension, THEN importing the mdb file into SQL Server 2005. Seems silly though.

To access native excel 2007 data, create an OLEDB connection manager (not an Excel connection) with the following connection string specific to the source file.

Data Source=c:\data\myfilehere.xlsx;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties="Excel 12.0;HDR=YES";

Access the spreadsheet in a dataflow using an OLEDB source with its select statement set to "select * from [Sheet1$]", replacing Sheet1$ with the name of the appropriate worksheet.

You will need the OLEDB provider, naturally, which comes with the install of Office 2007.

Immutability of procedure parameters

So, I was having just the most frustrating bug a few months ago, and my
boss says, "Oh, yeah, you should never assign a new value to an IN
parameter in SQL Server."
He couldn't give me a reference -- I'm happy enough not to do it, and
it solved that problem, but I really can't suggest to _other people
that they copy values and so forth unless I can point to some
documentation, which I've been unable to find. Is it anywhere written
that "assigning new values to IN parameters in SQL Server stored
procedures may product unpredictable results?"
Thanks!
IonIon
Do you mean that you get different data back, or the performance is
different.
Obviously, if you have a procedure that finds customers given their id
number, and you call the procedure with customer ID 112 and then the
procedure changes that value to 220 before searching, you'll get results for
different customer than what you were expecting.
I've also found cases of performance problems because of this, because the
proc is optimized based on the value passed IN, and then if you change it to
another value, it still uses the original plan based on the original value.
You should keep parameters as they were, and if you need to modify them,
assign the new value to a local variable, so it is clear what values are
parameters and which are variables. They are treated very differently.
HTH
Kalen Delaney, SQL Server MVP
<ionFreeman@.gmail.com> wrote in message
news:1150834879.252067.128120@.h76g2000cwa.googlegroups.com...
> So, I was having just the most frustrating bug a few months ago, and my
> boss says, "Oh, yeah, you should never assign a new value to an IN
> parameter in SQL Server."
> He couldn't give me a reference -- I'm happy enough not to do it, and
> it solved that problem, but I really can't suggest to _other people
> that they copy values and so forth unless I can point to some
> documentation, which I've been unable to find. Is it anywhere written
> that "assigning new values to IN parameters in SQL Server stored
> procedures may product unpredictable results?"
> Thanks!
> Ion
>|||Kalen,
Thanks for responding. The issue that I was seeing was that if I
modified a passed date parameter directly, the stored procedure never
came back -- the connection wasn't closed and no data was returned. The
calling application indicates that it wished to cover a maximal date
range by passing identical start and end times, and if I tried to
implement that logic by modifying the passed dates themselves, I lost
contact with the process -- it was really pretty mysterious.
It's possible this plan business is the ticket -- the proc expected
to return no data (based on its infinitesimal date range), but ended up
with all of it.
Thanks a bunch! Poking around in TechNet, I found
http://www.microsoft.com/technet/pr...comp.mspx#EDUAE
which asserts your point.
Ion
Kalen Delaney wrote:
> Ion
> Do you mean that you get different data back, or the performance is
> different.
> Obviously, if you have a procedure that finds customers given their id
> number, and you call the procedure with customer ID 112 and then the
> procedure changes that value to 220 before searching, you'll get results f
or
> different customer than what you were expecting.
> I've also found cases of performance problems because of this, because the
> proc is optimized based on the value passed IN, and then if you change it
to
> another value, it still uses the original plan based on the original value
.
> You should keep parameters as they were, and if you need to modify them,
> assign the new value to a local variable, so it is clear what values are
> parameters and which are variables. They are treated very differently.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> <ionFreeman@.gmail.com> wrote in message
> news:1150834879.252067.128120@.h76g2000cwa.googlegroups.com...sql

immediate updating triggers have "incorrect syntax near ')'."

Hello,
I am using SQL 7.0 to replicate to an immediate-updating subscriber and I am
having trouble when updating a table from the subscriber. When I run the
update statement, I get the error "incorrect syntax near ')'. I believe that
this is either in the auto-generated MSsync trigger or the sp on the
publisher. I am sure the original update statement is correct.
It seems almost as though a parameter is missing... I don't know.
One thing that I noticed is that I only receive this error on certain
tables. The main differences are:
1. the tables that don't work are really wide in columns (by all means they
should be three tables each) whereas the tables that do work are relatively
small.
2. the tables that don't work used to have text columns, but I changed them
to enormous varchar columns. The tables that do work have never had text
columns.
My experience with replication is limited, so any help or advice in this
matter would be greatly appreciated.
Thanks in advance,
-b.
Please can you confirm what service pack you are using, as this issue (or a
v.similar one) cropped up prior to sp2 and was fixed there
(http://support.microsoft.com/kb/238658/EN-US/).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you for your reply. Yes, I read something about that. I had applied sp4
and assumed that it was cumulative... is it not?
-b.
"Paul Ibison" wrote:

> Please can you confirm what service pack you are using, as this issue (or a
> v.similar one) cropped up prior to sp2 and was fixed there
> (http://support.microsoft.com/kb/238658/EN-US/).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Okay, I just read that the service packs are cumulative.
However, I did read something that said that text and image columns will not
replicate normally from immediate updating subscribers, and that special
considerations need to be made
(http://msdn.microsoft.com/library/de...lplan_1l4e.asp)
I no longer have text columns in the tables, but I DID, however, publish to
the subscriber previously with text fields before changing them to varchars
and publishing to the subscriber again. Is it possible that these triggers
remain from the previous publication...?
-b.
"pukeboot" wrote:
[vbcol=seagreen]
> Thank you for your reply. Yes, I read something about that. I had applied sp4
> and assumed that it was cumulative... is it not?
> -b.
> "Paul Ibison" wrote:
|||That shouldn't be the case, unless you did a nosync initialization. Can you
post up the text of some of the triggers so I can take a look?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||to avoid a massively lengthy post, I have sent the trigger code directly to
you via attachment...
Thanks again,
-b.
"Paul Ibison" wrote:

> That shouldn't be the case, unless you did a nosync initialization. Can you
> post up the text of some of the triggers so I can take a look?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||My email address isn't the one I post with (long story). Please send to Paul
.. Ibison@.ReplicationAnswers . Com (no spaces).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I read that as "paulibison@.replicationanswers.com" ... I wasn't sure with the
line breaks and the elipsis.
I forwarded the message there. I hope that's it.
-b.
"Paul Ibison" wrote:

> My email address isn't the one I post with (long story). Please send to Paul
> .. Ibison@.ReplicationAnswers . Com (no spaces).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||Bobby,
there's nothing I can see wrong in the trigger, and no reference to text
columns. As you are on sp4 (publisher, distributor and subscriber?), this is
baffling. I'd run profiler to see where it gets to, and fails, in the
synchronization, and also enable logging to see what that reveals
(http://support.microsoft.com/?id=312292).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I am unfamiliar with the profiler, but I gave it a go and found that the
"internal abort" event was occuring after this line in the trigger (bear with
this... it's a long line):
exec @.retcode = dbo.sp_executesql @.rpc_proc, @.rpc_types,
@.c1
output,@.c2,@.c3,@.c4,@.c5,@.c6,@.c7,@.c8,@.c9,@.c10,@.c11,@. c12,@.c13,@.c14,@.c15,@.c16,@.c17,@.c18,@.c19,@.c20,@.c21,@. c22,@.c23,@.c24,@.c25,@.c26,@.c27,@.c28,@.c29,@.c30,@.c31,@. c32,@.c33,@.c34,@.c35,@.c36,@.c37,@.c38,@.c39,@.c40,@.c41,@. c42,@.c43,@.c44,@.c45,@.c46,@.c47,@.c48,@.c49,@.c50,@.c51,@. c52,@.
c53,@.c54,@.c55,@.c56,@.c57,@.c58,@.c59,@.c60,@.c61,@.c62,@. c63,@.c64,@.c65,@.c66,@.c67,@.c68,@.c69,@.c70,@.c71,@.c72,@. c73,@.c74,@.c75,@.c76,@.c77,@.c78,@.c79,@.c80,@.c81,@.c82,@. c83,@.c84,@.c85,@.c86,@.c87,@.c88,@.c89,@.c90,@.c91,@.c92,@. c93,@.c94,@.c95,@.c96,@.c97,@.c98,@.c99,@.c100,@.c101,@.c10 2,@.c1
03,@.c104,@.c105,@.c106,@.c107,@.c108,@.c109,@.c110,@.c111 ,@.c112,@.c113,@.c114,@.c115,@.c116,@.c117,@.c118,@.c119,@. c120,@.c121,@.c122,@.c123,@.c124,@.c125,@.c126,@.c127,@.c1 28,@.c129,@.c130,@.c131,@.c132,@.c133,@.c134,@.c135,@.c136 ,@.c137,@.c138,@.c139,@.c140,@.c141,@.c142,@.c143,@.c144,@. c145,
@.c146,@.c147,@.c148,@.c149,@.c150,@.c151,@.c152,@.c153,@.c 154,@.c155,@.c156,@.c157,@.c158,@.c159,@.c160,@.c161,@.c16 2,@.c163,@.c164,@.c165,@.c166,@.c167,@.c168,@.c169,@.c170, @.c171,@.c172,@.c173,@.c174,@.c175,@.c176,@.c177,@.c178,@.c 179,@.c180,@.c181,@.c182,@.c183,@.c184,@.c185,@.c186,@.c18 7,@.c1
88,@.c189,@.c190,@.c191,@.c192,@.c193,@.c194,@.c195,@.c196 ,@.c197,@.c198,@.c199,@.c200,@.c201,@.c202,@.c203,@.c204,@. c205,@.c206,@.c207,@.c208,@.c209,@.c210,@.c211,@.c212,@.c2 13,@.c214,@.c215,@.c216,@.c217,@.c218,@.c219,@.c220,@.c221 ,@.c222,@.c223,@.c224,@.c225,@.c226,@.c227,@.c228,@.c229,@. c230,
@.c231,@.c232,@.c233,@.c234,@.c235,@.c236
output,@.c237,@.c238,@.c239,@.c240,@.c241,@.c242,@.c243,@. c244,@.c245,@.c246,@.c247,@.c248
,@.c1_old,@.c2_old,@.c3_old,@.c4_old,@.c5_old,@.c6_old,@. c7_old,@.c8_old,@.c9_old,@.c10_old,@.c11_old,@.c12_old, @.c13_old,@.c14_old,@.c15_old,@.c16_old,@.c17_old,@.c18_ old,@.c19_old,@.c20_old,@.c21_old,@.c22_old,@.c23_old,@. c24_old,@.c25_old,@.c26_old,@.c27_old,@.c28_old,@.c29_o ld,@.c
30_old,@.c31_old,@.c32_old,@.c33_old,@.c34_old,@.c35_ol d,@.c36_old,@.c37_old,@.c38_old,@.c39_old,@.c40_old,@.c4 1_old,@.c42_old,@.c43_old,@.c44_old,@.c45_old,@.c46_old ,@.c47_old,@.c48_old,@.c49_old,@.c50_old,@.c51_old,@.c52 _old,@.c53_old,@.c54_old,@.c55_old,@.c56_old,@.c57_old, @.c58_
old,@.c59_old,@.c60_old,@.c61_old,@.c62_old,@.c63_old,@. c64_old,@.c65_old,@.c66_old,@.c67_old,@.c68_old,@.c69_o ld,@.c70_old,@.c71_old,@.c72_old,@.c73_old,@.c74_old,@.c 75_old,@.c76_old,@.c77_old,@.c78_old,@.c79_old,@.c80_ol d,@.c81_old,@.c82_old,@.c83_old,@.c84_old,@.c85_old,@.c8 6_old
,@.c87_old,@.c88_old,@.c89_old,@.c90_old,@.c91_old,@.c92 _old,@.c93_old,@.c94_old,@.c95_old,@.c96_old,@.c97_old, @.c98_old,@.c99_old,@.c100_old,@.c101_old,@.c102_old,@.c 103_old,@.c104_old,@.c105_old,@.c106_old,@.c107_old,@.c 108_old,@.c109_old,@.c110_old,@.c111_old,@.c112_old,@.c 113_o
ld,@.c114_old,@.c115_old,@.c116_old,@.c117_old,@.c118_o ld,@.c119_old,@.c120_old,@.c121_old,@.c122_old,@.c123_o ld,@.c124_old,@.c125_old,@.c126_old,@.c127_old,@.c128_o ld,@.c129_old,@.c130_old,@.c131_old,@.c132_old,@.c133_o ld,@.c134_old,@.c135_old,@.c136_old,@.c137_old,@.c138_o ld,@.c
139_old,@.c140_old,@.c141_old,@.c142_old,@.c143_old,@.c 144_old,@.c145_old,@.c146_old,@.c147_old,@.c148_old,@.c 149_old,@.c150_old,@.c151_old,@.c152_old,@.c153_old,@.c 154_old,@.c155_old,@.c156_old,@.c157_old,@.c158_old,@.c 159_old,@.c160_old,@.c161_old,@.c162_old,@.c163_old,@.c 164_o
ld,@.c165_old,@.c166_old,@.c167_old,@.c168_old,@.c169_o ld,@.c170_old,@.c171_old,@.c172_old,@.c173_old,@.c174_o ld,@.c175_old,@.c176_old,@.c177_old,@.c178_old,@.c179_o ld,@.c180_old,@.c181_old,@.c182_old,@.c183_old,@.c184_o ld,@.c185_old,@.c186_old,@.c187_old,@.c188_old,@.c189_o ld,@.c
190_old,@.c191_old,@.c192_old,@.c193_old,@.c194_old,@.c 195_old,@.c196_old,@.c197_old,@.c198_old,@.c199_old,@.c 200_old,@.c201_old,@.c202_old,@.c203_old,@.c204_old,@.c 205_old,@.c206_old,@.c207_old,@.c208_old,@.c209_old,@.c 210_old,@.c211_old,@.c212_old,@.c213_old,@.c214_old,@.c 215_o
ld,@.c216_old,@.c217_old,@.c218_old,@.c219_old,@.c220_o ld,@.c221_old,@.c222_old,@.c223_old,@.c224_old,@.c225_o ld,@.c226_old,@.c227_old,@.c228_old,@.c229_old,@.c230_o ld,@.c231_old,@.c232_old,@.c233_old,@.c234_old,@.c235_o ld,@.c236_old,@.c237_old,@.c238_old,@.c239_old,@.c240_o ld,@.c
241_old,@.c242_old,@.c243_old,@.c244_old,@.c245_old,@.c 246_old,@.c247_old,@.c248_old
, @.bitmap
plus, I also get an error that says:
Server: Msg 137, Level 15, State 1, Line 5
Must declare the variable '@.c30o'.
If you have any ideas what might be causing this, or if you have any further
advice on what I can do to resolve the problem, I would love to hear it.
Thanks for all your input.
-b.
"Paul Ibison" wrote:

> Bobby,
> there's nothing I can see wrong in the trigger, and no reference to text
> columns. As you are on sp4 (publisher, distributor and subscriber?), this is
> baffling. I'd run profiler to see where it gets to, and fails, in the
> synchronization, and also enable logging to see what that reveals
> (http://support.microsoft.com/?id=312292).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Immediate Updating Subscription - Hangs

We have installed SQL Server 2000 on a new server running Windows 2003.
This server is the distirbution Server. It also contains a one database. THis database subscibes to data from other servers and publishes data to other servers.
One of the publications is an updateable subscription. Ths subscription is pushed to the subscriber.
The replication works fine from publisher to subscriber, but when the subscriber attempts an update it just hangs. I see no blocking on either server.
DTC is running on both servers. I executed sp_link_pubisher on the subscriber to populate the publisher login and password in the MSsubscription_properties table.
I must be missing something else.
Can anyone help?
When you say it hangs do you mean it is slow or it doesn't work?
Do you eventually get an error message saying "Access denied"?
If you get this error message, DTC on your Subcriber cannot talk to your Publisher. Its either a name resolution problem, or more likely a permissions problem. Check the accound your SQL Server agent runs under on the Susbcriber and make sure it is part o
f the local administrators group on your Publisher.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Thank you Hilary for your prompt reply.
WHat I mean by it hangs is it never completes. And it doesn't appear to time out.
I do feel it is something to do with DTC. I will investigate the account SQL Server agent runs under on the Susbcriber
and make sure it is part of the local administrators group on the Publisher.
If you cant hink of anything else in the meantime, I am all ears.
Thanks again.
|||I verified that the account the SQL Server agent runs under on the Susbcriber is part of the local administrators group on the Publisher.
I do not get an "Access denied" error. I get no response at all.
Can you suggest anything else?
-- Hilary Cotter wrote: --
When you say it hangs do you mean it is slow or it doesn't work?
Do you eventually get an error message saying "Access denied"?
If you get this error message, DTC on your Subcriber cannot talk to your Publisher. Its either a name resolution problem, or more likely a permissions problem. Check the accound your SQL Server agent runs under on the Susbcriber and make sure it is p
art of the local administrators group on your Publisher.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||what does your msdtc log say. It can be found in
C:\WINDOWS\System32\MSDtc
"casey@.newsgroups.nospam" <anonymous@.discussions.microsoft.com> wrote in
message news:24BA0D29-2D9B-417D-8730-3B2920FBF11B@.microsoft.com...
> I verified that the account the SQL Server agent runs under on the
Susbcriber is part of the local administrators group on the Publisher.
> I do not get an "Access denied" error. I get no response at all.
> Can you suggest anything else?
> -- Hilary Cotter wrote: --
> When you say it hangs do you mean it is slow or it doesn't work?
> Do you eventually get an error message saying "Access denied"?
> If you get this error message, DTC on your Subcriber cannot talk to
your Publisher. Its either a name resolution problem, or more likely a
permissions problem. Check the accound your SQL Server agent runs under on
the Susbcriber and make sure it is part of the local administrators group on
your Publisher.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
|||Helen,
The DTC log contains mostly non-displayable characters. Same for the Trace. Is there a tool I should be using to read this file?
|||My problem is resolved.
The trouble was that with Windows 2003, Enable network DTC access is not "enabled" by default. Once I enabled this, everything works fine.
Thanks for your help.

Immediate Updating Subscriber not Queuing updates

Hey all,
I have a problem with replication that I was hoping someone might be able to
shed some light on. Heres the scenario.
Server A, setup as a publisher + distributor. A publication was created and
marked for transaction replication with immediate updating with queued as a
failover.
Server B is the only subscriber. I had to run sp_link_publication in order
to get it to work.
I can successfully replication changes from A - B, and B - A. If B goes
down, and comes back up, it will re-sync no problem. My problem is that when
A goes down, I am not getting anything saved into the queue. When I try to
do any updating, all I get is 'SQL Server does not exist, etc'. There are no
other triggers on the table.
Thoughts?
One more thing, this is SQL 2000 SP3 (.818)
"MJM" wrote:

> Hey all,
> I have a problem with replication that I was hoping someone might be able to
> shed some light on. Heres the scenario.
> Server A, setup as a publisher + distributor. A publication was created and
> marked for transaction replication with immediate updating with queued as a
> failover.
> Server B is the only subscriber. I had to run sp_link_publication in order
> to get it to work.
> I can successfully replication changes from A - B, and B - A. If B goes
> down, and comes back up, it will re-sync no problem. My problem is that when
> A goes down, I am not getting anything saved into the queue. When I try to
> do any updating, all I get is 'SQL Server does not exist, etc'. There are no
> other triggers on the table.
> Thoughts?
|||you have to manually failover your subscriber from using immediate updating
to using queued updating. There is no automatic failover.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"MJM" <MJM@.discussions.microsoft.com> wrote in message
news:EDD81CC4-9696-4AAB-A468-B8734DC08473@.microsoft.com...[vbcol=seagreen]
> One more thing, this is SQL 2000 SP3 (.818)
> "MJM" wrote:
able to[vbcol=seagreen]
and[vbcol=seagreen]
as a[vbcol=seagreen]
order[vbcol=seagreen]
when[vbcol=seagreen]
to[vbcol=seagreen]
are no[vbcol=seagreen]

immediate updating isnt working

Im doing some testing on Immediate Updating Transactional Replication. I
generated a script to drop the Subscription and Article to a particular
table to add an Identity column on it. After that, I re-added the table to
the Publication and re-snapshotted the table. No problem and all is good at
this point. However now when I insert a record into the Subscriber, the row
isn't getting back to the Publisher *for this table only.* It is working for
other tables though. I scripted out the sp_addarticle for both this table
and a table that the immediate updating is working properly on and they are
identicle with the exception of the names. Whats the dealio? All ideas are
appreciated.
SQL2K SP3
TIA, ChrisR
I figured out whats up. The triggers for Insert, Update, and Deletes arent
being created like they were during the original snapshot. Does anyone know
why?
"ChrisR" <bla@.noemail.com> wrote in message
news:u58jVxkLFHA.2796@.tk2msftngp13.phx.gbl...
> Im doing some testing on Immediate Updating Transactional Replication. I
> generated a script to drop the Subscription and Article to a particular
> table to add an Identity column on it. After that, I re-added the table to
> the Publication and re-snapshotted the table. No problem and all is good
at
> this point. However now when I insert a record into the Subscriber, the
row
> isn't getting back to the Publisher *for this table only.* It is working
for
> other tables though. I scripted out the sp_addarticle for both this table
> and a table that the immediate updating is working properly on and they
are
> identicle with the exception of the names. Whats the dealio? All ideas are
> appreciated.
> --
> SQL2K SP3
> TIA, ChrisR
>
|||how did you resync after making changes to your subscription.
I think it would be best to recreate your snapshot - it sounds like your
metadata is out of sync.
Hilary Cotter
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
"ChrisR" <bla@.noemail.com> wrote in message
news:%23fFESGmLFHA.1476@.TK2MSFTNGP09.phx.gbl...
> I figured out whats up. The triggers for Insert, Update, and Deletes arent
> being created like they were during the original snapshot. Does anyone
know[vbcol=seagreen]
> why?
>
> "ChrisR" <bla@.noemail.com> wrote in message
> news:u58jVxkLFHA.2796@.tk2msftngp13.phx.gbl...
to[vbcol=seagreen]
> at
> row
> for
table[vbcol=seagreen]
> are
are
>
|||I re-snapshotted the table.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uKk9rboLFHA.580@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> how did you resync after making changes to your subscription.
> I think it would be best to recreate your snapshot - it sounds like your
> metadata is out of sync.
> --
> Hilary Cotter
> 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
> "ChrisR" <bla@.noemail.com> wrote in message
> news:%23fFESGmLFHA.1476@.TK2MSFTNGP09.phx.gbl...
arent[vbcol=seagreen]
> know
I[vbcol=seagreen]
particular[vbcol=seagreen]
table[vbcol=seagreen]
> to
good[vbcol=seagreen]
the[vbcol=seagreen]
working[vbcol=seagreen]
> table
they
> are
>
|||I realize that - but I think you need to re-snapshot the entire publication.
I had problems like this with merge replication, which could only be solved
by a complete resnapshotting and re-synchronization.
Hilary Cotter
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
"ChrisR" <bla@.noemail.com> wrote in message
news:eV5KqXvLFHA.3512@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> I re-snapshotted the table.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uKk9rboLFHA.580@.TK2MSFTNGP15.phx.gbl...
> arent
Replication.[vbcol=seagreen]
> I
> particular
> table
> good
> the
> working
> they
ideas
>
|||I was afraid of that. Just wanted to make sure we were on the same page.
Thanks Hillary.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:#BL4sBwLFHA.2796@.tk2msftngp13.phx.gbl...
> I realize that - but I think you need to re-snapshot the entire
publication.
> I had problems like this with merge replication, which could only be
solved[vbcol=seagreen]
> by a complete resnapshotting and re-synchronization.
> --
> Hilary Cotter
> 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
> "ChrisR" <bla@.noemail.com> wrote in message
> news:eV5KqXvLFHA.3512@.TK2MSFTNGP15.phx.gbl...
your[vbcol=seagreen]
anyone[vbcol=seagreen]
> Replication.
is[vbcol=seagreen]
Subscriber,[vbcol=seagreen]
this
> ideas
>
sql

Immediate update/Queued update problem

I'm having a problem creating immediate update/queued update transactional
replication on my test servers. I go through the wizards and create the
publication using the advanced mode and set it up. It appears to work fine.
When I create the subscription, nothing happens. I cannot get the inital
snapshot to work and it will not create the triggers and stored procedures.
Anyone have any ideas?
Connect to your publisher in EM, expand Replication Monitor, expand the
replication agents, expand the distribution agent folder, locate the
distribution agent for your publication, right click on it, select Agent
Properties and change the job owner to sa.
Right click on your distribution agent and restart it. You may have to also
do this for the snapshot agent.
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:5289270A-7B92-45F6-9887-A78D66ACB061@.microsoft.com...
> I'm having a problem creating immediate update/queued update transactional
> replication on my test servers. I go through the wizards and create the
> publication using the advanced mode and set it up. It appears to work
> fine.
> When I create the subscription, nothing happens. I cannot get the inital
> snapshot to work and it will not create the triggers and stored
> procedures.
> Anyone have any ideas?
|||Thank for your help.
I found that there was an issue with the computer name. The computer had
gotten its name changed and after rectifying that the triggers were written.
So.. Now I have a problem on the subscriber writing back in a two phase
commit back to the publisher. I get an error during the insert that indicates
that the sa password cannot connect. I traced the call and I see the call to
openserver where the sa login is used and the password is set as an empty
string. I cannot find where to set the login and password for the writeback.
Could someone help me out?
"Hilary Cotter" wrote:

> Connect to your publisher in EM, expand Replication Monitor, expand the
> replication agents, expand the distribution agent folder, locate the
> distribution agent for your publication, right click on it, select Agent
> Properties and change the job owner to sa.
> Right click on your distribution agent and restart it. You may have to also
> do this for the snapshot agent.
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:5289270A-7B92-45F6-9887-A78D66ACB061@.microsoft.com...
>
>

Immediate Synchronization?

Is it possible in SQL Server to have replication happen immediately as
changes are made. That is, a change is made on server A, and that change is
automatically applied to server B, rather than the replication happening at
set intervals?
Thanks.
Neil,
this is possible from subscriber to publisher in the case of immediate
updating subscribers. In other cases you have to accept a degree of latency,
which can be minimised by optimisation.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Consider distributed transactions for this. You can also set your polling
interval on your log reader and distribution agent to 1s, and your latency
will be around 10-20s.
Hilary Cotter
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
"Neil" <nospam@.nospam.net> wrote in message
news:8KRTe.2584$9x2.1236@.newsread3.news.pas.earthl ink.net...
> Is it possible in SQL Server to have replication happen immediately as
> changes are made. That is, a change is made on server A, and that change
is
> automatically applied to server B, rather than the replication happening
at
> set intervals?
> Thanks.
>
|||I'm using an Access 2000 front end which updates a lot of data through the
ODBC driver. Thus, I wouldn't be able to use distributed transactions in
every situation. Anything else that can immediately update one server from
the other?
Thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e6RW67FtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> Consider distributed transactions for this. You can also set your polling
> interval on your log reader and distribution agent to 1s, and your latency
> will be around 10-20s.
> --
> Hilary Cotter
> 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
> "Neil" <nospam@.nospam.net> wrote in message
> news:8KRTe.2584$9x2.1236@.newsread3.news.pas.earthl ink.net...
> is
> at
>
|||Distributed transactions are a coded solution where you do something like
this
begin trans
insert into server1.database1.dbo.table1 (charcol) values ('test')
insert into msaccessdb.table1 (charcol) values ('test')
if @.@.errornum<>0
rollback tran
else
commit tran
It can be done using MS DTC as well.
Hilary Cotter
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
"Neil" <nospam@.nospam.net> wrote in message
news:Jg9Ue.7705$4P5.4425@.newsread2.news.pas.earthl ink.net...[vbcol=seagreen]
> I'm using an Access 2000 front end which updates a lot of data through the
> ODBC driver. Thus, I wouldn't be able to use distributed transactions in
> every situation. Anything else that can immediately update one server from
> the other?
> Thanks.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:e6RW67FtFHA.3252@.TK2MSFTNGP10.phx.gbl...
polling[vbcol=seagreen]
latency[vbcol=seagreen]
change[vbcol=seagreen]
happening
>
|||What I mean is that I'm using bound forms in the MDB to a large extent.
Updating of the data through the forms is handled automatically through the
ODBC driver without code.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uAWYaJUtFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Distributed transactions are a coded solution where you do something like
> this
> begin trans
> insert into server1.database1.dbo.table1 (charcol) values ('test')
> insert into msaccessdb.table1 (charcol) values ('test')
> if @.@.errornum<>0
> rollback tran
> else
> commit tran
> It can be done using MS DTC as well.
>
> --
> Hilary Cotter
> 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
> "Neil" <nospam@.nospam.net> wrote in message
> news:Jg9Ue.7705$4P5.4425@.newsread2.news.pas.earthl ink.net...
> polling
> latency
> change
> happening
>
|||Neil wrote:
> What I mean is that I'm using bound forms in the MDB to a large extent.
> Updating of the data through the forms is handled automatically through the
> ODBC driver without code.
>
Hi Neil,
I know that in an ideal world, you'd like your two servers to be
perfectly synchronised. Can we explore the reasons for such a desire?
If it is for failover, it may be best to look into what clustering can
do for you. That is, if you have the $s to spend, since it quickly gets
expensive. Anything you "roll-your-own" with (e.g. having an update
trigger on the server A tables that updates the server B tables) can
actually make your system *more* brittle (if server B goes down, you'll
probably lose the ability to change anything in server A).
So, my first question is why do you want this immediate
synchronisation? We may be able to offer better ideas...
Damien

Immediate Synchronization?

Thanks.Consider distributed transactions for this. You can also set your polling
interval on your log reader and distribution agent to 1s, and your latency
will be around 10-20s.

--
Hilary Cotter
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
"Neil" <nospam@.nospam.net> wrote in message
news:8KRTe.2584$9x2.1236@.newsread3.news.pas.earthl ink.net...
> Is it possible in SQL Server to have replication happen immediately as
> changes are made. That is, a change is made on server A, and that change
is
> automatically applied to server B, rather than the replication happening
at
> set intervals?
> Thanks.|||I'm using an Access 2000 front end which updates a lot of data through the
ODBC driver. Thus, I wouldn't be able to use distributed transactions in
every situation. Anything else that can immediately update one server from
the other?

Thanks.

"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e6RW67FtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> Consider distributed transactions for this. You can also set your polling
> interval on your log reader and distribution agent to 1s, and your latency
> will be around 10-20s.
> --
> Hilary Cotter
> 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
> "Neil" <nospam@.nospam.net> wrote in message
> news:8KRTe.2584$9x2.1236@.newsread3.news.pas.earthl ink.net...
>> Is it possible in SQL Server to have replication happen immediately as
>> changes are made. That is, a change is made on server A, and that change
> is
>> automatically applied to server B, rather than the replication happening
> at
>> set intervals?
>>
>> Thanks.
>>
>>|||Distributed transactions are a coded solution where you do something like
this
begin trans
insert into server1.database1.dbo.table1 (charcol) values ('test')
insert into msaccessdb.table1 (charcol) values ('test')
if @.@.errornum<>0
rollback tran
else
commit tran

It can be done using MS DTC as well.

--
Hilary Cotter
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
"Neil" <nospam@.nospam.net> wrote in message
news:Jg9Ue.7705$4P5.4425@.newsread2.news.pas.earthl ink.net...
> I'm using an Access 2000 front end which updates a lot of data through the
> ODBC driver. Thus, I wouldn't be able to use distributed transactions in
> every situation. Anything else that can immediately update one server from
> the other?
> Thanks.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:e6RW67FtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> > Consider distributed transactions for this. You can also set your
polling
> > interval on your log reader and distribution agent to 1s, and your
latency
> > will be around 10-20s.
> > --
> > Hilary Cotter
> > 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
> > "Neil" <nospam@.nospam.net> wrote in message
> > news:8KRTe.2584$9x2.1236@.newsread3.news.pas.earthl ink.net...
> >> Is it possible in SQL Server to have replication happen immediately as
> >> changes are made. That is, a change is made on server A, and that
change
> > is
> >> automatically applied to server B, rather than the replication
happening
> > at
> >> set intervals?
> >>
> >> Thanks.
> >>
> >>|||What I mean is that I'm using bound forms in the MDB to a large extent.
Updating of the data through the forms is handled automatically through the
ODBC driver without code.

"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uAWYaJUtFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Distributed transactions are a coded solution where you do something like
> this
> begin trans
> insert into server1.database1.dbo.table1 (charcol) values ('test')
> insert into msaccessdb.table1 (charcol) values ('test')
> if @.@.errornum<>0
> rollback tran
> else
> commit tran
> It can be done using MS DTC as well.
>
> --
> Hilary Cotter
> 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
> "Neil" <nospam@.nospam.net> wrote in message
> news:Jg9Ue.7705$4P5.4425@.newsread2.news.pas.earthl ink.net...
>> I'm using an Access 2000 front end which updates a lot of data through
>> the
>> ODBC driver. Thus, I wouldn't be able to use distributed transactions in
>> every situation. Anything else that can immediately update one server
>> from
>> the other?
>>
>> Thanks.
>>
>> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
>> news:e6RW67FtFHA.3252@.TK2MSFTNGP10.phx.gbl...
>> > Consider distributed transactions for this. You can also set your
> polling
>> > interval on your log reader and distribution agent to 1s, and your
> latency
>> > will be around 10-20s.
>>> > --
>> > Hilary Cotter
>> > 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
>> > "Neil" <nospam@.nospam.net> wrote in message
>> > news:8KRTe.2584$9x2.1236@.newsread3.news.pas.earthl ink.net...
>> >> Is it possible in SQL Server to have replication happen immediately as
>> >> changes are made. That is, a change is made on server A, and that
> change
>> > is
>> >> automatically applied to server B, rather than the replication
> happening
>> > at
>> >> set intervals?
>> >>
>> >> Thanks.
>> >>
>> >>
>>>>
>>|||Neil wrote:
> What I mean is that I'm using bound forms in the MDB to a large extent.
> Updating of the data through the forms is handled automatically through the
> ODBC driver without code.
Hi Neil,

I know that in an ideal world, you'd like your two servers to be
perfectly synchronised. Can we explore the reasons for such a desire?

If it is for failover, it may be best to look into what clustering can
do for you. That is, if you have the $s to spend, since it quickly gets
expensive. Anything you "roll-your-own" with (e.g. having an update
trigger on the server A tables that updates the server B tables) can
actually make your system *more* brittle (if server B goes down, you'll
probably lose the ability to change anything in server A).

So, my first question is why do you want this immediate
synchronisation? We may be able to offer better ideas...

Damien

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

Impact of HD (de)fragmentation on SQL Server performance

Impact of HD (de)fragmentation on SQL Server performance

Impact of HD (de)fragmentation on SQL Server performance

Impact of Existing replication

Impact of empty tables on database size and performance

impact of changing recovery model on a database that is currently"live"

impact of changing recovery model on a database that is currently

Impact of Changing Authentication Mode

Impact of AD Migration on Windows authenticated SQL user account

Impact of "Rows per batch" on OLE DB Destination?

Impact if Change Server ame

Impact if Change Server ame

Imorting Excel 07 data into SQL Server 2005

Immutability of procedure parameters

immediate updating triggers have "incorrect syntax near ')'."

Immediate Updating Subscription - Hangs

Immediate Updating Subscriber not Queuing updates

immediate updating isnt working

Immediate update/Queued update problem

Immediate Synchronization?

Immediate Synchronization?

Sql Ignoring spaces

Blog Archive

About Me