Cross Database Queries in SQL Azure DB - Part 1

I wanted to write this post a long time ago but have not had the chance, so now with the new year - this will be the first post for 2016.
Cross Database Queries in SQL Azure DB was a key feature that was missing from day 1 on SQL Azure DB.
Finally we have this  key feature, and no, it is not the same as in the regular SQL Server. We have a new way to do this. It is bases on the new key feature  in SQL Server 2016 called "External Tables" (https://msdn.microsoft.com/en-us/library/dn935021.aspx).
Also known as "PolyBase".
The logic behind the feature is that you can query tables with-in your DB (like you are doing today) and also at any other DATA source like it is a table within the DB.
From one SQL Azure DB to another we can not connect to it via linked server. However a second SQL Azure DB is referenced like any other external data source.
Microsoft thinking is that SQL Azure DB is another DATA sources for External tables ( like Hadoop, Blob storage ect..).
I am adding some scripts to make it all easy for you to run and test (here I will not elaborate now about the query performance and execution plans...).
First are 2 scripts with reference to security issues (its all about trusted connections between 2 DATA sources):
WITH IDENTITY = 'youramazingID',
SECRET = 'youramazingpassword';

The "username" and "password" should be the username and password used to login into the DB2 database.
IN DB2 (you create a regular table):
CREATE TABLE [dbo].[Customers223](
[CustomerId] [int] NOT NULL,
[Name] [nvarchar](256) NOT NULL,
[RegionId] [int] NOT NULL


IN DB1  (you create an external table):

you create an external DATA Source :
CREDENTIAL= ElasticDBQueryCred



you create an external table that reference to that table in DB2 - with the same name:
CREATE EXTERNAL TABLE [dbo].[Customers223](
[CustomerId] [int] NOT NULL,
[Name] [nvarchar](256) NOT NULL,
[RegionId] [int] NOT NULL


DATA_SOURCE = RemoteReferenceData
insert into [Customers223] select 2,'TestPini',3
select * from [dbo].[Customers223]
you will see data from DB2
4 long years I am waiting for this !!!!!!
A few things to remember:
  1. If you will do not have an object with the same name in DB2 - you will get error when query it saying "The underlying error message received was: 'Invalid object name 'dbo.Customers224'.'." 
  2. If you add column in DB2 in the table. and you do not add it in the EXTERNAL Table... then you will not see the data. BUT an error " The feature 'ALTER TABLE' is not supported with external tables." You need to drop and create the external table.
  3. the first query - when creating the connection is extremely slow - make your coffee - relax and then all will be good.

  4. the external table can be in a different name then the source, but then - when you create it you must add this in the "WITH" clause:
      DATA_SOURCE = RemoteReferenceData,
    SCHEMA_NAME = N'dbo',
    OBJECT_NAME = N'Customers226',
in DB1 you can check these DMV's:
select * from sys.external_data_sources
select * from sys.external_tables
have a lovely year.


All about Security in SQL Azure DB

Hi All
Since this is become more and more important for SQL Azure DB I am writing about 'Security Features in SQL Azure DB'.
I will put everything in a list and links for all features.
1)    Azure SQL Database security guidelines and limitations:
  a) Link: https://azure.microsoft.com/en-us/documentation/articles/sql-database-security-guidelines/.
  b) This link explain about: Firewall, Connection encryption and certificate validation, and some best Practices.
2)    Connecting to SQL Database: Best Practices and Design Guidelines:
  a) Link: https://azure.microsoft.com/en-us/documentation/articles/sql-database-connect-central-recommendations/
  b) This link bring few other links of the FW issue and connection Ports.
3)    Connecting to SQL Database By Using Azure Active Directory Authentication
  a) Link: https://azure.microsoft.com/en-us/documentation/articles/sql-database-aad-authentication/
  b) You have in this link few other links of the options to connect to SQL Azure DB :
    i)  With Password Authentication.
    ii) With Integrated Authentication.
    iii)SQL Authentication (https://azure.microsoft.com/en-us/documentation/articles/sql-database-manage-logins/).
4)    Security Features Within The SQL:
  a) Protect DATA
    i)TDE - Transparent Data Encryption: https://msdn.microsoft.com/en-us/library/dn948096.aspx. This is encryption in files level.
    ii)Always Encrypted: https://azure.microsoft.com/en-us/blog/microsoft-azure-sql-database-provides-unparalleled-data-security-in-the-cloud-with-always-encrypted/ & https://msdn.microsoft.com/en-us/library/mt163865.aspx .  This is encryption in filed level.
  b) Control Access:
    i)  Azure Active Directory: 3.a
    ii) Row Level Security: https://azure.microsoft.com/en-us/blog/row-level-security-for-sql-database-is-generally-available/ & https://msdn.microsoft.com/en-us/library/dn765131.aspx
    iii)Dynamic Data Masking: https://azure.microsoft.com/en-us/documentation/articles/sql-database-dynamic-data-masking-get-started-portal/ .
  c) Monitor Activity:
    i) Auditing: https://azure.microsoft.com/en-us/documentation/articles/sql-database-auditing-get-started/ I love this feature….
    ii)Threat Detection: https://azure.microsoft.com/en-us/blog/threat-detection-public-preview/.
5) Security Features Within The Portal – RBAC - Role-based Access Control:
    i)We can delete DB's via portal – so we need to understand the concept of RBAC: https://azure.microsoft.com/en-us/documentation/articles/role-based-access-control-configure/ & look for SQL in https://azure.microsoft.com/en-us/documentation/articles/role-based-access-built-in-roles/.
Security Center

