How to Add Contained Databases in SQL Server 2016 Availability Group - SQL Server 2016 DBA Tutorial

Scenario:

You are working as SQL Server DBA and you are asked to add a Contained Database in AlwaysOn Availability Group. What steps you will follow?

Solution:

In this video demo, you will go through all the steps you need to add a Contained Database to AlwaysOn Availability Group.


Items covered

  • How to create Contained Databases in SQL Server 2016?
  • How to add Contained Database into Availability Group?
  • How to Connect to Contained Database using Availability Listener?
  • How to Failover Contained Database Availability Group?

How to Create Availability Group with Automatic Seeding in SQL Server 2016 - SQL Server 2016 DBA Tutorial

Scenario:

How to create Availability Group with Automatic Seeding in SQL Server 2016?

Solution:
The video demo will cover below items to perform Automatic Seeding in SQL Server 2016 for AlwaysOn Availability Group.


  1. What is Automatic Seeding?
  2. What are the pre-requisites of Availability Group with Automatic Seeding?
  3. How to prepare database to be added in Availability Group? 
  4. How to create Availability Group with Automatic Seeding using T-SQL? 
  5. What are the advantages of Availability Group with Automatic seeding?
  6. What are the limitation of Availability Group with Automatic Seeding? 
  7. How to provide Availability Group Create any Database permissions? 


How to find duplicate records by using Group by and Having clause in SQL Server - SQL Server / TSQL Tutorial Part 132

Scenario:

You are working as SQL Server developer. You need to write the query for dbo.Customer table that should return the duplicate records and count of duplicate record.


Solution:

You can use Group by and Count aggregate function to find all the duplicate records in table and count.

Let's say that we have dbo.Customer table with below definition and data. We would like to find the record if it is duplicate by columns FName,LName and CountryShortName.


Create table dbo.Customer
 (Id int,
  FName VARCHAR(50),
  LName VARCHAR(50),
  CountryShortName CHAR(2),
  SaleAmount Int)
GO
--Insert Rows in dbo.Customer Table
insert into dbo.Customer
Values (
1,'Raza','M','PK',10),
(2,'Rita','John','US',12),
(3,'Sukhi','Singh',Null,25),
(4,'James','Smith','CA',60),
(5,'Robert','Ladson','US',54),
(6,'Alice','John','US',87),
(6,'Raza','M','Pk',Null)


Let's write our query, we have include all the columns in select list with count(*) and then group by the same columns. At the end we will be using Having clause to filter the record which are duplicate.


SELECT fname, 
       lname, 
       countryshortname, 
       Count(*) AS RecordCount 
FROM   dbo.customer 
GROUP  BY fname, 
          lname, 
          countryshortname 
HAVING Count(*) > 1

As from data we can see that the only record which is duplicate by FName,LName and Country short name is  'Raza','M','PK'and have total two records as returned by our query.

How to find duplicate records by using Group by and Having Clause in SQL Server



How to apply Having Clause with Group by in Select Query - SQL Server / TSQL Tutorial Part 131

Scenario:

You are working as SQL Server developer, you are asked to write a query that should return Total SaleAmount from dbo.Customer table by CountryShortName. You are also asked to filter the records where Total SaleAmount by CountryShortName is greater than 10.


Solution:

From above scenario, you have noticed couple of things. First we need to sum the SaleAmount. Second we need to group by the SaleAmount by CountryShortName. The Last thing we need to filter those records after calculating the sum and only returns where total SaleAmount is greater than 10. We can not use Where clause here as where will filter the records before Group by. SQL Server provide us Having clause that we can use to filter the records after group by.

Let's create dbo.Customer Table with below records and then write our query by using Group by and Having clause.


Create table dbo.Customer
 (Id int,
  FName VARCHAR(50),
  LName VARCHAR(50),
  CountryShortName CHAR(2),
  SaleAmount Int)
GO
--Insert Rows in dbo.Customer Table
insert into dbo.Customer
Values (
1,'Raza','M','PK',10),
(2,'Rita','John','US',12),
(3,'Sukhi','Singh',Null,25),
(4,'James','Smith','CA',60),
(5,'Robert','Ladson','US',54),
(6,'Alice','John','US',87),
(6,'John',Null,'US',Null)



Let's write our query by using Group by and then use Having to filter the record where Sum(SaleAmount) is greater than 10.

SELECT countryshortname, 
       Sum(saleamount) SaleAmountByCountry 
FROM   dbo.customer 
GROUP  BY countryshortname 
HAVING Sum(saleamount) > 10 

How to filter aggregated data by using Having clause  in SQL 



Video Demo : How to use Having Clause in SQL Select Statement






How to Configure Availability Group in SQL Server 2016 - SQL Server 2016 DBA Tutorial

Scenario:

How to Configure Availability Group in SQL Server 2016?

Solution :

