Software development, .Net, SQL Server, TDD, Agile, Community and other Odds and Sods
Mitch Wheat has been working as a professional programmer since 1984, graduating with a honours degree in Mathematics from Warwick University, UK in 1986. He moved to Perth in 1995, having worked in software houses in London and Rotterdam. He has worked in the areas of mining, electronics, research, defence, financial, GIS, telecommunications, engineering, and information management. Mitch has worked mainly with Microsoft technologies (since Windows version 3.0) but has also used UNIX. He holds the following Microsoft certifications: MCPD (Web and Windows) using C# and SQL Server MCITP (Admin and Developer). His preferred development environment is C#, .Net Framework and SQL Server. Mitch has worked as an independent consultant for the last 10 years, and is currently involved with helping teams improve their Software Development Life Cycle. His areas of special interest lie in performance tuning
Thursday, June 16, 2016
SQL Server: Create a linked server from an on-premise SQL Server* to SQL Azure
* SQL Server 2008 onwards
The use of SQL Server’s sp_addlinkedserver can be a little arcane. I recently needed to connect from an on-premise SQL Server to SQL Azure for the purpose of querying and downloading data to a reporting server, and there a couple of things to note.
One is that you should ensure that data is encrypted on the wire (* and also when connecting to remote servers from SSMS).
The other is that you have to may specify the server name as a DNS name (depending on the client connection library you are using). This is the case if you receive this error message:
Here’s the working TSQL script I used:
-- If linked server already exists, drop it and any associated logins.
exec sp_dropserver 'LocalLinkedServername', 'droplogins'
-- Create the linked server:
@server = 'LocalLinkedServername',
@srvproduct = N'Any',
@provider = 'SQLNCLI',
@datasrc = '??????????.database.windows.net', -- Azure server name
@location = '',
@provstr = N'Encrypt=yes;', -- * Important!
@catalog = 'RemoteDatabaseName'; -- remote(Azure) database name
-- Create the login credentials for the linked server
@rmtsrvname = 'LocalLinkedServername',
@useself = 'false',
@rmtuser = 'remotesqlusername@??????????.database.windows.net',
@rmtpassword = 'remote password';
EXEC sp_serveroption 'LocalLinkedServername', 'rpc out', 'true';
-- Don’t elevate to distributed transactions
EXEC sp_serveroption 'LocalLinkedServername', 'remote proc transaction promotion', 'false';
---- Finally, check you can access remote data via your linked server:
select top 100 *
Another issue you might run into is if you have SQL Database auditing turned on in Azure, Azure wants all connections to be from a secure connection string, and if you run the above script from certain versions of SSMS (I believe SQL Server 2012 and below but not verified) then you might get an error saying only connections with secure connection string are allowed (despite a secure connection being specified). The fix is easy, change
Keep in mind that prior to SQL Server 2012 SP1, remote server statistics won’t be able to be used to determine query plans unless admin credentials are used (not a good idea!). Prior to SQL Server 2012 SP1 there is a workaround shown here: SQL Server Linked Servers and Remote Statistics
Thursday, June 02, 2016
SQL Server and SQL Azure: Clear Plan Cache
For on-premise SQL Servers you can run
which clears the entire server cache (provided you have the ALTER SERVER STATE permission). Many online resources and SQL Server Books Online give scary warnings about running this, but running DBCC FREEPROCCACHE will cause very few problems, even on a busy OLTP system. It will cause a small CPU spike for a few seconds as query plans get recompiled. It can be a useful tool when base-lining expensive queries or stored procedures.
If that’s not selective enough, you can free the cached plans for a single database using an undocumented DBCC command, FLUSHPROCINDB:
-- Flush all plans from the plan cache for a single database declare @dbid int; select @dbid = dbid from master.dbo.sysdatabases where name = 'MyDatabaseName'; dbcc flushprocindb(@dbid);
If you want to remove a single plan from the cache:
-- Get the plan handle (varbinary(64)) for a cached query plan select cp.plan_handle, st.text from sys.dm_exec_cached_plans cp cross apply sys.dm_exec_sql_text(plan_handle) st where text LIKE N'%GetJournal%'; -- Remove a specific plan from the cache using its plan handle dbcc freeproccache (0x060050000C267C1030CE4EC70300000001000000000000000000000000000000000000000000000000000000);
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
MSN, Email: mitch døt wheat at gmail.com