Menu

Virtual Geek

Tales from real IT system administrators world and non-production environment

MICROSOFT AZURE CREATING AND DEPLOYING PAAS SQL SERVER, DATABASE AS A SERVICE

December 5, 2016 11:34PM

Soon after deploying and establishing in-house to azure VPN tunnel PART 1 : MICROSOFT AZURE CREATION AND CONFIGURATION OF VPN TUNNEL SERIES, I got another task to architect complete project on Microsoft Azure, Very first requirement by application team was the Database server.

SQL Database is a cloud database service built for application developers that lets you scale on-the-fly without downtime and efficiently deliver your applications. Built-in advisors quickly learn you application's unique characteristics and dynamically adapt to maximize performance, reliability, and data protection.

Use this template to create an empty logical server, which can host databases and elastic database pools for SQL Database, host SQL Data Warehouse databases, or be used as the remote endpoint for a SQL Server stretch database.

Some planning and cost saving I decided to go and use PAAS Azure SQL Servers. As an architect I was seeking and seeing two benefits Database as a service, first I don't have to manage any VM OS, another is SQL server licensing and maintenance part taken by Microsoft Azure, Azure SQL instances are cheap, Everything is on the cloud, I don't have to manage anything, Connect them from anywhere. Below are the Azure SQL Service tiers, As this is my first database server I will be using Basic tier for testing.

MICROSOFT AZURE POWERSHELL DEPLOYING PAAS SQL DATABASE AS A SERVICE

Service tiers
Basic, Standard, and Premium service tiers all have an uptime SLA of 99.99% and offer predictable performance, flexible business continuity options, security features, and hourly billing. The following table provides examples of the tiers best suited for different application workloads.

Service tier Target workloads
Basic Best suited for a small database, supporting typically one single active operation at a given time. Examples include databases used for development or testing, or small-scale infrequently used applications.
Standard The go-to option for most cloud applications, supporting multiple concurrent queries. Examples include workgroup or web applications.
Premium Designed for high transactional volume, supporting many concurrent users and requiring the highest level of business continuity capabilities. Examples are databases supporting mission critical applications.

I logged into Microsoft Azure Portal, I already had created Resource Group in earlier PART 2 : MICROSOFT AZURE CREATING RESOURCE GROUP, A Resource Group is a collection of resources that share the same lifecycle, permissions, and policies, I chose the existing resource group and click add SQL server resource to it.

1. Microsoft Azure Resource Group add new sql database server paas sql database as a service

Type and search for SQL Server, Name will be appear first in the list, click create as shown in screenshot.

2. Microsoft Azure Resource Group add new sql database server paas, sql database as a service, cloud database service, creation

Type the new SQL server name and server admin login username and password, This username is the same as SA user. Chose the appropriate subscription and location. Allow azure services to access server is checked by default and always on, This is related to firewall and if even though there is no IP added in firewall, Azure services in the current subscription can access or use this SQL DB server.

microsoft azure sql server logical server paas, database as a service, sql admin login and password

Once Azure SQL Server process creation started, task status can be viewed on top right side bell icon.

3. Microsoft Azure Resource Group add new sql database server paas sql database as a service, deployment started succeeded

Refresh resource group, and new server can be seen in the list.

4. Microsoft Azure Resource Group add new sql database server paas sql database as a service, deployment started succeeded platform as a service cloud.png

Logical SQL server is created but it has no database yet, Next in the add button search for SQL database to create it.

5. Microsoft Azure Resource Group add new sql database server paas sql database as a service, deployment started succeeded platform as a service cloud, Sql DB database instance under sql server.png

Provide name to the database, choose source as Blank database, (Select Blank database to create a new blank database, Sample to create a new AdventureWorksLT for Azure sample database, or Backup to create a new database from an existing backup) Server name is the same as created earlier logical sql server, pricing tier is basic (Choose a SQL service tier and performance level that best fits your application needs), Database collation defines the rules that sort and compare data, and cannot be changed after database creation, the default database collation is SQL_Latin1_General_CP1_CI_AS. eDTUs no (Elastic pools provide a simple and cost effective solution for managing the performance of multiple databases within a fixed budget. An elastic pool provides compute (eDTUs) and storage resources that are shared between all the databases it contains. Databases within a pool only use the resources they need, when they need them, within configurable limits. The price of a pool is based only on the amount of resources configured and is independent of the number of databases it contains)

