Linked servers + stored procs used to be the go to ETL method in the good old days. But linked servers fell out of favour as ETL tools became more powerful, and physical data marts/warehouses became the flavour of the decade.
With the advent of in-memory analytics solutions they’ve made a bit of a comeback. The reason being, if the data is simple enough you can transform it into a star schema with views over the top of the operational data. These views are used as the source objects for loading into the in-memory analytics tool of choice (Power Pivot/Tabular cubes, Tableau, etc).
It’s a much more rapid dev/agile approach and delivers ROI within days/weeks as opposed to months (or longer!). There’s no monolithic ETL to manage, or need to acquire and manage a high performance server that only does stuff for an hour or two at night, then is only used for weekly or end of month reporting.
Database servers nowadays are fast enough to deliver data from views (you’d be doing the same transformation logic work, probably a few times over, to move the data from the OPS system to staging in your DM to merging into the final dim/facts, to loading into your cube or analytics package), and most organisations have at least one if not more data marts available already. Let the existing infrastructure do the work for you.
One scenario recently involved a number of horizontally partitioned databases. The schemas were identical, but the databases were partitioned by region. Each region had its own version of the database, some on different servers. And different functions were again separated (operational OLTP databases and customer details in the SAP databases)
I needed to transform the data into a star schema using a number views to UNION ALL the tables. But all the data needs to be visible from the same server instance, hence the linked servers.
There’s a few restrictions with the GUI tool that didn’t let me do what I wanted to and I couldn’t find much in the way of recent online details on how to do weird stuff with linked servers, such as:-
- Giving a linked server to SQL Server a ‘logical’ name that differed from the physical instance name (e.g. so I could move my view code from dev to test to prod without needing to refactor my linked server queries)
- Linking a server back to itself for situations where the infrastructure in dev didn’t match that in prod (e.g. all dev databases for the data that I’d be querying were on the same server, but were spread over multiple servers in production)
Here’s some snippets that’ll let you create circular linked servers (not allowed via the SSMS GUI), and also linked servers to MSSQL with a ‘logical’ name that differs from the server name (again, something not allowed via the SSMS GUI)
Linked Server with Different Logical Name
Say you want to create a linked server to instance [SQLDev\test1] and call it CustomerServer. You can put anything you want into the @datasrc parameter, including the server that you’re creating the linked server on.
Create the Linked Server
EXEC master.dbo.sp_addlinkedserver@server = N'CustomerServer',@srvproduct=N'MSSQL',@provider=N'SQLNCLI',@datasrc=N'SQLDev\test1'Create the Credentials
Now you just need to add the credentials. Here are two examples, one that leverages the AD credentials of the user trying to use the linked server (user needs to exist on destination server and have permissions to the objects they’re trying to access), and another using an SQL service account with username customerdata and password customerdata (SQL user needs to exist on the destination server and have the required permissions to access the requested objects)
AD pass-through Authentication - needs Kerberos when double hopping
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'CustomerServer',
@useself=N'True',@locallogin=NULL,@rmtuser= NULL,@rmtpassword= NULL
SQL Service Account - don’t forget to turn on mixed authentication on the target server
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'CustomerServer',@useself=N'False',@locallogin=NULL,
@rmtuser= N'customerdata',@rmtpassword= N'customerdata'Caution
Attempting to modify this linked server via the SSMS GUI will probably throw all kinds of weird errors. Just delete them and re-create if you need to change anything.
