Hello alltogether,
i'm working on a database-plattform which should work with
fail-over-technics.
Therefor 2 identic machines with internal RAID are available, which work
in single-host-netloadbalance on win2003-server.
Because of the internal RAID and caused by costs, a solution without
external SCSI-RAID and win2003-clustering-mechanism is looked for.
First i tried to use merge-replication but the rowguid-column, which is
added, makes the application unable to use the database (sql-statements
without column-specification). The application is bought and not easy
adaptable.
Second i tried transaction-replication, which seamed to work, but no
key- and identity-attributes are replicated. When the first machine now
stops and second gets master, there are problems caused by the missing
identities (auto-increment). I got errors caused by triggers too.
Manual copy of the structure with key- and identity-attributes and
triggers causes new problems when the replication is active (i.e. a
replication insert datas in a table with a trigger, the trigger on the
master takes effect, the replication tries to copy the effected datas,
but the trigger on the slave already work with the datas).
Actual way is a differential copy periodical from master to slave, but
is not nice and just a periodical actual slave.
Any ideas for more solutions?
PS: Sorry for X-Post, but i'm not sure, if there are "active" reader of
"m.p.s.r"
Thanks & Greets
Marko Damaschke
trainee at T-Systems-MMS GmbH, Dresden, Saxony, Germany
student of applied computer science at Chemnitz University of Technology
Hello,
i got an answer from Paul Ibison via a website-based newsforum, but
don't want to get a profile there - so the answer here.
Thanks for the idea via Log Shipping but i see there a problem with the
state of standby-database. The database has to be in "NORECOVERY-Mode",
when i understood it correct, while the whole Log-Shipping-process.
But how to "trigger" it, when suddenly the primary machine crashes?
Somehow the recover-mode has to be started.
Write-access is necessary and without manual actions the db is read-only.
We need automated failover without manual administrator actions.
Thanks & Greets
Marko Damaschke
Trainee at T-Systems-MMS GmbH, Dresden, Saxony, Germany
Student of applied computer science at Chemnitz University of Technology
|||Marko,
the only automatic failover is from clustering. Log-
shipping, replication each have a manual failover. SQL
Server 2005 has database mirroring, but this is a few
months away from release yet. There are 3rd party tools -
DataMirror, but I don't know offhand if these offer
automatic failover.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Showing posts with label identity. Show all posts
Showing posts with label identity. Show all posts
Friday, March 23, 2012
Tuesday, March 20, 2012
Replication problem
How to perform replication suppose on two tables but NOT for all columns'
Suppose if i have IDENTITY column in table i can use option NOT FOR
REPLICATION, however in case my column doesn't identity, so '
Message posted via http://www.webservertalk.comI THINK IT'S ONLY FOR identity COLUMNS
Message posted via http://www.webservertalk.com
Suppose if i have IDENTITY column in table i can use option NOT FOR
REPLICATION, however in case my column doesn't identity, so '
Message posted via http://www.webservertalk.comI THINK IT'S ONLY FOR identity COLUMNS
Message posted via http://www.webservertalk.com
Friday, March 9, 2012
Replication of check constraints
I have tables that are replicated using transactional and merge replication.As a result I am unable to use automatic identity management as transactional replication doesn’t seem to understand it.
Therefore I have implemented a version of the automatic mechanisms that seems to work in a hybrid environment.It is based on a central table that holds the maximum identity for each table that has been issued to date.Valid identity ranges are issued to each publisher and subscriber as needed in a similar way to the automatic mechanisms and tables are reseeded as needed.
I want to enforce the ranges in a similar way to the automatic mechanism using a check constraint similar to this:
alter table [dbo].[test1] with NOCHECK add CONSTRAINT repl_identity_range_48DF13ED_D503_4F5C_AED9_4E504D03E752 check NOT FOR REPLICATION (([id] > 10001 and [id] <= 20001) or ([id] > 50001 and [id] <= 70001))
This works OK on a client subscriber, but if the change is made on the publisher, then the alter statement itself is replicated out to all clients – which is not what is wanted.I have traced the automatic mechanisms using profiler and they issue an alter statement as above – following dropping of the constraint – but the check constraint isn’t replicated. I can't see how this is achieved.
How do I stop the check constraint being replicated?
The article property schema option can be set to stop replicating check constraints, but this seems to have no effect.If the publication property replicate_ddl is set to 0 then I do see the behaviour that I want.However, I do need to be able to replicate most schema changes due to upgrades etc – so this doesn’t look like a viable option – except possibly for the transactional publication.
Any help would be much appreciated
Thanks
aero1
I think I have found a way forward. I had been considering the publication setup as pretty static, but on subsequent thoughts I realised that there is no reason why I can't change the replicate_ddl property as and when I need to
I have tested out the following and it seems to work (Other unrelated schema changes get synched correctly)
begin tran
exec sp_changemergepublication to set replicate_ddl to 0
make the check constraint change
exec sp_changemergepublication to set replicate_ddl back to 1
commit
Is there any problem with this approach?
|||This is the supported way of not wanting to replicate DDL-type statements (enable/disable publication property replicate_ddl). Please let us know if you run into problems.|||Thanks for the info
aero1
Tuesday, February 21, 2012
Replication Identity Ranges
Hello,
We have set up Merge replication, using sql 2000. We have one publisher and
four subscribers. When setting up the articles on the publisher, we manually
set an identity range for one of the tables. The range size at the publisher
and the subscriber is set to 5000, and the assign new range is set to 95%.
When looking at the design of the table and ckecking the replication
constraint, on the first subscriber, the range is 103000 - 108000. The next
subscriber has a range of 105000 - 111000. All the subscribers seem to have
a range that overlap with each other. The table has an identity column and
it is set to Not for Replication.
It is my understanding that each subscriber should have a range that do not
overlap. I have checked other tables on the subscribers and they each have
ranges that do not overlap.
I hope I have given enough information, and I hope someone could shed some
light on this.
Thanks in advance
Identity ranges should never overlap.
Did you use the same snapshot for each subscriber?
|||Hello,
we used the same snapshot for each subscriber. This problem has now been
corrected. We manually modified the MSrepl_identity_range table on each
subscriber, then forced replication. This fixed the problem.
"Jim Breffni" wrote:
> Identity ranges should never overlap.
> Did you use the same snapshot for each subscriber?
>
We have set up Merge replication, using sql 2000. We have one publisher and
four subscribers. When setting up the articles on the publisher, we manually
set an identity range for one of the tables. The range size at the publisher
and the subscriber is set to 5000, and the assign new range is set to 95%.
When looking at the design of the table and ckecking the replication
constraint, on the first subscriber, the range is 103000 - 108000. The next
subscriber has a range of 105000 - 111000. All the subscribers seem to have
a range that overlap with each other. The table has an identity column and
it is set to Not for Replication.
It is my understanding that each subscriber should have a range that do not
overlap. I have checked other tables on the subscribers and they each have
ranges that do not overlap.
I hope I have given enough information, and I hope someone could shed some
light on this.
Thanks in advance
Identity ranges should never overlap.
Did you use the same snapshot for each subscriber?
|||Hello,
we used the same snapshot for each subscriber. This problem has now been
corrected. We manually modified the MSrepl_identity_range table on each
subscriber, then forced replication. This fixed the problem.
"Jim Breffni" wrote:
> Identity ranges should never overlap.
> Did you use the same snapshot for each subscriber?
>
Replication identity ranges
Any ideas!
I create a publication of one table with an identity range of 10
I create a subscription for this table with an identity range of 10
I use a new range percentage value of 50
At the publisher I can insert rows 1-19 (should be 1-5 ?)
At the subscriber I can insert rows 21-29 (should be 11-15 ?)
When the initial snapshot is created the next seed value alters to 30 and
when a merge is performed the publisher will insert rows 32-39 and the
subscriber will insert rows 42-49.
I have two observations.
1 the range percentage value does not seem to work at any value I try.
2. The ID values ending in 0 and 1 are always omitted regardless of what
identity ranges I try.
I am using SP3.
the ranges are updated every time a transaction is complete, so if you do an
update in a batch there will be no time for SQL server to adjust the ranges.
Normally you get a message saying that the indentity range is full. No
matter what value you put in here, if you do a batch the ranges will be
adjusted when the batch is complete. So you if you blow your percentage in a
batch, SQL will adjust it when the batch completes.
You should not adjust the indenty range on the Subscriber manually, it
should be done on the subscriber. Drop your subscriber, right click on your
publication, in the articles tab, click on the three ellipses to the right
of your article you wish to add identity range management to. Go to the
Identity Range tab. Make your changes here. If you do not do this, the range
on the subscriber will be blown away by the snapshot. Its not clear to me
from your post whether you did this or not.
"robham" <nospam@.co.uk> wrote in message
news:%23mC5FHiJEHA.3412@.TK2MSFTNGP09.phx.gbl...
> Any ideas!
>
> I create a publication of one table with an identity range of 10
> I create a subscription for this table with an identity range of 10
> I use a new range percentage value of 50
>
> At the publisher I can insert rows 1-19 (should be 1-5 ?)
> At the subscriber I can insert rows 21-29 (should be 11-15 ?)
>
> When the initial snapshot is created the next seed value alters to 30 and
> when a merge is performed the publisher will insert rows 32-39 and the
> subscriber will insert rows 42-49.
>
> I have two observations.
>
> 1 the range percentage value does not seem to work at any value I try.
>
> 2. The ID values ending in 0 and 1 are always omitted regardless of what
> identity ranges I try.
>
>
> I am using SP3.
>
|||Hilary,
I hope this explains it better.
Using the Enterprise Manager, I created table1 db1 on server1. This
contained two columns, tableid which is an identity column and descrip which
is a simple varchar column. I then created a publication of db1, checking
the identity ranges checkbox of the table article properties and assigning
values of 10 to the publisher and subscriber, and a value of 50 to the new
range column.
As soon as I created a subscription to repdb1 (using the create database
option) on server2 using a snapshot, the next identity seed value at the
publisher went from 20 to 30.
It was then I found that I could enter 19 rows into table1 (identity ranges
1-19) on db1 and 9 rows (identity ranges 21-29) in repdb1 before I needed to
start the merge agent.
This seems to have ignored the 50% range allowance. When I continued to
insert rows at db1 the identity range numbers went from 32-39 and at repdb1
they went from 42-49. Again this seems to have ignored the range allowance.
Advice please.
"robham" <nospam@.co.uk> wrote in message
news:%23mC5FHiJEHA.3412@.TK2MSFTNGP09.phx.gbl...
> Any ideas!
>
> I create a publication of one table with an identity range of 10
> I create a subscription for this table with an identity range of 10
> I use a new range percentage value of 50
>
> At the publisher I can insert rows 1-19 (should be 1-5 ?)
> At the subscriber I can insert rows 21-29 (should be 11-15 ?)
>
> When the initial snapshot is created the next seed value alters to 30 and
> when a merge is performed the publisher will insert rows 32-39 and the
> subscriber will insert rows 42-49.
>
> I have two observations.
>
> 1 the range percentage value does not seem to work at any value I try.
>
> 2. The ID values ending in 0 and 1 are always omitted regardless of what
> identity ranges I try.
>
>
> I am using SP3.
>
I create a publication of one table with an identity range of 10
I create a subscription for this table with an identity range of 10
I use a new range percentage value of 50
At the publisher I can insert rows 1-19 (should be 1-5 ?)
At the subscriber I can insert rows 21-29 (should be 11-15 ?)
When the initial snapshot is created the next seed value alters to 30 and
when a merge is performed the publisher will insert rows 32-39 and the
subscriber will insert rows 42-49.
I have two observations.
1 the range percentage value does not seem to work at any value I try.
2. The ID values ending in 0 and 1 are always omitted regardless of what
identity ranges I try.
I am using SP3.
the ranges are updated every time a transaction is complete, so if you do an
update in a batch there will be no time for SQL server to adjust the ranges.
Normally you get a message saying that the indentity range is full. No
matter what value you put in here, if you do a batch the ranges will be
adjusted when the batch is complete. So you if you blow your percentage in a
batch, SQL will adjust it when the batch completes.
You should not adjust the indenty range on the Subscriber manually, it
should be done on the subscriber. Drop your subscriber, right click on your
publication, in the articles tab, click on the three ellipses to the right
of your article you wish to add identity range management to. Go to the
Identity Range tab. Make your changes here. If you do not do this, the range
on the subscriber will be blown away by the snapshot. Its not clear to me
from your post whether you did this or not.
"robham" <nospam@.co.uk> wrote in message
news:%23mC5FHiJEHA.3412@.TK2MSFTNGP09.phx.gbl...
> Any ideas!
>
> I create a publication of one table with an identity range of 10
> I create a subscription for this table with an identity range of 10
> I use a new range percentage value of 50
>
> At the publisher I can insert rows 1-19 (should be 1-5 ?)
> At the subscriber I can insert rows 21-29 (should be 11-15 ?)
>
> When the initial snapshot is created the next seed value alters to 30 and
> when a merge is performed the publisher will insert rows 32-39 and the
> subscriber will insert rows 42-49.
>
> I have two observations.
>
> 1 the range percentage value does not seem to work at any value I try.
>
> 2. The ID values ending in 0 and 1 are always omitted regardless of what
> identity ranges I try.
>
>
> I am using SP3.
>
|||Hilary,
I hope this explains it better.
Using the Enterprise Manager, I created table1 db1 on server1. This
contained two columns, tableid which is an identity column and descrip which
is a simple varchar column. I then created a publication of db1, checking
the identity ranges checkbox of the table article properties and assigning
values of 10 to the publisher and subscriber, and a value of 50 to the new
range column.
As soon as I created a subscription to repdb1 (using the create database
option) on server2 using a snapshot, the next identity seed value at the
publisher went from 20 to 30.
It was then I found that I could enter 19 rows into table1 (identity ranges
1-19) on db1 and 9 rows (identity ranges 21-29) in repdb1 before I needed to
start the merge agent.
This seems to have ignored the 50% range allowance. When I continued to
insert rows at db1 the identity range numbers went from 32-39 and at repdb1
they went from 42-49. Again this seems to have ignored the range allowance.
Advice please.
"robham" <nospam@.co.uk> wrote in message
news:%23mC5FHiJEHA.3412@.TK2MSFTNGP09.phx.gbl...
> Any ideas!
>
> I create a publication of one table with an identity range of 10
> I create a subscription for this table with an identity range of 10
> I use a new range percentage value of 50
>
> At the publisher I can insert rows 1-19 (should be 1-5 ?)
> At the subscriber I can insert rows 21-29 (should be 11-15 ?)
>
> When the initial snapshot is created the next seed value alters to 30 and
> when a merge is performed the publisher will insert rows 32-39 and the
> subscriber will insert rows 42-49.
>
> I have two observations.
>
> 1 the range percentage value does not seem to work at any value I try.
>
> 2. The ID values ending in 0 and 1 are always omitted regardless of what
> identity ranges I try.
>
>
> I am using SP3.
>
Subscribe to:
Posts (Atom)