6. Microsoft Azure Cloud sql database as a services, Paas sql database instance tables online, db as a service.png

After refreshing resource group new SQL database resource can be seen inside Resource Group on the azure portal.

7. Microsoft Azure Cloud sql database as a services, Paas sql database instance tables online, db as a service, poc-vpn, azure sql db tables online.pnginstance tables online, db as a service.png

Next step is to access Azure SQL database, I am using SSMS (SQL Server management studio) to connect online azure database. One thing to note Database can not be accessed until SQL firewall rule is created (No need to enter any IP for Azure services). Azure SQL DB as a service can only be accessed over internet and cannot be connected to vNet it, First I am finding public IP address of local system, where SSMS client (Sql Server management studio) is installed. Open google.com in browser. and search for whats my IP, Use this public IP and click set server firewall, Create a new rule, add the IP in Start and End and save rule. Copy the server name as shown in 5th step, Azure SQL server will always prefixed FQDN with database.windows.net.

8. Microsoft Azure Cloud sql database as a services, Paas sql database instance tables online, db as a service, poc-vpn, add set azure sql server firewall rules, find client public IP.png

Now Search and open SSMS (SQL Server Management Studio), type or copy paste server name, select SQL server authentication, use the login username and password earlier created in SQL server, and click connect. 

9. Microsoft Sql server 2012 ssms sql server management studio microsoft azure sql database paas database as a service connectivity through ssms.png

Once connected the server, I can verify SQL version by executing command select @@version, This is special SQL version developed for Azure (Microsoft SQL Azure RTM - 12). 

10. Microsoft Sql server 2012 ssms sql server management studio microsoft azure sql database paas database as a service connectivity through ssms, Microsoft Sql Azure (RTM) Select @@version.png

If application team requires DB connection strings, that information can be viewed on overview,  click on database connection strings to view complete information.

PART 1 : MICROSOFT AZURE CREATION AND CONFIGURATION OF VPN TUNNEL SERIES
PART 2 : MICROSOFT AZURE CREATING RESOURCE GROUP 
PART 3 : MICROSOFT AZURE CREATING AND ADMINISTERING VIRTUAL NETWORK (VNET)
PART 3.1 : MICROSOFT AZURE POWERSHELL CREATING AND ADMINISTERING VIRTUAL NETWORK (VNET)
PART 4 : MICROSOFT AZURE CREATING AND ADMINISTRATING LOCAL NETWORK GATEWAY VPN
PART 4.1 : MICROSOFT AZURE POWERSHELL CREATING AND ADMINISTRATING LOCAL NETWORK GATEWAY 
PART 5: VIRTUAL NETWORK GATEWAY DEPLOYMENT ON MICROSOFT AZURE
PART 5.1: VIRTUAL NETWORK GATEWAY DEPLOYMENT USING MICROSOFT AZURE POWERSHELL
PART 6: INSTALLING ROUTING AND REMOTE ACCESS SERVER ROLE (MICROSOFT RRAS)
PART 6.1: CONFIGURING ROUTING AND REMOTE ACCESS SERVER DEMAND-DIAL (MICROSOFT RRAS AZURE VPN)
PART 6.2: CONFIGURING ROUTING AND REMOTE ACCESS SERVER ROUTER (MICROSOFT RRAS AZURE VPN)
PART 7: MICROSOFT AZURE CREATE CONNECTION IN VIRTUAL NETWORK GATEWAY
PART 7.1: MICROSOFT AZURE POWERSHELL VPN CONNECTION IN VIRTUAL NETWORK GATEWAY
PART 8: MICROSOFT AZURE ARM AND POWERSHELL CREATING AND MANAGING STORAGE ACCOUNT
PART 9: CREATING AND MANAGING VIRTUAL MACHINE (VM) USING MICROSOFT AZURE RESOURCE MANAGER PORTAL

Go Back

Comment