Finding Unused Resources Impacting Azure Costs

June 10, 2019 by Timothy Smith

To reduce Azure costs on unused and unnecessary resources, we should design with prevention in mind – considering whether we want to commit to reserved use or test with a pay-as-we-go model. We may experience situations where we already have many resources, but are unsure of their use – are these consistently used, sometimes used, never used? Before we can answer whether an unused resource (or what appears to be an unused resource) is unnecessary, we have to determine whether it’s used. In this tip, we’ll look at this challenge.

SQL Not Equal Operator introduction and examples

June 6, 2019 by Rajendra Gupta

This article explores the SQL Not Equal comparison operator (<>) along with its usage scenarios.

Handling Unused and Unnecessary Resources Impacting Azure Costs

June 5, 2019 by Timothy Smith

Two of the more challenging causes of an increase in Azure costs are unused and unnecessary resources. Unused and unnecessary resources may not always be the same, even though they can overlap. If we know the difference between these resource categories or when these resources categories overlap then we will see improvements in preventing these from adding to our costs. In addition, when we think about unused resources, we should consider options that we have with Azure to optimize for these, as an unused resource may still be necessary sometimes. In this tip, we’ll look at these topics to assist us with reducing our Azure use.

Get-DbaHelpIndex command in DBATools

June 5, 2019 by Rajendra Gupta

DBATools is an open-source PowerShell that contains a collection of useful commands. In this series of articles on DBATools (see TOC at the bottom) we performed installation of it. We also explored commands to do database backups, database restoration, Identity column maximum range threshold and create a SQL database.

Identity function tutorial in SQL Server

June 3, 2019 by Rajendra Gupta

This article explores the Identity function in SQL Server with examples and differences between these functions.

Tracking Azure Costs with Cost Management

June 3, 2019 by Timothy Smith

The Azure Portal offers the free tool Cost Management that we can use for managing Azure costs. As we’ll see, we can use this tool to organize how we manage our spending along with setting limits for thresholds to alert the appropriate members. While this tool can be useful for our organization, it has the potential to cause noise or disruptions, so we still want to review how we use it within our organization for managing the spending of our teams.

Get SQL Database details using DBATools

June 3, 2019 by Rajendra Gupta

In the series of articles on DBATools, (see TOC at the bottom) we are exploring useful DBATools command to perform administrative SQL tasks. In this article, we will explore useful commands to interact with SQL Server.

Certificate Management in SQL Server 2019

May 31, 2019 by Artemakis Artemiou

Introduction

Certificate Management in SQL Server 2019 has been enhanced a lot when compared with previous versions of SQL Server, and it is part of a large set of new features and enhancements in SQL Server 2019. The most significant enhancement is that that it now allows you to directly import SSL/TLS certificates into SQL Server, thus simplifying the entire process a lot.

AWS RDS – SQL Server Administration

May 30, 2019 by Ranga Babu

In this article, we will review common database administration tasks for AWS RDS (Relational Database Service) SQL Server instance. RDS does not provide access to some of the system procedures and tables that require advanced privileges. So, we cannot perform all the administration tasks on the RDS SQL Server instance.

Bulk-Model Migration in SQL Server Master Data Services

May 30, 2019 by Sifiso Ndlovu

Just over a year ago, I published How to migrate SQL Server 2017 Master Data Services Models into another server article, that essentially demonstrated the migration of a SQL Server Master Data Services (MDS) model from one MDS instance to another. Like many of my previously published articles, I have since used the aforementioned article as a technical reference (i.e. syntax lookup for using MDSModelDeploy.exe utility) during the implementation of MDS-related projects for my clients.

Creating Containers with PowerShell For Azure Cosmos DB

May 30, 2019 by Timothy Smith

In many situations, we will develop, test or prove new concepts by horizontally scaling new SQL API containers in Azure Cosmos DB over possibly using existing containers. As we’ve seen in previous tips, we can create and remove Cosmos database accounts and databases by using the Azure Portal or PowerShell’s Az module along with making some updates to the configuration, such as the RUs for performance reasons. Similarly, we can create and remove a container through the Azure Portal along with creating and removing the container with PowerShell’s Az module.

Why you should cleverly name Database Objects for SQL Unit Testing

May 29, 2019 by Haroon Ashraf

This article is focussed on clever database object naming from both development and SQL unit testing point of view.

This article also highlights the importance of naming database objects going through different development transitions including the SQL unit testing phase due to the agile nature of requirements.

SQL varchar data type deep dive

May 29, 2019 by Gauri Mahajan

In this article we’ll review the SQL varchar data type including a basic definition and overview, differences from varchar(n), UTF-8 support, Collation, performance considerations and more.

Increasing or Decreasing Scale for Azure Cosmos DB

May 28, 2019 by Timothy Smith

Now that we can create and remove an Azure Cosmos DB and databases that we can use for automation purposes, along with obtaining some information about these accounts, we’ll look at making some changes to these accounts for contexts related to performance. It’s possible that our unit and security testing or development with proof-of-concepts may face performance problems where we need to upgrade the settings of our database account. In this tip, we’ll be working with the SQL API database layer in a Cosmos database account by building on our get, create and remove automation to update its performance.

