Here we will deals with below points.
1.Provision a azure SQL database with Azure PowerShell using ARM - How to configure a Azure SQL databases
2.New-AzureRmSqlElasticPool & Set-AzureRmSqlElasticPool
3.Create an elastic pool, move database to elastic pool
4.Set the property of elastic pool
5.Configure elastic pools
Demo: Provision with Azure PowerShell using ARM
Let's check out a demo of provision in Azure SQL Database with Azure PowerShell using ARM
#setup your account
Login-AzureRmAccount
#verify your subscriptions
Get-AzureRmSubscription
#set your default Subscription
Get-AzureRmSubscription –SubscriptionName “Visual Studio Enterprise” | Select-AzureRmSubscription
#create the Azure SQL server
$adminCredential = Get-Credential
New-AzureRmSqlServer -ResourceGroupName CTLdemoresourcegroup' -Location 'Central India' -ServerName "rakeshdemosql' -SqlAdministratorCredentials $adminCredential -ServerVersion "12.0"
#create a new Basic database
New-AzureRmSqlDatabase -DatabaseName “rakdemodatabase" -ServerName " rakeshdemosql " -ResourceGroupName 'CTLdemoresourcegroup' -Edition "Basic"
# set a firewall rule
New-AzureRmSqlServerFirewallRule -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -FirewallRuleName "Rule01" -StartIpAddress "192.168.0.198" -EndIpAddress "192.168.0.199“
Configure an elastic pool
There's one additional decision that needs to be made when provisioning a new Azure SQL database, and that's whether you want to use an elastic pool of resources
or not.The databases that we've created so far did not use elastic pools, which means that the number of DTUs provided by the chosen service tier were only available for that database.
Those DTUs were reserved for use and paid for whether we used them or not.
With elastic pools, a single group of resources is made available and shared by a group of databases.
These resources are called elastic database transaction units, or EDTUs.Using EDTUs can help keep costs down while still allowing individual databases to consume
a higher number of resources when needed during unpredictable peak usage times.
Pooling resources is particularly cost-effective when databases experience peak traffic at different times.
You can create an elastic pool when you first configure the server or the database, so let's take a look at that.
The more common way is to create a pool on an already existing server and add databases to it.
Let's go ahead and take a look at that option.
Inside this resource group, we have one SQL Server instance called rakeshdemosql and that server has two databases, t1 & t2.
Now to create an elastic pool of resources for the two databases that are inside of the server instance we'll go up to the top and press New pool.
We have a basic pool, a standard pool, and a premium pool.
We can see their details down below. For instance, in the standard pool, we can choose to have between 50 and 3,000 EDTUs per pool.
We can also include up to 3,000 gigabytes of storage per pool.
Each standard pool can have up to 500 databases, and each database can be allocated up to 3,000 EDTUs.
Also each database can be up to 250 gigabytes in space.
We could also specify the size of the pool in gigabytes.
What is difference between New-AzureRmSqlElasticPool & Set-AzureRmSqlElasticPool
The Set-AzureRmSqlElasticPool cmdlet sets properties for an elastic pool in Azure SQL Database. This cmdlet can
modify the eDTUs per pool (Dtu),
storage max size per pool (StorageMB),
maximum eDTUs per database (DatabaseDtuMax), and
minimum eDTUs per database (DatqabaseDtuMin).
Several parameters (-Dtu, -DatabaseDtuMin, and -DatabaseDtuMax) require the value being set is from the list of valid values for that parameter.
For example, -DatabaseDtuMax for a Standard 100 eDTU pool can only be set to 10, 20, 50, or 100.
For details about which values are valid, see the table for your specific size pool in elastic pools.
Now coming to Create an elastic pool, move database to elastic pool
Login-AzureRmAccount
#verify your subscriptions
Get-AzureRmSubscription
#set your default Subscription
Get-AzureRmSubscription –SubscriptionName “Visual Studio Premium with MSDN” | Select-AzureRmSubscription
#create the elastic pool
New-AzureRmSqlElasticPool -ResourceGroupName "TestResources" -ServerName "testdbs" -ElasticPoolName "TestPool" -Edition "Standard" -Dtu 100 -DatabaseDtuMin 10 -DatabaseDtuMax 100
#move databases into the pool -Here We are moving t1 & t2 database to TestPool
Set-AzureRmSqlDatabase -ResourceGroupName "TestResources" -ServerName "testdbs" -DatabaseName "t1" -ElasticPoolName "TestPool"
Set-AzureRmSqlDatabase -ResourceGroupName "TestResources" -ServerName "testdbs" -DatabaseName "t2" -ElasticPoolName "TestPool"
/* Modify properties for an elastic pool*/
Set-AzureRmSqlDatabaseElasticPool -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -ElasticPoolName "ElasticPool01" -Dtu 1000 -DatabaseDtuMax 100 -DatabaseDtuMin 20
This command modifies properties for an elastic pool named elasticpool01. The command sets the number of DTUs for the elastic pool to 1000 and sets the minimum and maximum DTUs.
/*Modify the storage max size of an elastic pool */
Set-AzureRmSqlDatabaseElasticPool -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -ElasticPoolName "ElasticPool01" -StorageMB 2097152
This command modifies properties for an elastic pool named elasticpool01. The command sets the max storage for an elastic pool to 2 TB.
output will be like this:-
ResourceId : /subscriptions/00000000-0000-0000-0000-000000000001/resourceGroups/resourcegroup01/providers/Microsoft.Sql/servers/Server01/elasticPools/ElasticPool01
ResourceGroupName : ResourceGroup01
ServerName : Server01
ElasticPoolName : ElasticPool01
Location : Central US
CreationDate : 8/26/2015 10:00:17 PM
State : Ready
Edition : Premium
Dtu : 200
DatabaseDtuMax : 100
DatabaseDtuMin : 20
StorageMB : 2097152
Tags :
*/
Questions:1
You plan to implement a Microsoft Azure SQL database using classic model.
You need to create and manage the new database on a new server.
Which three cmdlets should you use? Each correct answer presents part of the solution.
A.New-AzureSqlDatabaseServer
B.New AzureSqlDatabaseServerFirewallRule
C.New-AzureSqlDatabaseServerContext
D.New-AzureVM
E.New-AzureSqlDatabase
Question 2:-
What is difference between Set-AzureRmSqlDatabaseElasticPool & Set-AzureRmSqlDatabase
Thanks for Reading.
1.Provision a azure SQL database with Azure PowerShell using ARM - How to configure a Azure SQL databases
2.New-AzureRmSqlElasticPool & Set-AzureRmSqlElasticPool
3.Create an elastic pool, move database to elastic pool
4.Set the property of elastic pool
5.Configure elastic pools
Demo: Provision with Azure PowerShell using ARM
Let's check out a demo of provision in Azure SQL Database with Azure PowerShell using ARM
#setup your account
Login-AzureRmAccount
#verify your subscriptions
Get-AzureRmSubscription
#set your default Subscription
Get-AzureRmSubscription –SubscriptionName “Visual Studio Enterprise” | Select-AzureRmSubscription
#create the Azure SQL server
$adminCredential = Get-Credential
New-AzureRmSqlServer -ResourceGroupName CTLdemoresourcegroup' -Location 'Central India' -ServerName "rakeshdemosql' -SqlAdministratorCredentials $adminCredential -ServerVersion "12.0"
#create a new Basic database
New-AzureRmSqlDatabase -DatabaseName “rakdemodatabase" -ServerName " rakeshdemosql " -ResourceGroupName 'CTLdemoresourcegroup' -Edition "Basic"
# set a firewall rule
New-AzureRmSqlServerFirewallRule -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -FirewallRuleName "Rule01" -StartIpAddress "192.168.0.198" -EndIpAddress "192.168.0.199“
Configure an elastic pool
There's one additional decision that needs to be made when provisioning a new Azure SQL database, and that's whether you want to use an elastic pool of resources
or not.The databases that we've created so far did not use elastic pools, which means that the number of DTUs provided by the chosen service tier were only available for that database.
Those DTUs were reserved for use and paid for whether we used them or not.
With elastic pools, a single group of resources is made available and shared by a group of databases.
These resources are called elastic database transaction units, or EDTUs.Using EDTUs can help keep costs down while still allowing individual databases to consume
a higher number of resources when needed during unpredictable peak usage times.
Pooling resources is particularly cost-effective when databases experience peak traffic at different times.
You can create an elastic pool when you first configure the server or the database, so let's take a look at that.
The more common way is to create a pool on an already existing server and add databases to it.
Let's go ahead and take a look at that option.
Inside this resource group, we have one SQL Server instance called rakeshdemosql and that server has two databases, t1 & t2.
Now to create an elastic pool of resources for the two databases that are inside of the server instance we'll go up to the top and press New pool.
We have a basic pool, a standard pool, and a premium pool.
We can see their details down below. For instance, in the standard pool, we can choose to have between 50 and 3,000 EDTUs per pool.
We can also include up to 3,000 gigabytes of storage per pool.
Each standard pool can have up to 500 databases, and each database can be allocated up to 3,000 EDTUs.
Also each database can be up to 250 gigabytes in space.
We could also specify the size of the pool in gigabytes.
What is difference between New-AzureRmSqlElasticPool & Set-AzureRmSqlElasticPool
The Set-AzureRmSqlElasticPool cmdlet sets properties for an elastic pool in Azure SQL Database. This cmdlet can
modify the eDTUs per pool (Dtu),
storage max size per pool (StorageMB),
maximum eDTUs per database (DatabaseDtuMax), and
minimum eDTUs per database (DatqabaseDtuMin).
Several parameters (-Dtu, -DatabaseDtuMin, and -DatabaseDtuMax) require the value being set is from the list of valid values for that parameter.
For example, -DatabaseDtuMax for a Standard 100 eDTU pool can only be set to 10, 20, 50, or 100.
For details about which values are valid, see the table for your specific size pool in elastic pools.
Now coming to Create an elastic pool, move database to elastic pool
Login-AzureRmAccount
#verify your subscriptions
Get-AzureRmSubscription
#set your default Subscription
Get-AzureRmSubscription –SubscriptionName “Visual Studio Premium with MSDN” | Select-AzureRmSubscription
#create the elastic pool
New-AzureRmSqlElasticPool -ResourceGroupName "TestResources" -ServerName "testdbs" -ElasticPoolName "TestPool" -Edition "Standard" -Dtu 100 -DatabaseDtuMin 10 -DatabaseDtuMax 100
#move databases into the pool -Here We are moving t1 & t2 database to TestPool
Set-AzureRmSqlDatabase -ResourceGroupName "TestResources" -ServerName "testdbs" -DatabaseName "t1" -ElasticPoolName "TestPool"
Set-AzureRmSqlDatabase -ResourceGroupName "TestResources" -ServerName "testdbs" -DatabaseName "t2" -ElasticPoolName "TestPool"
/* Modify properties for an elastic pool*/
Set-AzureRmSqlDatabaseElasticPool -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -ElasticPoolName "ElasticPool01" -Dtu 1000 -DatabaseDtuMax 100 -DatabaseDtuMin 20
This command modifies properties for an elastic pool named elasticpool01. The command sets the number of DTUs for the elastic pool to 1000 and sets the minimum and maximum DTUs.
/*Modify the storage max size of an elastic pool */
Set-AzureRmSqlDatabaseElasticPool -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -ElasticPoolName "ElasticPool01" -StorageMB 2097152
This command modifies properties for an elastic pool named elasticpool01. The command sets the max storage for an elastic pool to 2 TB.
output will be like this:-
ResourceId : /subscriptions/00000000-0000-0000-0000-000000000001/resourceGroups/resourcegroup01/providers/Microsoft.Sql/servers/Server01/elasticPools/ElasticPool01
ResourceGroupName : ResourceGroup01
ServerName : Server01
ElasticPoolName : ElasticPool01
Location : Central US
CreationDate : 8/26/2015 10:00:17 PM
State : Ready
Edition : Premium
Dtu : 200
DatabaseDtuMax : 100
DatabaseDtuMin : 20
StorageMB : 2097152
Tags :
*/
Questions:1
You plan to implement a Microsoft Azure SQL database using classic model.
You need to create and manage the new database on a new server.
Which three cmdlets should you use? Each correct answer presents part of the solution.
A.New-AzureSqlDatabaseServer
B.New AzureSqlDatabaseServerFirewallRule
C.New-AzureSqlDatabaseServerContext
D.New-AzureVM
E.New-AzureSqlDatabase
Question 2:-
What is difference between Set-AzureRmSqlDatabaseElasticPool & Set-AzureRmSqlDatabase
Thanks for Reading.