May 16, 2012 at 2:49 pm
Hi,
This is my first question post so if i have not followed any rules or procedure please let me know and I will try to get it right the next time.
Our company has 2000 stores and each one has SQL Server 2008 Express Edition install on them. As its a Express edition we cannot setup the transactional replication from all 2000 stores to home office database servers, we are using Merge Replication.
We are replicating tables from all these 2000 stores back here at the home office and we are using a Filter on storenumber. As data from all these 2000 stores goes to one single table on home office database server, this filter helps in seperating data for each stores.
At store server we have a stagging database and it gets data via replication triggers which we have created under main database. So whenever there is Insert/Delete/Update under this main database at store server, it will copy data to this stagging database at store server and from there it comes to home office replication database servers.
Our company releases new version of POS every few months, and whenever it happens we reinitialize the whole replication publication, as tables we are replicating gets the structure change. When we do that sending down initial snapshot is quicker, but, getting the Initial load of data from store server back here to the home office takes forever, as we have atleast 1Millions records coming down from store server back to the home office.
Our store servers are not powerful enough (they have 4GB of Ram and 2 CPU), as store server runs SQL Express Edition they cannot utilize more than 1GB of memory. Also our POS application is SQL based and utilizes some more resources. So when there is download of initial load happen when we reinitialize the subscription SQL Replication will bogg down the store server and will affect the POS functionality. Due to this our first initialization process takes foreverrrrrrr to finish and we get way behind in getting the initial load of data (which is everything) and then the daily updates.
Question is:- Is there a way where once we send down the snapshot (i.e. empty table structure, Microsoft initialization) and disable the replication subscription. Then get all the data from store server back here to the home office via a Text file (kind of Blob) and apply this blob to our home office replication server and then start normal replication sync process, so it will ONLY get the latest updates going forward?
I have attached our Merge Replication Topology to the post.
Any kind of help would be really appreciated
Thank you,
HirenA
May 18, 2012 at 6:37 am
hiren.patel 911 (5/16/2012)
Hi,This is my first question post so if i have not followed any rules or procedure please let me know and I will try to get it right the next time.
Our company has 2000 stores and each one has SQL Server 2008 Express Edition install on them. As its a Express edition we cannot setup the transactional replication from all 2000 stores to home office database servers, we are using Merge Replication.
We are replicating tables from all these 2000 stores back here at the home office and we are using a Filter on storenumber. As data from all these 2000 stores goes to one single table on home office database server, this filter helps in seperating data for each stores.
At store server we have a stagging database and it gets data via replication triggers which we have created under main database. So whenever there is Insert/Delete/Update under this main database at store server, it will copy data to this stagging database at store server and from there it comes to home office replication database servers.
Our company releases new version of POS every few months, and whenever it happens we reinitialize the whole replication publication, as tables we are replicating gets the structure change. When we do that sending down initial snapshot is quicker, but, getting the Initial load of data from store server back here to the home office takes forever, as we have atleast 1Millions records coming down from store server back to the home office.
Our store servers are not powerful enough (they have 4GB of Ram and 2 CPU), as store server runs SQL Express Edition they cannot utilize more than 1GB of memory. Also our POS application is SQL based and utilizes some more resources. So when there is download of initial load happen when we reinitialize the subscription SQL Replication will bogg down the store server and will affect the POS functionality. Due to this our first initialization process takes foreverrrrrrr to finish and we get way behind in getting the initial load of data (which is everything) and then the daily updates.
Question is:- Is there a way where once we send down the snapshot (i.e. empty table structure, Microsoft initialization) and disable the replication subscription. Then get all the data from store server back here to the home office via a Text file (kind of Blob) and apply this blob to our home office replication server and then start normal replication sync process, so it will ONLY get the latest updates going forward?
I have attached our Merge Replication Topology to the post.
Any kind of help would be really appreciated
Thank you,
HirenA
Does anyone have any idea or any thoughts or any suggestions please??
Thank you,
Hiren
May 18, 2012 at 6:50 am
hiren
i have done this before - there is an option that you can use, but it depends on how much of the data on your POS server is replicated back.
you can initialise replication from a database backup. - using the parameter @sync_type='initalize with backup'
sql backups can be compressed using zip and then uploaded to your main server - if you are clever you could script the whole process
MVDBA
May 18, 2012 at 6:55 am
michael vessey (5/18/2012)
hireni have done this before - there is an option that you can use, but it depends on how much of the data on your POS server is replicated back.
you can initialise replication from a database backup. - using the parameter @sync_type='initalize with backup'
sql backups can be compressed using zip and then uploaded to your main server - if you are clever you could script the whole process
Thank you Michael for your reply.
We get 1Million to 2Millions of records back from each (i.e. 2000 stores) stores when we initialize after our POS version change.
All these records gets replicated to our Home Office replication server from all stores into one common tables and we differentiate each stores records for those tables via "Store" columnname.
Can you please explain me step by step what I need to do once i am ready to reinitialize all store subscriptions?
I would really appreciate it
Thank you,
Hiren
May 18, 2012 at 7:08 am
hiren.patel 911 (5/18/2012)
michael vessey (5/18/2012)
hireni have done this before - there is an option that you can use, but it depends on how much of the data on your POS server is replicated back.
you can initialise replication from a database backup. - using the parameter @sync_type='initalize with backup'
sql backups can be compressed using zip and then uploaded to your main server - if you are clever you could script the whole process
Thank you Michael for your reply.
We get 1Million to 2Millions of records back from each (i.e. 2000 stores) stores when we initialize after our POS version change.
All these records gets replicated to our Home Office replication server from all stores into one common tables and we differentiate each stores records for those tables via "Store" columnname.
Can you please explain me step by step what I need to do once i am ready to reinitialize all store subscriptions?
I would really appreciate it
Thank you,
Hiren
you need to script all of this up and test it very thoughrouly before you apply it to live
issue a backup database command
zip database
copy to head office
drop the current subscription (sp_dropsubscription)
create a new subscription using sp_addsubscription and use the parameter @sync_type='initalize with backup'
restore the Database to a temporary location
copy the data from your temp database to the main table
drop the temporary database
start the replication agents
that should be pretty much it - there may be additional steps, but i'm sure you will be able to figure out the tiny details
MVDBA
May 18, 2012 at 7:10 am
if @sync_type initialize from backup is not the correct one, then it will be @sync_type='none'
see books on line
http://msdn.microsoft.com/en-us/library/ms181702.aspx
MVDBA
May 18, 2012 at 7:26 am
Mike,
I have the exact same situation and setup. Are you using a T1 between your location and the stores? T1 is a major bottleneck when it comes to this type of problem. What I can do here in the corporate office over our LAN in minutes translates into hours at the stores. I would assume tht while this is going on that your bandwidth utilization between the stores and you is fully utilized and that you are seeing network asynch waits on your distributor. Have you adjusted the batch sizes for your subscribers?
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
May 18, 2012 at 7:52 am
michael vessey (5/18/2012)
hiren.patel 911 (5/18/2012)
michael vessey (5/18/2012)
hireni have done this before - there is an option that you can use, but it depends on how much of the data on your POS server is replicated back.
you can initialise replication from a database backup. - using the parameter @sync_type='initalize with backup'
sql backups can be compressed using zip and then uploaded to your main server - if you are clever you could script the whole process
Thank you Michael for your reply.
We get 1Million to 2Millions of records back from each (i.e. 2000 stores) stores when we initialize after our POS version change.
All these records gets replicated to our Home Office replication server from all stores into one common tables and we differentiate each stores records for those tables via "Store" columnname.
Can you please explain me step by step what I need to do once i am ready to reinitialize all store subscriptions?
I would really appreciate it
Thank you,
Hiren
you need to script all of this up and test it very thoughrouly before you apply it to live
issue a backup database command
zip database
copy to head office
drop the current subscription (sp_dropsubscription)
create a new subscription using sp_addsubscription and use the parameter @sync_type='initalize with backup'
restore the Database to a temporary location
copy the data from your temp database to the main table
drop the temporary database
start the replication agents
that should be pretty much it - there may be additional steps, but i'm sure you will be able to figure out the tiny details
Hi Michael,
1. Issue a backup database command = I will do this at the each (i.e. 2000 stores) we run the SQL Express Edition and we create SQL native backups using SSIS package. Do i have to provide any command while taking backup? and should i create backup of the "Staging" database or the "Main" database at the store servers?
2. Zip database = I will zip the backup file once create at store server
3. copy to head office = I will copy the zip file once created to my home office server
4. drop the current subscription = Do i drop the subscription from the main replication server at the home office?
5. create a new subscription using sp_addsubscription and use the parameter @sync_type='initalize with backup' = We are using MERGE replication and procedure would be different "sp_addMergesubscription" does this can be used with Backup option?? I am assuming this would be again created under the the home office replication server from which i Drop the subscription from Step-5 ??
6. restore the Database to a temporary location = Copied zip file from store server when i try to restore on another database server, does that server needs to be setup for replication or it can be just normal database server and do i have to use any command while restoring the file??
7. copy the data from your temp database to the main table = I need to copy data from Step-6 database server to the main home office replication server under the common table (which will be used by other store data as well)?? won't it will say table is been used by the replication and cannot insert data??
8. Drop the temporary database = I will drop the database i created under step-6
9. Start the replication agents = You mean to start the replication subscription which i created under step-5??
Step-1 for backup, i wanted to make sure which database at the store i should backup, as we like this:-
Store.[Main Database] --> Store.[Staging Database] --> Home Office.[Replication Database Servers]
Sorry to ask you all this, as i wanted to make sure i get the whole concept and i do really appreciate all your help.
sjimmo,
We have " IPSec tunnels via the AFS and AT&T fiber connections.. " between Home Office and Store servers
Thank you,
Hiren
May 21, 2012 at 3:04 pm
Michael,
I tried what you said, but when I do first sync after doing above all the steps it still sends all the restored data (i.e. Upload) to the subscriber. How can I avoid that and how can tell merge subscription that all the data which I restored here at the Home office replication server are the same as that are with the store server.
Hers are the steps I did:-
1. I dropped the merge subscription from my home office replication server
2. Just to make sure, I dropped the Staging database we use at the store server (which get all the new data via replication triggers which are created under main database, so whenever Insert/Updates/Delete happens triggers will move data from main database to staging database on store server). I re-create the staging database (which has ONLY table we replicate via merge replication here at the home office from store server)
3. I recreated the merge subscription here at the home office with SYNC_TYPE = 2 under Sysmergesubscription, but, when I did that as tables were not there at the store server under staging database it failed and said table do not exists. So i changed the SYNC_TYPE = 1 from 2 and reinitialize the merge subscription, which successfully send down the initial snapshot.
4. I disable the merge subscription job, so it will not start on its scheduled time (we run sync every 30 mins). I than ran our initialization procedure which will re-create the replication triggers under main database and will insert all the records under staging database at the store server.
5. I changed the SYNC_TYPE = 2 from 1 under Sysmergesubscription and also change the SYNC_TYPE = 2 at the store server's staging database
6. I Did backup of staging database at the store and zip the file copied to home office dev server and restored the database.
7. I copied all the records from the restored database to our home office replication server.
8. I started the merge subscription back again under the replication server and it tried to send all the copied data from replication server back to store server. Which i want to avoid doing that and want to save time there.
Please any help would be appreciated or if i miss any step(s) please let me know.
Thank you,
HirenA
May 23, 2012 at 6:32 am
Any suggestions please???
Thank you,
HirenA
May 24, 2012 at 2:52 pm
Hi guys any help on this please??
Thank you,
HirenA
May 30, 2012 at 1:30 pm
Hi,
Does anybody have any suggestions on this issue?? I have been waiting on this issue for several days and if anybody have came across this issue it would be great to let us know please
Thank you,
HirenA
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply