Is this scenario possible?

Transactional Replication.
Box1: Publisher
Box2: Reporting only Subscriber
Box3: Updateable Subscriber
If Box1 modifies data, it obviously goes to both Subscribers. But if box 3
modifies data, will it show up on Box2? If memory serves correctly, it will
not by design. Is there a way to change this?
SQL2K SP3
TIA, ChrisR
it will. I just tried it.
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:OlbFqjZKFHA.2812@.TK2MSFTNGP15.phx.gbl...
> Transactional Replication.
> Box1: Publisher
> Box2: Reporting only Subscriber
> Box3: Updateable Subscriber
> If Box1 modifies data, it obviously goes to both Subscribers. But if box 3
> modifies data, will it show up on Box2? If memory serves correctly, it
will
> not by design. Is there a way to change this?
> --
> SQL2K SP3
> TIA, ChrisR
>
|||I've tried it with no luck. :-( Box2 will not recieve new rows inserted
from Box3.
Any ideas?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:#dSrqZkKFHA.3340@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> it will. I just tried it.
> --
> 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:OlbFqjZKFHA.2812@.TK2MSFTNGP15.phx.gbl...
3
> will
>
|||it does work for me however. I am not sure why it is not working for you.
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:%23wE1dPlKFHA.2852@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> I've tried it with no luck. :-( Box2 will not recieve new rows inserted
> from Box3.
> Any ideas?
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:#dSrqZkKFHA.3340@.TK2MSFTNGP14.phx.gbl...
box
> 3
>
|||Is your Box2 a Subscriber of both Box1 and Box3? I only have it set up as a
Subscriber of Box1.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OjssPelKFHA.508@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> it does work for me however. I am not sure why it is not working for you.
> --
> 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:%23wE1dPlKFHA.2852@.TK2MSFTNGP14.phx.gbl...
> box
it
>
|||Hillary I just got it. I don't know why, but I set up 2 different
Publications for this. One for the Immediate Updating Subscription, and one
for the Non Immediate Updating Subscription. Still not sure why I went that
route.
Once I used only 1 Publication, and configured Immediate Updating in the
Subscription options only, all went as expected.
Thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OjssPelKFHA.508@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> it does work for me however. I am not sure why it is not working for you.
> --
> 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:%23wE1dPlKFHA.2852@.TK2MSFTNGP14.phx.gbl...
> box
it
>

Is this possible? Monitor replication from VB 6.0

I have a client with approximately 30 laptops all running SQL Server
2000 (databse is about 13 gigabytes, this is _not_ MSDE). They have a
legacy application written in VB 6.0.
The decision has been made to change their replication model.
Replication between a "central" SQL Server database and the laptops
used to be triggered through the application whenever the laptop user
chose to. Now they are going to use a two-part method, so that the
user's logs are replicated whenever they connect to the central server,
and product data (which can be huge) will be scheduled in stages
overnight. The VB application will have no control over when they
replicate. The logs will use Merge replication and the product data
will use Transactional replication.
The problem is they expect the application to continue to monitor the
replication "progress" in both scenarios. I have gotten them to accept
that their progress bars will go away, that there will only be an item
in their status bars that it is running (or not).
I have figured out a way to monitor that Merge replication is going on
by watching the MSmerge_history and MSmerge_agents tables in the
Distribution database, but I am not sure that even this will continue
to work once the laptops stop using the ActiveX MS SQL Merge Control to
trigger replication, and it will not work at all for Transactional
replication.
Does anyone have a way (or have a suggestion that might lead to a way)
as to how I can tell from VB whether replication is in progress against
the local SQL Server database? If you believe this is impossible, I
would appreciate hearing that as well.
Thanks,
Austin
Have you looked at the status event?
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
"AustinMN" <tacooper260@.hotmail.com> wrote in message
news:1126531606.616225.228610@.g14g2000cwa.googlegr oups.com...
> I have a client with approximately 30 laptops all running SQL Server
> 2000 (databse is about 13 gigabytes, this is _not_ MSDE). They have a
> legacy application written in VB 6.0.
> The decision has been made to change their replication model.
> Replication between a "central" SQL Server database and the laptops
> used to be triggered through the application whenever the laptop user
> chose to. Now they are going to use a two-part method, so that the
> user's logs are replicated whenever they connect to the central server,
> and product data (which can be huge) will be scheduled in stages
> overnight. The VB application will have no control over when they
> replicate. The logs will use Merge replication and the product data
> will use Transactional replication.
> The problem is they expect the application to continue to monitor the
> replication "progress" in both scenarios. I have gotten them to accept
> that their progress bars will go away, that there will only be an item
> in their status bars that it is running (or not).
> I have figured out a way to monitor that Merge replication is going on
> by watching the MSmerge_history and MSmerge_agents tables in the
> Distribution database, but I am not sure that even this will continue
> to work once the laptops stop using the ActiveX MS SQL Merge Control to
> trigger replication, and it will not work at all for Transactional
> replication.
> Does anyone have a way (or have a suggestion that might lead to a way)
> as to how I can tell from VB whether replication is in progress against
> the local SQL Server database? If you believe this is impossible, I
> would appreciate hearing that as well.
> Thanks,
> Austin
>
|||Hilary Cotter wrote
> Have you looked at the status event?
Yes, I have. The Status event applies to the ActiveX replication
components, whicb are being removed from the application. It only reports
on replication started by the components, so is useless for replication
initiated by the Distributor/Publisher database.
Austin
You programmed with 1s and 0s? We only had 0s!
There are no X characters in my address
> --
> 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
> "AustinMN" <tacooper260@.hotmail.com> wrote in message
> news:1126531606.616225.228610@.g14g2000cwa.googlegr oups.com...
>
|||"AustinMN" <tacooper260@.hotmail.com> wrote in message
news:1126531606.616225.228610@.g14g2000cwa.googlegr oups.com...
>I have a client with approximately 30 laptops all running SQL Server
> The problem is they expect the application to continue to monitor the
> replication "progress" in both scenarios. I have gotten them to accept
> that their progress bars will go away, that there will only be an item
> in their status bars that it is running (or not).
> I have figured out a way to monitor that Merge replication is going on
> by watching the MSmerge_history and MSmerge_agents tables in the
> Distribution database, but I am not sure that even this will continue
> to work once the laptops stop using the ActiveX MS SQL Merge Control to
> trigger replication, and it will not work at all for Transactional
> replication.
> Does anyone have a way (or have a suggestion that might lead to a way)
> as to how I can tell from VB whether replication is in progress against
> the local SQL Server database? If you believe this is impossible, I
> would appreciate hearing that as well.
>
My experience is with Oracle. With Oracle Enterprise Manager, you can see a list
of current connections (which I do fairly often), and you can see lists or logs
of recent changes (which I don't do much). It seems like there would have to be
something similar in SQL Server. Activity at the level of replication can't be
completely hidden. It is just a matter of finding out which thing to look
for...Maybe you could even detect the running process on the laptop with an API
call?

Is there merge monitor sample in vb.net?

Hello,

We are a vb.net shop and while some of us know a little C# it's not enough. Is there a sample solution for the monitoring of replication in vb.net? I know about the sales one but it's in c#. Thanks.

Please refer to the Books On line link

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/repref9/html/b323da02-fadf-443c-8ba7-0cc050250e2d.htm

which will point to every replication step through RMO programming and sample code is written in VB, C#, C++, J#, etc.

Thanks

|||

Yunjing,

Thanks for the response. Unfortunetly I am rather new to raiseevents. Is there a way someone could convert this to vb.net so I can try and understand it? I am basically looking for a progress bar and text output during the sync progress in vb.net as the SalesOrder sample does in c#. Here is what I have in vb.net but am not sure if it's correct. I also have an error when trying to call the agent.status. I got snippets from the salesorder sample c# solution

Public Delegate Sub SyncWhenConnectedStatus(ByVal sender As Object, ByVal eventArgs As StatusEventArgs)
Public Event Status As SyncWhenConnectedStatus

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
...other code...
Dim subscription As MergePullSubscription
Dim agent As MergeSynchronizationAgent
...other code...
agent.Status += New AgentCore.StatusEventHandler(AddressOf Sync_Status)

**cannot build

**Error 1 'Public Event Status(sender As Object, e As Microsoft.SqlServer.Replication.StatusEventArgs)' is an event, and cannot be called Directly. Use a 'RaiseEvent' statement to raise an event.

agent.Synchronize()
end sub

**Not sure if I am doing this correct either

Private Sub Sync_Status(ByVal sender As Object, ByVal e As StatusEventArgs)
' Update the Synchronization Status form.
txtOutput.AppendText(e.Message.ToString() + Environment.NewLine)

' Call the Status event to update the main form
' if sync when connected is used.
RaiseEvent Status(sender, e)
'If Not Status Is Nothing Then
' RaiseEvent Status(sender, e)
'End If
Application.DoEvents()
End Sub

Is there anyway to do SUM on join table?

I would like to start using SQL Server CE rather than SQLite for an application I am developing, due to it's replication support for SQL Server however there appears to be a gaping hole in it that means I can't even consider it. Specifically to do with aggregates against detail tables.

Is there any way to do the equivalent of the following in SQL using SQL Server CE?

SELECT [Customer_Id], [Customer_name], COALESCE([OT].[Count_Orders],0) [CountOrders], COALESCE([OT].[Sum_Value],0) [OrderValue] FROM [Customers] LEFT OUTER JOIN (SELECT .[Customer_Id], COUNT(*) [Count_Orders], SUM(.[Order_Value]) [Sum_Value] FROM [Orders] GROUP BY .[Customer_Id]) [OT] ON [OT].[Customer_Id] = [Customers].[Customer_Id]);

Any help would be most welcome

Adrian Heath

I think the short answer is no. The long answer is no, not in a single statement. One of the differences when you are working with such a lightweight database is that you need to adjust the way you do operations. Typically if you are writing a client application that uses sql server ce you can split the data processing load between the database (ie selecting records) and the application itself.

In the example you have provided you would have to do the aggregation by Customer_Id across the Orders table and then in memory do the join across to Customer information..

Alternatively you could just maintain an additional table with this information already aggregated - again because it is a client application and you are not expecting thousands of concurrent updates this becomes a workable solution.

|||

This works fine (from Northwind sample database) (Ship Name = Customer Name)

SELECT Orders.[Ship Name], SUM([Order Details].[Unit Price]) AS Total
FROM [Order Details] INNER JOIN
Orders ON [Order Details].[Order ID] = Orders.[Order ID]
GROUP BY Orders.[Ship Name]

Hope this assists.

Is there any impact on server of increasing LOG File size

Thanks to all participants.

I am using SQL Server 2000 with replication object for two location. Log size on publisher go upto 25 times of data file size, I mean 80 MB Data files has maintains 2 GB log file and it is same for all five co's working on same windows 2000 advanced server board.

Since last week server randamly get disconnected from user applications and at that time few tables are not openable at server.

Can any one give a reason ? Why this type misbehaviou done by SQL Server 2000?

Thanks.If you aren't using the log file for recovery purposes, I'd suggest you set the database recovery mode to simple. That will allow SQL Server to jettision the log data once it has been replicated.

SQL Server has a lot of tunable options. It guesses correctly on the appropriate settings for them nearly all of the time (probably more than 99.95% of the time in my experience), but occaisionally it gets something mis-adjusted.

This kind of problem is probably unavoidable (although it can still be reduced), and in those few instances you need a bit of expert help to figure out what is wrong and how to fix it.

-PatP|||Thanks for your suggession. I shrink log file upt to 100 MB and set the database in simple recovery mode by 02-01-2004. After that for last three days nothing happen with user application. Is there any co-relation between problem and log size or any thing more will be findout anywhere else.

Where I can find the reson for orginal disconnection problem, which one not occured for last three working day.

Thanks

R.Mall|||PatP Sir,

I am waiting for your reply regarding following.

Thanks for your suggession. I shrink log file upt to 100 MB and set the database in simple recovery mode by 02-01-2004. After that for last three days nothing happen with user application. Is there any co-relation between problem and log size or any thing more will be findout anywhere else.

Where I can find the reson for orginal disconnection problem, which one not occured for last three working day.

Thanks

R.Mall|||There may be some connection, but I don't know of any easy way to determine if there is a connection or not. Without a lot more information about your configuration (NT, SQL, WAN network, client machine configurations, etc), I'm at a loss to help you more.

The problem lies in the apparent complexity of your configuration. If you have multiple SQL Server databases/instances, many clients, different application loads, etc... The number of items that could interact rapidly gets beyond what I can diagnose remotely.

-PatP|||PatP Sir,

Please look over my setup-

Hardware , OS and Application
Servers-Two
Intel Original server board - 7210TP
CPU HT 2.8GHz, P-IV
HDD - 80GB SATA
1GB Ram - DDR
ATX Cabinet
OS-Windows 2000 Advanced server with service pack IV.
Database - MSSQL Server 2000 with SP3.
Replication - Merge
No.of Database- 6th (1 Master and 5 cos)
Replicated no.of tables ( 42*5 ) + 12 = 222
Publisher - One
Subscriber - One (Pull Subscription)
Transaction - Medium.

Clients-45+ (10 at subscriber site and 35+ at publisher site)
Intel Alex ATX motherboard
CPU Celeron 400
LAN Card PCI 32Bit
128MB Ram
40GB HDD
OS-Windows-98
Application form - Exe and DLL (One Exe + 70+ DLL)
No. of Application - One.
No.Of modules - 11
Application Base Power Builder 7.0

ROUTER
ZYXEL-ISDN Dialup
100IH
Connectivity ISDN Dialup
If you need any more information, so let me know, I will send you immediately.

Thanks

R.Mall|||Dear PatP Sir,

Please see the details.|||All I can do is offer guesses, based on the information that you've provided. To really do a good job, it would take something on the order of 500 pages of information, and probably 40 hours of effort on my part. That isn't something I'm willing to do at this point in time.

A single ISDN line can't always support ten users plus replication on that scale. While it can sometimes do the job, there are othertimes that it will definitely be a bottleneck for performance. This can be mitigated by how you configure the servers to use the connection, but it is still potentially the largest potential problem that I see.

A busy SQL Server would consider 1 Gb of RAM as an hors d'oeurve. I routinely configure busy servers with 8 Gb, then let someone try to talk me down. For 45 users, I might drop as low as 2 Gb, but not without considerable discussion first. I really don't think you have enough RAM in the servers.

Powerbuilder's Datawindows are very easy for developers to use, but they can be "noisy" from a database traffic perspective. As one example, if a datawindow has many rows and the default update method is used after a single row is changed, the entire contents of the database can be written back to the server. Depending on how frequently you've got your subscriber set to "pull" a merge, there can be a lot of data queued up (even with SIMPLE recovery)!

Your problem is probably complex. I don't have the time or resources to do a good job helping you fix it remotely. You really need to get either a dedicated team to help you solve this remotely, or someone to come on site (which would be much better in my opinion). I don't think that casual help via a forum is going to be enough to help you solve this problem.

-PatP|||Dear PatP Sir,

Thanks for your valuable commend on my issue, I will see as per your guidelines to that issue.

I Think you have analyze methods for analyze the problem but due to lack of time and information you can't to it.

Can you help me in another way? Just tell me analyzer methods and help material site or hard copy book, so I will do it myself.

Thanks

R.Mall|||First order of business, get VERY familiar with the NT Performance Analyzer. For this kind of problem, it can provide at least 90% of the information you need to diagnose the problem.

Just as an observation, I wouldn't recommend using a machine to monitor its own performance if I could use a second machine to do it. Running the Performance Monitor can be rather demanding, especially if you are monitoring many counters at once.

Use Performance Monitor to monitor the server at a time when performance is acceptable, and at times when the performance is not acceptable. At first, you'll want to record all of the Performance counters that seem vaguely relevant.

The whole point of this process is to determine where the "weak link" is in your system. It shouldn't take a lot of measurements to trace the problem down to just a few counters (certainly less than ten, probably only three). At first you'll need to track all of the memory, disk, cpu, and network counters... Very shortly you should be able to identify which counters change drastically as performance degrades. Those are the counters that you need to watch carefully going forward.

In the begining, what I'd recommend is to save the performance counters into a file. Run a few (three to five) basline monitors at different times of the day, including at least one with no users online at all. When your server performance starts to degrade, run another stored trace. The worse the performance gets, the more important getting these traces will be.

When you have several traces stored, you'll need to start comparing them. Look for common trace information that is very similar when performance is good as they are when performance is bad. These are "background noise" that you can safely filter out.

As you begin to see which traces change drastically when performance changes, you'll start to understand where the bottleneck is in your system. This is the area where you need to focus your attention, but be forewarned that there can be more than one bottleneck, and sometimes fixing one exposes a completely different one.

As an example of this, I think that your servers are severly RAM starved. I think that they need at least twice as much memory, and quite possibly more than that. However, when you fix that problem, you might well find that something else is also a bottleneck, but due to the RAM problems it was never exposed.

