Automating SQL Server VM Provisioning on Azure with PowerShell

Running Wordpress with SQL Server on Azure

Analyzing Perfmon with SQL Query (Part 2)

Analyzing Perfmon with SQL Query (Part 1)

June 2014. SQLServer-Indo Meet-Up: Power BI and Azure Survival Guide

We are organizing SQLServer-Indo meet up this month, with a a special guest speaker. Julie Koesmarno is an SQL Server MVP living in Australia. She has presented in various international events such as PASS Summit and SQL Rally in USA. We will setup a Lync conference call so She can do the remote presentation, broadcasted in the room. As usual, there will be 2 topics presented in the meeting as follow: Session 1: Azure Survival Guide for [On Premise] SQL Server DBA By Choirul Amri, Senior Consultant – Microsoft Services Are you confuse with all Azure buzzwords and new stuff? Struggling to understand affinity groups, availability sets, cloud service, and Vnet? Do You know When to use SQL Server VM instead of SQL Azure Database? What is the catch and trap of configuring Availability Groups in Azure? Join the session and grab the simplified, practical discussion of Azure architecture. Session 2: Building Your Myth Busting Lab With Power BI By Julie Koesmarno, SQL Server MVP Did you know that a large portion of Information Workers’ and Data Analysts’ tasks are akin to “myth busting”? How can Power BI be used for these myth busting tasks? What if myth busting in…

Azure 101 Part (1): Understanding Affinity Groups

Azure comes with new keywords that many IT Pro and Developer may not be familiar with. Understanding the key concepts is important in designing and deploying Azure application, especially if We care about High Availability and Disaster Recovery. This post will observe the meaning and relationship between three important concepts: Affinity Groups, Availability Set, and Cloud Service.   Affinity Groups The idea of Affinity Groups is quite simple: any applications, services, and infrastructure should be in the same data center for best performance. Of course this also depends on the connection between locations, but keeping all of them in the same region will give faster accessibility than separate them to multiple region. An Affinity Groups is the way Azure ties various resources as close as possible, by keeping them in the same region. As the time of this writing, Azure data centers are located at the 9 areas as follow: North Central US South Central US East US West US South America North Europe West Europe East Asia South East Asia   Creating Affinity Groups An Affinity Groups can be created either in Management Portal or PowerShell. The setup is located under Settings menu of the portal. Once it is created,…

Automating SQL Server VM Provisioning on Azure with PowerShell

Azure IaaS provides a flexible way to fire up new VM without worrying about upfront infrastructure cost. We can provision new VM for DEV/TEST quickly, and proceed with development task. The common task I often do is installing new SQL Server VM, setting up, so it will be ready for testing bench or isolated development. It requires couple of clicks and wait, and sometime tedious. This article explains how to provision new SQL Server VM by using Azure Management portal. It is easy, but still require couple of click and wait between clicks. We can automate those tasks by using PowerShell, so everything can be done with an execution of a script file. This post also has step by step instruction on how to deploy simple SQL VM on Azure, but I will take another approach in this post. The script in this post will do the following stuffs in VM provisioning: Provision a stand alone SQL Server, this means does not join to AD domain Provision a storage account to store VHD Deploy the VM to specific Azure region, by supplying Affinity Group to control its location Since this is a stand alone deployment, there will be no Virtual Network…

Running WordPress with SQL Server on Azure

It has been more than a year this blog runs on Azure platform, since I moved from my Linux hosting (WordPress with PHP – MySQL) to Azure. It’s still WordPress (I love it) but sits on top of WAWS with MySQL. I have been experimenting to run WordPress on IIS with MySQL for quite a while, until I read that I can actually run WordPress with SQL Server instead of MySQL. I’m a SQL Server guy, and decide it to give it a try. Running it on SQL Server means I can do query, tuning, and everything on database that I really familiar with. Running it with SQL Server is even easier now in Azure by using special installer developed by Brandoo.   Creating Azure SQL Database So I started the deployment by creating an SQL database in Azure. It is and SQL Server engine, not MySQL. This is and empty database for WordPress storage in the next step. You may choose Quick Create or Custom, whichever is convenience. Specify the new server If You never create any before. Once the database is created, You need to take note of database name, SQL login, password and also server name. Those information will be…

Analyzing Perfmon with SQL Query (Part 2)

I wrote about how to import Perfmon file into SQL Server in previous post, and now I let’s see how to analyze it using SQL query. I will write couple of examples that I usually use. The exact query may varies depending on the requirement and server environment. The most common question are as follow: how high is CPU utilization? Do I have enough memory in the system? Is there bottleneck in the disk? A quick query will help to give high level idea, but in-depth analysis is required to pinpoint what’s actually happens in the system. High CPU does not always mean a CPU problem, until detail examination is done to other related counters. In many cases, We need to investigate SQL Server internal by using DMV or Profiler to get more comprehensive views. I will blog this separately, so let’s focus on how to mine the data from imported perfmon.   Simple Query: How’s my CPU and Memory? I wanted to see CPU and Memory information in the system. This is quite simple to do by querying the tables as follow: SELECT ct.CounterID, RTRIM(ct.ObjectName), RTRIM(ct.CounterName), CASE WHEN ct.[InstanceName] IS NULL THEN '' WHEN ct.[InstanceName] IS NOT NULL THEN…

Analyzing Perfmon with SQL Query (Part 1)

Performance counter is very useful for analyzing OS and SQL Server performance. Most DBA knows how to collect performance counter, so I won’t write it in this post. Just in case You don’t know, check Brent’s post here. Once the counters are collected, the next step is to analyze to find performance problem. I posted about Perfmon analysis with PAL , but in this post I will show different method by importing to SQL Server and query it T-SQL. Using T-SQL query has several advantages over PAL: You have full control on what counters to analyze and how to summarize (average, min. max, etc) You can compare several sets of data collection and see their trends. You can filter the query and build crosstab report to see the trending between different time interval So the steps to do it are as follow: Create empty database in SQL Server Setup ODBC System DSN Import Perfmon to SQL Server with relog tool Analyze perfmon by using T-SQL   Create empty database in SQL Server Create an empty database in SQL Server, as this will be a storage for imported data. Sizing is important because the data is quite big. In my case,…