SQL examples for beginners: SQL SELECT statement usage

May 27, 2019 by Esat Erkec

SQLSELECT statements are used to retrieve data from the database and also, they populate the result of the query into the result-sets. The SQL examples of this article discourse and explain the fundamental usage of the SELECT statement in the queries.

Read Scale Availability Group in a clusterless availability group

May 27, 2019 by Rajendra Gupta

In this article, we will explore configuring Read Scale Availability Group that does not require a failover clustering configuration. It helps to scale read-only connections to the secondary replica in a cluster less configuration.

Creating and Removing Databases with PowerShell In Azure Cosmos DB

May 27, 2019 by Timothy Smith

Our testing or development may call for dynamic creation on the database level for Azure Cosmos DB rather than the account level. As we’ve seen with dynamically working with a Cosmos database account using PowerShell, we can create, remove, and obtain properties of the account. Identically, we can do this on the database level as well and we may use this in testing if we need the same Cosmos database account for other testing purposes. Development situations may also involve use cases where we want to test a concept and dynamically create a database within our Cosmos database account. In this tip, we’ll look at working with our Azure Cosmos database account on the database object level where we do nothing to manipulate the account itself, only add databases to the account once it’s been setup.

SQL Server Statistics in Always On Availability Groups

May 27, 2019 by Rajendra Gupta

Introduction to SQL Server Statistics

SQL Server Statistics are an essential part of query performance in SQL Server. They help the query optimizer to prepare a query plan using the distribution of values in participating rows. If we do not have updated statistics, it might lead to resource intensive query execution plan. For example, for a large table having a billion records, SQL may choose to have an index scan instead of an index seek.

Using Power BI Free License to Embed Power BI into Dynamics 365

May 24, 2019 by Sifiso Ndlovu

Whilst Power BI continues to be the leading data visualisation tool in Gartner’s BI quadrant, Dynamics 365 for Customer Engagement (formerly Dynamics CRM) remains one of popular business platforms for driving sales and facilitating customer communications. Thus, an integration of Power BI into Dynamics 365 for Customer Engagement ensures that you get to explore your Dynamics 365 for Customer Engagement data via a powerful and rich data visualisation tool. However, as indicated in Figure 1, Dynamics 365 for Customer Engagement plans often come with a Power BI free license thereby limiting the type of Power BI features available to you. In this article, we look at how you can get the best out of your Power BI free license for the purposes of embedding Power BI artefacts into Dynamics 365 for Customer Engagement. Going forward, any references to Dynamics 365 for Customer Engagement plan in this article will be shortened to Dynamics 365.

SQL Server in Kubernetes Cluster using KOPS

May 24, 2019 by Ranga Babu

In this article, we will review how to create a Kubernetes cluster in AWS using KOPS, provision Elastic Block Store (EBS) as persistent volume to store the database files and deploy SQL Server in the K8s cluster.

Getting and Updating Connection Information for Azure Cosmos DB

May 24, 2019 by Timothy Smith

After we set up our Azure Cosmos DB, we may want to get, add to, or update existing properties. We may use some of the get functionality that PowerShell provides to dynamically save values to encrypted configuration files or tables that we use for application purposes and this functionality could be added to the creation of the Cosmos database account, or a separate step in addition to the creation. In secure contexts, this ensures security without the properties after passing through human eyes since they are saved directly to an encrypted location. In the same manner, we may want to regenerate the keys for the account and save the connection strings with the new keys.

Creating a SQL Server Database using DBATools

May 23, 2019 by Rajendra Gupta

DBATools is an open source PowerShell module with useful commands to do the administrative task in SQL Server. In my earlier articles on DBATools (see TOC at the bottom) we explored installation and performing database backups, restoration, and validation with DBATools.

SQL Server Auditing Best Practices

May 23, 2019 by Ahmad Yaseen

In this article on SQL Server Auditing Best practices, we will show the best practices that should be followed to have a successful auditing strategy that complies the company audit policies and requirements.

SQL Server in Azure Kubernetes Service (AKS)

May 22, 2019 by Ranga Babu

In this article, we will review how to create a Kubernetes cluster in Azure Kubernetes Service, provision the persistent volume to store the database files and deploy SQL server on Kubernetes cluster.

SQL Unit Testing: Working with exceptions

May 21, 2019 by Esat Erkec

With this article, we will complete our journey with SQL Unit Testing. But first, let’s remember the main theme of the SQL Unit Testing again briefly. With the help of the SQL Unit Testing, we can detect and find out the flaws and bugs in database projects. This reduces the possible loss of time and money and reputation during the use of the product. However, it should be kept in mind that no matter how many tests are carried out, there will still be some bugs in the project, but SQL unit tests provide a greater degree of confidence. The fact that there are no bugs or flaws in the application at that time does not mean that the application is perfect. We cannot test all combinations and probabilities of the database objects. That is why in real life, considering certain parameters (risk, priority, etc.) we must ensure that a “reasonably sufficient” test is done.