tag:blogger.com,1999:blog-60974098065919166982024-03-18T15:02:29.058-07:00 Solutions with Practical SQL DBA Author: Nelson John ANelsonhttp://www.blogger.com/profile/02156244559143388581noreply@blogger.comBlogger86110tag:blogger.com,1999:blog-6097409806591916698.post-29731163110319713252020-06-20T00:58:00.257-07:002020-07-02T10:52:30.005-07:00Stairway to Microsoft Azure SQL Database Part 5 : Azure SQL serve Server Firewall and virtual Network Setting <div style="text-align: justify;"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj3hIMy5sRRiuAzjy4LJL-1W6F1Tzla1TPJUqRWdeOkMDKku07vyBC5argLkRsHFnDs7vAG_4XgYyIXtFgrZtvhuHeF-irjA-A5v7haYQsSFK7yYQLW7PiEEpXhP9dnFgzYseTguNN6C9t2/s320/Part5.png" style="display: none;" /><span style="background-color: white; color: #191919; font-size: 16px; text-align: start;"><span style="background-color: white; color: #191919; font-size: 17px; text-align: start;"><font face="arial">In our last <a href="http://www.practicalsqldba.com/2020/05/stairway-to-microsoft-azure-sql_31.html" target="_blank">blog</a>, we were discussing about Azure SQL server and we learned that Azure SQL server is a logical construct that acts as a central administrative point for the databases hosted under that server. One of the important aspects of the server is managing the connections. All these connection settings are found in the firewall and virtual network under the Security tab. Let us discuss various connectivity options in this blog.</font></span></span></div><div style="text-align: justify;"><br /></div><div style="text-align: justify;"><span style="background-color: white; color: #191919; font-size: 16px; text-align: start;"><span style="background-color: white; color: #191919; font-size: 17px; text-align: start;"><font face="arial"><b>Public Network Access:</b> By default, Azure SQL server provides a public endpoint to access SQL Server through whitelisted IP addresses. You can access your database from anywhere in the world through the internet as long as your IP address is whitelisted. You can whitelist a single IP address or range of IP addresses using the option available under the page "Firewall and Virtual Network settings". As per the security best practices, you should never allow public endpoint to your database. To disable the public endpoint associated with your server, you can enable the "Deny Public network" option. </font></span></span><span style="background-color: white; color: #191919; font-family: arial; font-size: 17px;">Azure portal will not allow you to disable the public network option unless you have configured a private endpoint connection. This makes sense as without a private endpoint you will not able to connect the server once you disable the public network access. We will discuss private endpoints and its configuration in our next blog. </span><span style="background-color: white; color: #191919; font-family: arial; font-size: 17px;">You can whitelist the IP address in two-level, on the server level, and another one in the database level. When you whitelist the IP address from the portal, it will whitelist at the server level. You can find the whitelisted IP address information using the DMV <i>sys.firewall_rule</i> under the master database. You can also whitelist the IP address using the procedure </span><span style="font-size: 17px; text-align: left;"><font color="#191919" face="arial"><i>sp_set_firewall_rule</i>. To white list the IP in the database level, you can use the procedure </font></span><span style="font-size: 17px; text-align: left;"><font color="#191919" face="arial"><i>sp_set_database_firewall_rule. </i></font></span><span style="color: #191919; font-family: arial; font-size: 17px; text-align: left;">Database firewall rule can be configured for the master database as well as for the user databases. Database level firewall setting makes more sense while using the contained user. The below diagram provide a better understanding of this setup. While establishing the connection, Azure SQL checks database level whitelisting first and if it is not available will check server-level whitelisting.</span></div><div style="text-align: justify;"><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto;"><tbody><tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-VEFF3klKgBoC0ayZW5fcXyL765a2Y7oOZGkknKUFlpPj-1S__cSJrGYMlcYoqLXD5_4RvNFGNt5tns9z3KIU6jHRMIKevcH51NrcbCK9QXF4mYxm2EN6N5ApPIA9iW6kRWa18YqqE8rv/s729/Firewall+setting.png" style="margin-left: auto; margin-right: auto;"><img alt="Azure Firewall Setup" border="0" data-original-height="605" data-original-width="729" height="531" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-VEFF3klKgBoC0ayZW5fcXyL765a2Y7oOZGkknKUFlpPj-1S__cSJrGYMlcYoqLXD5_4RvNFGNt5tns9z3KIU6jHRMIKevcH51NrcbCK9QXF4mYxm2EN6N5ApPIA9iW6kRWa18YqqE8rv/w640-h531/Firewall+setting.png" width="640" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">Azure SQL Firewall </td></tr></tbody></table><span style="color: #191919; font-family: arial; font-size: 17px; text-align: left;"><br /></span></div><div style="text-align: justify;"><b style="color: #191919; font-family: arial; font-size: 17px; text-align: left;">Minimum TLS version:</b><span style="background-color: white; color: #191919; font-family: arial; font-size: 17px; text-align: left;"> TLS is a cryptographic protocol that provides end to end communication security over the network. The minimal TLS version setting allows you to control the version of TLS used by the server. If you set the Minimum TLS version as '>1.2' and try to connect from the client with a TLS version less than 1.2, your connection will fail with an error "Login failed with invalid TLS version". If your applications are using the unencrypted connection, do not set any minimum TLS version. You can use the 'encrypt_option' column available in DMV sys.dm_exec_connections to check the connection from the applications is encrypted or not. Azure suggest to have "</span><span style="font-size: 17px; text-align: left;"><font color="#191919" face="arial">Encrypt=True;TrustServerCertificate=False;" in the connection string. With this, the end to end communication between the server and client will be encrypted. By setting the </font></span><span style="color: #191919; font-family: arial; font-size: 17px; text-align: left;">TrustServerCertificate to false, it forces the client machine to verify the certificate provided by the server. That helps to avoid any kind of spoofing or man in the middle attack.</span></div><div style="text-align: justify;"><span style="color: #191919; font-family: arial; font-size: 17px; text-align: left;"><br /></span></div><div style="text-align: justify;"><span style="color: #191919; font-family: arial; font-size: 17px; text-align: left;"><b>Connection Policy: </b>Azure provides three connection policy to set. </span></div><div style="text-align: justify;"><ol><li><span style="color: #191919; font-family: arial; font-size: 17px; text-align: left;">Proxy</span></li><li><span style="color: #191919; font-family: arial; font-size: 17px; text-align: left;">Redirect </span></li><li><span style="color: #191919; font-family: arial; font-size: 17px; text-align: left;">Default</span></li></ol><div style="text-align: justify;"><font color="#191919" face="arial"><span style="font-size: 17px;">These connection policies define the communication channel between the server and the client machine. When you try to connect Azure SQL server, the request first reach to Azure SQL gateway. Azure has a set of gateway IP addresses for each region which keeps listening on the port 1433. The number of gateway in each region depends on the workload in that region. For example, East US2 has 5 gateway IP addresses whereas Australia central region has only one gateway IP address. You can do telnet to these IP addresses through port number 1433. Once it reached the gateway, based on the connection policy setting, request forwarded to the actual SQL server node hosting your database or return the redirection information back to the client. You can do nslookup to your Azure SQL server and it provides you the detail of associated gateway. I did nslookup to one of my Azure SQL servers hosted in the US East2 region. It returned the one of the gateway IP address and its DNS name. Now I can do telnet to this IP address using the port number 1433. </span></font></div><div style="text-align: justify;"><font color="#191919" face="arial"><span style="font-size: 17px;"><br /></span></font></div><div style="text-align: justify;"><font color="#191919" face="arial"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRRjITYack7EYvTLC07hhSE2Pchx18tYus8dquNeQM8QGoJIw0jvAQifGGGNuOBNx7pHlv__wD3F-0MOgjHfIeaLp5WpJ-65QeSNhbOQcEpYiW337qfWdp-QhhBR1eYKjGNNUMb0XPrm8t/s754/nslookup_Azure_server.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="227" data-original-width="754" height="192" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRRjITYack7EYvTLC07hhSE2Pchx18tYus8dquNeQM8QGoJIw0jvAQifGGGNuOBNx7pHlv__wD3F-0MOgjHfIeaLp5WpJ-65QeSNhbOQcEpYiW337qfWdp-QhhBR1eYKjGNNUMb0XPrm8t/w640-h192/nslookup_Azure_server.png" width="640" /></a></div><span style="font-size: 17px;"><br /></span></font></div><div style="text-align: justify;"><font color="#191919" face="arial"><span style="font-size: 17px;"><br /></span></font></div><div style="text-align: justify;"><font color="#191919" face="arial"><span style="font-size: 17px;">To establish the connection from your local machine or from the Virtual machine hosted in Azure, it should allow the outbound traffic to all the Azure gateway IP address of that region through port number 1433. The gateway IP address of each region is published on their website. </span></font><span style="color: #191919; font-family: arial; font-size: 17px;">Let us look into each connection policy in detail.</span></div><div style="text-align: justify;"><font color="#191919" face="arial"><span style="font-size: 17px;"><br /></span></font></div><div style="text-align: justify;"><font color="#191919" face="arial"><span style="font-size: 17px;"><b><span> </span><span> </span>Proxy: </b></span></font><span style="color: #191919; font-family: arial; font-size: 17px;"> With this policy, when the client machine initiates the connection to Azure SQL server, first it will connect to one of the gateways in that region and forward the connection to the actual compute node in which your SQL database/server is hosted. In the below case (diagram), I am trying to connect to my server "</span><font color="#191919" face="arial"><span style="font-size: 17px;"><i>mydbserver.database.windows.net</i>". When I did the nslookup to my server name, it resolved to 52.167.104.0 which is one of the gateway IP addresses in the US East 2 region. When the connection is initiated from the client to the server, it will get connected to the gateway and gateway forward the connection to the node in which my database is hosted. In this case, the gateway act as a proxy between the client machine and the server. The client machine does not have any idea to which the connection is forwarded.</span></font></div></div><div style="text-align: justify;"><span style="color: #191919; font-family: arial; font-size: 17px; text-align: left;"><br /></span></div><div style="text-align: justify;"><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto;"><tbody><tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh2Kf7lfemt39HHWPBHVC4khSS4kTGj2ERXNvbWCilUwLlq4lOT9cQoo1DeZ61P9lKDU6Oz3HOESzMtT9AQbuG3EGSlk1SJqvtgUo3OtHf9pD7vlNOLxXihm29Kpov0m8seK8hBkoMAZDcI/s666/proxy+connection+new+%25283%2529.png" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="666" data-original-width="611" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh2Kf7lfemt39HHWPBHVC4khSS4kTGj2ERXNvbWCilUwLlq4lOT9cQoo1DeZ61P9lKDU6Oz3HOESzMtT9AQbuG3EGSlk1SJqvtgUo3OtHf9pD7vlNOLxXihm29Kpov0m8seK8hBkoMAZDcI/w588-h640/proxy+connection+new+%25283%2529.png" width="588" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;"><b>Proxy Connection</b><br /></td></tr></tbody></table><span style="color: #191919; font-family: arial; font-size: 17px; text-align: left;"><br /></span></div><div style="text-align: justify;"><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><br /></div><font color="#191919" face="arial"><span style="font-size: 17px;"><b><span> </span><span> </span><span> </span>Redirect: </b></span></font><span style="color: #191919; font-family: arial; font-size: 17px;"> With this connection policy, when the client initiates the connection, it will connect to the gateway and gateway return the redirection information to connect the node in which the database is hosted to the client. Using that information, the client directly connects to the node in which the database is hosted bypassing the gateway. Azure claims that this will reduce the latency. To make this work, the client machine :</span></div><div style="text-align: justify;"><ul><li><span style="color: #191919; font-family: arial; font-size: 17px;">Should allow outbound communication to the SQL database gateway IP addresses on port 1433 (This is for the initial connection to get the redirection information)</span></li><li><font color="#191919" face="arial"><span style="font-size: 17px;">Should allow outbound communication to all Azure SQL IP addresses (nodes) in the region on a port range of 11000 to 11999 (All the SQL node in azure listen to one of port between 11000 and 11999). This can be easily done using the service tag. It is not practical to add numerous IP addresses of the node manually. </span></font></li></ul></div><div style="text-align: justify;"><span style="color: #191919; font-family: arial; font-size: 17px;"><br /></span></div><div style="text-align: justify;"><font color="#191919" face="arial"><span style="font-size: 17px;">In the below example, the client machine is initiating the connection to connect the server "Mydbserver.database.windows.net". The initial request will go to the gateway. On receiving the request, the gateway returns the redirection information (basically the name of the node in which the database is hosted), and the client uses that information to establish the direct communication with the node in which the database is hosted. There onwards all request in that connection goes directly to the node bypassing the gateway.</span></font></div><div style="text-align: justify;"><span style="color: #191919; font-family: arial; font-size: 17px;"><br /></span></div><div style="text-align: justify;"><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto;"><tbody><tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOFx0gsS57-ia9BajewRNRFn8IQoVO0Ld6hRCWopawt4y0JgSBpuRAp01u3JavYJYNanIzIDPGmn7GTNbq3_O0Hxe3s9eh2vUHYAKt7a_J5P2QuxJ028AUG2gxjCvi8UQRrEvzFKQiJBd-/s746/Redirect+Connection+Policy+%25281%2529.png" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="666" data-original-width="746" height="572" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOFx0gsS57-ia9BajewRNRFn8IQoVO0Ld6hRCWopawt4y0JgSBpuRAp01u3JavYJYNanIzIDPGmn7GTNbq3_O0Hxe3s9eh2vUHYAKt7a_J5P2QuxJ028AUG2gxjCvi8UQRrEvzFKQiJBd-/w640-h572/Redirect+Connection+Policy+%25281%2529.png" title="Redirection Connection Policy" width="640" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;"><b>Redirection Connection <br /></b><br /><table cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody><tr><td class="tr-caption" style="text-align: center;"><br /></td></tr></tbody></table><span style="color: #191919; font-family: arial; font-size: 17px; text-align: justify;"><div style="text-align: justify;"><b><span> </span>Default: </b>This is the default setting when you create the Azure SQL server unless you explicitly change to Proxy or redirect. With this setting, all the connections initiated from the Azure use the redirect connection policy and use the proxy connection policy for all connections initiated from outside Azure.</div></span><br /></td></tr></tbody></table><span style="color: #191919; font-family: arial; font-size: 17px;"><b>Allow Azure Services and resources to access this server:</b> When this setting is enabled, all the azure service and resources in that region can access this server. For example, if we have VM hosted in azure platform, that VM can access the SQL server without whitelisting the IP address of the VM. <u>Keep in mind that, this is not restricting to the resource hosted in your account.</u> Enabling this feature widely opens the communication from all the resources hosted in the Azure platform including the other customer's account also.</span></div><div style="text-align: justify;"><span style="color: #191919; font-family: arial; font-size: 17px;"><br /></span></div><div style="text-align: justify;"><span style="color: #191919; font-family: arial; font-size: 17px; font-weight: bold;">Connect </span><span style="color: #191919; font-family: arial; font-size: 17px;"><b> VNET/subnet: </b>This is one of the options where you can attach the subnet to this sever. This allows all resources hosted in that subnet to connect to the Azure SQL server without whitelisting the IP address. Moreover, this enables communication through the Azure backbone networks. Microsoft suggests to use this feature to enable a secure and fast connection. Before attaching the subnet, note that we need to enable the service endpoint for the SQL server at the subnet level.</span></div><div style="text-align: justify;"><span style="color: #191919; font-family: arial; font-size: 17px;"> </span></div><div style="text-align: justify;"><span style="color: #191919; font-family: arial; font-size: 17px;">These are the various option available under the Firewall and Virtual network setting. In our next post, we will discuss enabling private endpoints.</span></div><div style="text-align: justify;"><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj3hIMy5sRRiuAzjy4LJL-1W6F1Tzla1TPJUqRWdeOkMDKku07vyBC5argLkRsHFnDs7vAG_4XgYyIXtFgrZtvhuHeF-irjA-A5v7haYQsSFK7yYQLW7PiEEpXhP9dnFgzYseTguNN6C9t2/s1005/Part5.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="439" data-original-width="1005" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj3hIMy5sRRiuAzjy4LJL-1W6F1Tzla1TPJUqRWdeOkMDKku07vyBC5argLkRsHFnDs7vAG_4XgYyIXtFgrZtvhuHeF-irjA-A5v7haYQsSFK7yYQLW7PiEEpXhP9dnFgzYseTguNN6C9t2/s320/Part5.png" width="320" /></a></div><div style="text-align: justify;"><br /></div><div class="blogger-post-footer">www.PracticalSqlDba.com</div>Nelsonhttp://www.blogger.com/profile/02156244559143388581noreply@blogger.comtag:blogger.com,1999:blog-6097409806591916698.post-12789527499865109212020-05-31T10:06:00.002-07:002020-05-31T10:56:19.245-07:00Stairway to Microsoft Azure SQL Database Part 4 : Azure SQL server , an overview<div style="text-align: justify;"><span style="font-family: arial; font-size: large;">
<img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjVRgImm9oksSjESx9JFTyc1SsXO9yj1ZhszJk-UoVSsZR7VnZFTjXn5hjDk0PPj1tS0sSfYusfrMuifBup1OSUjouFzxPqT0-sLHKCxQqcfqbzFd74yZ397OBhPrP9OVwBX2ZM3sFAquhW/w200-h101/azure_sql_server_blog_heading.png" style="display: none;" />
In the last few posts, we were discussing about database deployment model and purchase model. Out of the three deployment models, managed instance works almost like on-premises SQL server and hence we are not going to discuss much about the managed instance. On the other side, a single database and elastic pool works a completely different ways.</span></div><div style="text-align: justify;"><span style="font-family: arial; font-size: large;"><br /></span></div><div style="text-align: justify;"><font face="arial" size="4">while creating a single database or elastic poor, you need to attach those into the Azure SQL server. In other words, you need to create a SQL server before creating the SQL database. While searching for services in the Azure portal, you can search for 'SQL database' and 'SQL server'. In this post, we are going to discuss about the SQL Server, which is a logical container for your single Azure databases and Elastic Pool.</font></div><div style="text-align: justify;"><font face="arial" size="4"><br /></font></div><div style="text-align: justify;"><font face="arial" size="4">As per the Azure documentation, "a SQL server is a logical construct that acts as a central administrative point for a collection of databases". At the server level, you can administrate the following components:</font></div><div style="text-align: justify;"><ul><li><font face="arial" size="4">Logins</font></li><li><font face="arial" size="4">Connectivity </font></li><li><font face="arial" size="4">Firewall rules </font></li><li><font face="arial" size="4">Auditing rules </font></li><li><font face="arial" size="4">Threat detection Policy </font></li><li><font face="arial" size="4">Auto Failover group</font></li><li><font face="arial" size="4">Transparent Data Encryption </font></li><li><font face="arial" size="4">Auditing </font></li><li><font face="arial" size="4">Backup Management </font></li></ul><div><font face="arial" size="4">When you create an empty SQL Server in Azure, it will not cost you anything until you create a single database or elastic pool under this SQL server. To create an empty SQL server, search for "SQL Server" in the Azure portal, and follow the instructions. While creating the SQL server, it will ask for a user name and password. This is the only SQL login that you can create from the Azure portal. This login has the highest privilege in the server and can access all databases in that server without providing explicit permission in each database. Azure SQL server does not support window authentication but supports Azure active directory authentication. For enabling the Azure active directory authentication, you need to connect your server with Azure AD. You can achieve this by setting the Active directory Admin from the Azure portal. You can find this option under the Settings section of the server. This can be an individual account or a group. This account also will have the highest privilege and can access all the databases under that server without providing explicit permission. These are the only two logins that you can create from the Azure portal. There is no option to change the SQL login once you have created it but you can change the Active directory admin at any time. These ar</font><span style="font-family: arial; font-size: large;">e the only two logins will have access to all the databases created under this server. There is no option to create server wide login which will have access to all the databases under this server. Still, you can create server wide login and can explicitly create the corresponding user in each database like we used to do in the on-premise installation. </span></div><div><br /></div><div><font face="arial" size="4">Azure SQL database provides two types of user :</font></div><div><font face="arial" size="4"><br /></font></div><div><font face="arial" size="4"><b>Login and user: </b>This is the same as we used to do in on-premise installations. Create a login and create corresponding user in each database. For example, connect to master database </font></div><div><font face="courier" size="4"><br /></font></div><div><font face="courier" size="4">create login njohn with password ='Password@123'</font></div><div><font face="courier" size="4">--Connect to DB1</font></div><div><font face="courier" size="4">create user njohn from login njohn </font></div><div><font face="courier" size="4">--Connect to DB2 </font></div><div><div><font face="courier" size="4">create user njohn from login njohn </font></div></div><div><span style="font-family: arial; font-size: large;"><br /></span></div><div><span style="font-family: arial; font-size: large;">In this case, the login information is stored at the server level and the password remains same across all databases. While copying the database DB1 and DB2 to another server, you also need to copy the login and need to map the SID properly.</span></div><div><span style="font-family: arial; font-size: large;"><br /></span></div><div><span style="font-family: arial; font-size: large;"><b>Contained user: </b>These are the users created under the database without having the logins. In this case, the user informations are stored in the database level. After the restoration of the databases, DB1 and DB1 to another server, we don't have to copy the login information separately as the user password and other information are stored in the user database. In this scenario, the same user can have different passwords to connect with each database. For example </span></div><div><br /></div><div><br /></div><div><div><font face="courier" size="4">--Connect to DB1</font></div><div><font face="courier" size="4">create user njohn with password ='Password1' </font></div><div><font face="courier" size="4">--Connect to DB2 </font></div><div><span style="font-family: courier; font-size: large;">create user njohn with password ='Password2'</span></div></div><div><font face="arial" size="4"><br /></font></div><div><font face="arial" size="4"><br /></font></div><div><font face="arial" size="4">When you create the SQL server, by default a virtual master database will be created. This is the only system database that you can see in the Azure SQL server. If you look into this database, you can see two additional database role added with Azure SQL server :</font></div><div><font face="arial" size="4"><br /></font></div><div><font face="arial" size="4"><b><u>dbmanager</u></b>: Members of this database role can create and delete the databases. When a member of the dbmanager role creates the database, becomes the owner of the database, and will have full access to that database as dbo. The members of this role will not have access to the database which is not created by them (owner) unless you explicitly created users under the database for those logins like we do in the on-premise installation. This role exists only in the master database and only the users of the master database can be added into this role. The ideal method is, create a login and create a user for that login and add that user to the dbmanager role. Contained database users in the master database also can be added to the dbmanger role, but they will not have permission to create new databases.</font></div><div><font face="arial" size="4"> </font></div><div><font face="arial" size="4"><b><u>loginmanager:</u> </b>can create and delete logins in the master database. Note that, members of this role does not have the permission to create users in the master database. </font></div><div><font face="arial" size="4"><br /></font></div><div><font face="arial" size="4">As mentioned earlier, the Azure SQL server is a logical construct and does not represent a specific VM or hardware like on-premise installation. The databases associated with an Azure SQL server, maybe running in different VMs which is not visible for us. In our on-premise installation, the SQL server is installed on VM and all databases created in that database server will be running on the same VM. Whereas Azure SQL server is a logical server and can deploy the databases under that server in multiple VMs. The below diagram helps you to understand the concept in a better way.</font></div><div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh0-isEJsFQ-gsItE6cGQmjdyFJoZVe41Deo5rlP0AN8fY167sgZcJlooipqGoKhs-yYOxJK-zxshglpUOVrzj0C5GphgwRGbmmlRqQALokshkxZw9rRSKvj31TxTy6Rl1C3dG3OaltnPt7/" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="431" data-original-width="776" height="356" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh0-isEJsFQ-gsItE6cGQmjdyFJoZVe41Deo5rlP0AN8fY167sgZcJlooipqGoKhs-yYOxJK-zxshglpUOVrzj0C5GphgwRGbmmlRqQALokshkxZw9rRSKvj31TxTy6Rl1C3dG3OaltnPt7/w640-h356/Azure+SQL+server.jpg" width="640" /></a></div><font face="arial" size="4"><br /></font></div><div><font face="arial" size="4"> </font></div><div><font face="arial" size="4">We have learned in our <a href="http://www.practicalsqldba.com/2020/05/stairway-to-microsoft-azure-sql.html" target="_blank">earlier post</a>, each Azure SQL database will have one or more standby replica for high availability depending on the service tier. The database name marked in red are standby replica. </font><span style="font-family: arial; font-size: large;">Azure SQL inherently multi-tenant and it needs to share its resource with multiple clients. You can see one extra database, DB5 which is not there in our on-premises diagram. When you connect to Azure SQL server, "mysqlserver.database.windows.net", based on the database need to be connected, it will redirect the connection to data node hold the primary replica. This is the reason why we need to specify the database name while connecting through SSMS and not allowing us to change the database context using the USE statement. To change the context of the database, it needs to reroute the traffic to different data nodes. If we are not specifying the database name, it will connect to the virtual master database. If the 'datanode1' goes down for some reason, DB1, and DB2 comes online as the primary in Datanode2 and standby replica will get created in another data node. During this time, the connection to these two databases will be broken and the user will get an error message. To overcome these transient errors and to give a smooth experience to the application users, Microsoft suggests to implement <a href="https://docs.microsoft.com/en-us/azure/azure-sql/database/troubleshoot-common-errors-issues" target="_blank">retry mechanism</a> for application connecting to Azure SQL. This is also beneficial </span><span style="font-family: arial; font-size: large;">to </span></div><div><ul><li><span style="font-family: arial; font-size: large;">Handle the errors happens while azure perform the maintenance or patch update. Azure SQL Database is a managed service and Azure performs the regular patch and version update. User does not have control over the date and time of these maintenance windows.</span></li><li><span style="font-family: arial; font-size: large;">When we scale up the SQL database, there is a possibility to failover the database to another node due to a lack of resources in the current node. In that scenario, the existing connection will be terminated.</span></li></ul></div><div><font face="arial" size="4"><br /></font></div><div><font face="arial" size="4">In our next post, we will discuss about connection architecture and different settings available under the "firewall and virtual network"</font></div><div><font face="arial" size="4"><br /></font></div><div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjVRgImm9oksSjESx9JFTyc1SsXO9yj1ZhszJk-UoVSsZR7VnZFTjXn5hjDk0PPj1tS0sSfYusfrMuifBup1OSUjouFzxPqT0-sLHKCxQqcfqbzFd74yZ397OBhPrP9OVwBX2ZM3sFAquhW/" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="499" data-original-width="991" height="101" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjVRgImm9oksSjESx9JFTyc1SsXO9yj1ZhszJk-UoVSsZR7VnZFTjXn5hjDk0PPj1tS0sSfYusfrMuifBup1OSUjouFzxPqT0-sLHKCxQqcfqbzFd74yZ397OBhPrP9OVwBX2ZM3sFAquhW/w200-h101/azure_sql_server_blog_heading.png" width="200" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><font face="arial" size="4"><br /></font></div><div><font face="arial" size="4"><br /></font></div><div><br /></div></div><div class="blogger-post-footer">www.PracticalSqlDba.com</div>Nelsonhttp://www.blogger.com/profile/02156244559143388581noreply@blogger.com11tag:blogger.com,1999:blog-6097409806591916698.post-71513789675888050292020-05-25T04:41:00.008-07:002020-05-25T04:57:15.975-07:00Ghost is taking full backup and breaking my backup chain . Be careful with Azure VM backup on VM having SQL server installation<img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4QLbwXYpevRlVNuz1j5ulZE4cJa1ECAn5kLDRfWgFTTPmXfeJRYDJU_X-6IJWzvSaYNMv51UIaQcueTVBYkbs_hlhcvwjrpEHrvF5kcbx0xkQNx4cU25rRKU76c5JmfMliZK-c-VsN6SP/s320/Azure_VM_backup.png" style="display: none;" />
<div style="text-align: justify;"><font face="arial" size="4"><span style="font-family: verdana; font-size: large;">I believe that I have strong knowledge in SQL Server backup and recovery process, but recently there was an incident which shook my confidence. I was working on a migration for a noncritical production server from SQL server </span><span style="font-family: verdana; font-size: large;">2008 to 2017. As we need to upgrade the OS also, we decided to go ahead with the side by side migration. The backup plan on the existing server</span><span style="font-family: verdana; font-size: large;"> was full backup on a weekly basis. So I decided to restore the previous week's full backup well in advance and during the migration window to take a differential backup and apply that on the new server and I communicated this process will take approximately 15 minutes. As I was pretty confident in this process, I didn't put any effort to do a dry run prior to the migration day and everything set for migration.</span></font></div><div style="text-align: justify;"><span style="font-family: verdana; font-size: large;"><font face="arial" size="4"><br /></font></span></div><div style="text-align: justify;"><span style="font-family: verdana; font-size: large;"><font face="arial" size="4">During the migration window, as a first step, I fired the command to take differential backup on the existing server. To my surprise, it throws the following error:</font></span></div><div style="text-align: justify;"><span style="font-family: verdana; font-size: large;"><font color="#d52c1f"><br /></font></span></div><div style="text-align: justify;"><font color="#d52c1f"><span style="background-color: white; font-family: "courier new", courier, monospace; font-size: 14px;">Msg 3035, Level 16, State 1, Line 1</span><br style="background-color: white; box-sizing: border-box; font-family: "courier new", courier, monospace; font-size: 14px;" /><span style="background-color: white; font-family: "courier new", courier, monospace; font-size: 14px;">Cannot perform a differential backup for database “Mydatabase”, because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE</span></font></div><div style="text-align: justify;"><font color="#d52c1f"><span style="background-color: white; font-family: "courier new", courier, monospace; font-size: 14px;"><br /></span></font></div><div style="text-align: justify;"><font face="arial" size="4"><span style="font-family: verdana; font-size: large;">I was not able to believe this. Immediately I queried the backup set table and I saw multiple full backup records after the previous week full backup. I ran a set of queries and it revealed that some backup operation is happening without DBA's knowledge to a virtual device at a regular interval. As per the <a href="https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/backupmediafamily-transact-sql?view=sql-server-ver15" target="_blank">Microsoft documentation</a>, Device_type 7 is a virtual device.</span><span style="font-family: verdana; font-size: large;"> </span></font></div><div style="text-align: justify;"><span style="font-family: verdana; font-size: large;"><br /></span></div><div style="text-align: justify;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi6qDp7H8_18ktRodQpSCVrHH1zpCzFBx8UlEPTmoq7ehZezrmBSHiSH3YwwelvEV8p1B8o-3_GXHIvRB5aqmtNUMR-HZfpFXRH-z_4zL8IgkXdGBe0au3lPq93TVoi0T0rr8eXqFXNyTmY/" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="448" data-original-width="1194" height="240" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi6qDp7H8_18ktRodQpSCVrHH1zpCzFBx8UlEPTmoq7ehZezrmBSHiSH3YwwelvEV8p1B8o-3_GXHIvRB5aqmtNUMR-HZfpFXRH-z_4zL8IgkXdGBe0au3lPq93TVoi0T0rr8eXqFXNyTmY/w640-h240/Backup_info.png" width="640" /></a></div><span style="font-family: verdana; font-size: large;"><br /></span></div><div style="text-align: justify;"><font face="verdana" size="4"><br /></font></div><div style="text-align: justify;"><font face="verdana" size="4"><br /></font></div><div style="text-align: justify;"><font face="verdana" size="4"><br /></font></div><div style="text-align: justify;"><font face="verdana" size="4"><br /></font></div><div style="text-align: justify;"><font face="verdana" size="4"><br /></font></div><div style="text-align: justify;"><font face="verdana" size="4"><br /></font></div><div style="text-align: justify;"><font face="verdana" size="4"><br /></font></div><div style="text-align: justify;"><font face="verdana" size="4"><br /></font></div><div style="text-align: justify;"><font face="verdana" size="4"><br /></font></div><div style="text-align: justify;"><font face="verdana" size="4"><br /></font></div><div style="text-align: justify;"><font face="verdana" size="4"><br /></font></div><div style="text-align: justify;"><font face="arial" size="4">I don't have time to dig further, I continued the migration by issuing full backup and restoring the same. This delayed the entire migration process by 3-4 hours and it was an embarrassing situation. </font></div><div style="text-align: justify;"><font face="arial" size="4"><br /></font></div><div style="text-align: justify;"><font face="arial" size="4">After the migration, I decided to figure out the issue and went ahead and searched the SQL error log and I found the following messages along with the backup message.</font></div><div style="text-align: justify;"><font face="verdana" size="4"><br /></font></div><div style="text-align: justify;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-GfrMGVfzau5O4-Dde10ZKLw5tLibcob7q-xR_1tOGvb9nWRa5GMnY7JLwnomYp9pvbKqYZVwJsji5yctacpDQHNcfW_pd8PISmUU5w-yCcGGE99GjinY0hv1abhQQYpjhsrJMjFw_Ezp/" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="169" data-original-width="1807" height="60" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-GfrMGVfzau5O4-Dde10ZKLw5tLibcob7q-xR_1tOGvb9nWRa5GMnY7JLwnomYp9pvbKqYZVwJsji5yctacpDQHNcfW_pd8PISmUU5w-yCcGGE99GjinY0hv1abhQQYpjhsrJMjFw_Ezp/w640-h60/IO_frozen.png" width="640" /></a></div></div><div style="text-align: justify;"><font face="courier" size="4"><div style="text-align: justify;"><ul><li><font size="4"><div style="text-align: justify;">I/O is frozen on database Mydatabase. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup</div></font></li><li><font size="4"><div style="text-align: justify;">I/O was resumed on database Mydatabase. No user action is required.</div></font></li><li><font size="4"><div style="text-align: justify;">Database backed up. Database: Mydatabase, creation date(time): 2012/02/02(09:42:18), pages dumped: 578, first LSN: 528:626:37, last LSN: 528:643:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{4618D52D-9C83-400A-A698-AB391E30369A}26'}).This is an informational message only. No user action is required.<span style="white-space: pre;"> </span></div></font></li></ul></div></font></div><div style="text-align: justify;"><font face="arial" size="4"><span style="font-family: arial;">The I/O frozen error was just before the backup and that triggered me to think about disk snapshot backups running behind the scene and contacted the infra team to understand this. On contacting them, they told me that, they have enabled the Azur VM backup on all VM on a daily basis and the Azure VM backup schedule on the server in question was matching with the above error message. With that, we were sure that the azure VM is the culprit behind this ghost SQL server backup. On reading further on azure VM backup, we learned that Azure VM backup uses the VSS (Volume Shadow copy) service to take the backup and SQL server is a VSS aware application and it takes a full backup of SQL Server databases using SQL writer service. Before taking backup, it will freeze the disk operation to get a consistent backup.</span><span style="font-family: arial; font-size: large;"> </span></font></div><div style="text-align: justify;"><font face="arial" size="4"><b><br /></b></font></div><div style="text-align: justify;"><font face="arial" size="4"><b>What is VSS backup? </b></font></div><div style="text-align: justify;"><font face="arial" size="4"><span style="background-color: white; text-align: left;">Volume Shadow Copy is a technology included in Microsoft Windows that can create backup copies or snapshots of computer files or volumes, even when they are in use. It is implemented as a Windows service called the Volume Shadow Copy service. It </span><span style="text-align: left;">allows backup applications to safely back up locked and open files</span><span style="color: #4e6973; text-align: left;">.</span><span style="color: #4e6973; text-align: left;"> </span></font></div><div style="text-align: justify;"><font face="arial" size="4"><span style="color: #4e6973; text-align: left;"><br /></span></font></div><div style="text-align: justify;"><font face="arial" size="4"><span style="text-align: left;"><b>Components of VSS:</b></span></font></div><div style="text-align: justify;"><font face="arial" size="4"><span style="text-align: left;"><b><br /></b></span></font></div><div style="text-align: justify;"><ul><li><font face="arial" size="4"><span style="background-color: white; box-sizing: inherit; color: #171717; font-weight: bolder; outline-color: inherit; text-align: start;">VSS service</span><span style="background-color: white; color: #171717; text-align: start;">: Part of windows operating system and installed as by default. This Orichistate the communication between the backup tool and VSS aware applications.</span></font></li></ul></div><div style="text-align: justify;"><ul><li><font face="arial" size="4"><span style="background-color: white; box-sizing: inherit; font-weight: bolder; outline-color: inherit; text-align: start;">VSS requester</span><span style="background-color: white; text-align: start;">: The application who initiate the backup request to the VSS service. This can be Microsoft or non-microsof backup applications such as Windows server backup utility, any third party VM backup tool, etc.</span></font></li></ul></div><div style="text-align: justify;"><ul><li><font face="arial" size="4"><span style="background-color: white; box-sizing: inherit; font-weight: bolder; outline-color: inherit; text-align: left;">VSS writer</span><span style="background-color: white; text-align: left;"> This is the component that guarantees a consistent data set to back up. For example, Microsoft provides the VSS writers for SQL server, Exchange server, windows registry, etc. Many non-Microsoft </span><span style="background-color: white; text-align: left;">applications also provide VSS components that need to guarantee data consistency during back up.</span><span style="background-color: white; text-align: start;"> </span></font></li></ul></div><div style="text-align: justify;"><span style="background-color: white; font-family: verdana; font-size: large; text-align: left;"><font face="arial" size="4">Finally, we understood what was happening behind the scene and decided to stop the backup operation initiated by the Azure VM backup as DBA's are responsible for SQL server backup and recovery. We can not stop the Azure VM backup as the infra team need this for recovering the instance in case of disaster. So we found two solutions.</font></span></div><div style="text-align: justify;"><ul><li><span style="background-color: white; text-align: left;"><font face="arial" size="4">Stop and disable the SQL writer service. With this VSS will not able to take the SQL server database backup but still takes the VM backup.</font></span></li><li><font face="arial" size="4"><span style="background-color: white; text-align: left;">Add below registry key</span> [ HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\BCDRAGENT] <br /> "USEVSSCOPYBACKUP"="TRUE" This will force the VSS to take COPY_ONLY backup which will not break the backup chain </font></li></ul><font face="arial" size="4"> </font></div><div style="text-align: justify;"><span style="background-color: white; font-family: verdana; font-size: large; text-align: left;"><font face="arial" size="4">I hope this piece of information will help you one day. If you are more curious about VSS service, refer the below links:</font></span></div><div style="text-align: justify;"><ul><li><span style="background-color: white; font-family: verdana; font-size: large; text-align: left;"><font face="arial" size="4"><a href="https://docs.microsoft.com/en-us/windows-server/storage/file-server/volume-shadow-copy-service" target="_blank">Microsoft Volume Shadow Copy (VSS) services</a> </font></span></li><li><span style="background-color: white; font-family: verdana; font-size: large; text-align: left;"><a href="https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-vss-writer-backup-guide?view=sql-server-ver15" target="_blank"><font face="arial" size="4">SQL writer for VSS</font></a></span></li></ul></div><div style="text-align: justify;"><font face="verdana" size="4"><br /></font></div><div style="text-align: justify;"><font face="verdana" size="4"> </font></div><div style="text-align: justify;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4QLbwXYpevRlVNuz1j5ulZE4cJa1ECAn5kLDRfWgFTTPmXfeJRYDJU_X-6IJWzvSaYNMv51UIaQcueTVBYkbs_hlhcvwjrpEHrvF5kcbx0xkQNx4cU25rRKU76c5JmfMliZK-c-VsN6SP/" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="525" data-original-width="1167" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4QLbwXYpevRlVNuz1j5ulZE4cJa1ECAn5kLDRfWgFTTPmXfeJRYDJU_X-6IJWzvSaYNMv51UIaQcueTVBYkbs_hlhcvwjrpEHrvF5kcbx0xkQNx4cU25rRKU76c5JmfMliZK-c-VsN6SP/s320/Azure_VM_backup.png" width="320" /></a></div><font face="verdana" size="4"><br /></font></div><div style="text-align: justify;"><font face="verdana" size="4"><br /></font></div><div style="text-align: justify;"><font face="verdana" size="4"><br /></font></div><div style="text-align: justify;"><font face="verdana" size="4"><br /></font></div><div style="text-align: justify;"><font face="verdana" size="4"><br /></font></div><div style="text-align: justify;"><font face="verdana" size="4"><br /></font></div><div class="blogger-post-footer">www.PracticalSqlDba.com</div>Nelsonhttp://www.blogger.com/profile/02156244559143388581noreply@blogger.com8tag:blogger.com,1999:blog-6097409806591916698.post-77103399565419947322020-05-17T06:46:00.024-07:002020-05-17T20:14:27.168-07:00Stairway to Microsoft Azure SQL Database Part 3 : Purchase Models DTU vs vCore continue <div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;"><span style="font-family: arial; font-size: large;">In the </span><a href="http://www.practicalsqldba.com/2020/05/stairway-to-microsoft-azure-sql_3.html" style="font-family: arial; font-size: large;" target="_blank">last post</a><span style="font-family: arial; font-size: large;">, we have discussed the DTU purchase model. Azure also provides another purchase mode which is called vCore. vCore purchase model available with all three deployment models (Managed instance, Single database, and elastic pool). </span></div>
<img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgEMODl95OXeghBTs3UR8DbycS4TJi6V1A2tvLG1kB5MvqYqnQJD9t5TK0L7Bee1Hk6uOyY35QionwY918ZhMFO4w6GCh-nmOlGwcsILYWDX08xrtUOl-MncYfxakcdW_Q2335FPqgvLJuj/w200-h83/blog_title.png" style="display: none;" />
<div style="text-align: justify;">
<span style="font-family: arial, helvetica, sans-serif; font-size: large;"><font face="arial" size="4"><br /></font></span></div>
<div style="text-align: justify;">
<span style="font-family: arial, helvetica, sans-serif; font-size: large;"><font face="arial" size="4">vCore purchase model provides :</font></span></div>
<div style="text-align: justify;">
</div>
<ul>
<li><span style="font-family: arial, helvetica, sans-serif; font-size: large;"><font face="arial" size="4">Higher compute, memory, IO, and storage limit. </font></span></li>
<li><span style="font-family: arial, helvetica, sans-serif; font-size: large;"><font face="arial" size="4">Better control over opting for the hardware configuration.</font></span></li>
<li><span style="font-family: arial, helvetica, sans-serif; font-size: large;"><font face="arial" size="4">Option to bring your own license using the Azure Hybrid Benefit(AHB) and to reserve the instance to reduce the cost.</font></span></li>
<li><span style="font-family: arial, helvetica, sans-serif; font-size: large;"><font face="arial" size="4">Flexibility to choose the compute and storage independently.</font></span></li><li style="text-align: justify;"><font face="arial, helvetica, sans-serif" size="4">Storage and compute charged separately. In the DTU model ,limited amount od storage bundled with the computing power. Storage allocated above that is charged separately.</font></li>
</ul>
<div style="text-align: justify;">
<span style="font-family: arial, helvetica, sans-serif; font-size: large;"><font face="arial" size="4"><br /></font></span></div>
<div style="text-align: justify;">
<span style="font-family: arial, helvetica, sans-serif; font-size: large;"><font face="arial" size="4"><b><u>Service Tier</u>: </b>Like in the DTU model, the vCore model also provides multiple service tier to match with your performance requirements and budget. vCore purchase model provides the following service tiers.</font></span></div><div><ul style="text-align: left;"><li><span style="font-family: arial, helvetica, sans-serif; font-size: large;"><font face="arial" size="4">General Purpose </font></span></li><li><span style="font-family: arial, helvetica, sans-serif; font-size: large;"><font face="arial" size="4">Hyper-Scale</font></span></li><li><span style="font-family: arial, helvetica, sans-serif; font-size: large;"><font face="arial" size="4">Business Critical</font></span></li></ul><div><font face="arial, helvetica, sans-serif" size="4"><b>General Purpose :</b></font></div></div><div><ul style="text-align: left;"><li><font face="arial, helvetica, sans-serif" size="4">Offer Budget-oriented, balanced and scalable compute and storage option.</font></li><li><font face="arial, helvetica, sans-serif" size="4">Suited for general workload.</font></li><li><font face="arial, helvetica, sans-serif" size="4">Available with all three deployment model (Single, elastic and managed)</font></li><li><font face="arial, helvetica, sans-serif" size="4">Option to select 2 to 80 vCore. The options are 2 ,4 ,6 ,8 ,10,12,14,16,18, 20,24,32,40 and 80 vCore</font></li><li><font face="arial, helvetica, sans-serif" size="4">Storage upto 4 TB (0.12 USD per GB). </font></li><li><font face="arial, helvetica, sans-serif" size="4">Remote storage </font></li><li><font face="arial, helvetica, sans-serif" size="4">500 to 20000 IOPS</font></li><li><font face="arial, helvetica, sans-serif" size="4">2 to 10 ms latency </font></li><li><font face="arial, helvetica, sans-serif" size="4">For high availability, it provides one replica.</font></li><li><font face="arial, helvetica, sans-serif" size="4">No read replica.</font></li><li><font face="arial, helvetica, sans-serif" size="4">7 to 35 Backup retention </font></li></ul><div><font face="arial, helvetica, sans-serif" size="4"><b>Hyperscale :</b></font></div></div><div><ul style="text-align: left;"><li><font face="arial, helvetica, sans-serif" size="4">Available only for single database deployment model</font></li><li><font face="arial, helvetica, sans-serif" size="4">Provide highly scalable storage up to 100TB </font></li><li><font face="arial, helvetica, sans-serif" size="4">For rapid read scale-out deployment, provide up to 4 read replica </font></li><li><font face="arial, helvetica, sans-serif" size="4">Read replicas are charged.</font></li><li><font face="arial, helvetica, sans-serif" size="4">Option to select compute power from 2 to 80 vCore (options are the same as in general-purpose) </font></li><li><font face="arial, helvetica, sans-serif" size="4">Use local SSD storage.</font></li><li><font face="arial, helvetica, sans-serif" size="4">Snapshot based backup in Azure remote storage. Restore use these snapshots for fast recovery. </font></li><li><font face="arial, helvetica, sans-serif" size="4">Backup/restore does not depend on the size of the database and does not affect the IO or compute as it is performed using the snapshot. Hence backup and restorations are instantaneous.</font></li><li><font face="arial, helvetica, sans-serif" size="4">For example. 50 TB database can be restored in less than 15 minutes. You can watch the video <a href="https://www.facebook.com/watch/?v=475388336365858">here </a></font></li><li><font face="arial" size="4">Depending on the workloads, IOPS between 500 to 204800 with 1 to 10 ms latency.</font></li><li><font face="arial" size="4">Most suited for databases that will not fit into the 4TB storage limit of general-purpose and business-critical service tier.</font></li><li><font face="arial" size="4"> Hyperscale database engines use distributed function architecture and use the following components (we will discuss the architecture of hyper-scale in a different blog post). This architecture is different from the existing database engine architecture </font></li><ul><li><font face="arial" size="4">Compute </font></li><li><font face="arial" size="4">Page Server</font></li><li><font face="arial" size="4">Log Service </font></li><li><font face="arial" size="4">Aure storage </font></li></ul></ul><div><b style="font-size: large;">Business Critical :</b></div></div><div><ul style="text-align: left;"><li><font face="arial, helvetica, sans-serif" size="4">Available with all three deployment model (Single, elastic and managed)</font></li><li><font face="arial, helvetica, sans-serif" size="4">Option to select 2 to 80 vCore. The options are 2 ,4 ,6 ,8 ,10,12,14,16,18, 20,24,32,40 and 80 vCore</font></li><li><span style="background-color: white; color: #171717;"><font face="arial" size="4">Offers business applications the highest resilience to failures by using several isolated replicas.</font></span></li><li><font color="#171717" face="arial" size="4"><span style="background-color: white;">It keeps 4 replicas for better high availability </span></font></li><li><font color="#171717" face="arial" size="4"><span style="background-color: white;">To offload the read workload from the writable replica, provide read replica without additional charge.</span></font></li><li><font color="#171717" face="arial" size="4"><span style="background-color: white;">Use local SSD storage for better IO performance.</span></font></li><li><font color="#171717" face="arial" size="4"><span style="background-color: white;">Provides up to 4 TB storage with 1 -2 ms latency (0.25 USD per GB)</span></font></li><li><font face="arial"><font color="#171717" size="4"><span style="background-color: white;">5000 to </span></font><font size="4">204800 IOPS</font></font></li><li><font face="arial" size="4">7 to 35 days backup retention.</font></li></ul><div><br /></div></div><div style="text-align: justify;"><font face="arial" size="4"><b><u>Compute Tier</u>: </b>Under the service tier, azure SQL server provides the compute tier. For managed instance and elastic pool deployment models, azure provides only provisioned compute tier and this option will not be available to choose. For the single database deployment, there is one more additional compute tier called Serverless. Let us see the difference between these two compute tier:</font></div><div style="text-align: justify;"><font face="arial" size="4"><br /></font></div><div style="text-align: justify;"><font face="arial" size="4"><b>Provisioned : </b></font></div><div style="text-align: justify;"><ul><li><font face="arial" size="4">compute tier is provisioned through out the lifecycle of elastic poo/ single database/managed instance and billed hourly basis.</font></li><li><font face="arial" size="4">There is no change in the billing based on the workload on the database.</font></li><li><font face="arial" size="4">Suitable for regular usage patterns with higher average compute utilization over time.</font></li><li><font face="arial" size="4">Compute scaling should be done manually (increasing the vCore)</font></li></ul><b style="font-family: arial; font-size: large;">Serverless :</b></div><div style="text-align: justify;"><ul><li><font face="arial" size="4">Suitable for Intermittent, unpredictable usage with lower average compute utilization over time.</font></li><li><font face="arial" size="4">Automatically scale up the compute based on the workload and billed per second.</font></li><li><font face="arial" size="4">Automatically pause the database if there is no activity for a preconfigured time (Auto pause delay) and start automatically when the wake-up events happen. During this period compute is not charged.</font></li><li><font face="arial" size="4">While provisioning, we need to define the minimum and maximum vCore. </font></li><li><font face="arial" size="4">Based on the workload, the compute tier is scaled up to the max vCore defined. Compute is billed per second.</font></li><li><font face="arial" size="4">Auto pause delay is a configurable parameter that defines the required inactive period before pausing the database. This can vary from 1 hour to 7 days. For example, if we define the auto-pause delay as 2 hrs, then if there is no activity in the database for 2 hours, then the database will go to pause state and will not be charged for computing until the next wake up event. Till that point, only the storage cost will be charged.</font></li><li><font face="arial" size="4">The minimum and maximum vCore are configurable parameters that define the range of computing power available for the database. The maximum vCore can vary between 1 to 16 vCore. (1,2,4,6,8.1,10,12,14 and 16 are possible options) . The minimum vCore can vary from 0.5 vCPU to maximum vCore configured. Each vCpu provides 3GB of memory. If the workload requires memory more than that will be charged.</font></li><li><font face="arial" size="4">Cost</font></li><ul><li><font face="arial" size="4">Compute cost and storage costs are charged separately. Storage cost remains the same throughout the life cycle unless you scale up the storage.</font></li><li><font face="arial" size="4">When the compute usage is between the min vCore and max vCore configured, compute cost is based on vCore or memory.</font></li><li><font face="arial" size="4">When the compute usage is below the min vCore setting, it will be still charged for min vCpu/ Memory.</font></li><li><font face="arial" size="4">For the duration of the paused state, Computing is not charged. Only storage will be charged.</font></li></ul><li><font face="arial" size="4">Auto pause and auto-scaling</font></li><ul><li><font face="arial" size="4">When the database is in a paused state, the first wake up event (example connection request) fails with the error message "database is unavailable" with error code 40613. It will take approximately one minute to resume the operation. Applications that are working with a serverless database should have mechanisms to handle this error message and retry the connection after a minute.</font></li><li><font face="arial" size="4">When the load on the database increases, azure automatically scales up the compute / memory up to the max vCore configured. while scaling up the compute, at the end of the process all connections to the database are dropped. The applications which use the serverless database should have a mechanism to handle these error and try to reconnect after a specific time to provide smooth user experience to the end-users. </font></li></ul><li><font face="arial" size="4">Billing </font></li><ul><li><font face="arial" size="4">Use per second billing.</font></li><li><font face="arial" size="4">Each vCore in action provides 3GB memory. If the workload uses more memory than the memory bundled with the vCore, billing will be based on the memory. For example, for a specific time interval, the load on the database is using 2 vCore and 10 GB memory. In this case, billing will be based on the memory as the memory utilization is above 6 GB (2vCore*3 GB). To derive the number vCore to be billed from the memory utilization, memory utilized will be divided by 3 (3GB for each Vcore). In our case, it will be billed for 10GB/3 = 3.33 vCore. The below table gives you a better understanding of the billing. Let us consider one database, that is provisioned with 3 vCore as minimum vCore and 16 as maximum vCore.The auto pause delay is configured as 2 hrs. Each vCore seconds is charged at </font><font face="arial" size="4">0.000145<span style="background-color: white; font-weight: 600; text-align: start;"> </span></font></li></ul></ul><font face="arial" size="4"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPXZwh6mq57gFYvhrPhuXh-xrv6jw3DMl_6lQ2ZqIheMTeawk3RapPQZ8imlHhYWdhdNMzsVed2TzLhqXC9jFADhGmgNjKAZdHyLaowLKxJBCieXdX-WdcQMXm3K-Ybf3kudYMr6yAKMlU/" style="margin-left: 1em; margin-right: 1em;"><img alt="Serverless Billing calculation" border="0" data-original-height="665" data-original-width="1181" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPXZwh6mq57gFYvhrPhuXh-xrv6jw3DMl_6lQ2ZqIheMTeawk3RapPQZ8imlHhYWdhdNMzsVed2TzLhqXC9jFADhGmgNjKAZdHyLaowLKxJBCieXdX-WdcQMXm3K-Ybf3kudYMr6yAKMlU/d/Serverless_billing.png" title="Serverless Billing" /></a></div><b><br /></b></font></div><div style="text-align: justify;"><br /></div><div style="text-align: justify;"><span style="font-family: arial; font-size: large;">The total vCore seconds are 389952 and it will cost 56.54 USD (0.000145 USD * 389952 vCore seconds) for the workload mentioned in the above table which spread across 24hrs.</span></div><div style="text-align: justify;"><span style="font-family: arial; font-size: large;"><br /></span></div><div style="text-align: justify;"><ul><li><span style="font-family: arial; font-size: large;">Best suited for :</span></li><ul><li><span style="font-family: arial; font-size: large;">Single database with intermittent and unpredictable usage patterns.</span></li><li><span style="font-family: arial; font-size: large;">The database workload is only during specific intervals. So after the auto-pause delay, the database enters into stop state and not charged for the compute.</span></li><li><span style="font-family: arial; font-size: large;">Workload is not sensitive to the response time and the application is capable fo handling the errors while resuming the service and auto-scaling</span></li></ul></ul><div><font face="arial" size="4">Apart from connecting to the database, there are many other events that resume the database from a paused state. You can find those events in the Azure <a href="https://docs.microsoft.com/en-us/azure/sql-database/sql-database-serverless">documentation page </a></font></div></div></div><div dir="ltr" style="text-align: left;" trbidi="on"><div style="text-align: justify;"><span style="font-family: arial; font-size: large;"><br /></span></div><div style="text-align: justify;"><span style="font-family: arial; font-size: large;"><br /></span></div><div style="text-align: justify;"><span style="font-family: arial; font-size: large;"><b><u>Hardware Configuration</u> </b>Azure provides multiple hardware options to choose from under the service tier. These options differ from region to region. Commonly available hardware options are </span></div><div style="text-align: justify;"><span style="font-family: arial; font-size: large;"><br /></span></div><div style="text-align: justify;"><ul><li><span style="font-family: arial; font-size: large;">Gen4 </span></li><ul><li><span style="font-family: arial; font-size: large;">Provide 7 GB memory for each vCore.</span></li><li><span style="font-family: arial; font-size: large;">Approaching the end of life by Jan 2023</span></li></ul><li><font face="arial" size="4">Gen5 </font></li><ul><li><font face="arial" size="4">Has new generation hardware</font></li><li><font face="arial" size="4">Has network acceleration and provide better IO performance on remote storage (General purpose service tier)</font></li><li><font face="arial" size="4">It uses faster local SSD disk than Gen4</font></li></ul><li><font face="arial" size="4">M-Series (preview)</font></li><ul><li><font face="arial" size="4">New Memory-optimized hardware </font></li><li><font face="arial" size="4">Provide 29GB per vCore.</font></li><li><font face="arial" size="4">It provides up to 128 vCore.</font></li><li><font face="arial" size="4">In full capacity memory can be nearly 4 TB (128 vCore X 29 GB per vCore).</font></li></ul><li><font face="arial" size="4"><span style="background-color: white; box-sizing: border-box; color: #333333; text-align: left;">Fsv2-series (Preview)</span></font></li><ul><li><font face="arial" size="4"><span style="background-color: white; box-sizing: border-box; color: #333333; text-align: left;">Compute-optimized hardware.</span></font></li><li><font face="arial" size="4"><span style="background-color: white; box-sizing: border-box; color: #333333; text-align: left;">Low CPU latency and high clock speed for the most CPU demanding workloads.</span></font></li><li><font face="arial" size="4"><span style="background-color: white; box-sizing: border-box; color: #333333; text-align: left;">Can deliver more CPU performance per vCore than Gen5.</span></font></li><li><font face="arial" size="4"><span style="background-color: white; box-sizing: border-box; color: #333333; text-align: left;">The 72 vCore size can also provide more CPU performance for less cost than 80 vCores on Gen5.</span></font></li><li><font face="arial" size="4"><span style="background-color: white; box-sizing: border-box; color: #333333; text-align: left;">provides less memory and tempdb size per vCore than other hardware. so workloads sensitive to those limits may want to consider Gen5 or M-series.</span></font></li></ul></ul></div><div style="text-align: justify;"><br /></div><div style="text-align: justify;"><span style="background-color: white; color: #333333; font-family: arial; font-size: large; text-align: left;">The below diagram provides you a visual representation of vCore purchase model options for Single database and Elastic pool.</span></div><div style="text-align: justify;"><br /></div><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto;"><tbody><tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi912wkn-MrNbPWppHF5dLZryoJHGCMW3uF-4YUf63kjZXhSc_RiDmzIxV9tmwD_eZEkQ3kPYbRkddt53IHxu0j0sO7sZfg-Xf_PdhokpjtDp_xE9QOm9ei-owtKlIM0-AxOjsWtIfTYm0g/" style="margin-left: auto; margin-right: auto;"><img alt="vCore purchase model" border="0" data-original-height="791" data-original-width="762" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi912wkn-MrNbPWppHF5dLZryoJHGCMW3uF-4YUf63kjZXhSc_RiDmzIxV9tmwD_eZEkQ3kPYbRkddt53IHxu0j0sO7sZfg-Xf_PdhokpjtDp_xE9QOm9ei-owtKlIM0-AxOjsWtIfTYm0g/d/azure+SQL+purchase+option+_vCore.png" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;"><br />vCore Purchase Model</td></tr></tbody></table><div style="text-align: justify;"><br /></div><div style="text-align: justify;">
<span style="font-family: arial, helvetica, sans-serif; font-size: large;"><br />Managed Instance deployment model also provides General Purpose and Business Critical service tier. Under the service tier, it provides Gen4 and Gen5 hardware. </span></div><div style="text-align: justify;"><span style="font-family: arial, helvetica, sans-serif; font-size: large;"><br /></span></div><div style="text-align: justify;"><span style="font-family: arial, helvetica, sans-serif; font-size: large;">Each combination of these options has many other characteristics like tempdb space allocated, log space allocated, IOPS, no of concurrent session, log write throughput, etc. All those detailed information can be found in the following Azure documentation.</span></div><div style="text-align: justify;"><ul><li><span style="font-family: arial, helvetica, sans-serif; font-size: large;"><a href="https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-resource-limits">Managed instance resource limit </a></span></li><li><a href=" https://docs.microsoft.com/en-us/azure/sql-database/sql-database-vcore-resource-limits-single-databases"><font face="arial" size="4">Single Database instance resource limit</font></a></li><li><a href="https://docs.microsoft.com/en-us/azure/sql-database/sql-database-vcore-resource-limits-elastic-pools"><font face="arial" size="4">Elastic Pool resource limit</font></a></li></ul></div><div style="text-align: justify;"><br /></div><div style="text-align: justify;"><span style="font-family: arial, helvetica, sans-serif; font-size: large;">I hope, this post helped you understand the vCore purchase mode in detail. </span></div><div style="text-align: justify;"><span style="font-family: arial, helvetica, sans-serif; font-size: large;"><br /></span></div><div style="text-align: justify;"><div style="text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgEMODl95OXeghBTs3UR8DbycS4TJi6V1A2tvLG1kB5MvqYqnQJD9t5TK0L7Bee1Hk6uOyY35QionwY918ZhMFO4w6GCh-nmOlGwcsILYWDX08xrtUOl-MncYfxakcdW_Q2335FPqgvLJuj/" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="580" data-original-width="1393" height="83" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgEMODl95OXeghBTs3UR8DbycS4TJi6V1A2tvLG1kB5MvqYqnQJD9t5TK0L7Bee1Hk6uOyY35QionwY918ZhMFO4w6GCh-nmOlGwcsILYWDX08xrtUOl-MncYfxakcdW_Q2335FPqgvLJuj/w200-h83/blog_title.png" width="200" /></a></div><span style="font-family: arial, helvetica, sans-serif; font-size: large;"><br /></span></div><div style="text-align: justify;"><span style="font-family: arial, helvetica, sans-serif; font-size: large;"><br /></span></div>
<div style="text-align: justify;">
<br /></div>
</div>
<div class="blogger-post-footer">www.PracticalSqlDba.com</div>Nelsonhttp://www.blogger.com/profile/02156244559143388581noreply@blogger.com22tag:blogger.com,1999:blog-6097409806591916698.post-15359894217258031392020-05-03T02:51:00.004-07:002020-05-12T05:14:43.232-07:00Stairway to Microsoft Azure SQL Database Part 2 : Purchase Models DTU vs vCore<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
<span style="font-family: georgia, "times new roman", serif; font-size: large;">In the <a href="http://www.practicalsqldba.com/2020/04/stairway-to-microsoft-azure-sql.html" target="_blank">last post</a>, we have discussed about different deployment options available with Azure SQL server. I hope you got a good understanding of various deployment models. In this post let us discuss the purchase models available with each deployment model.</span></div>
<div style="text-align: justify;">
<span style="font-family: georgia, "times new roman", serif; font-size: large;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: georgia, "times new roman", serif; font-size: large;">Azure SQL database provides multiple purchase models based on the deployment model to accommodate your performance requirements and budgets. At a high level, these purchase models are divided into two:</span></div>
<ul style="text-align: left;">
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">DTU or eDTU purchase model</span></li>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">vCore purchase model</span></li>
</ul>
<div>
<span style="font-family: georgia, "times new roman", serif; font-size: large; text-align: justify;">Each of these purchase model provides multiple service tier for more customization of your performance requirements and budgets.</span></div>
<div>
<ul style="text-align: left;">
<li><span style="font-family: georgia, "times new roman", serif; font-size: large; text-align: justify;">DTU or eDTU </span></li>
<ul>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large; text-align: justify;">Basic</span></li>
<li style="text-align: justify;"><span style="font-family: georgia, "times new roman", serif; font-size: large;">Standard</span></li>
<li style="text-align: justify;"><span style="font-family: georgia, "times new roman", serif; font-size: large;">Premium </span> </li>
</ul>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">vCore </span></li>
<ul>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">General Purpose</span></li>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">Hyperscale</span></li>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">Business Critical</span></li>
</ul>
</ul>
<div>
<span style="font-family: georgia, "times new roman", serif; font-size: large;"><u><b>DTU and eDTU purchase model </b></u></span></div>
</div>
<div>
<span style="font-family: georgia, "times new roman", serif; font-size: large;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: georgia, "times new roman", serif; font-size: large;">The database transaction unit (DTU) is a specific composition of compute power, memory, storage, and IO. </span><span style="font-family: georgia, "times new roman", serif; font-size: large;">This purchase model provides us all the resources as a bundle which includes a specific amount of compute power, storage, and IO. It provides the simplicity and full control of your budget. This purchase model available with single database and elastic pool deployment model. As mentioned earlier, Azure provides multiple service tier to match our performance requirements and budget.</span></div>
<div style="text-align: justify;">
<ul>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">Basic tier: </span></li>
<ul>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">Suitable for very low CPU intensive workload. </span></li>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">Provides 5 DTU with 2GB of storage. </span></li>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">Each DTU provides 1-5 IOPS. </span></li>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">35 days backup retention. </span></li>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">Each DTU costs 1 USD per month. </span></li>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">Suitable for initial design and developments. </span></li>
</ul>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">Standard tier: </span></li>
<ul>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">Suitable for low and medium CPU intensive workload. </span></li>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">It provides a DTU range between 10 and 3000 DTU with 250 GB storage. </span></li>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">Additional storage up to 1 TB with additional cost. To go beyond 250 GB, we have to opt for 100 or more DTU. </span></li>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">35 days backup retention. </span></li>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">Each DTU provides 1-5 IOPS. </span></li>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">Each DTU costs 1.5 USD per month. </span></li>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">The range of DTU in this tier is divided into 9 compute size: S0 10 DTU), S1 20 DTU, S250 DTU,S3 100 DTU, S4 200 DTU, S6 400 DTU, S7 800 DTU, S9 1600 DTU, and S12 3000 DTU. There is no option for us to randomly select the DTU in the range of DTU.</span></li>
</ul>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">Premium Tier: </span></li>
<ul>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">Suitable for medium and high CPU and IO intensive workload. </span></li>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">Provides DTU range between 125 and 4000 with a storage of 500 maximum to 4 TB depends on DTU.</span></li>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">Each DTU unit provides 25 IOPS. That makes it suitable for IO intensive workload. </span></li>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">It also provides better IO latency (2ms for read and write) compared to basic and standard (5 ms for read and 10 ms for write).</span></li>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">35 days backup retention. </span></li>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">Support column store index and in-memory OLTP</span></li>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">DTU range divided into multiple compute sizes. p1 125 DTU,p2 250 DTU ,p4 500 DTU, p6 1000 DTU p11 1750 DTU, p15 4000 DTU </span></li><li><span style="font-family: georgia, "times new roman", serif; font-size: large;">Premium tier also supports read scale-out to load balance the read workload with no extra cost. As part of high availability architecture, each database in the premium service tier is automatically provisioned with multiple secondary replicas. These secondary replicas are provisioned with the same compute size of the primary replica. Enabling this feature, allow you to load balance your read-only workload using one of the read replica instead of read-write replica. This is very useful for read-intensive application which can segregate their read and write operations by passing the readintent=true property in the connection string. </span></li><li><span style="font-family: georgia, "times new roman", serif; font-size: large;">Premium tier also supports Zone redundancy. As part of high availability architecture, each database in the premium service tier is automatically provisioned with multiple secondary replicas in the same data center. This architecture helps to protect us from the server failure. By enabling the Zone redundant feature, the replicas are placed in different availability zones, it makes the databases resilient to the data center outage </span></li>
</ul>
</ul>
<span style="font-family: georgia, "times new roman", serif; font-size: large;">For more accurate details of each service tier, refer the following Azure documentation :</span></div>
<div style="text-align: justify;">
<ul>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;"><a href="https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers-dtu" target="_blank">Service tiers in the DTU based purchase model</a></span></li>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;"><a href="https://docs.microsoft.com/en-us/azure/sql-database/sql-database-dtu-resource-limits-single-databases#single-database-storage-sizes-and-compute-sizes" target="_blank">Resource limit for single databases using the DTU purchase model </a></span></li>
</ul>
</div>
<div style="text-align: justify;">
<span style="font-family: georgia, "times new roman", serif; font-size: large;">While using the single database deployment model, the DTU purchase model comes into the picture. In the same way, for an elastic pool deployment model, the eDTU purchase model is used. eDTU purchase model also provides different service tiers like in the DTU model but with different limits. For the details of each service tier, refer to the A<a href="https://docs.microsoft.com/en-us/azure/sql-database/sql-database-dtu-resource-limits-elastic-pools" target="_blank">zure documentation</a>. As you know, in the elastic pool deployment model, the resource is allocated at the pool level and each database shares the resource. To avoid one database consuming all the resources, there is a couple of parameter settings in the elastic pool level.</span></div>
<div style="text-align: justify;">
<ul>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;"><u>Max eDTUs per database</u>: This is the maximum number of eDTU that any database in the pool may use if available based on utilization by other databases in the pool. Max eDTU per database is not a resource guarantee for each database. if the workload on these databases are spread across time, we can over commit the max eDTU per database. For example, we have an elastic pool with 1000 eDTU and 5 databases attached to that. Out of that 2 of them are reporting data and most load comes to those two at night. The other two databases are part of the OLTP system will have varying workload but the peak will be between 10 AM and 5 PM. The fifth database is an internal database most of the load on this database will be between 9 AM and 12 PM. In this scenario, we can safely set max eDTU per database to 300 or 400 even if the total goes beyond the 1000 eDTU. </span></li>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;"><u>Min eDTUs per database: </u>This is the guaranteed minimum number of eDTU for each database in the pool. This can set to 0 and that is the default value. This value multiplied by the number of databases in the pool should not exceed the eDTU allocated to the pool. To set the value of this parameter to 100 of an elastic pool which has 20 databases, we need to allocate a minimum of 2000 eDTU to the elastic pool.</span></li>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;"><u>Max storage per database</u>: This is the maximum size of any database attached to this elastic pool. None of the databases can grow beyond this limit. Even if we set this value to a higher number which exceeds, (no.of database X Max storage per database), the total available storage space of the pool storage limit, it will never allow all database storage to grow more than available storage space fo the pool.</span></li>
</ul>
<div>
<span style="font-family: georgia, "times new roman", serif; font-size: large;"><br /></span></div>
<div>
<span style="font-family: georgia, "times new roman", serif; font-size: large;">Each service tiers in the elastic pool has a limit on the individual database resource utilization. The below table explains those limitations:</span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj_lcw2L5g_jCDMkdB4d-h76_Wde0N6y3d-00bqpzBk-DpB0vsKoYHitsb61MoBnAdIWRGFdYu9Bb6nQEaIMECgyuTPEHaol11Ht2golCX-Okl80MShSEUHJb9YRiCaOrgWJlYWwvszwSYa/s1600/Edtu.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="239" data-original-width="555" height="274" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj_lcw2L5g_jCDMkdB4d-h76_Wde0N6y3d-00bqpzBk-DpB0vsKoYHitsb61MoBnAdIWRGFdYu9Bb6nQEaIMECgyuTPEHaol11Ht2golCX-Okl80MShSEUHJb9YRiCaOrgWJlYWwvszwSYa/s640/Edtu.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div>
<br /></div>
<div>
<span style="font-family: georgia, "times new roman", serif; font-size: large;">In case if you are migrating your workload from your on-premise installation of SQL Server to Azure SQL and you are not sure how much DTU need to be allocated and which service tier to be provisioned, <a href="https://dtucalculator.azurewebsites.net/" target="_blank">DTU calculator</a> comes handy. You need to provide values of the following performance </span><span style="font-family: georgia, "times new roman", serif; font-size: large;">counters</span><span style="font-family: georgia, "times new roman", serif; font-size: large;"> :</span></div>
<div>
<div>
<ul>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">Processor - % Processor Time</span></li>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">Logical Disk - Disk Reads/sec</span></li>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">Logical Disk - Disk Writes/sec</span></li>
<li><span style="font-family: georgia, "times new roman", serif; font-size: large;">Database - Log Bytes Flushed/sec</span></li>
</ul>
<div>
<span style="font-family: georgia, "times new roman", serif; font-size: large;">I hope with this you got a good understanding of the DTU and eDTU purchase model. In the next post, we will cover the vCore purchase model.</span></div>
</div>
</div>
<div>
<br /></div>
</div>
<div style="text-align: justify;">
<div style="text-align: left;">
<br /></div>
</div>
<div style="text-align: justify;">
<span style="font-family: georgia, "times new roman", serif; font-size: large;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: georgia, "times new roman", serif; font-size: large;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: georgia, "times new roman", serif; font-size: large;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: georgia, "times new roman", serif; font-size: large;"><br /></span></div>
<div style="text-align: justify;">
<br /></div>
<div>
<span style="font-family: georgia, "times new roman", serif; font-size: large;"><br /></span></div>
<div>
<span style="font-family: georgia, "times new roman", serif; font-size: large; text-align: justify;"><br /></span></div>
</div>
<div class="blogger-post-footer">www.PracticalSqlDba.com</div>Nelsonhttp://www.blogger.com/profile/02156244559143388581noreply@blogger.com4tag:blogger.com,1999:blog-6097409806591916698.post-60292839591638996402020-04-26T07:12:00.000-07:002020-04-26T08:56:08.679-07:00Stairway to Microsoft Azure SQL Database Part 1 : Deployment Options<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Recently I started working with Azure SQL Database. It took some time for me to understand the concepts of the Azure SQL database. Earlier I have experience in AWS RDS for SQL server, but in Azure it works in a little complex way. Once you understand the different offerings, security, deployment model, etc, it is is easy to provision the resources as per the requirements.</span></div>
<div style="text-align: justify;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Through a set of articles, I will try to explain the Azure SQL database in a very concise way. All this information is available in Azure documentation. For the granular level of information, always refer to Azure documentation. </span></div>
<div style="text-align: justify;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Microsoft Azure database is a fully managed platform as a service (PaaS). Azure provides three different deployment options to cater to the different needs of the customer.</span></div>
<div style="text-align: justify;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div style="text-align: justify;">
</div>
<ul>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Managed Instance </span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Single Database </span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Elastic Pool</span></li>
</ul>
<div>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Let us look into the details of each deployment model :</span></div>
<div>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div>
<div style="text-align: justify;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>Managed Instance: </b>Managed instance is one of the most suitable deployment option for those who are moving to cloud from an on-premise server. This deployment model supports most of our on-premise database features.</span></div>
</div>
<div>
<ul>
<li style="text-align: justify;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Managed instance does not provide access to the Operating system.</span></li>
<li style="text-align: justify;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">It does not allow you to specify the version of the SQL Server. Managed instance always runs the latest stable SQL engine version, which is equal to or higher than the latest available RTM version of SQL Server. </span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Supports </span></li>
<ul>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">SQL Agent service </span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">DB mail with external SMTP server</span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Service broker</span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">SQL Agent service </span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Transactional replication</span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Change Data Capture (CDC) </span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">CLR creation from binary (Not using the assembly file)</span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">linked server </span></li>
<li><span style="background-color: white; color: #171717; text-align: left;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Managed instance supports SQL Server Integration Services (SSIS) and can host SSIS catalog (SSISDB) that stores SSIS packages, but they are executed on a managed Azure-SSIS Integration Runtime (IR) in Azure Data Factory (ADF)</span></span></li>
<li><span style="background-color: white; color: #171717; text-align: left;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">To choose the time zone</span></span></li>
</ul>
<li style="text-align: left;"><span style="color: #171717; font-family: "georgia" , "times new roman" , serif; font-size: large;">Not yet supporting the file stream or file table.</span></li>
</ul>
<span style="text-align: left;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">On top of that, managed instance support all PaaS features like :</span></span></div>
<div>
<div style="text-align: left;">
<ul style="text-align: left;">
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">No separate purchase of infrastructure and managing the underlying hardware of the database server.</span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Automated patching and version upgrade. There is no option to control the maintenance window of patching or upgrade. To handle the connection error during the maintenance window, you need to implement the retry mechanism in your code.</span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">No headache of scheduling and monitoring of backups. Automated backups are available </span></li>
</ul>
</div>
<div style="text-align: justify;">
<span style="color: #171717; font-family: "georgia" , "times new roman" , serif; font-size: large;">Migration to the managed instance is much easier as this supports restoring from the native backup created from the on-premises server. To restore the backup in the managed instance, the backup should be available in Azure storage account and should use RESTORE DATABASE FROM URL. Managed instances also allow the customer to take COPY_ONLY backups which do not break the azure automated backup chain. </span></div>
<div style="text-align: justify;">
<span style="color: #171717; font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div style="text-align: justify;">
<span style="background-color: white; color: #171717;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Managed instance is placed inside the Azure virtual network and in a dedicated subnet. This provides :</span></span></div>
</div>
<div>
<div style="text-align: justify;">
<ul style="background-color: white; box-sizing: inherit; color: #171717; list-style: none; margin: 16px 0px 16px 38px; padding: 0px; text-align: start;">
<li style="box-sizing: inherit; list-style: outside none disc; margin: 0px; outline: 0px; padding: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Secure private IP address.</span></li>
<li style="box-sizing: inherit; list-style: outside none disc; margin: 0px; outline: 0px; padding: 0px;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">The ability to connect an on-premises network to a managed instance.</span></li>
<li style="box-sizing: inherit; list-style: outside none disc; margin: 0px; outline: 0px; padding: 0px; text-align: justify;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">The ability to connect a managed instance or another on-premises database server through a linked server.</span></li>
</ul>
</div>
<div style="text-align: justify;">
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Managed instances also provide public endpoints. P<span style="background-color: white;">ublic endpoint provides the ability to connect to the Managed Instance from the Internet without using a VPN. Access is disabled by default unless explicitly allowed. We need to explicitly whitelist the IP address to access through the public IP address.</span></span></div>
</div>
<div style="text-align: justify;">
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="background-color: white; font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></span></div>
<div style="text-align: justify;">
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="background-color: white; font-family: "georgia" , "times new roman" , serif; font-size: large;">managed instance provides two flavors of compute and storage :</span></span></div>
<div style="text-align: justify;">
<ul>
<li><span style="font-family: "georgia" , "times new roman" , serif;"><span style="background-color: white; font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>General Purpose</b>: This supports most of the production workload. Support up to 80 vCore and 8TB fast storage</span></span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif;"><span style="background-color: white; font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>Business Critical</b>: For IO intensive and compute-intensive workload.Support up to 80 vCore and 4 TB super fast storage </span></span></li>
</ul>
</div>
<div style="text-align: justify;">
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="background-color: white; font-family: "georgia" , "times new roman" , serif; font-size: large;">Managed instance also support to bring your own license with software assurance (SA). Considering all these, managed instances are the best option for lift and shift of your existing workload to the cloud. Note that, if you need direct access to OS/filesystem or dependent on a specific version of SQL server or required specific features that are not supported in the Azure SQL server then Azure Paas database service is not suitable unless you resolve those dependencies.</span></span></div>
<div style="text-align: justify;">
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="background-color: white; font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></span></div>
<div style="text-align: justify;">
<span style="font-family: "georgia" , "times new roman" , serif;"><span style="background-color: white; font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>Single Database: </b> A single database is a fully-managed database as a service (DbaaS) under the Azure PaaS offering. This deployment option is most suited for modern application development like microservices. The single database deployment option creates a database in Azure with its own set of resources and is managed via a SQL database server. With a single database, each database resources are isolated from each other. A SQL database server is completely different from the SQL server that we used to have in the on-premise installation. In this context of azure DbaaS, an SQL database server is a logical construct that acts as a central administrative point for one or more single databases.</span></span></div>
<div style="text-align: justify;">
<ul style="text-align: left;">
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Single database does not provide access to the Operating system.</span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">It does not allow you to specify the version of the SQL Server. Single database always runs the latest stable SQL engine version, which is equal to or higher than the latest available RTM version of SQL Server. </span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Does not Supports </span></li>
<ul>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">SQL Agent service </span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">DB mail service</span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Service broker</span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Replication (can be push subscriber )</span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Change Data Capture (CDC) </span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">CLR creation </span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">linked server </span></li>
<li><span style="background-color: white; color: #171717;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">To choose the time zone</span></span></li>
<li><span style="background-color: white; color: #171717;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">File Stream and File table</span></span></li>
</ul>
</ul>
<div style="text-align: justify;">
<span style="color: #171717; font-family: "georgia" , "times new roman" , serif; font-size: large;">In short, Single database support only database-level features and does not support server level features. To migrate your existing workload to a single database, there are three primary methods.</span></div>
<div style="text-align: justify;">
<ul>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><span style="color: #171717;"><b>Export to a BACPAC </b>file and import the BACPAC file into azure single database. (</span>A <span style="background-color: white; color: #222222; text-align: left;">BACPAC file</span><span style="background-color: white; color: #222222; text-align: left;"> is a ZIP </span><span style="background-color: white; color: #222222; text-align: left;">file</span><span style="background-color: white; color: #222222; text-align: left;"> with an extension of </span><span style="background-color: white; color: #222222; text-align: left;">BACPAC</span><span style="background-color: white; color: #222222; text-align: left;"> containing the metadata and data from a SQL Server database. A </span><span style="background-color: white; color: #222222; text-align: left;">BACPAC </span><span style="background-color: white; color: #222222; text-align: left;">file</span><span style="background-color: white; color: #222222; text-align: left;"> can be stored in Azure Blob storage or in local storage in an on-premises location and later imported back into Azure SQL Database or into a SQL Server on-premises installation). This is a time-consuming process and requires downtime. The time required to complete the migration depends on the size of the database.</span></span></li>
<li><span style="background-color: white; color: #222222; text-align: left;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>Using transactional replication</b>: Azure single database can be configured as a subscriber of your on-premise database publication. There is no UI available. Need to configure through T-SQL. Once the complete data synched with the source server, on migration day cut down the traffic to your on-premise database and after synchronizing the data, point your application to the new Azure database </span></span></li>
<li><span style="background-color: white; color: #222222; text-align: left;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Use the <b>Azure DMS </b>(Data Migration service ).</span></span></li>
</ul>
<div style="text-align: justify;">
<span style="color: #222222; font-family: "georgia" , "times new roman" , serif; font-size: large;">Azure database server does not allow you to host inside the Vnet. By default, the access is through the public endpoint. All the traffic through this endpoint is blocked and you need to explicitly whitelist the required IP address to connect to the database server through this endpoint. There is no option to disable the private endpoint but as mentioned earlier, no one can connect through public endpoint unless you provide the access by whitelisting the IP addresses. You can enable the private endpoint by integrating virtual network private endpoints. Private endpoint helps to connect from the same VNet or peered VNet in the same/cross-region or from on-premises using a VPN. Note that virtual network private endpoint will be billed separately </span></div>
<div style="text-align: justify;">
<span style="color: #222222;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></span></div>
<div style="text-align: justify;">
<span style="color: #222222;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Azure Database provides two types of purchase :</span></span></div>
<div style="text-align: justify;">
<ul>
<li><span style="color: #222222;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">DTU base</span></span></li>
<li><span style="color: #222222;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">vCore base</span></span></li>
</ul>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Users can select the option based on their workload. In the vCore model, azure allows you to bring your own license. We will discuss these options in another post.</span><br />
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span>
<div style="text-align: left;">
<span style="text-align: left;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Single database support PaaS features like :</span></span></div>
<span style="font-family: "georgia" , "times new roman" , serif; font-size: large;"></span><br />
<div style="text-align: left;">
<div style="text-align: left;">
<ul style="text-align: left;">
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">No separate purchase of infrastructure and managing the underlying hardware of the database server.</span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">Automated patching and version upgrade. There is no option to control the maintenance window of patching or upgrade. To handle the connection error during the maintenance window, you need to implement the retry mechanism in your code.</span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif; font-size: large;">No headache of scheduling and monitoring of backups. Automated backups are available </span></li>
</ul>
</div>
</div>
</div>
<div style="text-align: justify;">
<span style="color: #222222; font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div style="text-align: justify;">
<span style="color: #222222; font-family: "georgia" , "times new roman" , serif; font-size: large;"><b>Elastic Pool: </b>Elastic Pool Is a set of single databases with a shared resource pool. In a single database, each database will have its own dedicated resource. In the elastic pool, resources are configured in the pool level and each single database connected with that pool shares the resource of elastic pool. SQL Database elastic pools are a simple solution for managing and scaling multiple databases that have varying and unpredictable usage demands. For example, let us assume you are providing some SaaS solutions to multiple clients in different time zone. Each customer data are stored in independent databases. In this scenario, the resource can be utilized in a much better way by adding those databases to the elastic pool instead of allocating dedicated resources to each individual customer database. Apart from this, the rest of the characteristics are the same as the Single database mentioned in the above section.</span></div>
<div style="text-align: justify;">
<span style="color: #222222; font-family: "georgia" , "times new roman" , serif; font-size: large;"><br /></span></div>
<div style="text-align: justify;">
<span style="color: #222222; font-family: "georgia" , "times new roman" , serif; font-size: large;">Hope the below pictorial representation helps you to understand different deployment option available in Azure DbaaS. </span></div>
<div style="text-align: justify;">
<span style="color: #222222; font-family: "georgia" , "times new roman" , serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="color: #222222;"><br /></span></div>
<div style="text-align: justify;">
<span style="color: #222222;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEixlLP9zNkld6OxPr-XiINJy2qf2ZMyXKk1KcPgrLS9aTr2xsTKDk1IYbPqgWDTxZHhXEmDR8Qgcuq2cXQhGHmPk_UiDEaebInKjwtDMyBUHI7nSpeHsSDmKtfwqWxvfawEsaPsL-ds7xXh/s1600/Untitled+Diagram+%25285%2529.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="590" data-original-width="811" height="464" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEixlLP9zNkld6OxPr-XiINJy2qf2ZMyXKk1KcPgrLS9aTr2xsTKDk1IYbPqgWDTxZHhXEmDR8Qgcuq2cXQhGHmPk_UiDEaebInKjwtDMyBUHI7nSpeHsSDmKtfwqWxvfawEsaPsL-ds7xXh/s640/Untitled+Diagram+%25285%2529.png" width="640" /></a></div>
<div style="text-align: justify;">
<span style="color: #222222;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div style="text-align: justify;">
<span style="color: #222222;"><br /></span></div>
<div style="text-align: justify;">
<span style="color: #222222;"><br /></span></div>
</div>
</div>
</div>
<div class="blogger-post-footer">www.PracticalSqlDba.com</div>Nelsonhttp://www.blogger.com/profile/02156244559143388581noreply@blogger.com6tag:blogger.com,1999:blog-6097409806591916698.post-83980141237480172012020-04-19T12:14:00.000-07:002020-04-19T12:14:32.189-07:00Disk Performance Metrics in AWS and Azure <div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<br />
<div style="text-align: justify;">
In the last two posts, we have discussed the different types of storage disks available in the AWS and Azure cloud platforms. We also discussed the characteristics that affect the performance of the disk. You can read those article using the below links:</div>
<div style="text-align: justify;">
</div>
<ul>
<li><a href="http://www.practicalsqldba.com/2020/04/disks-performance-in-azure.html" target="_blank">Factors affect the disk performance and Aws disk offering </a></li>
<li><a href="http://www.practicalsqldba.com/2020/03/understanding-disk-performance-and-iops.html" target="_blank">Azure Disk performance </a></li>
</ul>
<br />
<div style="text-align: justify;">
In this post, let us discuss about performance counters that can be used to measure the performance of your disk. These counters can be captured using the perfmon tool available in windows. Aws and azure provides a graphical representation of some of these counters in their portal.</div>
<br />
<b>Disk Reads/sec and Disk Writes/sec (IOPS)</b><br />
<div style="text-align: justify;">
Measures the rate of read/write operations from/to the disk. In other words, these counters measure the number of read/write operations completed by the disk. You can use disk operations/Sec (IOPS) counter in the Azure console to measure the same. On the other side AWS provides VolumeReadOps and VolumeWriteOps metrics to measure the same. This sum of these two counters gives us IOPS utilization of the disk.</div>
<div style="text-align: justify;">
<br /></div>
<b>Disk Read Bytes/sec and Disk Write Bytes/sec (Throughput)</b><br />
<div style="text-align: justify;">
Measure the number of bytes read from the disk and write to the disk per second. Disk bytes/sec counter provides the throughput utilization of the disk. Azure provides Disk Bytes/Sec to measure the throughput in the dashboard. In AWS, you can use the VolumeReadBytes and VolumeWriteByte cloud watch metrics to measure the throughput.</div>
<div style="text-align: justify;">
<b><br /></b></div>
<b>Average Disk sec/Read and Average disk sec/Write (Latency)</b><br />
<div style="text-align: justify;">
The Avg. Disk sec/Read performance counter indicates the average time, in seconds, of a read of data from the disk. The average value of the Avg. Disk sec/Read performance counter should be under 10 milliseconds. In other words, the number of seconds to complete one read/write operation.</div>
<div>
<b><br /></b></div>
<div>
<b>Average Disk Bytes/Read and Average Disk Bytes/Write (Size of the IO)</b></div>
<div>
This counter shows the average size of the IO operation of the disk workload.</div>
<div>
<br /></div>
<div>
<div>
<b>Avg. Disk Queue Length and Current Disk Queue Length</b></div>
<div>
<div style="text-align: justify;">
Avg. Disk Queue Length is the average number of (read and write) requests that are queued for the disk operations during the sample interval. Current Disk Queue Length is the number of IO requests pending on the disk at that point in time. Higher value of this counter results in high disk latency. Azure provides Disk QD (Queue Depth) to measure the average disk queue length. Whereas AWS provides the cloud watch metrics VolumeQueueLength to measure the same. </div>
</div>
</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
I hope this article helps you to troubleshoot the database performance issues in the cloud virtual machines. It may be much easier to identify the CPU and memory bottleneck of your database server. Detecting the IO bottleneck requires more effort and time.</div>
<div style="text-align: justify;">
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<br />
<br /></div>
<div class="blogger-post-footer">www.PracticalSqlDba.com</div>Nelsonhttp://www.blogger.com/profile/02156244559143388581noreply@blogger.com5tag:blogger.com,1999:blog-6097409806591916698.post-71612239561929465392020-04-05T11:58:00.002-07:002020-04-05T12:02:26.652-07:00Disks performance in Azure <div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
In our earliest <a href="http://www.practicalsqldba.com/2020/03/understanding-disk-performance-and-iops.html" target="_blank">post</a>, we have discussed in detail about the factors that affect the disk performance and different types of disk offerings in the AWS platform. In this blog post, let us go through different types of disk available in the Azure Platform.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Azure offers three types of disk :</div>
<div style="text-align: justify;">
</div>
<ul>
<li>Standard SSD</li>
<li>Premium SSD </li>
<li>Ultra Disk</li>
</ul>
Apart from this, Azure provides a standard HDD. These disks are low-cost disk support for VMs running low latency workload. Standard HDD offers write latencies under 10ms and read latencies under 20ms.<br />
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Before describing the characteristic of each disk type, let us go through some common facts:</div>
<div style="text-align: justify;">
</div>
<ul>
<li>I/O operation less than or equal to 256 KiB of size is considered as a single IO operation. AWS supports the IO size of 128 KiB for each IOPS. In azure, a disk with 1000 IOPS can attain maximum throughput of 1000X256=256000KB=250MiB/sec, but Azure put max throughput limit for each disk type and that will be less than or equal to the possible max throughput (IOPS X 256 KB)</li>
<li>Azure provides predefined disk size except for ultra disk. Users are allowed to create custom size disks but will be charged at the rate of next available size. For example, if the user creates a disk with 200 GB, he will be billed for the next available disk size offered by Azure. This is not applicable to Ultra Disk. With ultra disk, users can select the custom size and charge for that size.</li>
<li>Apart from the disk level maximum limit of IOPS and throughput, Azure implements throttling at the VM level for IOPS and throughput. This cap works on the aggregate value of IOPS and throughput of all disk attached to the VM.</li>
</ul>
<br />
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<b>Standard SSD: </b>Standard SSD disks are low-cost SSD provides better latency than the standard HDD. Standard SSD disks are best suitable for OS disk, web servers, and low IOPS application servers. Azure offers fourteen different disk sizes ranging from 4GB to 32 TB with IOPS ranging from 500 IOPS to 6000 IOPS and throughput range between 25MiB/sec to 750MiB/sec. Actual IOPS and throughput may vary depending on the traffic pattern. As far as I understood from the documentation, these are the maximum IOPS and throughput but not guaranteed always. For exact details of size and IOPS refer to azure <a href="https://docs.microsoft.com/en-us/azure/virtual-machines/windows/disks-types" target="_blank">page </a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<b>Premium SSD : </b>Azure premium SSD disks are high performing and low-latency disks designed to support IO-intensive workloads. Premium SSDs can only be used with the VM series that are compatible with premium SSD disks. Azure offers fourteen different disk sizes ranging from 4GB to 32 TB with IOPS ranging from 120 IOPS to 20000 IOPS and throughput range between 25MiB/sec to 900MiB/sec. The major difference from the standard SSD, premium SSD provides more consistent performance in terms of IOPS and throughput. For exact details of size and IOPS refer to azure <a href="https://docs.microsoft.com/en-us/azure/virtual-machines/windows/disks-types" target="_blank">page </a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Apart from this, premium SSD supports bursting for disk size up to 512 GB. These disks can burst up to 3500 IOPS and throughput up to 170 MiB/sec. Bursting is based on a credit system. Credits are automatically accumulated when disk traffic is below the provisioned performance and credits are automatically consumed when traffic bursts beyond the provisioned performance. The max burst limit defines the ceiling of disk IOPS and throughput even if you have accumulated burst credits. This helps to support random unpredictable spikes of the IO workload for disk up to 512 GB in size.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
For example, a disk with a size of 128 GB has provisioned 500 IOPS and 100MiB/sec throughput. This disk supports the burstable performance of 3500 IOPS and 170 MiB/sec for maximum of 30 minutes. The burstable limit is the same for all the disk size. When the disk workload goes below the provisioned IOPS and throughput ( 500 IOPS and 100MiB/sec), it starts accumulating the burstable credits and the same will be used when the workload goes beyond the provisioned IOPS and throughput. For example, if the actual workload is 200 IOPS and 80MiB/sec in the past one-second interval, then the unused 300 IOPS and 20MB are accumulated to the burst bucket of the disk.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Disk bursting will never go beyond VM throttling on IOPS and throughput. Irrespective of burstable credits available, the disk performance never goes beyond the burstable limit (1700 IOPS and 170MiB/sec). For example, even if the disk has 10,000 IOPS in the burstable credit bucket, the disk can not support more than the max burst limit of 3500 IOPS. In the same way, if the disk is attached to a VM with an IOPS limit of 1000 IOPS and throughput limit of 150 MiB/sec, the disk can't burst beyond the VM limit.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
When a VM starts with the burstable disk, it starts with a full burst credit bucket. This helps the booting speed of the VM if the boot disk is a burstable disk . A burstable disk credit bucket can be in any of the three states :</div>
<div style="text-align: justify;">
</div>
<ul>
<li><b>Accruing</b>: When the disk traffic is less than the provisioned IOPS and throughput. </li>
<li><b>Declining</b>: when the disk traffic is using more than the provisioned IOPS and throughput. The burst traffic will independently consume credits from IOPS and throughput.</li>
<li><b>Remaining Constant: </b>When the disk performance is exactly at the provisioned IOPS and throughput level.</li>
</ul>
<br />
<div style="text-align: justify;">
<b>Ultra Disk : </b>Azure ultra disks provide high throughput, high IOPS and consistent low latency disk storage for IO intensive mission-critical application. Ultra disk can only be used as a data disk(not as OS disk). Ultra disk supports the disk size ranging from 4 GB to 64 TB. When we configure the ultra disk, we can customize the size and performance variables (IOPS and throughput) </div>
<div style="text-align: justify;">
</div>
<ul>
<li>Ultra disk support 300 IOPS per GB with a maximum limit of 160000 IOPS per disk. For example, an 8GB disk can be configured to support a maximum of 2400 IOPS. All disk above the size of 533 GB, support only 160K IOPS. (533*300=159900)</li>
<li>The minimum configurable IOPS for an ultra disk is 2 IOPS per GB with a baseline of 100 IOPS per disk. for example, a 32 GB disks can be configured minimum of 100 IOPS instead of 64 IOPS. A 1000 GB disk minimum configurable value for IOPS is 2000</li>
<li>To get the benefit of the provisioned IOPS, ensure that the VM IOPS limit is higher than the IOPS configured for the disk. </li>
<li>The throughput limit is 256KiB/sec for each provisioned IOPS with a maximum of 2000 MiB/sec per disk. The minimum throughput per disk is 4KiB/sec for each provisioned IOPS with a baseline of 1MB/sec.</li>
<li>Ultra disk supports adjusting the performance variables (Throughput and IOPS) at runtime without detaching disk or restarting the VM. After changing the performance variable, it will take a maximum of one hour to get it effective.</li>
</ul>
<div>
There are lots of restrictions in using the ultra disk. Refer to the azure <a href="https://docs.microsoft.com/en-us/azure/virtual-machines/windows/disks-types" target="_blank">website</a> for up to date information. </div>
<div>
<br /></div>
<div>
<b>Disk Stripping for better performance: </b>Use multiple disks and stripes them together to get a combined higher IOPS and throughput. For example, in premium storage , P40 (2TB) and P50(4TB) provide the same IOPS (7500) and throughput (250MiB/sec). Instead of going for P50 disk, we can allocate two P40 disks and stripes them in OS level to get combined IOPS of 15000 and 500MiB/sec throughput. </div>
<br />
<div style="text-align: justify;">
</div>
<div>
<br /></div>
<div>
Hope this explains well about the different types of disk available in the Azure portal and keep these points in mind while allocating disk for better performance. In the next blog, we will go through different parameters to measure the performance of the disk. </div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
</div>
<div class="blogger-post-footer">www.PracticalSqlDba.com</div>Nelsonhttp://www.blogger.com/profile/02156244559143388581noreply@blogger.com6tag:blogger.com,1999:blog-6097409806591916698.post-77249312016651030092020-03-28T20:59:00.000-07:002020-04-05T12:02:55.119-07:00Understanding Disk Performance and IOPS in cloud platform <div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
In the past many years, apart from working as DBA, I was working with multiple cloud technologies and actively involved in the migration process to cloud infrastructure. In between, I managed to get certified in AWS as Associate Solution Architect.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
While working with database operation in the cloud, the most challenging part was to understand the Storage/disk performance. In this post, we will discuss the IOPS and disk performance in both AWS and Azure platforms.<br />
<br />
Before jumping into that, let us understand the<b> size of the IO, </b>This depends on two factors. The Allocation unit size of the disk and application requesting the IO operation. If the application IO request size is more than the size of the Allocation Unit Size (Block Size), OS split the IO request into smaller IO operations. For example, if an application requesting to open 10MB file residing in a disk with an allocation block size of 64 KB, the request will get divided into 160 requests with a size of 64KB (10 MB=10240 kb/64kb =160 IO <span style="font-family: inherit;">operation</span>). If the block size of the disk is 8 KB, this requires 1280 IO operations. To identify the Allocation Unit Size of the existing disk, run the below command from the command prompt From the result look for <b>Bytes Per Cluster</b>, which is the allocation unit size of the disk. This is the value you set while formatting the drive.</div>
<div style="text-align: justify;">
<span style="background-color: white; font-family: "segoe ui" , "segoeui" , "helvetica neue" , "helvetica" , "arial" , sans-serif; font-size: 15px; text-align: left;"><b><br /></b></span>
<span style="background-color: white; font-family: "segoe ui" , "segoeui" , "helvetica neue" , "helvetica" , "arial" , sans-serif; font-size: 15px; text-align: left;"><i><b>C:\>fsutil fsinfo ntfsinfo D: (Where D stands for the drive letter of your drive)</b></i></span><br />
<br />
<div style="text-align: justify;">
<span style="background-color: white; font-size: 15px;"><span style="font-family: inherit;">Now let us understand the concepts of IOPS and throughput. Let us assume we have a disk that supports 3000 IOPS with an allocation unit size of 64KB. The maximum throughput of the disk is </span></span></div>
<div style="text-align: justify;">
<span style="background-color: white; font-size: 15px;"><span style="font-family: inherit;"><br /></span></span></div>
<div style="text-align: justify;">
<span style="background-color: white; font-size: 15px;"><span style="font-family: inherit;">Throughput of the disk = 3000 IOPS X 64KB (size of the IO) = 192000KB =187.5 MB . </span></span></div>
<div style="text-align: justify;">
<span style="background-color: white; font-size: 15px;"><span style="font-family: inherit;"><br /></span></span></div>
<div style="text-align: justify;">
<span style="background-color: white; font-size: 15px;"><span style="font-family: inherit;">As the IOPS is IO per second, throughput is 187.5 MB/sec. Whether the disk can achieve this max throughput limits depends on the application requesting for the IO operation. If the application request comes with smaller than the size of the Allocation unit size, we can't make use of the max throughput of the disk. Let us take an example of the application requesting for IO operation with the size of 8KB, then max throughput will be :</span></span></div>
<div style="text-align: justify;">
<span style="background-color: white; font-size: 15px;"><span style="font-family: inherit;"><br /></span></span></div>
<span style="background-color: white; font-size: 15px;"><span style="font-family: inherit;">Throughput of the disk = 3000 IOPS X 4KB (size of the IO Operation) = 24000KB =23.44 MB/sec </span></span><br />
<div>
<br /></div>
<div>
Let us keep these three points in mind while discussing further the performance of the disks in the cloud environment. The performance of the disks is controlled by IOPS and throughput limits. These limits are there in both disk and instance (VM) level. </div>
<div style="text-align: justify;">
<br />
Before going into more detail, let us look into the different types of disk available on both platforms.</div>
<b>AWS, provide two major types of disks</b> :</div>
<div style="text-align: justify;">
</div>
<ul>
<li>General Purpose SSD (gp2)</li>
<li>Provisioned IOPS SSD (io1</li>
</ul>
<span style="text-align: justify;">Apart from those, AWS provides a couple of other disk types that are not relevant for our discussion. <b>gp2 disk comes </b>with predefined IOPS based on the size of the disk. Amazon offers 3 IOPS per GB. A single volume can have a maximum of 16000 IOPS (16 Kib IO) and 250 Mib/s throughput based on the disk size. To make it clear both 5.333 TB (5333 GB * 3 IOPS=~16000) and 10 TB (10240 GB*3 IOPS=30720 =16000(max limit per volume) </span><span style="text-align: justify;">provides 16000 IOPS and maximum throughput od 250 Mib/s . If the maximum throughput is 250 Mib/s with 16000 IOPS, what is the IO size </span><br />
<br />
<div style="text-align: justify;">
IO size = 250MB=(256000 KB) / 16000 IOPS =16 KB.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
That tells us that one IO operation can read /write a maximum of 16KB. It is very important to look into the size of the IO disk supports while reading the IOPS parameter of the disk. Let us take the same example of opening a 10MB file that resides on disk with the allocation unit size of 64KB. In this case, it requires a 640 IO operation. If the same files reside in a disk with the allocation unit size of 4KB, then it requires 1280 IO Operation. So the number of IO operation requires to complete one IO request is =</div>
<div style="text-align: justify;">
<u><br /></u></div>
<div style="text-align: center;">
<u> Size of the IO request (in KB) </u></div>
<div style="text-align: justify;">
MIN(size of the IO request, size of the IO supported by the disk, Allocation unit size of the disk)</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
In this :</div>
<div style="text-align: justify;">
size of the IO request: decided by the application design</div>
<div style="text-align: justify;">
size of the IO supported by the disk: decided by the manufacture (provider) of the disk</div>
<div style="text-align: justify;">
Allocation unit size of the disk: decided the by the user who is configuring the disk in VM</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
All these three plays a vital role in the performance of the disk.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<b>With the provisioned IOPS (io1) disk</b>, the user can define the IOPS of the disk. The user needs to pay an extra amount for the IOPS of the ioI disk, whereas this is included in the cost of the disk for gp2 disks. Io1 disk supports a maximum of 64000(16Kib I/O) IOPS and 1000MiB/s throughput. The maximum throughput is nothing but 64000*16=1024000KB=1000MiB.<br />
<br />
Apart from this, instance(ec2) IOPS and throughput throttling comes into the picture and plays a role in the performance of the disk. For example, 13.xlarge support maximum throughput of 106.25Mib/s and 6000 IOPS. <a href="https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-optimized.html">https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-optimized.html</a>. If you attach a gp2 disk with the capacity of 5 TB to this instance, we will not able to make use of the maximum IOPS and throughput of the disk as throttling happen at the instance level. The disk can support 5TB=5120GBX3 IOPS =15360 IOPS and maximum throughput of 15360X16KB=240MiB/s but we will not get the full benefit of this due to throttling in the instance level. To get the maximum benefit of disk performance, we need to attach this disk to i3.4xlarge which supports a maximum throughput of 437.5MiB/s and 16000 IOPS.<br />
<br />
I have tried my level best to explain the elements that affect the performance of the disks in cloud platform . Keep these points in mind while selecting the ec2 instances and disk for your workload. We will discuss further on the azure side in another <a href="http://www.practicalsqldba.com/2020/04/disks-performance-in-azure.html" target="_blank">blog post.</a><br />
<br />
<br />
<br />
<br />
<br />
<br /></div>
<div style="text-align: justify;">
<br /></div>
</div>
<div class="blogger-post-footer">www.PracticalSqlDba.com</div>Nelsonhttp://www.blogger.com/profile/02156244559143388581noreply@blogger.com9tag:blogger.com,1999:blog-6097409806591916698.post-11601536578459299142013-09-30T18:13:00.000-07:002013-11-11T17:45:04.202-08:00SQL Server :Part 1 : Architecture of Transaction Log<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div>
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;">In our earlier <a href="http://www.practicalsqldba.com/2013/09/sql-server-data-file-structure.html">post</a>, we have discussed in detail about the architecture of the data file and different types of pages. In this post, we will be discussing about the architecture of log file.</span></div>
<br />
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;">Logging is a mechanism used in RDBMS to support various ACID (Atomicity,Consistency,Isolation and Durability) properties of transactions. A transaction log is a physical file in which SQL server stores the details of all transactions and data modifications performed on the database.In the event of of disaster, that causes SQL server to shutdown unexpectedly (Power failure/hardware failure), the transaction log is used to bring the database in a consistent state while restarting the server.On restarting the server, database goes through the recovery process.During this recovery process , the transaction log is used to make sure that all committed transactions are written to respective data pages (rolled forward) and revert the uncommitted transaction that were written to data pages.</span></div>
<span style="font-family: Arial, Helvetica, sans-serif; text-align: justify;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><div style="text-align: justify;">
Logically transaction log is a set of log records.Each records is identified by a log sequence number (LSN). The new log records is always written at the logical end of log file with a LSN which is greater than the previous one.Each LSN will be associated with a transaction id , which can be used to group the log records of a transaction. As log file store the log records in the sequential order as it happens, It is not necessary that, log records of a transaction are always available in sequence in the log file.Each log records will have the LSN of previous log as a backward pointer and that will help in rollback of transaction.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Transaction log will store separate log entries for each operation.For example, while inserting a record into a table, transaction log will store separate log entry for inserting into clustered index and other non clustered index. In the same way, if a single update statement is updating 10 records, transaction log will capture 10 separate log entries.For data modification, transaction log store either the logical operation performed or the before and after image of the record.</div>
</span><br /></div>
<div>
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><div style="text-align: justify;">
To understand it better, let us look into the transaction log using the sys.fn_dblog. It is an undocumented function which will help us to read the active portion of the log. we are using the below query to create two tables and insert some records into that.We will delete the records from these table to keep the table clean. This insert and delete operation is to make sure that the pages are allocated to the table and we will not get unnecessary entries in the transaction log while examining it.We have issued a manual checkpoint to force the SQL server to write the log information to data file and clear the log. Note that, one table is heap table and other one is clustered index table.</div>
<div style="text-align: justify;">
<br /></div>
</span><br />
<div style="text-align: justify;">
</div>
<div style="text-align: left;">
<code style="font-size: 12px;"><span style="color: blue;">USE </span><span style="color: black;">Mydb</span></code></div>
<div style="text-align: left;">
<code style="font-size: 12px;">GO</code></div>
<div style="text-align: left;">
<code style="font-size: 12px;"><span style="color: blue;">CREATE TABLE </span><span style="color: black;">LoginfoHeap</span></code></div>
<div style="text-align: left;">
<code style="font-size: 12px;"><span style="color: grey;">(<br /> </span><span style="color: black;">id </span><span style="color: blue;">INT</span><span style="color: grey;">)</span></code></div>
<div style="text-align: left;">
<code style="font-size: 12px;"><span style="color: grey;"><br /></span><span style="color: black;">GO</span></code></div>
<div style="text-align: left;">
<code style="font-size: 12px;"><span style="color: blue;">CREATE TABLE </span><span style="color: black;">LoginfoCI</span></code></div>
<div style="text-align: left;">
<code style="font-size: 12px;"><span style="color: grey;">(<br /> </span><span style="color: black;">id </span><span style="color: blue;">INT PRIMARY KEY</span><span style="color: grey;">)</span></code></div>
<div style="text-align: left;">
<code style="font-size: 12px;"><span style="color: grey;"><br /></span><span style="color: blue;">INSERT INTO </span><span style="color: black;">LoginfoHeap </span><span style="color: blue;">VALUES</span><span style="color: grey;">(</span><span style="color: black;">1</span><span style="color: grey;">),(</span><span style="color: black;">2</span><span style="color: grey;">)</span></code></div>
<div style="text-align: left;">
<code style="font-size: 12px;"><span style="color: blue;">INSERT INTO </span><span style="color: black;">LoginfoCI </span><span style="color: blue;">VALUES</span><span style="color: grey;">(</span><span style="color: black;">2</span><span style="color: grey;">),(</span><span style="color: black;">4</span><span style="color: grey;">)</span></code></div>
<div style="text-align: left;">
<code style="font-size: 12px;">GO</code></div>
<div style="text-align: left;">
<code style="font-size: 12px;"><span style="color: blue;">DELETE FROM </span><span style="color: black;">LoginfoHeap </span></code></div>
<div style="text-align: left;">
<code style="font-size: 12px;"><span style="color: blue;">DELETE FROM </span><span style="color: black;">LoginfoCI</span></code></div>
<div style="text-align: left;">
<code style="font-size: 12px;">GO</code></div>
<div style="text-align: left;">
<code style="font-size: 12px;"><span style="color: blue;">CHECKPOINT</span></code></div>
<div style="text-align: left;">
<code style="font-size: 12px;">GO</code></div>
<div style="text-align: left;">
<code style="font-size: 12px;"><span style="color: blue;">SELECT </span></code><br />
<code style="font-size: 12px;"><span style="color: black;">[Current LSN]</span><span style="color: grey;">, </span></code><br />
<code style="font-size: 12px;"><span style="color: black;">Operation </span><span style="color: grey;">,</span><span style="color: black;">Context </span><span style="color: grey;">,</span></code><br />
<code style="font-size: 12px;"><span style="color: black;">[Transaction ID]</span><span style="color: grey;">,</span></code><br />
<code style="font-size: 12px;"><span style="color: black;">[Previous LSN]</span><span style="color: grey;">,</span><span style="color: black;">AllocUnitName</span><span style="color: grey;">,</span><span style="color: black;">[</span></code><br />
<code style="font-size: 12px;"><span style="color: black;">Previous Page LSN]</span><span style="color: grey;">,</span></code><br />
<code style="font-size: 12px;"><span style="color: black;">[Page ID]</span><span style="color: grey;">,</span><span style="color: black;">[XACT ID]</span><span style="color: grey;">,</span><span style="color: magenta;">SUSER_SNAME</span><span style="color: grey;">(</span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARBINARY</span><span style="color: grey;">,</span><span style="color: black;">[Transaction SID]</span><span style="color: grey;">)) </span><span style="color: blue;">AS </span><span style="color: red;">'Login'</span><span style="color: grey;">,</span></code><br />
<code style="font-size: 12px;"><span style="color: black;">[Begin Time]</span><span style="color: grey;">,</span><span style="color: black;">[End Time]</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">FROM </span><span style="color: black;">sys.</span><span style="color: darkred;">fn_dblog </span><span style="color: grey;">(NULL, NULL)</span></code></div>
<div style="text-align: left;">
<code style="font-size: 12px;"><span style="color: grey;"><br /></span></code></div>
<div style="text-align: left;">
<code style="font-size: 12px;"><span style="color: grey;"><br /></span></code></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjxx-OAvMYry6N_rSq1IYUiZYeVFvBa0H8QKFxr5Nmk1qbib7rveOcamIsrRLK3xODKJp8AWG3a3Fpbsr1E31lq-PLG90-cAYAigEJKnrSSpraOY-bJmlrdCxGuUnUK9S2T2eptubkRzcF1/s1600/Txnlog3.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="35" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjxx-OAvMYry6N_rSq1IYUiZYeVFvBa0H8QKFxr5Nmk1qbib7rveOcamIsrRLK3xODKJp8AWG3a3Fpbsr1E31lq-PLG90-cAYAigEJKnrSSpraOY-bJmlrdCxGuUnUK9S2T2eptubkRzcF1/s640/Txnlog3.png" width="640" /></a></div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span><br />
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;">From the output it is very clear that , we have only two active log entries.First one is written ,when the checkpoint started. The second one is written, when the checkpoint completed the process.</span></div>
<br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Now we will insert,update and delete records to these tables through two session </span><br />
<span style="color: green; font-family: monospace; font-size: 12px; text-align: left;"><br /></span>
<span style="color: green; font-family: monospace; font-size: 12px; text-align: left;">---SESSION I</span><br />
<span style="color: green; font-family: monospace; font-size: 12px; text-align: left;"> </span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">BEGIN TRAN</span><br />
<span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;"> INSERT INTO </span><span style="color: black; font-family: monospace; font-size: 12px; text-align: left;">LoginfoCI </span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">VALUES</span><span style="color: grey; font-family: monospace; font-size: 12px; text-align: left;">(</span><span style="color: black; font-family: monospace; font-size: 12px; text-align: left;">2</span><span style="color: grey; font-family: monospace; font-size: 12px; text-align: left;">)</span><br />
<span style="color: grey; font-family: monospace; font-size: 12px; text-align: left;"><br /></span><span style="color: green; font-family: monospace; font-size: 12px; text-align: left;">--SESSION 2</span><br />
<span style="color: green; font-family: monospace; font-size: 12px; text-align: left;"> </span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">BEGIN TRAN</span><br />
<span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;"> INSERT INTO </span><span style="color: black; font-family: monospace; font-size: 12px; text-align: left;">LoginfoHeap </span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">VALUES</span><span style="color: grey; font-family: monospace; font-size: 12px; text-align: left;">(</span><span style="color: black; font-family: monospace; font-size: 12px; text-align: left;">1</span><span style="color: grey; font-family: monospace; font-size: 12px; text-align: left;">),(</span><span style="color: black; font-family: monospace; font-size: 12px; text-align: left;">2</span><span style="color: grey; font-family: monospace; font-size: 12px; text-align: left;">)</span><br />
<span style="color: grey; font-family: monospace; font-size: 12px; text-align: left;"><br /></span><span style="color: green; font-family: monospace; font-size: 12px; text-align: left;">---SESSION I</span><br />
<span style="color: green; font-family: monospace; font-size: 12px; text-align: left;"> </span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">INSERT INTO </span><span style="color: black; font-family: monospace; font-size: 12px; text-align: left;">LoginfoCI </span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">VALUES</span><span style="color: grey; font-family: monospace; font-size: 12px; text-align: left;">(</span><span style="color: black; font-family: monospace; font-size: 12px; text-align: left;">4</span><span style="color: grey; font-family: monospace; font-size: 12px; text-align: left;">)</span><br />
<span style="color: grey; font-family: monospace; font-size: 12px; text-align: left;"><br /></span><span style="color: green; font-family: monospace; font-size: 12px; text-align: left;">--SESSION 2</span><br />
<span style="color: green; font-family: monospace; font-size: 12px; text-align: left;"> </span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">UPDATE </span><span style="color: black; font-family: monospace; font-size: 12px; text-align: left;">LoginfoHeap </span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">SET </span><span style="color: black; font-family: monospace; font-size: 12px; text-align: left;">id </span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">=</span><span style="color: black; font-family: monospace; font-size: 12px; text-align: left;">8 </span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">WHERE </span><span style="color: black; font-family: monospace; font-size: 12px; text-align: left;">id</span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">=</span><span style="color: black; font-family: monospace; font-size: 12px; text-align: left;">1</span><br />
<span style="color: black; font-family: monospace; font-size: 12px; text-align: left;"><br /></span><span style="color: green; font-family: monospace; font-size: 12px; text-align: left;">---SESSION I</span><br />
<span style="color: green; font-family: monospace; font-size: 12px; text-align: left;"> </span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">UPDATE </span><span style="color: black; font-family: monospace; font-size: 12px; text-align: left;">LoginfoCI </span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">SET </span><span style="color: black; font-family: monospace; font-size: 12px; text-align: left;">id </span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">=</span><span style="color: black; font-family: monospace; font-size: 12px; text-align: left;">6 </span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">WHERE </span><span style="color: black; font-family: monospace; font-size: 12px; text-align: left;">id</span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">=</span><span style="color: black; font-family: monospace; font-size: 12px; text-align: left;">2</span><br />
<span style="color: black; font-family: monospace; font-size: 12px; text-align: left;"><br /></span><span style="color: green; font-family: monospace; font-size: 12px; text-align: left;">--SESSION 2<br /> </span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">DELETE FROM </span><span style="color: black; font-family: monospace; font-size: 12px; text-align: left;">LoginfoHeap </span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">WHERE </span><span style="color: black; font-family: monospace; font-size: 12px; text-align: left;">id</span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">=</span><span style="color: black; font-family: monospace; font-size: 12px; text-align: left;">2</span><br />
<span style="color: black; font-family: monospace; font-size: 12px; text-align: left;"><br /></span><span style="color: green; font-family: monospace; font-size: 12px; text-align: left;">---SESSION I</span><br />
<span style="color: green; font-family: monospace; font-size: 12px; text-align: left;"> </span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">DELETE FROM </span><span style="color: black; font-family: monospace; font-size: 12px; text-align: left;">LoginfoCI </span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">WHERE </span><span style="color: black; font-family: monospace; font-size: 12px; text-align: left;">id</span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">=</span><span style="color: black; font-family: monospace; font-size: 12px; text-align: left;">4</span><br />
<span style="color: black; font-family: monospace; font-size: 12px; text-align: left;"> </span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">SELECT </span><span style="color: grey; font-family: monospace; font-size: 12px; text-align: left;">* </span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">FROM </span><span style="color: black; font-family: monospace; font-size: 12px; text-align: left;">sys.dm_tran_current_transaction</span><br />
<span style="color: black; font-family: monospace; font-size: 12px; text-align: left;"> </span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">COMMIT</span><br />
<span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;"><br /></span><span style="color: green; font-family: monospace; font-size: 12px; text-align: left;">--SESSION 2</span><br />
<span style="color: green; font-family: monospace; font-size: 12px; text-align: left;"> </span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">SELECT </span><span style="color: grey; font-family: monospace; font-size: 12px; text-align: left;">* </span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">FROM </span><span style="color: black; font-family: monospace; font-size: 12px; text-align: left;">sys.dm_tran_current_transaction</span><br />
<span style="color: black; font-family: monospace; font-size: 12px; text-align: left;"> </span><span style="color: blue; font-family: monospace; font-size: 12px; text-align: left;">COMMIT</span><br />
<br />
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;">The DMV sys.dm_tran_current_transcation returns a single row that displays the state information of the current transaction in the current session.We are interested only in the transaction_id, which will help us to filter the output of sys.fn_dblog. Let us see the output of sys.fn_dblog.</span></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: left;">
<span style="color: blue; font-family: monospace; font-size: 12px;">SELECT </span><br />
<span style="color: black; font-family: monospace; font-size: 12px;">[Current LSN]</span><span style="color: grey; font-family: monospace; font-size: 12px;">, </span><br />
<span style="color: black; font-family: monospace; font-size: 12px;">Operation </span><span style="color: grey; font-family: monospace; font-size: 12px;">,</span><br />
<span style="color: black; font-family: monospace; font-size: 12px;">Context </span><span style="color: grey; font-family: monospace; font-size: 12px;">,</span><br />
<span style="color: black; font-family: monospace; font-size: 12px;">[Transaction ID]</span><span style="color: grey; font-family: monospace; font-size: 12px;">,</span><br />
<span style="color: black; font-family: monospace; font-size: 12px;">[Previous LSN]</span><span style="color: grey; font-family: monospace; font-size: 12px;">,</span><br />
<span style="color: black; font-family: monospace; font-size: 12px;">AllocUnitName</span><span style="color: grey; font-family: monospace; font-size: 12px;">,</span><br />
<span style="color: black; font-family: monospace; font-size: 12px;">[Previous Page LSN]</span><span style="color: grey; font-family: monospace; font-size: 12px;">,</span><br />
<span style="color: black; font-family: monospace; font-size: 12px;">[Page ID]</span><span style="color: grey; font-family: monospace; font-size: 12px;">,</span><span style="color: black; font-family: monospace; font-size: 12px;">[XACT ID]</span><span style="color: grey; font-family: monospace; font-size: 12px;">,</span><br />
<span style="color: magenta; font-family: monospace; font-size: 12px;">SUSER_SNAME</span><span style="color: grey; font-family: monospace; font-size: 12px;">(</span><span style="color: magenta; font-family: monospace; font-size: 12px;">CONVERT</span><span style="color: grey; font-family: monospace; font-size: 12px;">(</span><span style="color: blue; font-family: monospace; font-size: 12px;">VARBINARY</span><span style="color: grey; font-family: monospace; font-size: 12px;">,</span><span style="color: black; font-family: monospace; font-size: 12px;">[Transaction SID]</span><span style="color: grey; font-family: monospace; font-size: 12px;">)) </span><span style="color: blue; font-family: monospace; font-size: 12px;">AS </span><span style="color: red; font-family: monospace; font-size: 12px;">'Login'</span><span style="color: grey; font-family: monospace; font-size: 12px;">,</span><br />
<span style="color: black; font-family: monospace; font-size: 12px;">[Begin Time]</span><span style="color: grey; font-family: monospace; font-size: 12px;">,</span><br />
<span style="color: black; font-family: monospace; font-size: 12px;">[End Time]</span><br />
<span style="color: blue; font-family: monospace; font-size: 12px;">FROM </span><span style="color: black; font-family: monospace; font-size: 12px;">sys.</span><span style="color: darkred; font-family: monospace; font-size: 12px;">fn_dblog </span><span style="color: grey; font-family: monospace; font-size: 12px;">(NULL, NULL) </span><br />
<span style="color: blue; font-family: monospace; font-size: 12px;">WHERE </span><span style="color: black; font-family: monospace; font-size: 12px;">[Transaction ID] </span><span style="color: blue; font-family: monospace; font-size: 12px;">IN </span><br />
<span style="color: grey; font-family: monospace; font-size: 12px;">(</span><br />
<span style="color: grey; font-family: monospace; font-size: 12px;"> </span><span style="color: blue; font-family: monospace; font-size: 12px;">SELECT </span><span style="color: black; font-family: monospace; font-size: 12px;">[Transaction ID] </span><span style="color: blue; font-family: monospace; font-size: 12px;">FROM </span><span style="color: black; font-family: monospace; font-size: 12px;">sys.</span><span style="color: darkred; font-family: monospace; font-size: 12px;">fn_dblog </span><span style="color: grey; font-family: monospace; font-size: 12px;">(NULL, NULL) </span><br />
<span style="color: grey; font-family: monospace; font-size: 12px;"> </span><span style="color: blue; font-family: monospace; font-size: 12px;">WHERE </span><span style="color: black; font-family: monospace; font-size: 12px;">[XACT ID] </span><span style="color: blue; font-family: monospace; font-size: 12px;">IN </span><span style="color: grey; font-family: monospace; font-size: 12px;">(</span><span style="color: black; font-family: monospace; font-size: 12px;">856960</span><span style="color: grey; font-family: monospace; font-size: 12px;">,</span><span style="color: black; font-family: monospace; font-size: 12px;">856981</span><span style="color: grey; font-family: monospace; font-size: 12px;">)</span><br />
<span style="color: grey; font-family: monospace; font-size: 12px;">)</span><br />
<span style="color: grey; font-family: monospace; font-size: 12px;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif; text-align: justify;">The values 856960 and 856981 are the transaction_id returned from sys.dm_tran_current_transaction.We have filter the output to get only the </span><span style="font-family: Arial, Helvetica, sans-serif;">relevant rows in which we are interested.</span></div>
<div style="text-align: left;">
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjimQfypelAJpHt379qOebU3I-qgISpNVhqZ9NR1YRtVN0MzQKRvmzYaxTO7H8X0R8DMHFH1NPcyys2rJnLviFNyqMk-RFCD-iO-TggR7WC7DKVBx_jGINTeIEbXGOyvXG0uswZ1BD8ywEu/s1600/Txnlog5.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjimQfypelAJpHt379qOebU3I-qgISpNVhqZ9NR1YRtVN0MzQKRvmzYaxTO7H8X0R8DMHFH1NPcyys2rJnLviFNyqMk-RFCD-iO-TggR7WC7DKVBx_jGINTeIEbXGOyvXG0uswZ1BD8ywEu/s1600/Txnlog5.png" /></a></div>
<span style="font-family: Arial, Helvetica, sans-serif; text-align: justify;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif; text-align: justify;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif; text-align: justify;"><br /></span></div>
<div style="text-align: left;">
<br /></div>
<br />
<br /></div>
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">In our script, we have opened two transaction and all our transaction log entries are grouped to </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">one of these transaction_id marked in red and green.Let us analyse what we did and how it is captured in the transaction log.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">In the session 1, we have started the transaction and inserted a single record.The first records in the output map to the BEGIN TRAN command. This is the starting point of the transaction and created a new transaction_id.The <i>previous LSN </i>column value is 0 as this is the first log entry in this transaction.In the same log records,it stores the XACT_ID,login and transaction start time.The second record represent the insert into the clustered table.The transaction_id is used to group the entries associated with a transaction. The <i>previouse LSN </i>column, </span><span style="font-family: Arial, Helvetica, sans-serif;">is a pointer to the previous log entry in the same transaction which help SQL server to move backwards in case of rollback.<i>Page id</i> column refer the the page number where this LSN made the change.</span><span style="font-family: Arial, Helvetica, sans-serif;"><i>Previous Page LSN </i>column refer the last log sequence number(LSN) which modify this page.When LSN modify a page, it will also update the corresponding LSN number in the page header (m_lsn field in the header. For more detail refer <a href="http://www.practicalsqldba.com/2012/08/sql-server-understanding-data-page.html">this</a> post)</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">In the session 2, we have opened another transaction and inserted two records through single insert statement to the heap table. You can map these operations to row number 3,4, and 5 in the transaction log output. Third row represent the Begin tran command. Even if we inserted two records in single insert statement , SQL server recorded two separate entry in the transaction log. </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">As a next step, in session 1 we have added 1 record to the clustered index table.We can map this operation to the 6th record in the transaction log output.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">In the next statement , we have modified a record in heap table through Session 2. You can map this to the 7th record in the transaction log output.If you look into the <i>previous LSN </i>column , it will be <i>current LSN</i> column value of the last record associated with this transaction.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">In the same way, as a next statement we have modified a record in the clustered table through session 1. We can map the 8th and 9th records in the transaction log output to the update operation on the clustered table. You might have noticed that, when we modified a record in the heap table, transaction log recorded operation in a single row. Where as the same operation in a clustered table has two record in the transaction log. One for delete and other one for insert. When you modify the clustered index key, SQL server internally delete the existing record and insert a new record. This is because, the record need to be stored in the new location based on the modified value(based on the order of clustered index column). The easiest way for SQL server to achieve this is , delete the existing record and insert it as new records with modified clustered column value.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">In the next two statement, we are deleting one record from heap table and clustered table.This can be mapped to the 10th and 11th records in the output.Finally we have issued the commit statement in both sessions.12th and 13th record in the transaction log output can be mapped to the commit operation.The <i>Previous LSN </i>column refer the <i>Current LSN column of </i>corresponding <i>begin tran</i> statement. It will also capture the transaction end time in the <i>End time </i>column.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><b>Understanding the VLF(Virtual Log File)</b></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">A database can have one or more log file. In general there will be only one log file as there is no performance improvement by having multiple log file. SQL server uses the transaction log in sequential manner.As the data file divided into pages,log files are divided into virtual log file(VLF).The size of the VLFs in a log file may not be in equal size. SQL server decide the size and number of VLF in a log file based on the size of the log file growth as given below.</span></div>
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;">Growth upto 64 MB = 4 VLF</span></div>
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;">From 64 MB to 1 GB = 8 VLF</span></div>
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;">Larger than 1 GB = 16 VLF</span></div>
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;">Let us create a database with 64 MB initial log size and later increase it to 1 GB. As per above calculation the log file should have 12 VLFs. 4 VLF based on initial size and 8 VLF due to changing the log size to 1 GB.</span></div>
<div style="border: 0px currentColor; color: #666666; font-family: verdana, sans-serif; font-size: 14px; line-height: 21px; margin-bottom: 15px; padding: 0px; vertical-align: baseline;">
<span style="color: blue; font-size: 12px;"><br /></span>
<span style="color: blue; font-size: 12px;">USE MASTER</span><span style="color: grey; font-size: 12px;">;</span><br />
<span style="color: black; font-size: 12px;">GO</span><br />
<span style="color: blue; font-size: 12px;">CREATE DATABASE </span><span style="color: black; font-size: 12px;">Mydb</span><br />
<span style="color: blue; font-size: 12px;">ON </span><br />
<span style="color: grey; font-size: 12px;">( </span><span style="color: black; font-size: 12px;">NAME </span><span style="color: blue; font-size: 12px;">= </span><span style="color: black; font-size: 12px;">MyDb_dat</span><span style="color: grey; font-size: 12px;">,</span><span style="color: grey; font-size: 12px;"> </span><span style="color: black; font-size: 12px;">FILENAME </span><span style="color: blue; font-size: 12px;">= </span><span style="color: red; font-size: 12px;">'D:\MyDb\Mydb.mdf'</span><span style="color: grey; font-size: 12px;">,</span><br />
<span style="color: grey; font-size: 12px;"> </span><span style="color: black; font-size: 12px;">SIZE </span><span style="color: blue; font-size: 12px;">= </span><span style="color: black; font-size: 12px;">10MB</span><span style="color: grey; font-size: 12px;">,</span><span style="color: grey; font-size: 12px;"> </span><span style="color: black; font-size: 12px;">MAXSIZE </span><span style="color: blue; font-size: 12px;">= </span><span style="color: black; font-size: 12px;">3072MB</span><span style="color: grey; font-size: 12px;">,</span><span style="color: grey; font-size: 12px;"> </span><span style="color: black; font-size: 12px;">FILEGROWTH </span><span style="color: blue; font-size: 12px;">= </span><span style="color: black; font-size: 12px;">5MB </span><span style="color: grey; font-size: 12px;">)</span></div>
</div>
<code style="font-size: 12px;"><span style="color: magenta;">LOG </span><span style="color: blue;">ON </span><span style="color: grey;">( </span><span style="color: black;">NAME </span><span style="color: blue;">= </span><span style="color: black;">MyDb_log</span><span style="color: grey;">,</span><span style="color: black;">FILENAME </span><span style="color: blue;">= </span><span style="color: red;">'D:\MyDb\MyDB.ldf'</span><span style="color: grey;">,<br /> </span><span style="color: black;">SIZE </span><span style="color: blue;">= </span><span style="color: black;">64MB</span><span style="color: grey;">, </span><span style="color: black;">MAXSIZE </span><span style="color: blue;">= </span><span style="color: black;">2048MB</span><span style="color: grey;">, </span><span style="color: black;">FILEGROWTH </span><span style="color: blue;">= </span><span style="color: black;">5MB </span><span style="color: grey;">) ;</span></code><br />
<code style="font-size: 12px;"><span style="color: black;">GO</span></code><br />
<span style="color: blue; font-size: 12px;"><br /></span>
<span style="color: blue; font-size: 12px;">ALTER DATABASE </span><span style="color: black; font-size: 12px;">Mydb </span><br />
<span style="color: black; font-size: 12px;">MODIFY </span><span style="color: blue; font-size: 12px;">FILE </span><span style="color: grey; font-size: 12px;">( </span><span style="color: black; font-size: 12px;">NAME </span><span style="color: blue; font-size: 12px;">= </span><span style="color: black; font-size: 12px;">MyDb_Log</span><span style="color: grey; font-size: 12px;">,</span><span style="color: black; font-size: 12px;">FILENAME </span><span style="color: blue; font-size: 12px;">= </span><span style="color: red; font-size: 12px;">'D:\MyDb\MyDB.ldf'</span><span style="color: grey; font-size: 12px;">, </span><span style="color: black; font-size: 12px;">SIZE </span><span style="color: blue; font-size: 12px;">= </span><span style="color: black; font-size: 12px;">1024MB</span><span style="color: grey; font-size: 12px;">)</span><br />
<span style="color: grey; font-size: 12px;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif; text-align: justify;">Now Let us see how many VLF got created. To find out the number of VLF in database log file, we can make use of DBCC Loginfo.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif; text-align: justify;"><br /></span></div>
<code style="font-size: 12px;"><span style="color: blue;">DBCC </span><span style="color: black;">loginfo</span><span style="color: grey;">(</span><span style="color: red;">'mydb'</span><span style="color: grey;">)</span></code>
<br />
<code style="font-size: 12px;"><span style="color: grey;"><br /></span></code>
<span style="font-family: Arial, Helvetica, sans-serif; text-align: justify;">The output is given below.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif; text-align: justify;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijZV4z2URUMfFWq2UKfZ2ZRBwocfe3gGh04E7ZaeUUuuNO9JQeoPYIjIhTq9DllPQng-vLgTlXTXOhll6t4TdNNJpWpvzEB6C9U_d2sRF9ustN7If6DCjgySE5twCmCZJ2zu9g4RVRJeiu/s1600/Txnlog1.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="208" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijZV4z2URUMfFWq2UKfZ2ZRBwocfe3gGh04E7ZaeUUuuNO9JQeoPYIjIhTq9DllPQng-vLgTlXTXOhll6t4TdNNJpWpvzEB6C9U_d2sRF9ustN7If6DCjgySE5twCmCZJ2zu9g4RVRJeiu/s400/Txnlog1.png" width="400" /></a></div>
<span style="font-family: Arial, Helvetica, sans-serif; text-align: justify;"><br /></span>
<code style="font-size: 12px;"><span style="color: grey;"><br /></span></code>
<code style="font-size: 12px;"><span style="color: grey;"><br /></span></code>
<code style="font-size: 12px;"><span style="color: grey;"><br /></span></code>
<code style="font-size: 12px;"><span style="color: grey;"><br /></span></code>
<code style="font-size: 12px;"><span style="color: grey;"><br /></span></code>
<code style="font-size: 12px;"><span style="color: grey;"><br /></span></code>
<code style="font-size: 12px;"><span style="color: grey;"><br /></span></code>
<code style="font-size: 12px;"><span style="color: grey;"><br /></span></code>
<code style="font-size: 12px;"><span style="color: grey;"><br /></span></code>
<code style="font-size: 12px;"><span style="color: grey;"><br /></span></code>
<code style="font-size: 12px;"><span style="color: grey;"><br /></span></code>
<code style="font-size: 12px;"><span style="color: grey;"><br /></span></code>
<code style="font-size: 12px;"><span style="color: grey;"><br /></span></code>
<code style="font-size: 12px;"><span style="color: grey;"><br /></span></code>
<span style="font-family: Arial, Helvetica, sans-serif; text-align: justify;">There are 12 records in the output each represent a VLF.Let us try to understand the result</span><br />
<span style="font-family: Arial, Helvetica, sans-serif; text-align: justify;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif; text-align: justify;"><b>FileId: </b>This is the file id of the log file and will be same for all 12 records as we have only one log file.If we have multiple log file , we can multiple numbers here</span><br />
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;"><b>FileSize: </b>This is the size of the VLF. If you look into the first four, have same size except the fourth one. This because first 8KB of the log file is used for file header. If you add filesize value of first four records along with 8192(8KB) , you will get 64MB which is the initial size of the log file.</span></div>
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;">16711680+16711680+16711680+16965632 =67100672+8192 =67108864bytes =64MB</span></div>
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;">In the same if you add the last 8 records it will account the 960 MB (1024-64) , the growth happened due to the alter statement.</span></div>
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;"><b>StartOffSet:</b> This values is also in bytes, and is the sort column of the output. The first VLF alwasy start from 8192, which is the number of bytes in a page.As mentioned above, the first 8KB is used for file header and will not store any log.</span></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;"><b>FSeqNo:</b> The file sequence number indicates the order of usage of the VLFs. The row with the highest FSeqNo value is the VLF where current log records are being written.FSeqNo values are not consistent. It will keep changing each time when VLF are getting reused. We will discuss more about this later in this post. A value of 0 in this column means that this VLF has never been used at all. That is the reason we have 0 for all records except one where it is currently logging.</span></div>
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;"><b>Status: </b>Status has two possible values : 0 and 2. A value of 2 means the VLF is not reusable and a value 0 means it can be reused.It will be more clear as we go further.</span></div>
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;"><b>Parity: </b>Parity has three possible values 0 ,64 and 128. If the VLF is not used yet, it will have a value 0 and will be set to 64 on first use.Every time a VLF is reused, the parity value is switched between 64 and 128.</span></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<b>CreateLSN:</b> T<span style="font-family: Arial, Helvetica, sans-serif;">he value indicates when the VLF is created or to group the VLF based on the creation. A values 0 indicates, those VLFs are created as part of database creation. In our case first four records has a value 0 which indicate these VLFs are created as part of database creation with 64MB log size. The remaining 8 records has the same value. These VLF are created as part of our alter database statement to increase the size of the log file from 64 MB to 1024MB</span></div>
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;"><i>The above output description is referred from Kalen Delaney Blog <a href="http://sqlblog.com/blogs/kalen_delaney/archive/2009/12/21/exploring-the-transaction-log-structure.aspx">Post</a></i></span></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;">Now our transaction log will looks like below</span></div>
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilz9nqwS9VvntqzNjrncoRHlJFZT9VWlPmMvnFxhqYzGhHLSiBVSkFy7FlDgJnj_VsH9zJ7AvorJ1XVlncFPvISiZVgt9LIzd6ibg0OYzuB08Hj8KxMulz401BRuSpqMJiEqWLdt5mn5cu/s1600/Txnlog2.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="100" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilz9nqwS9VvntqzNjrncoRHlJFZT9VWlPmMvnFxhqYzGhHLSiBVSkFy7FlDgJnj_VsH9zJ7AvorJ1XVlncFPvISiZVgt9LIzd6ibg0OYzuB08Hj8KxMulz401BRuSpqMJiEqWLdt5mn5cu/s640/Txnlog2.png" width="640" /></a></div>
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<span style="font-family: Arial, Helvetica, sans-serif;">Now we have learned about the LSN and VLF. we will discuss more about transaction log in the next post.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
</div>
</div>
</div>
<span style="font-family: Arial, Helvetica, sans-serif;">If you liked this post, do like my page on <a href="http://www.facebook.com/practicalSqlDba">FaceBook</a></span><br />
<div id="fb-root">
</div>
<script>(function(d, s, id) {
var js, fjs = d.getElementsByTagName(s)[0];
if (d.getElementById(id)) return;
js = d.createElement(s); js.id = id;
js.src = "//connect.facebook.net/en_US/all.js#xfbml=1";
fjs.parentNode.insertBefore(js, fjs);
}(document, 'script', 'facebook-jssdk'));</script>
<br />
<div class="fb-like" data-font="arial" data-href="http://www.facebook.com/practicalSqlDba" data-send="true" data-show-faces="true" data-width="450">
</div>
</div>
<div class="blogger-post-footer">www.PracticalSqlDba.com</div>Nelsonhttp://www.blogger.com/profile/02156244559143388581noreply@blogger.com223