SQL Shared Hosting Security Issues for Online Inventory Publishing

• aaron.d.brown

  Newbie

  Points: 1

  More actions

  June 12, 2014 at 9:39 am

  #99914

  Hey All,

  Very new to Advanced SQL Administration, my knowledge is more medium level SQL statements for .net websites. I am working something new to me with the company I work for and I am struggling here. Could use any help anyone could provide, even just suggestions and tutorials or a point in the right direction.

  Scenario:

  14 retail stores with a centralized physical local MS SQL Server 2005 as a datastore for all inventory items within all 14 stores. Each store has a CRM/POS software package that remotely connects and uses the centralized SQL Server 2005. I have full control of this server. Trying to “Real-time” or “Near Real-time” publish inventory online to our windows shared webhost running MS SQL Server 2012 as inventory items become available for sale, or removed when sold.

  Problem:

  Due to security of shared webhosting 'Ad Hoc Distributed Queries' and SSIS are not able to be used. I only have control of created database(s) and no other functionality of the hosted SQL Server. I have no way of getting the 2 SQL servers to communicate with each other directly.

  I need to be able to have an automated procedure to SELECT a single inventory record from the local SQL server on a per transaction basis and INSERT it into the webhost server without the use of 'Ad Hoc Distributed Queries', SSIS or any other form of SQL server to server direct connection.

  Possible Solution(s):

  So far, I have just for testing purposes created a test.aspx webform, and in the page load event handler I've written some VB code that successfully extracts a single inventory record from the local SQL database and inserts that record into the remote webhost database, works perfect. So I would expect my next step would be to create this same VB code as a SQL CLR assembly and execute it from an INSERT/UPDATE trigger in the local SQL database and let the code work as a “go between” to select and insert records between the 2 SQL Servers databases...

  YES/NO...? Any input?... I've never done any SQL CLR coding before....

  Or is there something else someone can think of that I am missing? Should I have any concerns with bogging down the local SQL server causing havoc within the stores? There will be about 250 transactions per day hitting the database. So volume is not crazy. Also what about security?

  Thanks in advance,

  Aaron