locked
Ideas about how to do that RRS feed

  • Question

  • hi there,

    In every retail store we have an Express edition of SQL (2014) with a couple of databases, one is for messaging and another is for the stock and several times a day is synchronized against an Asyn client and then to our Headquarters Always On ERP.

    Obviously SQL Server Agent does not exist there. I am wondering how to design a standard maintenance plan (backups and reindexes as first priority) for this installations. Once is consolidated the second step is spread the same plan to the rest of the stores.

    Apart of using Windows Scheduler tool, any ideas?

    Thanks for sharing ideas,

    Wednesday, October 5, 2016 1:45 PM

Answers

  • It sounds like you have some connectivity between the head office and the retail stores as you mention an async client. Replication should be able to run on whatever port this is running on. You can also use this port to run SQLCMD on to connect from the head office to the retail stores.

    Now the fact that these machines are not in the same domain is irrelevant as you can use pass through authentication (same account and password - different domain/workgroup), or sql authentication.

    • Proposed as answer by Teige Gao Thursday, October 6, 2016 2:48 AM
    • Marked as answer by Enric Vives Friday, October 7, 2016 4:19 PM
    Wednesday, October 5, 2016 3:50 PM

All replies

  • I use merge replication for this to synchronize the data. I use Windows task scheduler to do the sync. I also use it and SQLCMD to do the backups and reindexing.

    I am unaware of any other option.

    Wednesday, October 5, 2016 2:54 PM
  • thanks for your reply Hilary, merge replication? But afaik Express stuff can be serve as publisher/distributor.
    Wednesday, October 5, 2016 3:15 PM
  • Head office would need to be standard edition or above. The subscribers - your branch offices/retail stores can run express.
    Wednesday, October 5, 2016 3:21 PM
  • would not be easier to restore a backup into my Enterprise toy, apply all the REBUILD/REORGANIZE/STATS and restore back in my Express box?
    Wednesday, October 5, 2016 3:25 PM
  • While that is possible I think you would find that difficult to automate and much more complex than doing it in place.

    Note also that you might be able to connect to your retail stored via sqlcmd.exe on your heard office and do the reindexing, update statistics there. You could schedule this via your job scheduler on your head office.

    Wednesday, October 5, 2016 3:36 PM
  • Hilary,

    Well, as first issue the stores are not in our domain are just workgroups (pos +  pc with Express or sometimes POS and Express at the same box). If I want to check something Team Viewer is required.

    Wednesday, October 5, 2016 3:46 PM
  • It sounds like you have some connectivity between the head office and the retail stores as you mention an async client. Replication should be able to run on whatever port this is running on. You can also use this port to run SQLCMD on to connect from the head office to the retail stores.

    Now the fact that these machines are not in the same domain is irrelevant as you can use pass through authentication (same account and password - different domain/workgroup), or sql authentication.

    • Proposed as answer by Teige Gao Thursday, October 6, 2016 2:48 AM
    • Marked as answer by Enric Vives Friday, October 7, 2016 4:19 PM
    Wednesday, October 5, 2016 3:50 PM
  • Hi Hilary, how can I get my SqlExpress store from our headquarters if it runs in a local workgroup in the same store... I can't create a trust relationship between two domains in this case.
    Wednesday, October 5, 2016 5:02 PM
  • Either use sql authentication or have identically named accounts on both your headquarter server and your SQL Express server. For example create a windows account in your headquarters server called repl, Give is a password of Password123! Create the same account called repl on each of your stores and give it the password of Password123!

    The way authentication works is that it checks the local machine for the matching account and password and if it does not authenticate it goes against the domain.

    Wednesday, October 5, 2016 5:55 PM
  • Thanks for your reply, ok, created both sql login with same password in both sides. 

    In spite of PING is responding from the headquartered SQL Server to my Express, from SSMS I can't get that SQL.

    Issue here: I can't open the firewall neither domain or public sections from my store for security reasons.

    I attach you my schema,


    • Edited by Enric Vives Friday, October 7, 2016 7:38 AM
    Friday, October 7, 2016 7:38 AM
  • CORRECTION: yes, I can connect from my headquartered Always On but only for IP.

    So I just need to take a look on hosts file.

    Thanks a lot Hilary!!

    Friday, October 7, 2016 7:57 AM