Future Maintenance of SQL 2005 Merge Replication

I have remote offices that are entering data into their local database, then
using merge replication to "upload" their changes to a publisher on my web
server, and then possibly download any changes made on my web server. My
Web server is running SQL 2005 Standard, and the local offices are running
SQL 2005 Express. I've got it working via https/replisapi.dll, and once
it's started, it works very nicely.
Typically when a new office once to get started, they'll backup their
(never-before-replicated) database to a .bak file, send me that file, and
I'll restore their (new) database onto my server, and create a publication.
Then I'll send them instructions on how to start a subscription. This
causes the data they just send me to come back down, as a snapshot is
applied to init the subscription. I realize that I could start a no-sync
initialization, but if any changes were made from when they made the backup
to when they start/init the subscripion, I believe the two-databases would
be out of sync. Am I right?
I understand that once a subscription is started, that if a backup is
restored (on either the subscription side or publisher side) that as long as
you're within the retention period, merge replication will correctly bring
over the changes that have occurred since the backup. With this in mind...
Would it be possible on the soon-to-be subscriber database to get the
replication triggers installed and working (tracking all changes) before the
backup is made, so that if any changes occur on the subscriber side (from
the time the backup was made to when it's applied to the publisher database
to when the subscription is started), these changes will be applied to the
publisher when the new subscription is initialized?
--Troy
Unfortunately you cannot enable change tracking before setting up the
subscription. So you would have to track the subscriber side changes
yourself while the subscription is being setup and update those rows that
changed in that interval so that it triggers a replication upload.
“This posting is provided "AS IS" with no warranties, and confers no rights.”
"Troy Wolbrink" wrote:

> I have remote offices that are entering data into their local database, then
> using merge replication to "upload" their changes to a publisher on my web
> server, and then possibly download any changes made on my web server. My
> Web server is running SQL 2005 Standard, and the local offices are running
> SQL 2005 Express. I've got it working via https/replisapi.dll, and once
> it's started, it works very nicely.
> Typically when a new office once to get started, they'll backup their
> (never-before-replicated) database to a .bak file, send me that file, and
> I'll restore their (new) database onto my server, and create a publication.
> Then I'll send them instructions on how to start a subscription. This
> causes the data they just send me to come back down, as a snapshot is
> applied to init the subscription. I realize that I could start a no-sync
> initialization, but if any changes were made from when they made the backup
> to when they start/init the subscripion, I believe the two-databases would
> be out of sync. Am I right?
> I understand that once a subscription is started, that if a backup is
> restored (on either the subscription side or publisher side) that as long as
> you're within the retention period, merge replication will correctly bring
> over the changes that have occurred since the backup. With this in mind...
> Would it be possible on the soon-to-be subscriber database to get the
> replication triggers installed and working (tracking all changes) before the
> backup is made, so that if any changes occur on the subscriber side (from
> the time the backup was made to when it's applied to the publisher database
> to when the subscription is started), these changes will be applied to the
> publisher when the new subscription is initialized?
> --Troy
>
>
|||Thanks for confirming that. I'm considering setting the database to
read-only during the interim to keep the user from making any changes that
will get discarded. Or atleast just give the user a warning that any
changes during the interim will be ignored.
--Troy
"Vijay TS" <VijayTS@.discussions.microsoft.com> wrote in message
news:FF785F35-E3BA-4966-953F-69B58A30F057@.microsoft.com...[vbcol=seagreen]
> Unfortunately you cannot enable change tracking before setting up the
> subscription. So you would have to track the subscriber side changes
> yourself while the subscription is being setup and update those rows that
> changed in that interval so that it triggers a replication upload.
> --
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."
>
> "Troy Wolbrink" wrote:

Function for Determining Completion Time

The company I work for uses tracking software for entering help desk
(IT-related) issues. The issues is timestamped and assigned a Priority
level (A-D). Each level relates to an estimated completion time. What
I'm trying to accomplish is to add the estimated completion time to the
time and date. For example. If a customer sends in an issue at 12:21PM
with a priority level of 'A' then it should be known that the issue
should be done at 2:22PM.
Key:
A - 2 hours
B - 8 hours
C - 48 hours
D - 72 hours
This is where it gets tricky. Our business hours are from 7:30am -
4:00pm. I would like to account for this in the function. So if a
customer sends in a request on 01/21/2006 @. 3:30PM with a 'B' priority
level it should be fulfilled by 01/22/2006 @. 3:00PM.
I don't expect anyone to write up a function for this write away. Any
pointers, suggestions, or direction would be greatly appreciated.
*** Sent via Developersdex http://www.examnotes.net ***Hello, Will
Hint: If your workday is 8 and a half hours long (and wends don't
count) you can say:
A = 2 hours = 1 day minus 6.5 hours
B = 8 hours = 1 day minus 0.5 hours
C = 48 hours = 6 days minus 3 hours
D = 72 hours = 9 days minus 4.5 hours
and use a CASE expression to see which of the above is within working
hours.
But I have a feeling that you don't work on sundays, so this method
will not be good enough...
Razvan|||Because of wends and holidays, you cannot use a formula for this.
Try a look-up table instead:
CREATE TABLE CompletionTimes
(submission_time DATETIME NOT NULL PRIMARY KEY,
A_complete_time DATETIME NOT NULL,
B_complete_time DATETIME NOT NULL,
C_complete_time DATETIME NOT NULL,
D_complete_time DATETIME NOT NULL);
15 minute intervals is probably good enough, so each day is 07:30 to
16:00 hrs to give us about 30 time slots. Again, just estimating, you
have 250 work days to a year, so the table is about 75,000 rows for ten
years.
A calendar table for US Secular holidays can be built from the data at
this website, so you will get the three-day wends:
http://www.smart.net/~mmontes/ushols.html