To put this in a "real world" context, if you have an truck that's in bad shape and you need to move something very heavy, you might decide to work on the truck first because you know it can't do the job in its present condition. You tune up the engine (new spark plugs and rotar), only to discover that you need a carburator overhaul too that was masked by the need for the tune up. Once you get the engine running well, you discover there isn't enough transmission fluid. As you fix one problem, it exposes another problem that has been there all along, but that wasn't serious enough to be an issue.

Don't let my comments make you depressed. Your problem is definitely something that can be fixed. I just don't want you to think you'll go in with a "magic bullet", fire one shot and the world will be lovely again. This is a problem that probably took a while to become an issue, and it will take some time and effort to fix. Actually, in my mind performance monitoring and management are part of the ongoing job... The issues are always there, they just don't always get the attention that they should!

-PatP|||PatP sir,

Lot of thanks to you for your valuable suggession, I will do it accordingly and hope I will resolve it with the help of you.

I would like to thanks more to you for past time advice also , when I was in loop of Replication 4 month ago.

Thanks

R.Mall|||Amount of activity that can be generated by the number of users you indicated can not possibly be a source of concern regarding you hardware configuration. If you're experiancing "disconnects" it's probably due to clien timeouts which are a direct result of host CPU being too busy to process any consequent requests. This is a direct affect of under-utilization of indexing capabilities (this statement is dictated purely by the amount of info you provided, and may be easily refuted if the presence of indexes is indicated). Merge replication does not pose this kind of behavior with 45+ concurrent connections. In fact it doesn't manifest itself like this even with 450+ concurrent connections with 20+ runnable processes (I bet you have less than that).

I think you need to analyze execution plans for most frequently invoked statements and concentrate your attention on optimizing them before you burry yourself in perfmon counters.

Is there another way out?

Guys,
We have a transactional replication set up with a single publisher and
a single subscriber.
Recently, replication failed due to the following reason:
Data, erroneously, was deleted from subscriber. Then, to make the
publisher and subscriber synch, the same data was deleted from
publisher.
At that point, the publisher, as it should, attempted to delete the
data in the subscriber. But since the data is not there, the attempt
failed, and replication failed.
I tried to restart the agent, but replication kept failing. Since
nothing worked, I just deleted the subscription and recreated it. The
replication then started, but since it had to go through all the steps
again (i.e. Snapshot ...), it took us 2 hours to get the data back in
synch.
Luckily this happened in the testing environment. If it would be
production, we would be in trouble, as availability is our #1 concern.
Would anyone know of any other (i.e. more elegant) way I could've
solved this problem?
The only other idea I had was the following: It is possible that the
data which the publisher plans to delete is temporarily stored in some
file and if that file is located and deleted, and then the agent is
restarted, it could possibly work - but, I could not locate any such
files - and this is a completely hypothetical idea.
Any suggestions would be appreciated.
Thanks
have a look at the continue on data consistency error profile.
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
"sql_er" <sql_er@.yahoo.com> wrote in message
news:1165954222.427226.20530@.j44g2000cwa.googlegro ups.com...
> Guys,
> We have a transactional replication set up with a single publisher and
> a single subscriber.
> Recently, replication failed due to the following reason:
> Data, erroneously, was deleted from subscriber. Then, to make the
> publisher and subscriber synch, the same data was deleted from
> publisher.
> At that point, the publisher, as it should, attempted to delete the
> data in the subscriber. But since the data is not there, the attempt
> failed, and replication failed.
> I tried to restart the agent, but replication kept failing. Since
> nothing worked, I just deleted the subscription and recreated it. The
> replication then started, but since it had to go through all the steps
> again (i.e. Snapshot ...), it took us 2 hours to get the data back in
> synch.
> Luckily this happened in the testing environment. If it would be
> production, we would be in trouble, as availability is our #1 concern.
> Would anyone know of any other (i.e. more elegant) way I could've
> solved this problem?
> The only other idea I had was the following: It is possible that the
> data which the publisher plans to delete is temporarily stored in some
> file and if that file is located and deleted, and then the agent is
> restarted, it could possibly work - but, I could not locate any such
> files - and this is a completely hypothetical idea.
>
> Any suggestions would be appreciated.
>
> Thanks
>