Last and not least is the new Security Center. Insert into the Portal and go to the Security Center:
now we can see the wrong configurations for each type : VM's, network and DB
what the suggest now is the auditing and TDE features - if we enabled or disabled them.


Very nice feature


FailOver in SQL Azure DB with Geo Replication

Hi All
New feature was released lately - Fail Over in the Geo Replication in SQL Azure.
The Announcement is here:
As You can see they added Failover feature. That means that until today you have Primary and Secondary DB's, DATA moved on one direction and you could disconnect the connection when ever you wish. but then you have to build everything from the beginning, (you can look in my old post about geo-replications types in sql azure).
Now DATA moved backward, when you click Fail over - you still have 2 DB's but the Secondary become Primary and vise versa. I this this is a great feature.
you can also read in Hebrew about it in Geri's Reshef Blog
This is the new link


Extended Events in SQL Azure

Hi Everybody
Today an English post about 'Extended Events in SQL Azure', some of you shorten the name to 'EE' and some to 'XEvent'. I Love EE so this is how I will call it in this post.
This feature was introduce in SQL Server 2008 and its should help collecting DATA about what is running in the Server.
More Details about this SQL Server feature can be found in this Link: https://msdn.microsoft.com/library/bb630282.aspx?f=255&MSPPError=-2147217396
There are a few differences between EE in SQL Azure and regular SQL Server:
  1. In SQL Server versions the EE are on the Server level and therefore you create sessions on Server. In SQL Azure the server is a virtual entity - so the EE is in DB level and you create the session on DataBase.
  2. In SQL Server versions the EE can write to files on the server. SQL Azure does not have drives for files (SQL Azure is PAAS.....:-)). There is an option to write to blob storage, for this we need to grant special credentials.
  3. In SQL Server versions the EE uses some server level DMVs, as we wrote in point 1, we are in a virtual server entity, we have other new DMV's to use.
A few links to help explain things:
Below is an example that creates an event into the Buffer Pool (avoiding writing to blob storage) on DB errors (This is a only sample script...). The scripts create event, create error (ignore the error in the results) and select from the XML in the Buffer Pool that holds the DATA.
--Drop Session
IF EXISTS (SELECT * from sys.database_event_sessions WHERE name = 'ErrorWithSQL')

--Create Session
ADD EVENT sqlserver.error_reported(
        package0.ring_buffer   (SET
                max_memory = 500   -- Units of KB.
--Start Session

--create error
select 1/0

--select from EE
declare @xml xml
SELECT @xml= CAST(target_data AS XML)
FROM  sys.dm_xe_database_session_targets AS t ;
--select @xml;
with MyCTE as (
 select tbl.xcol.query('.') TheXML,
 tbl.xcol.value('(@timestamp)[1]','datetime') as ErrorDate,
 tbl.xcol.value('(data[@name="error_number"]/value)[1]', 'int') as ErrorNumber,
 tbl.xcol.value('(data[@name="message"]/value)[1]', 'varchar(300)') as ErrorMessage,
 tbl.xcol.value('(action[@name="sql_text"]/value)[1]', 'varchar(300)') as SQLText,
  tbl.xcol.value('(action[@name="tsql_stack"]/value/frames/frame/@offsetStart)[1]','int') as OffsetStart,
 tbl.xcol.value('(action[@name="tsql_stack"]/value/frames/frame/@offsetEnd)[1]','int') as offsetEnd,
 tbl.xcol.value('(action[@name="tsql_stack"]/value/frames/frame/@handle)[1]','binary(64)') as SQLHandle,
 tbl.xcol.value('(action[@name="client_app_name"]/value)[1]', 'varchar(300)') as client_app_name,
 tbl.xcol.value('(action[@name="client_hostname"]/value)[1]', 'varchar(300)') as client_hostname,
 tbl.xcol.value('(action[@name="username"]/value)[1]', 'varchar(300)') as username
 from @xml.nodes ('/RingBufferTarget/event') tbl (xcol))
select top 100 * from MyCTE
order by ErrorDate desc
I was very happy to get this feature I think its a game changer.


Deadlocks in SQL Azure V12

Hi All
Today I want to explain how to find and troubleshoot Deadlocks in SQL Azure V12. 
At V2 we had this query to run and find our deadlocks:
SELECT * FROM sys.event_log WHERE event_type = 'deadlock'
This query return XML, and we could have change it to XDL and see the deadlock Chart.
You can see this in the 2 links by Thomas Larock (@SQLRockstar):
Now in V12 this feature is not supported - so how do you get the deadlock data?
MSFT Gave us this query (Running on Master DB):
SELECT top 100 *,
CAST(event_data as XML).value('(/event/@timestamp)[1]', 'datetime2') AS timestamp
,CAST(event_data as XML).value('(/event/data[@name="error"]/value)[1]', 'INT') AS error
,CAST(event_data as XML).value('(/event/data[@name="state"]/value)[1]', 'INT') AS state
,CAST(event_data as XML).value('(/event/data[@name="is_success"]/value)[1]', 'bit') AS is_success
,CAST(event_data as XML).value('(/event/data[@name="database_name"]/value)[1]', 'sysname') AS database_name, CAST(event_data as XML)
FROM sys.fn_xe_telemetry_blob_target_read_file('el', null, null, null)
where object_name = 'database_xml_deadlock_report'
order by CAST(event_data as XML).value('(/event/@timestamp)[1]', 'datetime2') desc


We get XML and can use it or its graphical view (save as xdl).
This is written in some delay.
This is new  and Un documented fn, that reads the event log from BLOB (as I understand), so this is actually the first look of EE in SQL Azure.
Enjoy and Thanks to Geri Resef, helped me with this.