The video demo will walk you through all the steps required to create / configure AlwaysOn availability Group in SQL Server 2016. Below items are covered.


  • What are requirements and best practices of configuring Availability group in SQL Server 2016?
  • How to prepare Windows node to be added as part of AG 2016 Cluster
  • How to Create Windows Cluster step by step 
  • What are the best practices of creating Availability Group Windows Cluster
  • How to Configure Availability Group Role in SQL Server 2016
  • How to resolve error like 
  • User/Cluster doesn’t have permission to create computer object in Active Directory or DNS
  • Node 1 cannot communicate with Node2 
  • Joining Database takes forever and throw permission error
  • Missing End Point in Availability Group properties

Part 1:

Part 2:


Part 3:


How to Install and Configure SQL Server 2016 Master Data Services - SQL Server 2016 DBA Tutorial

Scenario:

How to install Master Data Services in SQL Server 2016?

Solution : 

The video demo shows how to install SQL Server Master Data Services Step by Step in SQL Server 2016.


  • How to Install and Configure SQL Server 2016 Master Data Services 
  • How to install SQL Server 2016 Master Data Services
  • What are SQL Server 2016 Master Data Services
  • What are pre-requisites of SQL Server 2016 Master Data Services and how to install them step by step
  • MDS Database Configuration
  • MDS 2016 Web Configuration
  • Best practices of Master Data Services installation and Configuration
  • How to provide permission to Master Data Services
  • How to connect to MDS 2016 Web interface


How to Install SQL Server 2016 Management Studio - SQL Server 2016 DBA Tutorial

How to Install SQL Server 2016 Management Studio
1.     How to install SQL Server 2016 Management Studio using SQL Server 2016 Media
2.     How to direct download SSMS 2016
3.     What pre-requisites of SQL Server gets installed during installation


Video Demo : How to Install SQL Server Management Studio( SSMS) 




Understand Group by Clause in SQL Server - SQL Server / TSQL Tutorial Part 130

Scenario: 

You are working as SQL Server Developer. You have a dbo.Customer table which has CountryShortName and SaleAmount. You are asked to write a query that should return Sum of SaleAmount , count of records by CountryShortName.


Solution:

Group by clause is often used with aggregate functions such as Sum, Avg,Count,Max,Min to group the result set by column/s.

Let's create our sample table with some data and write our query with Group by to answer our question.

Create table dbo.Customer
 (Id int,
  FName VARCHAR(50),
  LName VARCHAR(50),
  CountryShortName CHAR(2),
  SaleAmount Int)
GO
--Insert Rows in dbo.Customer Table
insert into dbo.Customer
Values (
1,'Raza','M','PK',10),
(2,'Rita','John','US',12),
(3,'Sukhi','Singh',Null,25),
(4,'James','Smith','CA',60),
(5,'Robert','Ladson','US',54),
(6,'Alice','John','US',87),
(6,'John',Null,'US',Null)


Let's write our query by using Sum, Count and Group by Clause

SELECT Sum(saleamount) AS TotalSaleByCountry, 
       Count(*) AS RecordCountByCountry, 
       countryshortname 
FROM   dbo.customer 
GROUP  BY countryshortname

How to use Group by Clause in SQL Server


You can also use multiple columns in group by clause. think about if our table would have states and you would like to group by CountryShortName and State, You would simple include State in query as shown below.


SELECT Sum(saleamount) AS TotalSaleByCountry, 
       Count(*) AS RecordCountByCountry, 
       countryshortname,
       [State]
FROM   dbo.customer 
GROUP  BY countryshortname,[State]





Video Demo: What is Group by Clause in SQL Server


How to Get Max and Min values from a Table by using Aggregate Function - SQL Server / TSQL Tutorial Part 129

Scenario:

Let's say you are working as SQL Server Developer, you have dbo.Customer table with SaleAmount. You are asked to write a query that should return Max SaleAmount and Min SaleAmount from the dbo.Customer table.


Solution:

You can use Max and Min aggregate functions in SQL Server to find the Maximum and Minimum values.

Let's create dbo.Customer Table with sample data so we can use Max and Min functions.


Create table dbo.Customer
 (Id int,
  FName VARCHAR(50),
  LName VARCHAR(50),
  CountryShortName CHAR(2),
  SaleAmount Int)
GO
--Insert Rows in dbo.Customer Table
insert into dbo.Customer
Values (
1,'Raza','M','PK',10),
(2,'Rita','John','US',12),
(3,'Sukhi','Singh',Null,25),
(4,'James','Smith','CA',60),
(5,'Robert','Ladson','US',54),
(6,'Alice','John','US',87),
(6,'John',Null,'US',Null)

Let's run the below query with Max and Min functions to finder the answer of our question.

SELECT Max(saleamount) AS MaxSaleAmount, 
       Min(saleamount) AS MinSaleAmount 
FROM   dbo.customer 

How to use Max and Min function in SQL Server




How to use Sum, Avg and Count in Select Statement - SQL Server / TSQL Tutorial Part 128

Scenario:

Let's say that you have dbo.Customer table with SaleAmount column and you are asked to write a query that should return you sum of SaleAmount, Average of SaleAmount and Count of all the records.

Solution:

You can use Aggregate functions such as Sum, Avg and count in TSQL to find the answer of your question.

