Free Courses
Posted: June 15, 2015 Filed under: Training | Tags: Training Leave a commentFree courses
Data Scientist
Pluralsight Starter Subscription for Microsoft TechNet (up to 30 June)
https://offers.pluralsight.com/technet
IBM Open Badge Program
http://www-304.ibm.com/services/learning/ites.wss/zz/en?pageType=page&c=K023717Z98079G33
Microsoft Virtual Academy
http://www.microsoftvirtualacademy.com/
SQL Server 2016 Query Store with Linked Servers
Posted: June 1, 2015 Filed under: Microsoft SQL Server 2016 | Tags: Microsoft SQL Server 2016 Leave a commentOn 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
Posted: June 1, 2015 Filed under: Microsoft SQL Server 2016 | Tags: Microsoft SQL Server 2016 Leave a commentSQL 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