April 13, 2012 at 11:36 am
I NEED YOU HELP
ANY IDEA ANY SOLUTION
SCENARIO:
An organization suppose Jason industries have its branches in different regions of country. In each branch there is a local database where they maintain their data locally. Suppose one branch in New York, one in Washington, one in Texas.
There is a main database in the main branch of the organization. At the end of each week or month each branch submits their data to the main database. I can say that they synchronize there data in the end of week or month. Note that each branch has its local database where data is kept in disconnected mode from main database. Only at end of each week or month the whole data of every branch is submitted to the main database..
My question is that is it possible? If yes, what this concept is called? And where from can I get help on this topic?
(if there is in mistake in my post i m sorry, i m so weak in english)
April 13, 2012 at 11:44 am
there's different ways i can think of offhand.
one is ETL (Extract, Transform, Load); that would typically be export to a file, and an import file pulls the data into the "master" database, usually into staging tables first, transforms data where required , and hten loads it into the final table.
Another method is with Replication.
you can use Replication to copy data between Sql servers like you describe.
Lowell
April 13, 2012 at 11:46 am
My 2c:
Yes, it is possible. And, it is called Merge (?)
What I can think of is that every branch has its own locationID and unique. So, when you merge (or consolidate) the data from branches, you don't have confiliction.
April 13, 2012 at 12:34 pm
Sounds like homework to me.
April 13, 2012 at 2:15 pm
aliraj0 (4/13/2012)
I NEED YOU HELPANY IDEA ANY SOLUTION
SCENARIO:
An organization suppose Jason industries have its branches in different regions of country. In each branch there is a local database where they maintain their data locally. Suppose one branch in New York, one in Washington, one in Texas.
There is a main database in the main branch of the organization. At the end of each week or month each branch submits their data to the main database. I can say that they synchronize there data in the end of week or month. Note that each branch has its local database where data is kept in disconnected mode from main database. Only at end of each week or month the whole data of every branch is submitted to the main database..
My question is that is it possible? If yes, what this concept is called? And where from can I get help on this topic?
(if there is in mistake in my post i m sorry, i m so weak in english)
From my understanding, you can use Merge replication...The main database is the publisher and the local databases are the subscribers. Initially you can use snapshot to synchronize all the databases. In merge replication, the publisher and subscribers are allowed to make changes independently and once they are connected, they synchronize again.
Regards,
TA
Regards,
SQLisAwe5oMe.
April 13, 2012 at 11:45 pm
Seems like ETL to me. The various branches can accumulate the data in XML form or in the form of Excel sheets. This data can then be mailed to the Head Office at the end of every week or month(or whenever they want to synchronise the data).
This can be then transformed and Loaded into the Main Database.
An example could Shopping mall's database where customer, Sales details may be maintained at different branches and then could be transferred to the Main Database at the end of every day.
Here's a link on ETL:
April 14, 2012 at 9:49 am
vinu512 (4/13/2012)
Seems like ETL to me. The various branches can accumulate the data in XML form or in the form of Excel sheets. This data can then be mailed to the Head Office at the end of every week or month(or whenever they want to synchronise the data).This can be then transformed and Loaded into the Main Database.
An example could Shopping mall's database where customer, Sales details may be maintained at different branches and then could be transferred to the Main Database at the end of every day.
Here's a link on ETL:
why do a manual process? use xp_cmdshell (SP) and a program such as winscp to schedule a job to export the info and upload it to an ftp server at the head office. then a job at the head office imports the data. takes the human element out and as we all know, the less human element the less a chance for failure.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 14, 2012 at 11:03 am
Lynn Pettis (4/13/2012)
Sounds like homework to me.
Agreed.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 14, 2012 at 11:21 am
Thanks to all of you... Your ideas helped me a lot...
actually i need the same database to be on both main branch and in sub branches..
for example
tblBranch is the main table...
1. in main branch all the data of all branches will be available in this table,...
2. in sub branches every branch will have its own data in this table...
3. remaining tables are suppose two tables, tblSales & tblPurchases...
both have a branchID... now each branch will have their own data in these tables...
excepting to main branch... when sub branches sync data with main branch... the main branch then will have over all data...
but every sub branch will have only their own data in their local DB
April 14, 2012 at 12:13 pm
aliraj0 (4/14/2012)
Thanks to all of you... Your ideas helped me a lot...actually i need the same database to be on both main branch and in sub branches..
for example
tblBranch is the main table...
1. in main branch all the data of all branches will be available in this table,...
2. in sub branches every branch will have its own data in this table...
3. remaining tables are suppose two tables, tblSales & tblPurchases...
both have a branchID... now each branch will have their own data in these tables...
excepting to main branch... when sub branches sync data with main branch... the main branch then will have over all data...
but every sub branch will have only their own data in their local DB
How does everyone feels about merge replication?....I think merge replication would work for this requirement.....my only concern is about the requirement about "every sub branch will have only have their own data"....this true when these local DBs disconnected....but once they are connected it will synchronize all data to maintain DB consistency.
Looking forward to everyones inputs.
Thanks,
TA
Regards,
SQLisAwe5oMe.
April 14, 2012 at 12:16 pm
Merge replication looks like overkill here. If all that's needed is to copy data once a month that's a simple ETL process with SSIS packages, scheduled in SQLAgent most likely.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2012 at 1:29 am
GilaMonster!,
I've all details in docx file, please read these two page... and you will be clear... your ideas are highly appriciated..
attached here with this quote
April 15, 2012 at 2:08 am
Hi everyOne.... i've attached a look of the db of every branch and main branch...
please read this, and tell me how to achieve this
file contains the DBs of sub branches before submission of data,
after submission of data MainDB and every branch DB will have the data as shown in file....
April 15, 2012 at 5:55 am
SSIS packages or merge replication with filtered subscriptions. If I was doing this, I'd probably go with SSIS packages since it's a once-a-month sync. Merge replication would work too, probably less up-front work and more admin in the future.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2012 at 10:24 pm
capn.hector (4/14/2012)
vinu512 (4/13/2012)
Seems like ETL to me. The various branches can accumulate the data in XML form or in the form of Excel sheets. This data can then be mailed to the Head Office at the end of every week or month(or whenever they want to synchronise the data).This can be then transformed and Loaded into the Main Database.
An example could Shopping mall's database where customer, Sales details may be maintained at different branches and then could be transferred to the Main Database at the end of every day.
Here's a link on ETL:
why do a manual process? use xp_cmdshell (SP) and a program such as winscp to schedule a job to export the info and upload it to an ftp server at the head office. then a job at the head office imports the data. takes the human element out and as we all know, the less human element the less a chance for failure.
Agreed Capn., the Human element can be taken out.
But that's not the whole scenario where I work. It depends on the importance or sensitivity of the data. Depending on the sensitivity and other important properties we either - use FTP or get hard disks delivered to the head office.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply