Category: SQL

How to get Azure Data Factory connecting to your data on a VNet (or internal network)

Azure Data Factory (ADF) is a great tool as part of your cloud based ETL tool set. However not all your data is necessarily accessible from the public internet. These instruction go through the steps required to allow ADF access to your internal or VNet data-sets.

Continue reading “How to get Azure Data Factory connecting to your data on a VNet (or internal network)”

How to connect to an AD Domain Secured SQL Server from a non-domain PC

The following are instructions for setting up a connection to an Active Directory (AD) Domain secured SQL Server using either SQL Server Management Studio or Visual Studio. This is especially applicable when not using a domain attached PC.

Step-by-step guide

Continue reading “How to connect to an AD Domain Secured SQL Server from a non-domain PC”

Location of your SQL Templates and how to take them with you

If you have started to create a good collection of you own SQL Templates and snippets, then you may want to copy or share them from PC to PC or from user account to user account.

You will find your personal templates here Continue reading “Location of your SQL Templates and how to take them with you”

Azure SQL Database Level Firewall Rules

If you have been using Azure SQL Servers and databases, you will already be aware that you need to configure the server level firewall. You may not know that you can also set firewall rules at database level too.
However this cannot be done through the Azure Portal. However both server and database level firewall rules can be easily managed using SQL.

Server Level

-- ========== SERVER LEVEL FIREWALL (master database connection)

-- List firewall rules
 SELECT * FROM sys.firewall_rules ORDER BY name;

 -- ADD Server firewall rule
 EXECUTE sp_set_firewall_rule @name = N'MyFirewallRule', @start_ip_address = '192.168.1.1', @end_ip_address = '192.168.1.10'

 -- DELETE Server firewall rule
 EXECUTE sp_delete_firewall_rule @name = N'MyFirewallRule'

Database Level

 -- ========== DATABASE LEVEL FIREWALL (specific database connection)

 -- List firewall rules
 SELECT * FROM sys.database_firewall_rules ORDER BY name;

 -- ADD Database firewall rule
 EXEC sp_set_database_firewall_rule @name = N'MyDBFirewallRule', @start_ip_address = '192.168.1.11', @end_ip_address = '192.168.1.11'

 -- DELETE Server firewall rule
 EXEC sp_delete_database_firewall_rule @name = N'MyDBFirewallRule'

See also

https://docs.microsoft.com/en-gb/azure/sql-database/sql-database-configure-firewall-settings-tsql

Finding missing indexes and quick optimising of SQL Azure

If you are looking for a quick way to improve your SQL Azure performance then you can check to see if SQL Azure has determined if any useful indexes are missing.

In SQL Server Management Studio (SSMS) run the following against your database

select * from sys.dm_db_missing_index_details

This will output something like (I have obscured some sensitive names)

 

For example, the above suggested I made the following indexes on AccessNoXRefs table.

For more information see http://msdn.microsoft.com/library/ms345434.aspx and http://msdn.microsoft.com/en-US/library/dn133166.aspx