I have a SP where if I run it directly against the vendor's database, it takes only 2 seconds to process. However, when I run the same SP through a link server, it takes about 4 minutes. Besides a OpenQuery to do a pass-through query, is there anything else I can do for performance?
Are you joining I local tables with remote tables?
No Yosiasz. The SP is calling table from vendor database.
What are you doing with the results from the stored procedure?
As for performance - if the stored procedure is returning data across the linked server then you are probably suffering from network latency. Where is the linked server located in relation to the local server? Are they in the same data center - or different data centers?
It could also be a driver issue - make sure the drivers are the latest available for the version of the database setup in the linked server.
The results from the SP goes into a SSRS report. The SP and Link Server are setup internally in our database.
We are on SQL Server 2016 Standard. Would you be kind enough to point out the drivers? Are they drivers for the database or specific drivers for connections? Thanks Jeff.
I think what Jeff might be asking is where is the Linked, not Link, server .
Are both servers within your network? Or in close physical or virtual proximity?
Can you pull the linked server data and sp to a database on the ssrs server via ssis and see if it faster?
The vendor server is in a different state from my current location.
As for pulling the data to the SSRS server via SSIS, that sounds like an excellent idea. However, I have never done it before. So do I use SSIS to pull it in real time into the SSRS server? How would I activate SSIS package from SP? Does the SP resides on the SSRS server or a local internal server?
Thank You
Do you have Integration Service Installed on your SSRS server? If so follow these steps
- Create a database on your SSRS Server named VendorStaging (Under OPtions select Simple recovery model)
- Right Click on database and then select Tasks >> Import Data
- Then Choose a Data Source of Microsoft OLE DB Provider for SQL Server (if Vendor server is ms sql) and put in there the name of the LinkedServer string
- Then Click Next and again select Destination of Microsoft OLE DB Provider for SQL Server. Add Server Name which is the server where you created VendorStaging database.
- Then select whichever way you want to copy the data, via tables or view or second choice write a query
on Last step you will notice it will give you two checkboxes
Run Immediately and Save SSIS Package. Click Save SSIS Package, uncheck Run Immediately.
After you pick a location (remember where it is ) you can open the SSIS package and modify the ssis package to your specs
So - to understand this better...you are querying from a linked server that pulls data from a server in another state. When you execute that stored procedure directly (assume you mean when connected directly to the vendor system) it returns in seconds - but over the linked server connection it takes minutes.
Question: why are you calling the stored procedure on your local server over a linked server? Is there something else in the local stored procedure that you are doing? If not - why not just call the stored procedure directly on the vendor server from SSRS?
Thank you yosiasz for that example. I will try it out tonight and get back with you. One question is since I got the SSIS package from the import, how would I fire it off from the SP so that I can refresh the data? Also, would the SP resides on the SSRS Server?
Hi Jeff, yes and confirming to your scenario. As for the question, it is because of two reasons. First, their main database is read-only. I cannot create anything on it. They did create us another database on the same server to stick our SPs. However, this kicks in for the second reasons. We do not want to give out the SQL Authentication account & password for the Vendor's server database to our contractors. Therefore, I setup a Link Server for security layer where I added the SQL Authentication account to it and our contractors would just query internally. I have also added in Synonyms as advised from a few on this forum.
So you have a database local to the vendor's database where your stored procedures reside. Your contractors do not develop on that system - they can only develop on your local system which uses a linked server to get to the data.
Here is how I would do this...
- Developers build and develop against the local database with the linked server. They utilize synonyms to access the data.
- Create shared data sources in SSRS for DEV and PROD - where DEV points to your local database and uses a fixed SQL account - and PROD points to the remote database and uses a different fixed SQL account (developers would not have access to either SQL account - they connect using Windows Authentication).
- Developers deploy to SSRS using the DEV data source - test and validate report
- Code Review procedure and report - once approved - the stored procedure is created on the remote database and tested/validated. Once tested/validate - the deployed report is then modified to use the PROD shared data source.
1 Like
Hi Jeff, thanks for being engaged in this.
"So you have a database local to the vendor's database where your stored procedures reside. Your contractors do not develop on that system - they can only develop on your local system which uses a linked server to get to the data."
Yes, this is correct.
"Developers build and develop against the local database with the linked server. They utilize synonyms to access the data."
When you said "Local Database", is that the local database on the Vendor's server or our local database? The reason I ask is because if it is a local database on our side, then why would I need a Linked Server? Or do you mean a local database on our internal server that links to our reporting server?
"Create shared data sources in SSRS for DEV and PROD - where DEV points to your local database and uses a fixed SQL account - and PROD points to the remote database and uses a different fixed SQL account (developers would not have access to either SQL account - they connect using Windows Authentication)."
So here, do I create DEV and Prod databases on the SSRS server? I apologize for not getting this part.
"Developers deploy to SSRS using the DEV data source - test and validate report"
If I can understand the step above, this would make perfect sense.
"Code Review procedure and report - once approved - the stored procedure is created on the remote database and tested/validated. Once tested/validate - the deployed report is then modified to use the PROD shared data source."
Yes, understood that the report will sits on our SSRS server and used the vendor's provided SQL Authentication account to connect to the SP sitting on their server with their local database. This means there is no need for Linked Server. Yes, I agree with this and can you confirm if I understood it correctly?
Thank You Jeff.
Do your vendors have access through the linked server directly to the vendor databases - or only to the stored procedures on the custom database on the vendors server? How exactly do they utilize the linked server...
Local = internal server
Remote = vendor server in other data center
I am assuming you have a dedicated database on your local server for the developers to work - and a dedicated database on the vendor/remote server...
The issue it seems you are having is using the dedicated database on your local/internal server to support these reports. The problem is that running this code across the linked server is slow and you need a better way of getting to that data.
No - SSRS uses data sources to connect to the databases where the data resides. If the developer creates a shared data source in their SSDT project where they have built the report - then deploy that report to SSRS - a shared data source will be created if it doesn't already exist. If all developers use the same shared data source - when deployed the data source will not be overwritten (unless you change that value in the project - which is not recommended).
This allows the developers to utilize Windows Authentication in their projects - and for that same named data source in SSRS to use 'different' authentication method. The developers do not need to know how SSRS is connecting...
This also allows you to build a separate data source - which points to the vendor system and uses yet another authentication method or account. Once the report has been deployed and tested - you can update the definition of that report in SSRS and change the data source from the DEV data source to the PROD data source and the report will execute against the vendor system directly.
This requires that any code developed on the internal system to be migrated to the vendor system. This is the change control process that allows you to code review and test the new code or changes prior to moving to production.
Another option is to allow developers to deploy the report to a non-production folder in SSRS - and then you move the report to a production folder once approved. As part of the move you change the data source to the PROD data source.
Correct - the deployed report available to the end users would not run against the internal server using the linked server. That report will run directly on the vendor's system...
With that said - this could cause performance issues in production if those reports are not well written and do not take into account...but then again, using a linked server is also running directly against the vendor system and would cause the same performance issues (if not worse).
The ideal solution is to build a reporting database on your internal server. That reporting database would contain the data needed to support these reports - and would then be populated on a daily/weekly/monthly schedule. The reporting database could be constructed as a data warehouse - or regular tables with all available lookup tables - or flat reporting tables - with appropriate indexing to support the reports. This is a bit more complex though - it requires that you build an extract/transform/load process and schedule that process.
ETL can be done in SSIS or any other toolset you are comfortable with - the SSIS packages would be deployed to your internal server and run on your internal server and would only extract the necessary data to support the reports. That is - you don't extract all columns from all tables - just the columns needed from only those tables - filtered for the amount you need (ex: don't pull 15 years of data when you only need the past 2 years).
This solution is much safer because the reports are never run against your production system...you don't have to give access directly to production for the developers. The downside is that you don't have real-time reporting...data would be lagged at least a day - depending on how often you extract the data.
It also means the developers would have to request changes to what is extracted - if they need a new column or a new set of data a request would need to be put in to have that change made before they could move forward with their project.
Final note: every synonym that is created in the internal server database would need to be created on the remote server database. The difference is that the internal server database synonyms are setup to use the linked server - and the remote server database does not...
Example:
Local Internal Database: CREATE SYNONYM mySyn FOR linkedserver.database.dbo.table;
Remote Server Database: CREATE SYNONYM mySyn FOR database.dbo.table;
1 Like
You will need to create a sql job that points to that ssis package file or load it into the ssis catalog folder and point the sql job to it.
Then you can schedule it to run at a certain interval etc.
But first you would have to refine the ssis package by editing it in your visual studio, to bring in a subset of data broken down into maybe dimensions and fact tables.
Or you can opt to stage subsets of data then combine these into a flat table that contains all of the data.
For reporting purposes it is best practice to use a dedicated data warehouse as Jeff points out rather than hitting oltp tables especially across the wire over a linked server
1 Like
Good Morning Jeff,
"Do your vendors have access through the linked server directly to the vendor databases - or only to the stored procedures on the custom database on the vendors server? How exactly do they utilize the linked server..."
Our vendor only provides their read-only database. Their databases are connected to their cloud base application for our staffs to gather data about our patients. Our vendor don't have access to our internal database which the Linked Server is setup internally. Therefore, they don't have access to our Linked Server.
"I am assuming you have a dedicated database on your local server for the developers to work - and a dedicated database on the vendor/remote server..."
Yes, that is correct Jeff. On their server, there are two databases. One is read-only which is the production data. We (our company) cannot do anything to this database, except read-only. Any change of data has to be done from the the application. Their other database is read-write so that we can store our Stored Procedures, etc... Based from your last post, I am planning to store the final SPs in this database and point the reports to their servers.
As for internal, yes, we have databases for Developers to work on and this is where we do not want to give them the SQL Authentication account to the vendor database. Hence the reason for the setup of the Linked Server.
"The issue it seems you are having is using the dedicated database on your local/internal server to support these reports. The problem is that running this code across the linked server is slow and you need a better way of getting to that data."
Again, that is correct Jeff.
"No - SSRS uses data sources to connect to the databases where the data resides. If the developer creates a shared data source in their SSDT project where they have built the report - then deploy that report to SSRS - a shared data source will be created if it doesn't already exist. If all developers use the same shared data source - when deployed the data source will not be overwritten (unless you change that value in the project - which is not recommended)."
Yes, I agree and we have Shared Datasources setup for each environment. However, this maybe the missing link that you are have helped me understand. We both agreed that the final production reports should use a datasource that has the SQL Authentication to access the Vendor's environment. Therefore, no LinkServer is needed. I will incorporate this into our flow.
What remains is the performance during development. I realized during Development, we don't need real-time data. Therefore, it be ideal to pull the data locally to the internal databases as you and Yosiasz have suggested.
But as i finished reading the rest of yours and Yosiasz posts, you pretty much pointed me in the right direction with the missing knowledge of ETL and Data Warehouse. I was a bit confused before posting this question but THANK YOU SO MUCH to both for painting a clear picture for me. This is a great post!
Happy Holidays Jeff and Yosiasz!
dont get lost in the woods with DW and ETL. start small then work your way up. do it right the first time by reading and understading the principles of etl and dw. plan ahead well but don't be too persnickety but don't fly by the seat of your pants either.
1 Like
I do have a question for you Yosiasz.
So for the production reports, the flow will be like this.
- Window Authentication (internal users) will be used to access the SSRS tier.
- A dedicated Service Account will be used to access the SQL Server data tier from the SSRS tier as you have recommended. In our case, we will use the provided the SQL Authentication Account that the Vendor has provided as our Service Account. That SQL Authentication Account and password will be setup in a Shared Datasource.
So I am good with the production reports and environment.
The question pertains to the Development environment.
- Window Authentication (internal users) will be used to access the SSRS tier.
Should I setup a Service Account for this too? Probably not right because how would the Developers get to the tables? Should I let the Developers access the internal development database with Window Authentication? I would think so.
Last, should I use SSIS to pull all tables from Vendor's database to the reporting server or a separate server? I will setup a nightly job as you have recommended. Since I only have read access to the Vendor's database, is it possible to use SSIS to pull all tables and their foreign keys relationships or can it only pull data. Thanks
Best not to give perms to Ssrs to individual users but rather give perms to AD groups.
Developers should have access only to the dev instance of the dw. You can host the database on a diff server or keep it on the same server as where ssrs service runs.
Each environment with it's own legit windows service account (consult with your AD guardian sys admin)
- Dev
svc-ReportDev to read data from Dev DW
The sql user provided by vendor to fetch data from vendor. For security reasons you might not want to fetch vendor data to dev. You might want to fetch to an instance only few people can see then you obfuscate the data there and load to dev - QA
svc-ReportQa to read data from qa DW
Same as Dev - PROD
svc-Report
The sql account provided by vendor is used only on data connection on ssis package nothing else.
So now each svc- accounts in their respective environ will be used as the accounts in the data source of ssrs reports, read only.
The reason you might want to scramble the data in dev and Sqa is in case there is sensitive data. Come audit time your head might be on the plate if people had access to sensitive data. Financial, personal and HIPAA stuff
1 Like
Yes, we would give permission to SSRS by AD groups.
I would like to share with you that our Developers develop the SSRS reports and also write their own SPs as well.
When you abbreviate DW, I am assuming that is the Data Warehouse. Yes, Developers would only have access to developer environment, including Data Warehouse database since they will be writing their own SPs.
So two questions for clarification Yosiasz.
First, I also understand your setup of a Service Account for each environment (Dev, QA, Prod). It is in case those who do not have direct access to the databases. However, I would still allow our Developers/Contractors access to our DEV database through Window Authentication right? Of course, otherwise, how would they create their SPs. 
Second, why would the SQL Account provided by the vendor only used on data connection of SSIS package? I am not using SSIS package at all yet. I use their SQL Account in a Shared Datasource to call the SPs sitting on their server. On their server, the main database is read-only while the second database (reporting database), they allow us to create SPs on there. I think we are talking about the same concept but I just want to make sure I am not missing anything. Thank You Yosiasz
Yes on both questions. But pretty sure you are going to hit performance issues with calling a sp remotely still.
Also how many people will be using this report and how often?