SQL Server 2016 Query Store with Linked Servers

On servers [WIN-TC4FTRLFICH\SQL2016X1] run

create database davwill;
use davwill;
create table dbo.justdave1(a int, b int);
insert into dbo.justdave1 values(1,1);
ALTER DATABASE CURRENT SET QUERY_STORE = ON;

On linked server  [WIN-TC4FTRLFICH\SQL2016X2] run

create database davwill;
use davwill;
create table dbo.justdave1(a int, b int);
insert into dbo.justdave1 values(1,2);
ALTER DATABASE CURRENT SET QUERY_STORE = ON;

Then on the first server run:

use davwill;

select top 1 a.b,b.b from davwill.dbo.justdave1 a,
[WIN-TC4FTRLFICH\SQL2016X2].davwill.dbo.justdave1 b
WHERE a.a=b.a

Then check the query store on the first server database davwill

select top 3 * from sys.query_store_query_text order by query_text_id desc

query_text_id    query_sql_text    statement_sql_handle    is_part_of_encrypted_module    has_restricted_text
3    select top 3 * from sys.query_store_query_text order by query_text_id desc    0x09006E854375827B18C9B88B2280364E194F0000000000000000000000000000000000000000000000000000    0    0
2    select top 1 a.b,b.b from davwill.dbo.justdave1 a,  [WIN-TC4FTRLFICH\SQL2016X2].davwill.dbo.justdave1 b  WHERE a.a=b.a    0x0900E5E3F6B306B5BF3E00E6F014C0384CED0000000000000000000000000000000000000000000000000000    0    0
1    SELECT * FROM sys.database_query_store_options    0x09003CD7308C321E569EFC0C639317339F8C0000000000000000000000000000000000000000000000000000    0    0

Check the query store on the 2nd server database davwill:

select top 3 * from sys.query_store_query_text order by query_text_id desc

query_text_id    query_sql_text    statement_sql_handle    is_part_of_encrypted_module    has_restricted_text
2    select top 3 * from sys.query_store_query_text order by query_text_id desc    0x09006E854375827B18C9B88B2280364E194F0000000000000000000000000000000000000000000000000000    0    0
1    SELECT * FROM sys.database_query_store_options    0x09003CD7308C321E569EFC0C639317339F8C0000000000000000000000000000000000000000000000000000    0    0

Nothing appears in the query store on the linked server!

SQL Server 2016 CTP2 – Create Linked Server

SQL Server 2016 CTP2 – Create Linked Server

First expand linked servers -> Providers and check the properties for “SQLOLEDB”, enable propery “Allow inprocess” – https://support.microsoft.com/en-us/kb/2450479?wa=wsignin1.0

Create the linked server

– on the General Properties page Choose “Server Type” SQL Server, add the server as “machine\server”

– on the Securtiy Properties page under “For a login not defined in the list above,connections will be made:” Choose “Be made using the login’s current security context”

Test with

select top 1 a.name,b.name from master.dbo.spt_values a,
[WIN-TC4FTRLFICH\SQL2016X2].master.dbo.spt_values b
where a.number=b.number and b.number=1