Many to Many replication in sql server 2000

Hi

I'm trying to determine if it is possible to do many to many replication in sql server 2000.

What i basically want is to have n databases share the same basedate (share a common database) and allow updates in any database to be replicated to all the other databases (with a simple conflict resolution, like last update wins).

My goal is total autonomy, without a single point of failure. If any node goes down, the other nodes will continue to work and continue to replicate their data to the remaining nodes. When a node comes back up it will catch up with the over nodes (or get reinitialized if it was a serious crash).

The amount of data i want to replicate is not that big (less than 100MB) and does not change that often. All servers are sql server 2000 instances connected by a gigabit network and the number of nodes involved is less than 10. Some latency is also acceptable.

the question is: is this at all possible? I have read i bit in 'SQL Server High Availability By Paul Bertucci' and some other resources and it looks like a multiple publishers or multiple subscribers with merge replication setup should work, but i'm not too sure if it will work for n > 2 nodes (where all nodes publish and subscribe to each other) and it also mentions constraints on which data a given node is allowed to update (i hope this could be handled by simple conflict resolution).

And if it is not possible in 2000, could it be accomplished en 2005?

TIA Jens

Unfortunately you cannot do peer to peer replication you must have a publisher / subscriber model. You can have a cluster of servers at the center for availablilty in SQL 2005 i believe.

However one server (or cluster of servers) needs to be the master (publisher) and all the others subscribers.

Martin

|||

Martin_McNally wrote:

Unfortunately you cannot do peer to peer replication you must have a publisher / subscriber model. You can have a cluster of servers at the center for availablilty in SQL 2005 i believe.

However one server (or cluster of servers) needs to be the master (publisher) and all the others subscribers.

Martin

This was what i thought, but in the book i mention there is examples of a Multiple Publishers/Multiple Subscribers setups, where each node is both subscriber, publisher and distributer, to quote from the book (full book and figures available here):

Microsoft? SQL Server High Availability By Paul Bertucci wrote:

In the multiple publishers or multiple subscribers scenario, as shown in Figure 7.15, a common table (such as the Customers table) is maintained on every server participating in the scenario. Each server publishes a particular set of rows that pertain to it—usually via filtering on something that identifies that site to the data rows it owns—and subscribes to the rows that all the other servers are publishing. The result is that each server has all the data at all times, and can make changes to its data only. You must be careful when implementing this scenario to ensure that all sites remain synchronized. The most frequently used applications of this configuration are regional order processing systems and reservation tracking systems. When setting up this configuration, make sure that only local users update local data. This check can be implemented through the use of stored procedures, restrictive views, or a check constraint.

But i guess setting up multiple servers as both subscriber, publisher and distributer and letting them all subscribe to each other will cause havoc without the above mentioned constraints.

But i guess i'm not the first person to want this kind of setup and if it was possible i would have found some references somewhere on the net (i just did another search and found this article which describes what i'm looking for, but it is only for 2005 and when looking further into it, it does not support conflict resolution, which i think is required, guess we will have to build it ourselves if we really want it).

Best regards Jens

many to many relationship inserts

Hi all

I have a SQL Server 2000 database that I converted from an access database. The interface is still existing in Access...

Basically I have 2 tables in a many to many relationship - Member and Nominators of members. But because the same nominator can nominate many members and a member can be nominated by multiple nominators, i've added a linking table call MemberNomLink that has the MemberId and the NominatorId.

So in my access interface I have a parent form for the Member, and a subform of all the nominators for that member. When i enter, if the nominator is brand new (never nominated before) then I'd just do an insert - which would:

a) insert into the nominator form (giving a new IDENTITY Primary key - Nominator Id) and
b) would insert into the MemberNomLink table - the new NominatorId and the parent form's Member Id.

When I was running this in access, this worked fine by simply using a left join query on my subform between Nominators and MemberNomLink. When I inserted into this query, access seemed to automatically insert nominator, then create the MemberNomLink fine populating it with the new id.

When I'm attached to the SQL Server 2000 db though, it will insert into the nominator table, however it won't insert into the MemberNomLink.

I don't think this is an unusual circumstance but if anyone has done anything like this, I'd like to know if I'm doing something obviously wrong...

thanks in advance
scott

Quote:

Originally Posted by smook

Hi all

I have a SQL Server 2000 database that I converted from an access database. The interface is still existing in Access...

