In this video we're going to create Microsoft SQL server link server to broker all of our
rights to the data archive as part of our SSIS package. A Microsoft SQL server link
server is basically a wrapper around a OLEDB data source that presents itself
as a SQL server to third party clients. If something is set to connect to a SQL server
the SQL server can then pass the connection to the OLEDB datasource on its behalf. For
example a third party application can interact with the SQL server while the SQL server in the
back-end unknown to the client is interacting with the PI System via OLEDB. There are a few
pros and cons to using this method and it depends on the exact situation on whether
or not you should use a link server or else just use a direct OLEDB connection to the PI System.
For example when using a SQL server link server the data flow has changed instead of
the client connecting directly to the PI System it connects only to the SQL server. This means
that your network from the client to the SQL server is all that needs to be opened and you
do not need to open ports to connect to the PI System. As well as the software that's installed
on the client machines only needs to include SQL server Native Client which should be installed
by default by the operating system on Windows machine. By contrast if you're making a direct
connection to the OLEDB datasource you'll need to have the OLEDB provider installed on every
machine. Across a large enterprise this could be a difficult task to maintain by the IT department.
From a security perspective the extra layer introduces SQL server security which adds a
few more options and also makes it a little less trivial to configure. This can be both a pro and
a con as I could give you more options however could take more steps to complete successfully.
From a machine resource perspective using a SQL server link server allows all of your connections
to go through the SQL server which is a server. This allows your client to be thin and pass off
all the work to the SQL server even if you're using PI OLEDB provider and PI OLEDB enterprise
which are executed client-side for the query processing because in this case the client
itself would be the SQL server and your client for the application is a true thin client. If you
are using an OLEDB data source directly all this query processing would be on the client. However,
in the RTQP data model this isn't much of a difference because the query processing is
already happening on the AF server and not on the client in the first place. From a compatibility
perspective there may be some third-party tool that you're using that can only connect to SQL
servers, it cannot connect to OLEDB data sources for example. Therefore you could use the SQL
server link server as a wrapper around the PI System such that it uses a SQL connection but
in reality is getting data from an OLEDB data source. And finally the reason that we're going
to use it in this instance is the fact that we're writing to a tag. Anytime you're ready
to a tag using the buffer subsystem on a PI System the buffer subsystem creates a buffer
lock on the tag. If you're then having a bunch of clients writing to that same tag from let's
say multiple different dev machines building out the SSIS package you can get into a buffer
lock situation if the client machine that you're using does not have PI buffer subsystem installed
whereas another machine that's running the same tool during development does have it installed.
By keeping everything on one machine in terms of who is writing to the tag itself we're able to
get around these problems very easily. In this instance we just need to make sure that the PI
buffer subsystem is installed and configured on the SQL server hosting the link server and any
client machine executing this SSIS package whether in production or in development is
able to successfully write to the tag. To get started let's open up SSMS and look at the link
server options. Under server objects we see link servers and providers. Here we have a
list of all the providers that are installed on a machine including PI OLEDB provider, PI OLEDB
enterprise and PI SQL client. In this situation we're going to be using PI OLEDB provider.
Now to make sure we configure everything correct let's go to the OSIsoft tech support website and
find the KB that was written to assist in the creation of linked servers. Here I've signed
into the tech support website of tech support dot OSIsoft dot com and I'll go to my support
and search knowledge base. I'll click on KB articles and I'll start typing linked
server. Here we see how to configure a linked server with PI OLEDB or PI OLEDB enterprise
so I'll click on that. And here we have the steps to configure it by starting off with
the provider properties and then moving into the link server properties. In this specific
KB the PI OLEDB provider properties are in orange so let's go through and configure our
link server. We'll right click on properties for the provider and fill in these values.
Now I'll right click on linked servers and create new linked server.
Now for the security option we'll have everything just pass straight through with
Windows security. On the server options we'll return to the KB and look to see what it says.
We'll right click on the linked server and test connection and see that it works. We expand out
we can see all the catalogs we saw earlier in PI SQL commander. Let's open one now to prove it.
All the catalogs listed on the right are listed on the left as well. Now
let's execute a simple query in PI SQL commander and execute the same query in
the Microsoft SQL server link server. Right click on picomp2 and execute predefined query
This is the last hour of values for sinusoid and we'll copy this over and execute it on the SQL
server. We need to give it a context, which linked server we're talking about and then execute the
query. We notice that it fails because the date and/or time cannot be converted from the character
string that was provided into something usable by the SQL server. This is because the Microsoft
SQL server is actively trying to process the statement: * and *-1h are not Microsoft SQL
server syntax they are PI syntax. So there's two ways around this. The first is that we
can change these to static timestamps, so let's do that now and let's execute this query. Now
that we've given it static time stamps for the last five hours we can see some values appear.
This is because the SQL server knows how to parse these state time strings as opposed
to the * and *-1h that we get it earlier. We could also use dynamic times stamps with T-SQL
for example using the date add and get date T-SQL functions to get an hour from now and
now as our two timestamps. We execute that, we see that we have one value returned. From this
you can see that whenever you use the Microsoft SQL server to parse your queries you need to
make sure that you're speaking the language that the Microsoft SQL server can understand.
Another alternative is to use what's called an open query and I'll show that now. With
open query we pass a query string to a linked server and the Microsoft SQL server
does not try to process anything it simply hands-off the query provided to the linked
server provided and displays the results, in this case pasting select star from the
result. When doing this we also have to escape the quotes because the PI OLEDB provider needs
to have the quote provided to it. Let's execute this query to make sure that it
returns the results that we expect. So in this instance we're using the Microsoft SQL server
linked server however we're providing it with PI syntax. These are the two options and it really
depends on your situation whether you want to use open query or use the direct query.
Now for our SSIS package we're going to want to write a value to the PI server using this
linked server so let's make sure that we can do that now. Let's open up the query file that
we had before and let's execute this within an open query. Now let's execute the query. When
we try to do this we see the error message that we cannot process the insert statement because
there are no columns or the user does not have permission on the object. If you receive an error
message like this I encourage you to look at our tech support website to see if you can find the
answer already in one of our KB's or just simply open a tech support case with one of our tech
support representatives. In the interest of time on this video I'm going to jump directly to the
answer which is found in our PI OLEDB provider user documentation. Here we see our PI OLEDB
specific initialization properties one of which is always return row set. The description is if set,
all SQL commands return row sets containing the number of rows affected by the execution.
This property enables execution of non query commands as pass through SQL server queries.
This is exactly what we're doing right now by passing through an insert statement through
the linked server to the PI OLEDB provider. So we need to change our linked server properties
to include this OLEDB property. So if we go into our linked server and we right click on it go to
properties and I'll see that the provider string as well as many other items on the configuration
are grayed out. This is because once a Microsoft SQL server linked server is created it needs to be
deleted before it can be changed. Luckily it's not as difficult as simply deleting the linked
server entirely and starting over but instead can be scripted very easily by right clicking
on the linked server. So let's do that now. Right click, go to script linked server as,
drop and create to, new query window. What this is is a sequence of commands that starts off
by dropping the link server that we have and then recreating it using all the configuration
items that we already provided to it. This means that all we need to do is change the
provider string here in this script, run it and our linked server will never actually appear to
be deleted. So let's do that now. Let's find the provider string and let's add two more
properties. We'll execute this. Show that it completed successfully and I'll right-click and
go to properties of our linked server and see that our provider string has changed.
As you can see this was a relatively straightforward process and our linked
server never appeared to disappear at any time. So now let's go back and execute that query that gave
us the error last time. Now we see the affected row count is 1 and we did not get any error
messages. Let's run this query to make sure that we have written the value successfully. Great,
here is our test from before and here is our test now. Now our query is ready to
be used within our SSIS package which we'll build out in our next videos.
This concludes the video on Microsoft SQL server
linked servers, when to use them and how they work. Thanks for watching