admin

SQL Server: Create a linked server from an on-premise SQL Server* to SQL Azure

by
  • 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:

“Server name cannot be determined.  It must appear as the first segment of the server’s dns name (servername.database.windows.net).  Some libraries do not send the server name, in which case the server name must be included as part of the user name (username@servername).  In addition, if both formats are used, the server names must match. “

Here’s the working TSQL script I used:

 

— If linked server already exists, drop it and any associated logins.

begin try

    exec sp_dropserver ‘LocalLinkedServername’, ‘droplogins’

end try

begin catch

end catch

 

— Create the linked server: 

EXEC sp_addlinkedserver

    @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

go

 

— Create the login credentials for the linked server 

EXEC sp_addlinkedsrvlogin

    @rmtsrvname  = ‘LocalLinkedServername’,

    @useself     = ‘false’,

    @rmtuser     = ‘remotesqlusername@??????????.database.windows.net’,

    @rmtpassword = ‘remote password’;

go

 

 

EXEC sp_serveroption ‘LocalLinkedServername’, ‘rpc out’, ‘true’;

go

 

— Don’t elevate to distributed transactions

EXEC sp_serveroption ‘LocalLinkedServername’, ‘remote proc transaction promotion’, ‘false’;

go

 

—- Finally, check you can access remote data via your linked server:

select top 100 *

from [LocalLinkedServerName].[RemoteDatabaseName].[RemoteSchemaName].[RemoteTableName];

go

 

 

 

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

myServerName.database.windows.net

to

myServerName.database.secure.windows.net

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

 

Refs.:

TOP 3 PERFORMANCE KILLERS FOR LINKED SERVER QUERIES

SQL Server and SQL Azure: Clear Plan Cache

by

For on-premise SQL Servers you can run

dbcc freeproccache

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);

DBCC FREEPROCCACHE is not supported in SQL Azure as that wouldn’t be practical in a multi-tenanted database environment. SQL Azure (and SQL Server 2016) has introduced a new mechanism for clearing the query plans for a single database:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

ALTER DATABASE SCOPED CONFIGURATION

This statement enables the configuration of a number of database configuration settings at the individual database level, independent of these settings for any other database. This statement is available in both SQL Database V12 [SQL Azure] and in SQL Server 2016. These options are:

  • Clear procedure cache.

  • Set the MAXDOP parameter to an arbitrary value (1,2, …) for the primary database based on what works best for that particular database and set a different value (e.g. 0) for all secondary database used (e.g. for reporting queries).

  • Set the query optimizer cardinality estimation model independent of the database to compatibility level.

  • Enable or disable parameter sniffing at the database level.

  • Enable or disable query optimization hotfixes at the database level.

Database Scoped Configuration

SSAS: The Measures cube either does not exist or has not been processed

by

If you are attempting to deploy your existing SQL Server Analysis Services cube with calculation changes and get the following (not very helpful!) error:

“The Measures cube either does not exist or has not been processed”

then you probably forgot a CURRENTCUBE prefix on one or more of your MDX Create Member statements:

CREATE MEMBER CURRENTCUBE.Measures.[MyMeasure] AS
              ^^^^^^^^^^^

SQL Server Agent Job Visualisation: SQLjobvis

by

SQL Server must be older than 20 years and yet it still has no agent job schedule visualiser, despite the fact it has been asked for many times. How can that be? Go figure…

I needed one at work recently to identify schedule clashes, so I thought “I wonder how hard it would be to write something fairly basic”. Then programmer laziness swiftly kicked in and the next thought was “If this problem is a pain for so many, then someone must have written something already…?”

And of course, they have: SQLjobvis

It’s basic, simple, works great, and it’s free!

It instantly identifies:

  • Failures
  • Long-running jobs
  • Multiple jobs running simultaneously

    Brilliant!

    • Office 2013 64 bit Setup

    by

    I’ve used every version of Microsoft Office pretty much since it came out. I currently have the 32 bit version of Office Pro 2010 installed. Attempting to install the 64 bit version of Office 2013 to evaluate Excel’s Power View results in:

    image

    Wouldn’t it be nice if it had the option to do this for me and migrate all my settings?!? Especially since I don’t recall there being a 64 bit version of Office 2010 on release (or was it just that it had issues with drivers and add-ins?).

    SQL Fiddle

    by

    No, it’s not Uncle Ernie!

    SQL Fiddle is a very useful online tool for evaluating TSQL against a plethora of database engines (like jsFiddle does for javascript / jQuery), written by Jake Feasel

    I found JS Fiddle to be a great tool for answering javascript / jQuery questions, but I also found that there was nothing available that offered similar functionality for the SQL questions. So, that was my inspiration to build this site. Basically, I built this site as a tool for developers like me to be more effective in assisting other developers.

    It currently supports these database engines (in various versions): MySQL, PostgreSQL, MS SQL Server, Oracle, and SQLite

    .NET Regex: Character Class Subtraction

    by

    You’ve heard of a positive match character group [] and a negative match character group [^]. But did you know there is also a Character Class Subtraction? I didn’t. It’s supported in .NET but not in the majority of RegEx flavours.

    A character class subtraction expression has the following form:

              [base_group – [excluded_group]]

    The square brackets ([]) and hyphen (-) are mandatory. The base_group is a positive or negative character group as described in the Character Class Syntax table. The excluded_group component is another positive or negative character group, or another character class subtraction expression (that is, you can nest character class subtraction expressions).

    For example, suppose you have a base group that consists of the character range from “a” through “z”. To define the set of characters that consists of the base group except for the character “m”, use [a-z-[m]]. To define the set of characters that consists of the base group except for the set of characters “d”, “j”, and “p”, use [a-z-[djp]]. To define the set of characters that consists of the base group except for the character range from “m” through “p”, use [a-z-[m-p]].

    Using this format, the pattern 

    ^[\w-[v123]]$

    can be used in .NET to match all alphanumeric characters (any word character) excluding the letter v and numbers 123. 

    The MSDN page for the .NET Regex definitions doesn’t seem to appear high in the search indexes, so bookmarking here for my future reference: Character Classes in Regular Expressions

    This is useful for comparing Regex capabilities in different languages: regex flavor comparison chart

    SQL Diagnostic Runner Updated

    by

    Thanks to Todd who reported a bug when connecting with username and password (I messed up the connection string).

    I’ve uploaded an updated version (v1.0.4.13057) which you can download from the links below (or from any of the previous posts):

    SQLDiagCmd.zip

    SQLDiagUI.zip

    [Servername will now take a semi-colon separated list of servers to run against, but with the limitation of using the same credentials and diagnostic script.]

    Parsing Command Line Arguments

    by

    If you want a full blown Command Line Parser then there are several good options available:

    [I used the Command Line Parser Library recently in the SQL Diagnostic Runner I wrote.]

    If you just want a very basic parser, supporting simple options in the format  /argname:argvalue  then you could use this:

    /// 
    /// Very basic Command Line Args extracter
    /// Parse command line args for args in the following format:
    ///     /argname:argvalue /argname:argvalue ...
    /// 
    public class CommandLineArgs
    {
        private const string Pattern = @"\/(?\w+):(?.+)";
        private readonly Regex _regex = new Regex(Pattern, RegexOptions.IgnoreCase|RegexOptions.Compiled);
        private readonly Dictionary _args = new Dictionary();

        public CommandLineArgs()
        {
            BuildArgDictionary();
        }

        public string this[string key]
        {
            get { return _args.ContainsKey(key) ? _args[key] : null; }
        }

        public bool ContainsKey(string key)
        {
            return _args.ContainsKey(key);
        }

        private void BuildArgDictionary()
        {
            var args = Environment.GetCommandLineArgs();
            foreach (var match in args.Select(arg => _regex.Match(arg)).Where(m => m.Success))
            {
                try
                {
                    _args.Add(match.Groups["argname"].Value, match.Groups["argvalue"].Value);
                }
                // Ignore any duplicate args
                catch (Exception) {}
            }
        }
    }