Ranga Babu
Parameter group

AWS RDS – SQL Server Administration

May 30, 2019 by

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.

Read more »
Sifiso W. Ndlovu
Successful Execution of SSIS components

Bulk-Model Migration in SQL Server Master Data Services

May 30, 2019 by

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.

Read more »
Timothy Smith
In Settings under our container in our Azure Cosmos DB, we see the options that we’ll use for our template

Creating Containers with PowerShell For Azure Cosmos DB

May 30, 2019 by

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.

Read more »
Timothy Smith
We see the same restrictions in the Azure Portal

Increasing or Decreasing Scale for Azure Cosmos DB

May 28, 2019 by

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.

Read more »
Timothy Smith
The Azure Portal view of our SQL API database in our Azure Cosmos DB

Creating and Removing Databases with PowerShell In Azure Cosmos DB

May 27, 2019 by

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.

Read more »
Rajendra Gupta
Execution plan and statistics

SQL Server Statistics in Always On Availability Groups

May 27, 2019 by

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.

Read more »
Sifiso W. Ndlovu
Choose the Power BI Dashboard you want to add

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

May 24, 2019 by

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.

Read more »
Timothy Smith
We can regenerate keys in the Azure Portal for our Azure Cosmos DB

Getting and Updating Connection Information for Azure Cosmos DB

May 24, 2019 by

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.

Read more »
Esat Erkec
SQL unit testing - tSQLt framework tsqlt.ExpectException result image

SQL Unit Testing: Working with exceptions

May 21, 2019 by

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.

Read more »
Timothy Smith
The options we see when we select the download template for automation

Creating and Removing Azure Cosmos DBs with PowerShell

May 21, 2019 by

When managing Azure Cosmos DB, we can use the Azure portal and create resources through the interface or use the command line in the portal and create resources. PowerShell also supports some functionality for creating and managing these resources, which can help development teams automate the creation of these databases for quick creations, unit and security tests, removals if the resources aren’t required following the tests. We can also use these scripts for creating templates that we may use in multi-scaling creations (like databases in a group designed for horizontal scale). Generally, in one-off situations, the Azure Portal will suffice for deployments if there is a cost to develop automation that is not required. In this tip, we’ll look at the process of creating a blank and removing the same Azure Cosmos DB.

Read more »
Timothy Smith
By querying Azure cost information, we can share with development and improve to reduce costs

Extract Azure Costs Using PowerShell

May 20, 2019 by

With strong organization and design for our development teams, cloud infrastructure and security considerations, we’ll now extract Azure cost information that we can share with our organization. In addition, we will see that we can retain this information if needed to track growth (or reduction) in costs. This step is important as it will allow our teams to have an insight into their development and it will also be another audit we can use on the security side to catch unusual growth (or significant reductions) in resource costs that may be the result of an attacker. Our ultimate goal with tracking these costs and sharing them with teams is to improve our development and possibly re-organize it as needed, giving us the ability to further reduce our spending.

Read more »
Timothy Smith
Compare auditing Azure costs using self-auditng on the same App Service versus an independent server auditing

User Security and Risks to Azure Costs

May 16, 2019 by

We’ve looked at both the organization and development side of managing Azure costs. One risk we have is attackers who compromise an account and mis-scale resources (such as scaling up), driving up our costs. Another scenario is attackers scaling resources too low that affects client’s ability to do their work (performance problems) – a separate risk that may result in lower costs on the cloud side, but higher costs against our reputation. A third risk is reconnaissance of our Azure use: this allows the attackers to get information about our design and later make a wide range of attacks that will appear as normal to us – in this case, Azure costs may be only one of the impacts with other impacts being as severe.

Read more »
Ranga Babu
Direct recursive triggers in SQL Server

Triggers in SQL Server

May 15, 2019 by

In this article, we will review triggers in SQL Server, different types of trigger events, trigger order and NOT FOR REPLICATION in triggers. A trigger is a database object that runs automatically when an event occurs. There are three different types of events.

Read more »