Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Possible or Impossible - Replication and Mirroring

Author  Topic 

Zangetsu
Starting Member

6 Posts

Posted - 2014-06-25 : 16:57:07
Hi all, hope I am posting this under the correct "Forum Topic"

I need to know if the following could be possible or not, as I am quite new to SQL Server (Know the basics).

First off, this is my setup:
(1) 7 Individual SQL Servers with its own Master DB(Head Office and 6 Branches across the State)Networked via the internet (VPN)

(2) Each branch containing their own data (Products, Stock on hand, Product prices etc.) All 7 master databases are identical with the exception of quantity values, the only difference would be the values of physical stock on hand per branch and prices may differ.

Question 1
I had the idea to setup table replication between Head office and Branches. Would it be possible for Head Office to update the dbo.Product_Price table - Price_Value and then be replicated down to the 6 individual branches so they would not also need to manually update after each price change occurs.

Question 2
At the same time Head office would like to see the Stock on Hand values per branch, I was wondering if it might be work to create 6 Sub-Databases on the Head Office SQL Server and setup table mirroring between branches and sub-databases(Sub-Database per branch).

Please let me know if this could work or if I have not explained it clear enough.

Regards


Always trying to make the impossible, possible

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-25 : 17:04:23
1. Yes you can use replication, though I don't think I'd recommend it. Please note that an update will, by default, trigger an insert/delete on the subscribers. How about Change Data Capture instead?
2. Let's not use the "mirroring" term here as it refers to a technology in SQL Server. I would recommend instead having the application query the 6 remote databases.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Zangetsu
Starting Member

6 Posts

Posted - 2014-06-25 : 17:13:02
Thanks so much for the quick reply and I am truly sorry about the cross post will remove it.
I will definitely look into "Change Data Capture" and how it works also on your suggestion about querying the 6 databases. As I have mentioned I am not all that clever with SQL but will go read up and get back to you.

quote:
Originally posted by tkizer

1. Yes you can use replication, though I don't think I'd recommend it. Please note that an update will, by default, trigger an insert/delete on the subscribers. How about Change Data Capture instead?
2. Let's not use the "mirroring" term here as it refers to a technology in SQL Server. I would recommend instead having the application query the 6 remote databases.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



Always trying to make the impossible, possible
Go to Top of Page

Zangetsu
Starting Member

6 Posts

Posted - 2014-06-25 : 18:26:34
I quickly went and read up on the "Change Data Capture" like you suggested.
It is perfect for what I need but unfortunately would not be able to go this route as its only available on the Enterprise, Developer and Evaluation editions and the client has just bought SQL Svr Std of all his new servers.

quote:
Originally posted by tkizer

1. Yes you can use replication, though I don't think I'd recommend it. Please note that an update will, by default, trigger an insert/delete on the subscribers. How about Change Data Capture instead?
2. Let's not use the "mirroring" term here as it refers to a technology in SQL Server. I would recommend instead having the application query the 6 remote databases.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



Always trying to make the impossible, possible
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-25 : 18:45:52
Maybe add a trigger to the head office's table that writes a row to a table when the price gets changed. Then have a separate process update those 6 other servers. That process could be as simple as a T-SQL job that uses linked servers.

I would not advise doing the update in the trigger itself as that'll mean a distributed transaction.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -