Displaying Azure Database Statistics in Kentico
When deploying Kentico to Azure, one of the biggest questions that comes up is what size database to use. While we publish some recommendations in our documentation, your individual mileage may vary. To know if your database is spec’d high enough, you need to understand how it’s performing. In this article, I’ll show you how you can quickly view your Azure Database information right from inside your site, using a custom utility.
Running Kentico in Azure is a great way to expand your global reach with unlimited resources. By hosting your application in Microsoft’s cloud, you can take advantage of auto-scaling, in-depth reporting, and unique licensing that allow you to spread the cost of ownership over time. Additionally, Azure has several options when architecting your solutions, enabling you to tailor your environment specifically for your needs. Knowing how to spec out your hosting is a key skill many companies struggle with when deploying to Azure.
When it comes to your database, this is an especially tricky part of deciding what option is right for your application. Because SQL Database (formerly Azure Database … formerly SQL Azure) has so many configuration options, matching your resource needs to the correct flavor may be difficult. In our documentation, we recommend starting with at least an S2 for Production systems, and scaling up/down, as needed.
Once you do select an option, you will want to monitor your site to determine if what you have is too much or too little. In this article, I’ll show you how I created a simple utility to query some built-in Azure tables to see how my database was performing; all from within my Kentico site.
Creating the stored procedure
The key to this utility is the data itself. SQL Database has some great built-in tables that store all sorts of information about your implementation. These include averages for CPU, memory, and data IO, as well as max usages. By querying this data, you can get a feel for how your database is performing over time and use that to determine your resource needs.
The first step was to create a stored procedure. In the Admin/Database objects module, I clicked Stored procedures and then New. This Database objects module allows you to create a new procedure in your database, that can then be called from the API.
The procedure looks at the sys.dm_db_resource_stats table to get the latest stats from my database. Additionally, it queries the environment to find out the type and tier of database I have. Note that the code is only pulling data from the last 60 minutes.
Here’s the full SQL for the procedure.
SELECT @@VERSION AS 'Azure Version',
DATABASEPROPERTYEX(DB_NAME(), 'Edition') AS 'Azure Edition',
COALESCE(DATABASEPROPERTYEX(DB_NAME(), 'ServiceObjective'), 'N/A in v11') AS 'Azure Tier'
SELECT
AVG(avg_cpu_percent) AS 'Average CPU Utilization In Percent',
MAX(avg_cpu_percent) AS 'Maximum CPU Utilization In Percent',
AVG(avg_data_io_percent) AS 'Average Data IO In Percent',
MAX(avg_data_io_percent) AS 'Maximum Data IO In Percent',
AVG(avg_log_write_percent) AS 'Average Log Write Utilization In Percent',
MAX(avg_log_write_percent) AS 'Maximum Log Write Utilization In Percent',
AVG(avg_memory_usage_percent) AS 'Average Memory Usage In Percent',
MAX(avg_memory_usage_percent) AS 'Maximum Memory Usage In Percent'
FROM sys.dm_db_resource_stats
WHERE end_time > DATEADD(minute, -60, GETDATE())
Here is the new stored procedure within Kentico.
Creating the utility
With the procedure in place, I was ready to create the utility. In my CMSModules/Custom folder, I created a new ASPX page for my functionality. I added the following layout and code. This design would display the stats for my Azure database. Because I wanted to load the stats dynamically, I added a simple repeater for the values.
<asp:Repeater ID="repdata" runat="server">
<ItemTemplate>
<div class="form-group">
<div class="editing-form-label-cell">
<span class="control-label"><%# Eval("key")%></span>
</div>
<div class="editing-form-value-cell">
<span class="form-control-text"><%# Eval("value")%></span>
</div>
</div>
</ItemTemplate>
</asp:Repeater>
For the logic, I used the DataEngine API to execute my new stored procedure to return my data. For each record, I dynamically added the column name and value to my KeyValuePair collection. I then bound that collection to my repeater.
List<KeyValuePair<string, string>> kvpData = new List<KeyValuePair<string, string>>();
DataSet ds = ConnectionHelper.ExecuteQuery("Proc_Custom_GetAzureDatabaseStats", null, QueryTypeEnum.StoredProcedure);
if(!DataHelper.IsEmpty(ds))
{
foreach(DataTable dt in ds.Tables)
{
foreach(DataColumn dc in dt.Columns)
{
foreach(DataRow dr in dt.Rows)
{
kvpData.Add(new KeyValuePair<string, string>(dc.ColumnName.ToString().Trim(), dr[dc.ColumnName].ToString().Trim()));
}
}
}
repdata.DataSource = kvpData;
repdata.DataBind();
plcMess.InfoText = "Stats updated!";
}
Registering the utility
After creating the utility, I registered it within the site. In the Modules/Custom section, I created a new UI Interface element under the System module. I added the path to my new ASPX page for the Page URL.
Testing
With the utility registered, I was ready to test the functionality. I accessed the tab under the System module and confirmed the results. Using this information, I could then understand how well my database is performing and adjust my configuration accordingly.
A few notes
While the above solution may work fine, your individual scenario may present some issues in implementing. Here are few notes about this article:
- The stored procedure will require the DB user that is used in the connection string for Kentico to be sys admin or at least have access to not only the Kentico database but also the sys tables. If this is an issue, a possible workaround is to have a second connection string for this report defined in web.config and then retrieve that connection string and use that to run the stored procedure for this utility.
- If you do not want to create a custom UI element, you could use the Reporting module to make a report with the query provided. Queries also have the built-in functionality of using a separate connection string defined in the web.config, which is an added security bonus. Admins would also be able to subscribe to these reports if they wanted.
- UI Elements created under the "Custom" Module will not be transferable to another instance. Kentico strongly encourages creating a separate module whenever possible.
Learn More
Moving Forward
Hopefully, this article showed you how easily you can integrate your custom functionality into Kentico using a custom utility. With this utility, you can view your SQL Database statistics and get an understanding of how well (or poorly) your system is doing. You can then scale your configuration up or down to meet your needs. Good luck!
Get the code
This blog is intended for informational purposes only and provides an example of one of the many ways to accomplish the described task. Always consult Kentico Documentation for the best practices and additional examples that may be more effective in your specific situation.