Microsoft Certified

It has been a while since I last pursued a Microsoft certification.

After a few years working as a developer, I focused primarily on SQL Server data platform and business intelligence. I learned from work experiences, Microsoft articles as well as various blogs from the awesome SQL Server communities. To test my level of knowledge and exposure in this field, I also took the challenge of certification for Microsoft Certified Solution Expert for Data Platform and Business Intelligence.

As my role at work continue to evolve, I have been wearing multiple hats and at time switch hat. I found myself heavily involved in Azure AD, Okta, Azure cloud resources, Azure Devops, Github, App / API Gateway, etc.. in addition to SQL Server administration.

Access Office 365 Exchange Online Mailbox with Client Credential Flow

As of this writing, Microsoft has disabled the basic authentication to Exchange Online, the alternate more secure method to authenticate and access Exchange Online is via Client Credential flow using OAuth token. OAuth (OIDC) Client Credential flow is typically used for background (eg. windows services or daemon) process without interaction with user.

Microsoft provides different APIs to access Exchange Online. To authenticate against these APIs, we need OAuth access token with appropriate permissions when invoking these APIs. And this type of access token is obtained from Azure AD.

GitHub Create Private Fork of a Public Repository

When making a fork of a GitHub public repository, the fork repository is set up with public visibility. However, under certain circumstances, it may be desirable to make the fork repository with private or internal (in an organization) visibility.

Here is one way to indirectly 'forking' a repository with with private or internal visibility.

Create Sharepoint Site with Azure Function using PowerShell

Business Case

Create SharePoint site based on user requirement without SharePoint Admin manually performing the task.

Design

A SharePoint List is created and shared with designated users to enter the desired SharePoint site criteria. Using an Azure function with PnP PowerShell module (compatible with PowerShell core in Azure function) to periodically check if any new entry in the SharePoint List and create SharePoint site based on the user entered criteria.

Network Traffic Capture Without Installing Any Software

From time to time, there is a need to capture network traffic for troubleshooting on server. Some network tool like Wireshark is pretty popular for network capturing. However, these tool often need additional installation on the server, and depending on your security team rules, it may take days to be installed on the server.

There is an alternate way to capture network traffic on Windows OS without additional software installation. Using Netsh trace. Most of you may familiar with Netsh for different type of common purpose like firewall, http listener, network interface info, etc. Netsh could be also used to collect network trace.

Inserting and Hosting 551 Millions Records Cheaply - Case Study

Recently I worked on a pet project with cloud implementation. There was a dump file in text format with 551 million line of records which needs to be stored in a way where filtered results could be quickly returned from a query, and with a goal of low cost implementation.

The dataset is static. Large number of records. No relational requirement. The storage and querying has to be fast and low cost. Azure table storage seems to be a good candidate for this purpose. Azure table storage is a NoSQL key value store and is very cheap.

Windows Guy Learning Linux - Basic Command for Linux

If you have been working mostly with Microsoft technology stacks, switching from Windows and learning Linux could be an exciting and daunting journey. So far my journey to Linux has been fun and I love it!

Linux has GUI but mostly (almost entirely) for server and application administration are performed using command line. If you are on Windows workstation, PuTTY software could be used to ssh into Linux server.

Microsoft Recertification

Year 2016 is just around the corner. People are in holiday mood and in fact a lot of folks are on vacation at this time around. Project usually take this slow period into account and avoid any heavy personnel involvement and introduce changes only where necessary. This slow time of the year could be a great opportunity to expand knowledge and skills, and possibly look into certification / recertification.

SQL Replication - Subscriber On Different Domain

Setting up SQL Server replication with publisher / distributor on one domain and subscriber on another domain could be tricky. It could be even more interesting when these servers have the same host name. This blog post discuss issues encountered and the steps of addressing them.

SQL Server Replication - Case Study & Implementation

This post discuss of evaluating a business case, with designing and implementing SQL Server replication in a step-by-step tutorial to address the requirement.

Powershell Password Encryption & Decryption

One of the common task in PowerShell script design and execution is credential encryption requirement. Some privileged account is used and its credential need to pass to the script in order to access resources. It becomes crucial especially when the execution tasks are being delegated to other users or being automated. As storing the password as clear text is huge security risk and the last thing desired, here in this blog post we discuss a few options on storing the credential securely.

Query Hint and Plan Guide

Query execution plan generated for a SQL query significantly affect the query performance. Parameter sniffing, indexes, statistics and other factors are taken into consideration for SQL Server query optimizer to produce an optimal query plan for the execution within a timely manner. Sometimes, due to certain scenarios and limitation, the query plan generated may be suboptimal. There are multiple ways to address this problem, like modifying the way the query is written, or using query hint or even plan guides under certain circumstances. This blog post discuss an example of using a query hint as well as plan guide.

Query Performance Tuning Example

As DBA, application performance issue and complaints often thrown our way. It is often fun to troubleshoot those performance issue. When it has been identified that the problem lies on SQL Server, it get even more exciting. Here is one of the example of how I tuned a query a while ago and also some lessons learned.

One Important Aspect - Mentor

Looking back to my education and career journey, there were times when I felt like it was totally stagnant, and other times of leap and bound moment. I have came to realize the importance of a mentor.

Execute SSIS remotely - PowerShell

Since SQL Server 2012, Microsoft introduce a new way of interacting and storing SSIS package. SSIS packages are frequently executed in scheduling format often through SQL Server agent jobs. Today blog post will be focusing on remotely calling SSIS package stored in this new SSISDB Catalog.

SQL Server Database File - Date Modified

Recently, there was a discussion if the date modified of the database files shown in Windows explorer could be used to determine when the database was last used (or recently used). Often, this date is used to determine when a file (eg. word, excel) is last updated. Could that also be applied to SQL Serve database?

Microsoft Specialist - Implementing Microsoft Azure Architecture Solutions

I have been using Microsoft Azure with credit from MSDN subscription (Free credit! See the MSDN subscription section on this post) for sometimes now. Mostly for VMs and SQL Database (Microsoft Azure SQL Database). Recently I have also started exploring other Azure services like Azure Active Directory for cloud identity and access management, as well as deploying and implementing .NET web application to Azure Websites along with Visual Studio Online.

SSIS Connection Manager Properties

The SSIS packages stored in SSIS Catalog (introduced in SQL Server 2012, and also SQL Server 2014) allow configuration changes made on the project or the package connection managers, depending how the connection was setup. It is important to have a good understanding of how the values in these properties are being used.

Defunct File Cause SQL Server Upgrade Failure

In the previous post, we discussed how a data file or its filegroup becomes defunct. As making data file defunct may be desirable in certain situation to resolve immediate issue and allow the database continue to be operational, you may encounter some surprise problem when you are ready to move up to the next version during the SQL Server upgrade.

SQL Server - Defunct Filegroup and Defunct Data File

A data file becomes DEFUNCT when its respective filegroup is removed or when the data file or its filegroup is not included during the piecemeal restore in simple recovery model. Usually a filegroup can not be removed if the data file is not empty. However, a filegroup could be removed if one of its data file is not online. This post illustrates how a data file becomes DEFUNCT.