0

This question maybe a matter of opinion, so I apologize if this falls under an opinionated question. To try to narrow the scope of the question I will be detailing what and why I am trying to do this.

I am using SQL Server 2014. The idea is that we have a main piece of software with a database hosted on one server which our HR department uses to add, update and delete company employees. I was brought in to design and develop new websites for specific work flows and I have tables in each of the websites I have been working on that store information about users. What I am trying to do is set triggers on the main system database tables that relate to employee information so that it inserts records into an employee update table which I then display to administrators to approve, in which case the changes will then be applied to the tables I have in my new website's database(s). The problem is that my databases are hosted on a different server than the one which hosts the main software database.

I have read through this article (https://www.sqlshack.com/querying-remote-data-sources-in-sql-server/) and I looked at the various ways to connect to a different server. The 3 ways I read about to accomplish this are as follows: OPENDATASOURCE, OPENROWSET, LINKED SERVER

After reading through the article this one statement sticks out to me, "A SQL Server Linked Server is different from ad hoc queries in that ad hoc queries open a temporary connection with the remote server and close it, where the permanent linked server is always available for use."

As I only need to open the connection on triggers to do an insert I thought the ad hoc method would be best as I won't continually have a connection to the remote server. This is the first time I have ever attempted something like this, so I appreciate any constructive comments as to which method you would choose to do this and why, and if I am going about this in the wrong way, please suggest how you would do it and provide some references as I would be happy to read through articles or links suggested to help me in development.

Improve this question
11
  • 3
    So presumably you are going to create INSTEAD OF trigger here? I would just do this as an insert via linked server. Pretty straight forward and easy. Commented Feb 9, 2018 at 19:38
  • 1
    You can connect sql server even oracle with linked server. Commented Feb 9, 2018 at 19:41
  • 1
    Then you would create those triggers on those tables. Not sure what the confusion is. If the trigger is on the remote database and the destination table is on that same server there is no need for any remote anything in your trigger. As for an instead of trigger it would run your trigger instead of a normal insert. This sounds like you want to happen here. It wouldn't really update the account info, instead it would insert into another table with the new values that would need to be approved before the insert or update happened on the main table. Commented Feb 9, 2018 at 19:48
  • 1
    I think you might want to sit down with a flowchart and map this out. It sounds like you are getting a bit lost in the details. It can be tricky doing this kind of thing across multiple databases in multiple servers. Commented Feb 9, 2018 at 19:49
  • 1
    OK. Seems a little oddball to approve a change in the master table before displaying it in another system but whatever. Commented Feb 9, 2018 at 19:53

2 Answers 2

Reset to default
2

You need create linked database and query this database with four part naming query.

you can create linked database use this syntax an sp_addlinkedserver like this:

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ] 
 [ , [ @provider= ] 'provider_name' ]
 [ , [ @datasrc= ] 'data_source' ] 
 [ , [ @location= ] 'location' ] 
 [ , [ @provstr= ] 'provider_string' ] 
 [ , [ @catalog= ] 'catalog' ]

and then in your trigger insert to this linked database table using four part naming query like this:

insert into [server].[database].[schema].[object] values(...)

more info about linked server and datatbase : create-linked-servers-sql-server-database-engine

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

@Ryan i hope this is helpful for you
I believe it will be, thanks again for your advice and example.
2

If you don't want to have a hard coupling between the two systems, then consider SQL Server Service Broker. Service Broker will allow you to asynchronously communicate between the two systems by passing messages back and forth. For example, what happens if your application database is not available? Should the insert on the source system fail? If so, then a linked server query in a trigger is your easiest option. If you want a more fault tolerant approach, then Service Broker can meet your needs.

Intro to Service Broker. https://technet.microsoft.com/en-us/library/ms345108(v=sql.90).aspx

Improve this answer

1 Comment

Thank you for the link and for the suggestion, I believe that I am going to go with the linked server approach for now, but I may use your suggestion in the future. I appreciate it and wish I could mark your post as an answer as well, but since I am going with the linked server approach, I will be marking Hasan's post as my accepted answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.