SQL Azure for DBAs and Data Engineers

Hi   Today I will be reviewing the Azure Portal Dashboard. We have seen lots of improvements in that field. It has developed to include a variety of options, and lot of use cases:   Monitoring, with full screen and charts. Shortcuts to the most usable apps. Share dashboards between users. You can have multi Dashboards, like Dashboard for DB's, for Storage or VM's or per application or resource group. I find these new features very useful and very easy to implement. Here are some screenshots showing the uses.   Image 1 - shows what options we have for the Dashboards     : We can add new, edit an exiting one, share to other users, clone and delete.     Image 2 - shows the options when clicking on the arrow near the Dashboard, we see the list of my dashboards, and the dashboards that was shared with me.       Image 3 - shows the screen after clicking on...

Hi All   So After a month of using Cross Database Queries in SQL Azure DB, I can say that we have a very useful tool.   We can use in many scenarios, I will show today one.   We are using 10 shards DB's to balance read operations to many DB's. The write operations goes to the DB's as well.   I' as a DBA wants to know the status of all DB's, so instead of creating PowerShell that connect to all of the DB's I have created external tables in 1 DB that reads the     sys . dm_db_resource_stats     from all DB's' so I have now 1 real table and 9 external tables.   I have created a view that reads from al 10 tables' and now I see all data from all shards.   I also put it in PowerBI and I see it in a visual way - and also in my Mobile.   Here I have in the first line DTU AVG value for each shard in the last 10 minutes, 1 clock that avg all shards.   The second line is the DTU real value in the ...

Hi   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 s...

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-databa...

Hi All   New feature was released lately - Fail Over in the Geo Replication in SQL Azure. The Announcement is here:   https://azure.microsoft.com/en-us/blog/spotlight-on-new-capabilities-of-azure-sql-database-geo-replication/   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   http://blogs.microsoft.co.il/gerireshef/2015/11/23/%D7%90%D7%95%D7%A4%D7%A6%D7%99%D7%99%D7%AA-geo-replication-%D7%91-azure/   This is the new link   enjoy   ...

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: 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. 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 t...

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 ):     http://thomaslarock.com/2013/05/how-to-troubleshoot-deadlocks-in-windows-azure-sql-database-wasd/ & https://www.youtube.com/watch?v=RlKCe05WDOw   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="erro...