Basically I have 2 tables in a many to many relationship - Member and Nominators of members. But because the same nominator can nominate many members and a member can be nominated by multiple nominators, i've added a linking table call MemberNomLink that has the MemberId and the NominatorId.

So in my access interface I have a parent form for the Member, and a subform of all the nominators for that member. When i enter, if the nominator is brand new (never nominated before) then I'd just do an insert - which would:

a) insert into the nominator form (giving a new IDENTITY Primary key - Nominator Id) and
b) would insert into the MemberNomLink table - the new NominatorId and the parent form's Member Id.

When I was running this in access, this worked fine by simply using a left join query on my subform between Nominators and MemberNomLink. When I inserted into this query, access seemed to automatically insert nominator, then create the MemberNomLink fine populating it with the new id.

When I'm attached to the SQL Server 2000 db though, it will insert into the nominator table, however it won't insert into the MemberNomLink.

I don't think this is an unusual circumstance but if anyone has done anything like this, I'd like to know if I'm doing something obviously wrong...

thanks in advance
scott

You can achieve the same functionality using a 'trigger' in SQL although I have to say the after_update event of the appropriate NominatorID field in your interface could be used primarily to determine if apropriate values exists in the MemberNomLink table and if not to then insert them immediately following an insertion of a new nominator. You would then simply requery to refresh the interface.

What format frontend are you using MDB or ADP? Have a good look at the ADP format in Access if you are upsizing for the first time. You can choose either of these of course whichever you arecomfortable with, however the ADP format handily exposes views and stored procedures to you in the interface and connects directly to SQL server using UDL Universal data link ie it doesnt use ODBC connectivity. If you need to create tables locally on the client then stick with the MDB format (mdb is back in favour for Access 2007 too)

Have a go at the after update event based on my simple response here and if you get stuck then post your table structure ie 'exact' table and field names and I'll replicate the tables and necessary SQL on my server, create MDB and an ADP solutions to show you the relevant differences and then mail you the files which should work on your system provided the db name is the same too. Don't post your email address though. (if this progresses to that) you would have to PM me with it and I,ll mail you

Regards

Jim :)

Manipulating parameters passed into a report - errors with parameters

