MSSQL Server. Linked Server Application Example
Today I decided to share an article as once a linked server came to my rescue when working with MSSQL. First, I will describe the situation in which I had to meet him.
I worked as a web programmer in the information center of one of the ministries with about a hundred subordinate institutions. In each subordinate institution, a desktop program written in delphi was installed on the server, into which data was entered daily. Once a quarter, each such institution needed to upload a dbf file, come to our center, according to this upload, receive reports and submit them to the ministry. So it was still in the dosov program, and then this algorithm, simply without changing anything, was transferred to delphi. Unloading was performed using Transact-SQL, and the logic in it was not simple.
In parallel with this, a bus worked in offline mode, which collected data from all institutions on a single central server. There were buggies in the bus: it created duplicates on the primary key and not all data arrived. There was no specific algorithm for correcting these errors; different employees were engaged in this at different periods of time without notifying each other. The tire designer quit. After three years of such work, the data on the central server significantly differed from the data on the servers of the institutions, however, all officially adhered to the version that there were no problems with the bus.
At one point, the file upload scheme was considered obsolete, and money was allocated for revision. It was decided to submit reports on the site in your account at the click of a button. Now, employees of institutions did not have to go to submit a report to us first and then to the ministry, all communication should be carried out through the site. Data had to be taken from a central server. The head of the department, despite the fact that he knew about the problems with the bus, ordered to take the procedure that worked on the servers of the institutions, add a condition taking into account the segment of the institutions in the sample, and upload reports on the site from the central server. After this was done, he appointed me responsible for this process, and the management officially announced that we were submitting reports to the ministry according to the new scheme.
Everything collapsed. Due to data discrepancies between the servers, the reports were with incorrect numbers. Everything also came down in terms of performance, we were not ready for such a load. I was required to quickly solve the problem. The option to simply register the parameters for connecting to their database on each institution’s website and start the procedure on their server (using the programming language) was not suitable, since in addition to receiving the data, it was necessary to start processing each time to convert this data into reports. The processing procedure has already been implemented and debugged in mssql on a central server, and transferring it to a programming language would take a lot of resources and time. It was necessary to cope with the means of the database.
Googling, I found information that there are linked servers in MSSQL. Using them, for my server, I could configure communication with any remote server that is on the same network as mine and from which I have authorization data. After setting up, I could write a request on my server, indicate on which linked server it should be executed, and the request was executed on a remote server, using its databases and its resources.
To create a linked server, you need to run the script:
Server parameters
server - the name of the server we will use it
@datasrc - ip address of the remote server Server
authorization parameters
@rmtsrvname - the name that we assigned to the server
@locallogin - account name
@rmtpassword - account password
@rmtuser - database user
When creating a linked server, some of the parameters for accessing data are put in the value 'false' (a list of parameters you can see here ). If you need to set some parameters to 'true', for example, 'rpc' and 'rpc out', then you need to add the following commands to the creation script:
Note that in the server parameter we specified the name that we gave to the linked server.
As a result, the script to create a linked server would look like this
The request to the created server is executed, as well as to its own, but at the beginning a prefix with the name of the associated server is indicated. Also, when calling, you need to specify the name of the schema (in the example below, the schema is called 'DBO'):
In general, technical support for a couple of days for all institutions registered the connected server. I added the code so that the data is received from the servers of the institutions. The numbers in the reports became correct again and the issue of performance was resolved. That's how I quickly and easily got out of a difficult situation.
Of course, initially, when developing a system, you should not lay on linked servers to implement the described functionality. It is better to initially competently approach the design of the system, for example, by making it on the web, where there will be one central server and so that everything does not slow down to keep on staff specialists who are knowledgeable in database optimization. This example is for cases when the system is already designed, and it is unlikely that it can be redone. Also, a linked server will be useful when reconciling reports, if the data is only on the productive server, and you can change the stored procedure only on the development server.
I worked as a web programmer in the information center of one of the ministries with about a hundred subordinate institutions. In each subordinate institution, a desktop program written in delphi was installed on the server, into which data was entered daily. Once a quarter, each such institution needed to upload a dbf file, come to our center, according to this upload, receive reports and submit them to the ministry. So it was still in the dosov program, and then this algorithm, simply without changing anything, was transferred to delphi. Unloading was performed using Transact-SQL, and the logic in it was not simple.
In parallel with this, a bus worked in offline mode, which collected data from all institutions on a single central server. There were buggies in the bus: it created duplicates on the primary key and not all data arrived. There was no specific algorithm for correcting these errors; different employees were engaged in this at different periods of time without notifying each other. The tire designer quit. After three years of such work, the data on the central server significantly differed from the data on the servers of the institutions, however, all officially adhered to the version that there were no problems with the bus.
At one point, the file upload scheme was considered obsolete, and money was allocated for revision. It was decided to submit reports on the site in your account at the click of a button. Now, employees of institutions did not have to go to submit a report to us first and then to the ministry, all communication should be carried out through the site. Data had to be taken from a central server. The head of the department, despite the fact that he knew about the problems with the bus, ordered to take the procedure that worked on the servers of the institutions, add a condition taking into account the segment of the institutions in the sample, and upload reports on the site from the central server. After this was done, he appointed me responsible for this process, and the management officially announced that we were submitting reports to the ministry according to the new scheme.
Everything collapsed. Due to data discrepancies between the servers, the reports were with incorrect numbers. Everything also came down in terms of performance, we were not ready for such a load. I was required to quickly solve the problem. The option to simply register the parameters for connecting to their database on each institution’s website and start the procedure on their server (using the programming language) was not suitable, since in addition to receiving the data, it was necessary to start processing each time to convert this data into reports. The processing procedure has already been implemented and debugged in mssql on a central server, and transferring it to a programming language would take a lot of resources and time. It was necessary to cope with the means of the database.
Googling, I found information that there are linked servers in MSSQL. Using them, for my server, I could configure communication with any remote server that is on the same network as mine and from which I have authorization data. After setting up, I could write a request on my server, indicate on which linked server it should be executed, and the request was executed on a remote server, using its databases and its resources.
To create a linked server, you need to run the script:
EXEC master.dbo.sp_addlinkedserver @server = N'MY_SERV_1', @datasrc=N'192.168.1.1'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MY_SERV_1',@useself=N'False',@locallogin='sa',@rmtuser='sa',@rmtpassword='1111111'
Server parameters
server - the name of the server we will use it
@datasrc - ip address of the remote server Server
authorization parameters
@rmtsrvname - the name that we assigned to the server
@locallogin - account name
@rmtpassword - account password
@rmtuser - database user
When creating a linked server, some of the parameters for accessing data are put in the value 'false' (a list of parameters you can see here ). If you need to set some parameters to 'true', for example, 'rpc' and 'rpc out', then you need to add the following commands to the creation script:
EXEC master.dbo.sp_serveroption @server=N'MY_SERV_1', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server= N'MY_SERV_1', @optname=N'rpc out', @optvalue=N'true'
GO
Note that in the server parameter we specified the name that we gave to the linked server.
As a result, the script to create a linked server would look like this
EXEC master.dbo.sp_addlinkedserver @server = N'MY_SERV_1', @datasrc=N'192.168.1.1'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MY_SERV_1',@useself=N'False',@locallogin='sa',@rmtuser='sa',@rmtpassword='1111111'
GO
EXEC master.dbo.sp_serveroption @server=N'MY_SERV_1', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server= N'MY_SERV_1', @optname=N'rpc out', @optvalue=N'true'
GO
The request to the created server is executed, as well as to its own, but at the beginning a prefix with the name of the associated server is indicated. Also, when calling, you need to specify the name of the schema (in the example below, the schema is called 'DBO'):
SELECT * FROM [MY_SERV_1'].MY_BASE.DBO.MY_TABLE
In general, technical support for a couple of days for all institutions registered the connected server. I added the code so that the data is received from the servers of the institutions. The numbers in the reports became correct again and the issue of performance was resolved. That's how I quickly and easily got out of a difficult situation.
Of course, initially, when developing a system, you should not lay on linked servers to implement the described functionality. It is better to initially competently approach the design of the system, for example, by making it on the web, where there will be one central server and so that everything does not slow down to keep on staff specialists who are knowledgeable in database optimization. This example is for cases when the system is already designed, and it is unlikely that it can be redone. Also, a linked server will be useful when reconciling reports, if the data is only on the productive server, and you can change the stored procedure only on the development server.