Category: SQL

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

Easy guide to configuring Windows 2008 Firewall for SQL 2008 R2

After installing SQL Server 2008 R2 on a Windows 2008 server you will need to configure the firewall (if it’s turned on!) to allow access to the SQL server. Here is the simple method using the interface and assumes the following:

  • The SQL Instance is the default (first installed)
  • The ports have not been manually configured
  • Nothing too exotic like database mirroring is required.
  1. Open the Firewall either from the Control panel

    or by running WF.msc
  2. There are 3 default firewall profiles. One or more can be active depending on what and how you have configured your NICs. You will need to ensure you are editing the correct profile. Here you can see that mine is on a domain, so my Domain Profile is Active.
  3. Select ‘Inbound Rules’ in the left hand pane.
  4. Click on ‘New Rule…’ in the right actions pane.
  5. select Port Rule Type and click Next>
  6. Set TCP and Specific local Ports to 1433 and click Next>
  7. Leave the default of ‘Allow the connection’ and click Next>
  8. If you don’t ever want SQL to be directly accessible on a Public network (assuming you are going to ever connect your SQL server directly to the internet for some reason!!) then un-check the ‘Public’. Otherwise just click Next>
  9. Name it SQLPort and give it a description and click Finish.

That will enable inbound connections to your SQL Server. If you are also enabling SQL Browser Service then you will need to add UDP Port 1434 too another inbound rule.

If you have more than one instance of SQL then you will need to set the port and create rules for them.

For full details on ports and setting it up go to http://msdn.microsoft.com/en-us/library/cc646023(v=SQL.105).aspx

SQL script to kill all connections to a SQL Server database

I have finally perfected a nice script for killing connections to databases. Although basic, I found many of the ones I google’d, had one problem or another that stopped them working straight away, which just wastes time. This one works! I have just used it.

Just replace the database name and it’s good. It is easy to alter it to work across a list of databases too.

-- =============================================
-- Author: Nicholas Rogoff
-- Create date: 04/03/2010
-- Description: Script that Kills all connections to a database
-- except the current one
-- =============================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DECLARE @execSql NVARCHAR(1000)
DECLARE @databaseName VARCHAR(100)
DECLARE @NoKilled INT

-- *** CHANGE THE NAME OF THE DATABASE *** ---
SET @databaseName = '#### Replace this with a database name here ####'
PRINT 'START: Killing active connections to the "' + @databaseName + '" database'

-- Count Connections
select @NoKilled = COUNT(*)
from master.dbo.sysprocesses
where db_name(dbid) = @databaseName
 and
 DBID <> 0
 and
 spid <> @@spid

-- Create the sql to kill the active database connections
set @execSql = ''
select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '
from master.dbo.sysprocesses
where db_name(dbid) = @databaseName
 and
 DBID <> 0
 and
 spid <> @@spid

exec (@execSql)

PRINT 'END: Killed "' + CAST(@NoKilled AS VARCHAR(4))
+ '" active connections to the "' + @databaseName + '" database'
GO

The free online div table generator allows you to create nice grids for your websites. Please subsribe for a htmlg membership to stop adding promotional messages to the edited documents.