Hi,
I've discovered a problem with repserv sp1 that I just can't see a way
around at the moment.
Basically we have a custom front end that allows the user to select
the params from either dropdown controls or textboxes. The text boxes
allow four states:
1/ All records
2/ Exact match
3/ Partial Match
4/ Starts with
The partial match is causing me the problem. Basically in the code
behind i'm prepending/appending the like clause character % to the
contents of the textbox i.e.
user enters 0123 into the textbox, the param passed into the report is
%0123%
Thats where the problem lies, repserv doesnt seem to accept that as a
valid param. It doesnt give any errors but it doesnt display the
required resultset. In the params returned to the user on the report,
it shows
23% has been passed in. The same thing happens if I type the url in
manually i.e.
http://localhost/Reportserver/MyReports/ListReport&rs:Command=Render&rs:Format=HTML4.0&rc:parameters=false&NameRefValue=%0123%&NameRefType=3
NameRefValue is declared as a string param, NameRefType is declared as
an integer param
Anyone have any ideas on this? Is it possible to manipulate the params
passed into the report before the engine actually processes them? If
this is possible I could just pass in the string without the %
characters & based on NamRefType value, add the % in the report itself
before the dataset is returned.
Cheers
SiSi wrote:
> Hi,
> I've discovered a problem with repserv sp1 that I just can't see a way
> around at the moment.
> Basically we have a custom front end that allows the user to select
> the params from either dropdown controls or textboxes. The text boxes
> allow four states:
> 1/ All records
> 2/ Exact match
> 3/ Partial Match
> 4/ Starts with
> The partial match is causing me the problem. Basically in the code
> behind i'm prepending/appending the like clause character % to the
> contents of the textbox i.e.
> user enters 0123 into the textbox, the param passed into the report is
> %0123%
>
Just read this the other day:
"NOTE Wildcards in Your SQL The filters you specify for your reports
are based on Visual Basic .Net 2003 syntax, so you'll find that the
pattern matching with the Like operator uses * as a wildcard and not %,
as you might use in SQL Like expressions."
_Hitchhiker's Guide to SQL Server 2000 Reporting Services_ by Peter
Blackburn and William R. Vaughan, Addison-Wesley 2005, p. 272.
hth
--Mike|||Thanks Mike,
I've just tried it and although the strange corruption of the
parameter is now ok, I still get incorrect results returned.
I use the % syntax in the starting with section with no problems, it
just seems to fail on integers and partial matches
Cheers anyway, it gave me a couple of ideas there!
Si
On Mon, 14 Feb 2005 07:54:53 -0600, "Mike Donnellan"
<spamspamspamandspam@.AT@.donnellanDOTcom> wrote:
>Just read this the other day:
>"NOTE Wildcards in Your SQL The filters you specify for your reports
>are based on Visual Basic .Net 2003 syntax, so you'll find that the
>pattern matching with the Like operator uses * as a wildcard and not %,
>as you might use in SQL Like expressions."
>_Hitchhiker's Guide to SQL Server 2000 Reporting Services_ by Peter
>Blackburn and William R. Vaughan, Addison-Wesley 2005, p. 272.
>hth
>--Mike|||Percent character is used to URL encode/escape other characters, so it
should itself also be encoded.
Check if this url works:
http://localhost/Reportserver/MyReports/ListReport&rs:Command=Render&rs:Format=HTML4.0&rc:parameters=false&NameRefValue=%250123%25&NameRefType=3
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Si" <no@.spam.thanks> wrote in message
news:mn4111pcl3qv3p6803i6pbstmhpch4lcge@.4ax.com...
> Hi,
> I've discovered a problem with repserv sp1 that I just can't see a way
> around at the moment.
> Basically we have a custom front end that allows the user to select
> the params from either dropdown controls or textboxes. The text boxes
> allow four states:
> 1/ All records
> 2/ Exact match
> 3/ Partial Match
> 4/ Starts with
> The partial match is causing me the problem. Basically in the code
> behind i'm prepending/appending the like clause character % to the
> contents of the textbox i.e.
> user enters 0123 into the textbox, the param passed into the report is
> %0123%
> Thats where the problem lies, repserv doesnt seem to accept that as a
> valid param. It doesnt give any errors but it doesnt display the
> required resultset. In the params returned to the user on the report,
> it shows
> 23% has been passed in. The same thing happens if I type the url in
> manually i.e.
> http://localhost/Reportserver/MyReports/ListReport&rs:Command=Render&rs:Format=HTML4.0&rc:parameters=false&NameRefValue=%0123%&NameRefType=3
> NameRefValue is declared as a string param, NameRefType is declared as
> an integer param
> Anyone have any ideas on this? Is it possible to manipulate the params
> passed into the report before the engine actually processes them? If
> this is possible I could just pass in the string without the %
> characters & based on NamRefType value, add the % in the report itself
> before the dataset is returned.
> Cheers
> Si|||Thanks Lev,
Yes that does seem to work ok. IS there a specific way I should be
encoding these characters or simply replace % with %25 ?
Cheers
Si
On Mon, 14 Feb 2005 21:16:54 -0800, "Lev Semenets [MSFT]"
<levs@.microsoft.com> wrote:
>Percent character is used to URL encode/escape other characters, so it
>should itself also be encoded.
>Check if this url works:
>http://localhost/Reportserver/MyReports/ListReport&rs:Command=Render&rs:Format=HTML4.0&rc:parameters=false&NameRefValue=%250123%25&NameRefType=3|||As a rule, I always try to limit query-string parameter values to the
actual value, and do formatting elsewhere.
Instead of passing the value %0123% via the query-string, why don't you
just pass the 0123 and add the "%" characters via an expression within
the RDL. This should completely avoid the problem you encountered with
encoding.
~Lance
http://weblogs.asp.net/lhunt/|||Thanks Lance,
Thats what I originally wanted to do but was unsure as to how to
manipulate the parameters before passing through to the stored proc.
In the end i've passed it all through as you say but direct to the
stored proc for processing & it now works fine.
I'd still be interested to know how to do it the way you mention.
Regards
Si
On 15 Feb 2005 07:02:36 -0800, "Lance" <lancehunt@.gmail.com> wrote:
>As a rule, I always try to limit query-string parameter values to the
>actual value, and do formatting elsewhere.
>Instead of passing the value %0123% via the query-string, why don't you
>just pass the 0123 and add the "%" characters via an expression within
>the RDL. This should completely avoid the problem you encountered with
>encoding.
>~Lance
>http://weblogs.asp.net/lhunt/|||I would normally recommend using the exact solution you chose, except I
wasnt sure if you were using a StoredProcedure or SQL. You definitely
should stick with your current implementation.
However, there are some cases where you can't use Stored Procedures,
such as with many ODBC connections to legacy systems. In such cases, I
recommend dynamically building your value for the LIKE expression in
the RDL.
Here are the basic steps:
1. Open "Data" tab from designer.
2. Select your DataSet from dropdown
3. Click on the "..." to go to properties.
4. Click on the Parameters tab
5. Locate the parameter in question
6. Modify the parameter value (right hand column) to use an expression.
7. Use the expression:
"="%" & Parameters!MyParam.Value & "%"
Instead of the default expression
"=Parameters!MyParam.Value"
8. Close Properties and you're ready to go!
Enjoy!
Lance Hunt
http://weblogs.asp.net/lhunt/