Let's create dbo.Customer Table with sample data so we can use Sum, Avg and Count aggregate functions.



Create table dbo.Customer
 (Id int,
  FName VARCHAR(50),
  LName VARCHAR(50),
  CountryShortName CHAR(2),
  SaleAmount Int)
GO
--Insert Rows in dbo.Customer Table
insert into dbo.Customer
Values (
1,'Raza','M','PK',10),
(2,'Rita','John','US',12),
(3,'Sukhi','Singh',Null,25),
(4,'James','Smith','CA',60),
(5,'Robert','Ladson','US',54),
(6,'Alice','John','US',87),
(6,'John',Null,'US',Null)


Let's run our query to find sum of SaleAmount, Avg of SaleAmount and Count of records.

SELECT Sum(saleamount) AS TotalSale, 
       Avg(saleamount) AS AvgSale, 
       Count(*)        AS SaleRecordCount 
FROM   dbo.customer 


How to use Sum, Avg and Count Aggregate Functions in SQL Server




How to Install and Configure SQL Server 2016 Analysis Services - SQL Server 2016 DBA Tutorial

Scenario:

You are working as SQL Server DBA and you need to install SQL Server 2016 Analysis Services. How would you perform SSAS 2016 installation.


The below video demo will show you all the steps you need to perform SSAS installation.
1. SSAS 2016 installation step by step
2. What are pre-requisites of SQL Server 2016 Analysis Services
3. How to add SSAS 2016 to an existing SQL Server
4. When to choose Multidimensional Data and Mining mode
5. When to Choose Tabular Mode
6. When to Choose PowerPivot Mode
7. SSAS 2016 Installation best practices
8. SSAS 2016 Configuration best practices
9. How to connect to Analysis Services using SQL Server Management Studio


Video Demo : Install SSAS 2016 Installation


How to Install and Configure SQL Server Reporting Services 2016 SSRS - SQL Server 2016 Tutorial

Scenario: 

How to install and Configure SQL Server 2016 Enterprise Reporting Services?

In this video you will learn below items.
1. How to install SSRS 2016 step by step
2. What are the pre-requisites of SQL Server 2016 Reporting Services
3. SQL Server 2016 Reporting Services installation best practices
4. SQL Server 2016 Reporting Services Configuration best practices
5. Brief introduction of Scale-out deployment of Reporting services
6. Brief introduction of new features such as Power BI Integration


Video Demo : How to Install SSRS 2016 Step by Step from scratch

MariaDB : How to change Root Password for MariaDB

Scenario:

You are working as MariaDB / MySQL DBA and you need you change the password for root. How would you do that?

Solution:

You can use mysqladmin command to change the password for root user. use below statement to change the password

shell > sudo mysqladmin -u root -p password mynewpassword

Once you will hit enter, you will be asked to provide old password for root. Provide the password for root and hit Enter. 

You are all set to login to MariaDB by using new root password. Use below command to login to MariaDB


shell> mysql -u root -p
provide new password, in my case it was mynewpassword. 



Video Demo : How to change Root password in MariaDB or MySQL


How to Install SQL Server 2016 Enterprise Step by Step - SQL Server 2016 Tutorial

Scenario:

How to install SQL Server 2016 Enterprise Step by Step?

Solution:

In this video, we will learn how to install SQL Server 2016 Enterprise step by step. Following items are covered in this video.
  • What are the pre-requisites of SQL Server 2016 Enterprise
  • Brief introduction of new features in SQL server Enterprise 2016 such as Polybase
  • How to install JRE as a pre-requisites of SQL Server 2016 Enterprise Polybase new feature
  • New Tempdb configuration in SQL Server 2016 Enterprise
  • What are the best practices to install SQL server 2016

Video Tutorial : How to Install SQL Server Enterprise in real time 

MariaDB - How to Restore .sql file to Database in MariaDB or MySQL

Scenario: 

In our last post we created the logical backups of a database by using mysqldump. Now we need to restore that .SQL file to database.

Solution:

Login to MariaDB or MySQL database. Create an empty Database by using "create database DatabaseName" statement. In my case I create database ABC.

Fig 1: Create database in MariaDB or MySQL

Once you exited form MariaDB or MYSQL, on Shell provide below statement to restore .sql file to database. In my case I used empty database ABC.

shell> mysql -u root -p DatabaseName< sqlfile.sql




MariaDB - mysqldump: Got error: 1045: "Access denied for user 'username'@'localhost' (using password: NO)" when trying to connect

Scenario:

Today when was trying to take logical backup of MariaDB database by using below 
Shell> mysqldump DatabaseName > DatabaseName.sql

got below error

Shell> mysqldump MariaDB>MariaDB1.sql
error: Found option without preceding group in config file: /etc/my.cnf.d/server.cnf at line: 6
mysqldump: Got error: 1045: "Access denied for user 'username'@'localhost' (using password: NO)" when trying to connect

Solution:

Checked the server.cnf file but could not find anything missing. Found out that if you will use root with password that will work. Used the below statement and it worked.


Shell> mysqldump -u root -p DatabaseName > BackupFileName.sql