Sharing knowledge does not lessen your store, often it gets you more.
Success doesn't happen overnight and patience is key to living your dream life.
Success is a journey not a destination
Data Discovery and Classification in Azure SQL Database is part of the "Advanced Data Security" feature. This feature allows organisations to discover, classify, label and protect PII (Personally Identifiable Data). It also provide organisations to comply against audit and regulatory compliance.
The classification taxonomy for the sensitive columns will be located in one central place for organisations to manage.
a) Logon to the Azure Portal. Refer steps a) and b) for creation of resource group and Azure SQL Database.
b) Navigate to the "sampledb" under the "AAD-SQL" resource group and click on "Advanced data security"
c) Enable "Advanced Data Security on server"
d) Once enabled, you can see the columns recommended for classification along with the sensitivity label provided.
e) The database is scanned for sensitive columns and it will be listed along with the sensitivity labels. You can accept the automatic recommendations or you can manual add classifications. Custom sensitivity labels can also be added and columns can be changed to the new sensitive labels as required.
f) Save the recommendations.
g) The "Overview" tab provides the summary of classification information of the database including the list of classified columns along with the schema, informative types and sensitive labels. Once classified you can monitor the access to sensitive data through "SQL Auditing".
Azure SQL provides both server level as well as database level auditing. Auditing tracks database events and records them in Blob storage, Log Analytics or Event Hubs.
a) Logon to the Azure Portal. Refer steps a) and b) for creation of resource group and Azure SQL Database.
b) Navigate to the resource group "AAD-SQL" and to the "aad-azuresql" SQL Server and click on "Auditing".
c) Turn "Auditing" On at server level and configure the blob storage to capture the audit events.
d) Navigate to the "sampledb" database on the Azure SQL Server and click on "Auditing" and turn on Database level Auditing along with configuring Blob storage to store the Database level audit events.
e) Once configured you can navigate between Server level Auditing events and Database level Auditing events as shown below.
Note from Microsoft Docs
You should avoid enabling both server blob auditing and database blob auditing together, unless:
You want to use a different storage account or retention period for a specific database.
You want to audit event types or categories for a specific database that differ from the rest of the databases on the server. For example, you might have table inserts that need to be audited only for a specific database.
Otherwise, we recommended that you enable only server-level blob auditing and leave the database-level auditing disabled for all databases.
This article is about sharing my experiences with starting a
user group in Wellington and growing the user group.
The "Wellington
Data Management and Analytics" (https://www.meetup.com/Wellington-Data-Management-and-Analytics-Meetup/)
user group was started back in August
2017 with just 1 member. Also, I
am a vegetarian and non-alcoholic but still decided to organise the user group
and other events in Wellington.
I had the meetup site created and patiently waited for a
month before I scheduled the first user group session. I was surprised that
without much promotions or marketing I had more than 20 people attending the
session with more than 30 registrations. Now the user group in 2019 is close to reaching 1000 members.
Scheduling regular
sessions at user group
I have seen some interesting user groups getting started but
not being able to schedule regular sessions and the community losing the
interest with the group. I was clear with one thing which is to run at least
one session each month at our user group. This helped really to build the
community as we meet regularly each month and share our knowledge and
experiences.
Engaging more women
attending the user groups
Twice a year I make a point to run Women in Tech sessions to
engage more women to get involved in the tech community. It is always great to
see more women turning up for these sessions. I am working on few more ideas
around how to grow this initiative further.
Collaboration with
other user groups
I build relationships with other user group leaders within
the community to increase the visibility of the user groups. I met Xiaodi Yan who runs the Chinese IT
Association New Zealand (CITANZ) user group in Wellington. We met last year and
co-organised Global AI bootcamp, Global Azure bootcamp and few other events.
Xiaodi uses his experience related to the community to help CITA
grow rapidly. These days the CITA meetup has more than 500 members. As a core
member of CITA, Xiaodi helps CITA organize monthly meetups. Over the last year,
CITA has delivered more than 10 meet-ups, and successfully delivered a range of
diversified topics, which covered Azure DevOps, blockchain, cloud computing,
.NET Core, Container, Agile, job hunting and many more. The overall attendees
are more than 400.
Now I have more Chinese IT professionals attending our
events and thanks to Xiaodi for promoting the events and supporting us.
Also, this year most of the Microsoft user groups in
Wellington joined together as a single group and hosting the Christmas party on
December 5th, 2019. This is a great effort initiated by Ondrej Aubrecht (PowerShell User Group Leader) and we are
supporting this initiative. We have more than 100 people across the following
user groups attending the Christmas party this year.
Wellington PowerShell
Wellington Data Management and Analytics
Wellington Azure AI
Wellington Azure
Wellington Excel and Power BI
Wellington Dynamics
Wellington D365
Hosting more events and
talks to keep community engaged with support from MVP community
Post starting the Wellington Data Management and Analytics
user group, with the support and mentoring from the MVPs I decided to bring
SQLSaturday event back to Wellington which further increased the number of
people attending the user group and helped in growing the community further.
Along with that whenever I get to know about MVPs visiting Wellington, I try to
host a user group event to help the community to learn from the experts.
Support from
co-organisers and volunteers
User groups can be started with one person but to run it
long you need support. I am lucky enough to have Raju RH as my co-organiser who understands the core ideologies of
the user group and supports me running it from Day 1. Also, thanks to all my Volunteers and other User group leaders who assist with the user groups as well as other events that we host
in Wellington.
Here is a great video put together by one of my volunteer (Lavanya) showcasing the highlights of our SQLSaturday events in Wellington
It is great to be an MVP not just focusing from technical
front but also showcasing how to build a great community to learn and share
knowledge and learning.
c) Create a new Azure SQL Database Server named "aad-azuresql"
d) Configure Azure Active Directory user for accessing the Azure SQL Server.
e) Select "Set Admin" and select the AAD user who needs to be given access to the Azure SQL Server.
Now you can create a database under this Azure SQL Server and can access the database using the above configured Azure Directory Admin user using Password Authentication as shown below. Note: Make sure to configure the firewall settings on your Azure SQL Server.
Azure SQL Database is a relational database hosted on Azure and a fully managed service provided as the Platform as a Service (PaaS). It provides 3 deployment options. It's a pay-as-you-go option allowing users to scale up or out based on their needs. SQL Database has some additional features that are not available in SQL Server, such as built-in high availability, intelligence, and management.
Single Database - single database with defined set of resources
Elastic Pool - multiple databases within a elastic pool sharing resources
Database Server - manages single databases and elastic pools
SQL Databases
SQL Managed
Instances
Supports most
on-premises capabilities
Supports all
on-premises capabilities
99.995%
availability
99.99%
availability
Supports built-in
backups, patching and recovery
Supports built-in
backups, patching and recovery
Supports latest
stable Database Engine version
Supports latest
stable Database Engine version
Migration involves
validating whether all the features used by on-premises version is supported.
Easy migration
from on-premises
Supports databases
up to 100TB
Supports databases
up to 8 TB
On-premises
application can access data in Azure SQL Database.
Connectivity to
your on-premises environment using Azure Express Route or VPN Gateway
Elastic Pools on Azure are simple and cost effective way to manage and scale multiple databases of varying usage demands with a predictable cost. Multiple databases in an elastic pool are on a single Azure SQL Database server share a defined set of resources for a set price.
Elastic Pools are really handy for SaaS developers where by they have to provision separate database for each of their clients who may have varying demands. Elastic Pools will manage the resources based on the demand across the different databases within the pool.
If any additional resources are required they can be added to the pool without any downtime except when databases needs to be moved in and out of the pool there will be a minimum downtime at the end of the operation where connections will be dropped.
There is no per-database charge for elastic pools. You are billed for each hour a pool exists at the highest eDTU or vCores, regardless of usage or whether the pool was active for less than an hour.
When do you really need an Elastic Pool?
If you have multiple databases but really don't know the demands of the databases then it is better to assign them in an Elastic Pool with known set of resources and predictable cost. The Elastic Pool will take care of allocating required resources to the databases during demands.
Creating an Elastic Pool on Azure
a) Log on to the Azure Portal
b) Search for Elastic Pools on the Marketplace.
c) Provision an Elastic Pool and SQL Server on Azure.
d) Create 2 databases inside the SQL Server and assign the Elastic Pool as part of it. Now the resources will be shared within the 2 databases as part of the Elastic Pool meeting their demands efficiently for a known cost.
b) Navigate to the Azure SQL Database that needs to be exported and also make sure that you have a Azure Storage service provisioned which will hold the bacpac files.
c) Select the "Export" button.
d) Provide the details. Export bacpac file name, storage container and credentials and click ok.
e) You may receive an error with the following message.
Database export error
Failed to export the database: export-database-db.
ErrorCode: 400
ErrorMessage: There was an error that occurred during this operation : 'Error encountered during the service operation. ; Exception Microsoft.SqlServer.Management.Dac.Services.ServiceException:Unable to authenticate request; Inner exception System.Data.SqlClient.SqlException:Cannot open server 'export-database-db-server' requested by the login. Client with IP address '191.239.64.14' is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.; '
f) Set the Firewall rules to allow the IP address of the machine that tries to initiate the export as shown below.
g) Once done repeat step b) and initiate the export again.
h) Export request will be submitted and you also check the progress as shown below.
i) Once the request is complete, the bacpac file will be successfully created in the storage container.
Export using SQLPackage Utility
SQLPackage is a command-line utility that is used to export, import, extract, publish and run T-SQL scripts against databases.
There may be reasons why organisations need to move an existing Azure SQL Database from one region to another region. Let us see how to do that using Azure Portal.
a) Logon to the Azure Portal
b) Create a new resource group named "move-azure-sql-db"
c) Create a new database named "move-azure-sql-db" database and server named "move-azure-sql-db-server" in "AustraliaEast" region.
d) The "Move" option allows to move the database to another resource group or another subscription. But what we are trying to do is move the database to a new region and in order to do that we need use the Failover option.
e) Create a Failover group named "move-azure-sql-failover-group" and add the database to a new database server named "move-azure-sql-db-server1" in a different region "Southeast Asia".
f) Now initiate the failover by clicking the "failover" button so the database is moved to the other region.
g) Once the failover has happened, clear all the resources from the source region like the database and the database server.