Managing Release Compatibility

I have been tasked with the responsibility of managing "Release
Compatibility" of our products with Microsoft products.
Basically, it is my job to make sure that we keep a detailed list of which
of our products work with which of Microsofts products. This includes not
only different versions of the OS, but also different service packs. It
also includes versions of Microsoft Products such as IIS, SQL Server,
Exchange Server, Outlook as well as all the associated service packs for
each.
This is all to be cross-referenced against each version of our own products,
to see where we are compatible and where we are not.
Right now, we are tracking this in a simple word document with some tables
in it, that details the different versions, but this is not a very good
solution, as it is hard to maintain, has a lot of duplicate information, and
still does not hold all the information we would like it to. For example,
it does not track *interactions* of different products, nor does it track
things like hotfixes.
I am looking for a database driven solution that we could implement that
will facilitate this tracking. I know there must be other Microsoft
partners who have this same problem, what do you use to keep track of this
sort of thing?
-please reply in the newsgroup, I cannot receive emails from here.
Hi Brian,
Thanks for your posting!
Based on my understanding, you would like to have database to record and
keep the steps of release of our hotfix or service pack. Something like a
notification system. Correct me if I was wrong. However, there is no such
funcationality now. I will try to make a further research internally and
try to see whether there are any available workaround.
Thank you for your patience and cooperation.
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Brian,
This question is very specific to your environment and is more like a
consult type of an issue. You are welcome to wait for MVP or other peer
response, however we will not be able to assist you with this as this is
not a break/fix type of issue and is more like a consult type question. You
can contact Advisory Services (AS) . Microsoft Advisory Services provides
short-term advice and guidance for problems not covered by Problem
Resolution Service as well as requests for consultative assistance for
design, development and deployment issues. You may call this number to get
Advisory Services: (800) 936-5200.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Managing Release Compatibility

I have been tasked with the responsibility of managing "Release
Compatibility" of our products with Microsoft products.
Basically, it is my job to make sure that we keep a detailed list of which
of our products work with which of Microsofts products. This includes not
only different versions of the OS, but also different service packs. It
also includes versions of Microsoft Products such as IIS, SQL Server,
Exchange Server, Outlook as well as all the associated service packs for
each.
This is all to be cross-referenced against each version of our own products,
to see where we are compatible and where we are not.
Right now, we are tracking this in a simple word document with some tables
in it, that details the different versions, but this is not a very good
solution, as it is hard to maintain, has a lot of duplicate information, and
still does not hold all the information we would like it to. For example,
it does not track *interactions* of different products, nor does it track
things like hotfixes.
I am looking for a database driven solution that we could implement that
will facilitate this tracking. I know there must be other Microsoft
partners who have this same problem, what do you use to keep track of this
sort of thing?
-please reply in the newsgroup, I cannot receive emails from here.Hi Brian,
Thanks for your posting!
Based on my understanding, you would like to have database to record and
keep the steps of release of our hotfix or service pack. Something like a
notification system. Correct me if I was wrong. However, there is no such
funcationality now. I will try to make a further research internally and
try to see whether there are any available workaround.
Thank you for your patience and cooperation.
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Brian,
This question is very specific to your environment and is more like a
consult type of an issue. You are welcome to wait for MVP or other peer
response, however we will not be able to assist you with this as this is
not a break/fix type of issue and is more like a consult type question. You
can contact Advisory Services (AS) . Microsoft Advisory Services provides
short-term advice and guidance for problems not covered by Problem
Resolution Service as well as requests for consultative assistance for
design, development and deployment issues. You may call this number to get
Advisory Services: (800) 936-5200.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!