About Me
- Rakesh Kumar
- I am MCSE in Data Management and Analytics with specialization in MS SQL Server and MCP in Azure. I have over 13+ years of experience in IT industry with expertise in data management, Azure Cloud, Data-Canter Migration, Infrastructure Architecture planning and Virtualization and automation. Contact me if you are looking for any sort of guidance in getting your Infrastructure provisioning automated through Terraform. I sometime write for a place to store my own experiences for future search and read by own blog but can hopefully help others along the way. Thanks.
Azure SQL Database Query Performance Insight - Intelligent Insights -Performance recommendation - Automatic tuning in Azure SQL Database
Here we will discuss
1.Azure SQL Database Query Performance Insight
2.Intelligent Insights
3.Performance recommendation in the Azure portal
4.Automatic tuning in Azure SQL Database
How to Configure a Azure SQL Database using powershell & elastic pools & add dbs to elastic pool.
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.
SQL Server script related to logins & Permission.
Here We are creating 2 Windows users Name Adven\Rakesh & adven\kushagra and giving db_datareader database role on DB AdventureWorks2014
use master;
if(SUSER_ID('Adven\Rakesh') is NULL)
begin
create login [Adven\Rakesh] from WINDOWS;
end
if(SUSER_ID('adven\kushagra') is NULL)
begin
create login [adven\kushagra] from WINDOWS;
end
use AdventureWorks2014;
if not exists (select 1 from sys.database_principals where name = 'adven\rakesh')
create user [adven\rakesh] for login [adven\rakesh] with default_schema = [dbo];
exec sp_addrolemember @membername = N'adven\rakesh', @rolename = N'db_datareader';
if not exists (select 1 from sys.database_principals where name = 'adven\kushagra')
create user [adven\kushagra] for login [adven\kushagra] with default_schema = [dbo];
exec sp_addrolemember @membername = N'adven\kushagra', @rolename = N'db_datareader';
After giving permission, if i have to check script has executed and
1. logins & User has been created or not
2.permission has been added successfully or not execute below script
use AdventureWorks2014;
go
SELECT DP1.name AS DatabaseRoleName,
isnull (DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
and isnull (DP2.name, 'No members') in ('adven\kushagra','Adven\Rakesh')
ORDER BY DP1.name;
~~~~~~~~~~~~~~~~~~~~~~
Server and database Role:-
Ans : kindly reply answer in comment section..
kindly watch this space, i will keep adding more logical script related to SQL Server logins & Users
Using Powershell how to failover Resource Group & Available storage from one node to another..
In cluster Administrator, we have a storage resource name
SQL02, which is not assigned to any node and kept as Available Storage and its
Owner Node is XX1DBTSJR17F02.
The requirement is to install SQL Server 2016 on storage
SQL02.
I executed SQL server installation setup from XX1DBTSJR17F01.
The setup got failed stated there is no disk for SQL Server
installation.
It looks to me GUI does not give any option to failover
SQL02 from XX1DBTSJR17FO2 to XX1DBTSJR17FO1
Q:- Now the question is how to move SQL02 available
storage from XX1DBTSJR17FO2 to XX1DBTSJR17FO1 and start installation.
It gives an error Root of the path
G:\MSSQL14.XX1DBTSJR17SQL17\MSSQL\DATA does not exist.
Hence below PowerShell script will move Available storage
from one node to another node.
Function Failver-SQLServerServices()
{
<#
SYNOPSIS
Compile
this function in powershell window using run as Administrator.
.DESCRIPTION
Failover SQL Server services from Active to Passive Node & Vice Versa
DBA team will check back up and move the roles to other node -
Need the script / commands which you use to do this activity .
windows team will include this in the main script.
.NOTES
Author: Rakesh Kumar
.LINK
#>
[cmdletbinding()]
param(
[Parameter(
Mandatory=$true
, HelpMessage='The Name of the SQL Server resource group'
)
]
[string]$GroupName
,[Parameter(
Mandatory=$true
, HelpMessage='The TargetNode'
)
]
[string]$TargetNode
,
[Parameter(
Mandatory=$true
, HelpMessage='The Windows Cluster Name'
)
]
[string]$Workingcluster
)
Import-Module FailoverClusters
Write-Host "Resource group $GroupName moving to node $TargetNode
...";
Move-ClusterGroup –Name
$GroupName -Node
$TargetNode -Cluster
$Workingcluster;
Write-Host "Resource group $GroupName was moved to node $TargetNode succesfully...";
}
|
1.PowerShell
Command to retrieve cluster FQDN
Get-Cluster | fl * -- command to retrieve complete cluster details
2.PowerShell
Command to retrieve cluster resources
Get-ClusterResource – command to retrieve cluster resources
3.PowerShell
Command to retrieve cluster nodes
Get-ClusterNode -- command to retrieve Cluster nodes
4.PowerShell Command to retrieve cluster group -- This command will give all roles and its owner Node Get-Clustergroup |
# Calling
functions- Now we have to call Failver-SQLServerServices function based on
changing parameters..
1. In order to move SQL Server resource 'Available Storage' group from 'XX1DBTSJR17F01' to XX1DBTSJR17F02 we need toexecute below function.
Failver-SQLServerServices -GroupName 'Available Storage'
-TargetNode 'XX1DBTSJR17F01'
-Workingcluster 'XX1DBTSJR17FA.JXXAB.LOCAL'
|
Additional
if we have to failover SQL Server role from one node to another node you can use same user defined function "Failver-SQLServerServices", hence you will save lot of time.
Before executing below function we need to retrieve 3 values
1. Cluster Group Name
2. Target Node
3.Working Cluster Name
1.How to retrieve Cluster group Name: - IN order to retrieve Cluster Group Name we have to execute command Get-Cluster | fl *
it will give output like
Domain : JXXAB.LOCAL
Name : XX1DBTSJR17FA
whole string will be :XX1DBTSJR17FA.JXXAB.LOCAL
2. How to retrieve Target Node:- in order to retrieve Target Node, execute Get-ClusterNode commmand. output of this command will give to Node name, you have to decide which node you have to select to failover of role.
3. Cluster Group Name: - IN order to retrieve Cluster group Name you have to execute
Get-ClusterGroup
output of Get-ClusterGroup will be like this:-
PS C:\Users\LabGuestAdmin> Get-Clustergroup
Name OwnerNode State
---- --------- -----
Available Storage XX1DBTSJR17F02 Online
Cluster Group XX1DBTSJR17F02 Online
SQL Server (MSSQLSERVER) XX1DBTSJR17F01 Failed
XX1DBTSJR17SQL17 XX1DBTSJR17F01 Online
hence
1. In order to move SQL Server Role from XX1DBTSJR17F01 to XX1DBTSJR17F02
use below command
Failver-SQLServerServices -GroupName 'SQL Server (MSSQLSERVER)' -TargetNode 'XX1DBTSJR17F02' -Workingcluster 'XX1DBTSJR17FA.JXXAB.LOCAL'
How to retrieve list of drives or mount points that contain the database files and the free space on those drives
This query returns a list of drives or mount points that contain the database files and the free space on those drives
How to write SQL Server Audit Events to the Security Log
How to write SQL Server Audit Events to the Security Log
Errors related to Database Mirroring\AlwaysOn or HADR endpoint
Errors related to Database Mirroring
or HADR endpoint
Few
facts about HADR endpoint
Ø Endpoints are objects
which are used to receive connections from other server instances.
Ø They use Transmission
Control Protocol (TCP) to communicate.
Ø Database Mirroring endpoints
needs to be created manually just to participate in DB Mirroring\AlwaysOn sessions.
Ø Listens on a unique TCP
port number
Ø By default, endpoint
requires encryption of data. We can disable encryption. If encryption is
disabled, data is never encrypted when getting transferred between replicas. An
endpoint without configured without encryption, cannot connect to an endpoint
that requires encryption.
Encryption algorithms: RC4, AES, AES RC4, RC4 AES
RC4 is a deprecated Algorithm and by default AES is used.
If
there are issues with endpoint configuration or the account with which they are
authenticated then the connectivity between replicas will be impacted. When we
will analyse the SQL errorlogs from both replicas then we may see following
errors:
Case 1: The SQL Server service
account running with ‘Network Service’ account
Primary
Replica
2018-04-09
08:47:39.99 spid36s A connection
timeout has occurred while attempting to establish a connection to availability
replica 'WIN2K12-3' with id [2711ED1C-C852-487C-9AEC-C424C08AB4E7]. Either a
networking or firewall issue exists, or the endpoint address provided for the
replica is not the database mirroring endpoint of the host server instance.
2018-04-09
08:47:56.33 spid35s Recovery
completed for database AdventureWorks2014 (database ID 5) in 25 second(s)
(analysis 0 ms, redo 0 ms, undo 0 ms.) This is an informational message only.
No user action is required.
Secondary
Replica
2018-04-09
08:47:30.00 Logon Database
Mirroring login attempt by user 'ADVEN\WIN2K12-1$.' failed with error:
'Connection handshake failed. The login 'ADVEN\WIN2K12-1$' does not have
CONNECT permission on the endpoint. State 84.'.
[CLIENT: 192.168.1.101]
2018-04-09
08:47:32.21 Logon Database
Mirroring login attempt by user 'ADVEN\WIN2K12-1$.' failed with error:
'Connection handshake failed. The login 'ADVEN\WIN2K12-1$' does not have
CONNECT permission on the endpoint. State 84.'.
[CLIENT: 192.168.1.101]
Ø In this example, the
secondary replica is not able to authenticate the connection coming from
primary replica because the account which is sender of requests is not added as
a login in secondary replica. Here, the primary replica is running with Network
Service account so the host computer account (Domainname\ComputerName$) must be
created in master of each of the other servers. Then need to give connect
permission to the endpoints.
USE [master]
GO
CREATE LOGIN [ADVEN\WIN2K12-1$] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [ADVEN\WIN2K12-1$]
GO
Replace
the login name as per the account coming for you in the errorlog
Your error will solve
Thanks for reading..
Your error will solve
Thanks for reading..
Important link for Certification No 70-473 -Designing and Implementing Cloud Data Platform Solutions
Performance recommendations for SQL Database
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-advisor
For more information about automatic tuning of database indexes and query execution plans, see Azure SQL Database automatic tuning.
For more information about automatically monitoring database performance with automated diagnostics and root cause analysis of performance issues, see Azure SQL Intelligent Insights.
For more information about how to use performance recommendations in the Azure portal, see Performance recommendations in the Azure portal.
See Query Performance Insights to learn about and view the performance impact of your top queries.
A.Monitor Subscription Activity with the Azure Activity Log
B.Use AzureRM.RecoveryServices.Backup cmdlets to back up virtual machines
C.Monitoring Azure SQL Database using dynamic management views
D.Artificial Intelligence tunes Azure SQL Databases
E.Write SQL Server Audit Events to the Security Log
F.Always Encrypted (Database Engine)
G.SQL Server Audit feature – Introduction
H.How to set up and use SQL Server Audit
I.Azure SQL Database elastic query overview (preview)
J.Using the Deploy Database to SQL Azure Wizard in SQL Server Management Studio to move to the Cloud
K.Azure SQL Database Query Performance Insight
L.Monitoring Azure SQL Database using dynamic management views
M.Troubleshoot Azure SQL Database performance issues with Intelligent Insights
N.Elastic pools help you manage and scale multiple Azure SQL databases
O.Always Encrypted: Protect sensitive data in SQL Database and store your encryption keys in the Windows certificate store
P.Always Encrypted: Protect sensitive data in SQL Database and store your encryption keys in Azure Key Vault
Q.Data Warehouse Units (DWUs) and compute Data Warehouse Units (cDWUs)
L.Migrate SQL Server database to SQL Database using Deploy Database to Microsoft Azure Database Wizard
M.Create,view, and manage alerts using Azure Monitor
N.What are Azure resource diagnostic logs &Collect and consume log data from your Azure resources
what is difference between sys.dm_db_resource_stats & sys.resource_stats & sys.dm_os_performance_counters
what is difference between sys.dm_db_resource_stats & sys.resource_stats & sys.dm_os_performance_counters
Difference between sys.fn_get_audit_file & sys.fn_xe_file_target_read_file with example
Difference between sys.fn_get_audit_file & sys.fn_xe_file_target_read_file
What is TLS & How to enable TLS 1.2 on MS SQL Server Database Servers.
What is TLS & How to check and enable TLS 1.2 on various versions of MS SQL Server DB SERVERS?
How to set different ipaddresses in each server for configure multi subnet server using RRAS
Here the requirement is we have to configure 3 nodes in Multi subnet environment and each node should ping to each other, later we will add these nodes in a cluster.
how to fix Powershell error – The term ‘Login-AzureRmAccount’ is not recognized as the name of a cmdlet, function, script file, or operable program
Powershell error – The term ‘Login-AzureRmAccount’ is not recognized as the name of a cmdlet, function, script file, or operable program
ARM Template overview and How to DEMO : ARM Template Deployment with Powershell and JSON.
How to deploy a Virtual machine using ARM Template
Important links for SQL Server 2016 & 2017 Database Administration & 70-764 Certification
Very Important links for SQL Server 2016 & 2017 Database Administration & 70-764 Certification
Perform Index Operations Online
Perform Index Operations Online
Here we will
1. Create a clustered index on the PRIMARY filegroup if the index does not exist
2 Verify filegroup location of the clustered index.
3 Create filegroup NewGroup if it does not exist.
4 Verify new filegroup
5.
-- Drop the clustered index and move the BillOfMaterials table to
-- the Newgroup filegroup.
-- Set ONLINE = OFF to execute this example on editions other than Enterprise Edition.
6.
-- Verify filegroup location of the moved table.
SQL
USE AdventureWorks2012;
GO
--Create a clustered index on the PRIMARY filegroup if the index does not exist.
IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name =
N'AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate')
CREATE UNIQUE CLUSTERED INDEX
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
ON Production.BillOfMaterials (ProductAssemblyID, ComponentID,
StartDate)
ON 'PRIMARY';
GO
-- Verify filegroup location of the clustered index.
SELECT t.name AS [Table Name], i.name AS [Index Name], i.type_desc,
i.data_space_id, f.name AS [Filegroup Name]
FROM sys.indexes AS i
JOIN sys.filegroups AS f ON i.data_space_id = f.data_space_id
JOIN sys.tables as t ON i.object_id = t.object_id
AND i.object_id = OBJECT_ID(N'Production.BillOfMaterials','U')
GO
--Create filegroup NewGroup if it does not exist.
IF NOT EXISTS (SELECT name FROM sys.filegroups
WHERE name = N'NewGroup')
BEGIN
ALTER DATABASE AdventureWorks2012
ADD FILEGROUP NewGroup;
ALTER DATABASE AdventureWorks2012
ADD FILE (NAME = File1,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\File1.ndf')
TO FILEGROUP NewGroup;
END
GO
--Verify new filegroup
SELECT * from sys.filegroups;
GO
-- Drop the clustered index and move the BillOfMaterials table to
-- the Newgroup filegroup.
-- Set ONLINE = OFF to execute this example on editions other than Enterprise Edition.
DROP INDEX AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
ON Production.BillOfMaterials
WITH (ONLINE = ON, MOVE TO NewGroup);
GO
-- Verify filegroup location of the moved table.
SELECT t.name AS [Table Name], i.name AS [Index Name], i.type_desc,
i.data_space_id, f.name AS [Filegroup Name]
FROM sys.indexes AS i
JOIN sys.filegroups AS f ON i.data_space_id = f.data_space_id
JOIN sys.tables as t ON i.object_id = t.object_id
AND i.object_id = OBJECT_ID(N'Production.BillOfMaterials','U');
GO
Subscribe to:
Posts (Atom)