Welcome To TechBrothersIT: 2017

How to Grant all the permissions to User in MariaDB

How to Grant all the permissions to User in MariaDB explains all the steps which are required to create a user in MariaDB and provide him all the permissions like root in MariaDB.

How to Create/Drop User in MariaDB - MariaDB Admin Tutorial

In this video demo, you will learn how to create and drop user in MariaDB.

Below items are covered in video

How to Create/Drop User in MariaDB video demo shows
How to create user in MariaDB without password
How to create user in MairaDB with Password
How to check existing users in MariaDB
How to login to MariaDB by using new user account
How to drop the user account in MariaDB

How to Migrate from MySQL to MariaDB on Linux CentOS - MariaDB Admin Training

How to migrate from MySQL to MariaDB on Linux CentOS video demo explains all the steps which you need to take to migrate from MySQL 5.6 to MariaDB 10.2.

Remove all MySQL installed packages
The steps are below. For Script visit www.techbrotherstutorials.com
Check the version of MySQL
Create MariaDB.Repo
Stop MySQL Service
Install MariaDB 10.2 to replace MySQL
Login to MariaDB and Validate databases

How to Upgrade MariaDB 10.1 to MariaDB 10.2 on CentOS server

How to Upgrade MariaDB 10.1 to MariaDB 10.2 on CentOS server is detailed video demo how to upgrade MariaDB from 10.1 to 10.2. Below items are covered in demo

Run the Installation with new version 10.2
Check the Current Version of MariaDB
Stop MariaDB (Mysql) service
Uninstall MariaDB 10.1
Update the Repo file with new version
Start MariaDB service
Run mysql_upgrade to update databases from MariaDB 10.1 to MariaDB 10.2
Check the version of MariaDB
Validation

How To Create a Galera Cluster with MariaDB 10.2 on CentOS Servers - MariaDB Admin Tutorial

How to Create a Galera Cluster with MariaDB 10 2 on CentOS Servers video demo covers below items

How to check Linux version for MariaDB installation
Create or Edit MairaDB.repo
Install MariaDB Server and MariaDB Client
Start MariaDB Service
Run secure Installation of MariaDB
Log into MariaDB by using root and create database to test if installation of MariaDB went fine.
How to make changes to sever.cnf to enable Galera Cluster
How to start Galera Cluster on firest Node
How to start MariaDB service on 2nd node to join Galera Cluster
Create Database on each of the node and check if Replication is working fine in Galera Cluster.

How to install and configure Galera Cluster with MariaDB step by step

How to Uninstall MairaDB from Linux CentOS

The video demo explains all the steps that you need to take to Uninstall MairaDB from Linux CentOS.

How to Uninstall MariaDB from CentOS step by step

How To Move a MariaDB Data Directory to a New Location on Linux

The demo video covers all the steps which are required to move MariaDB data Directory to New Location.

Below are the items covered in the demo video. For scripts you can visit www.techbrotherstutorials.com

Check the current Location of Data Directory in MariaDB
How to Stop the MariaDB Service
Check the MariaDB service status by using below command
Copy Entire mysql directory to new location in MariaDB
How to edit my.cnf configuration file in MariaDB to change data directory
How to Start MariaDB Service
Create a database to test new location is working fine in MariaDB after data directory moved to new location

How to Install MariaDB on Linux CentOS Step by Step

How to Install MariaDB on Linux Centos, video covers these topics

Create or Edit MairaDB.repo
How to check Linux version for MariaDB installation
Install MariaDB Server and MariaDB Client
Start MariaDB Service
Run secure Installation of MariaDB
Login to MariaDB and validate if everything looking good by creating Database, creating table and inserting data into table.

How to Install MariaDB on Windows 2012 Server Step by Step

How to Install MariaDB on Windows 2012 Server video demo explains all the steps from downloading to installation of MariaDB on Windows Server.

How to Uninstall MariaDB from Windows 10

Step by Step video demo "How to Uninstall MariaDB from Windows 10".

How to Change MariaDB Data Directory on Windows 10

In this video you will learn how to change data directory for MariaDB from current/default to new data directory.
Below steps are required when you perform this action.

Find current/default data directory location
Stop MySQL Service
Check my.ini file
Update my.ini to new Data Directory
Move existing files to new Data Directory
Restart MySQL Service
Validate if Data Directory is changed successfully by creating Database and Test tables

How to Install MariaDB on Windows 10

MariaDB Administration Tutorial

How to Install MariaDB on Windows 10

TechBrothers has started a new Tutorial " MariaDB Administration". This is first video of Tutorial which covers below topics

How to download MariaDB for Windows Installation
How to Run Installation Process for MariaDB on Windows 10.
How to provide location for MariaDB installation directory
Provide the Root Password during MariaDB installation on Windows 10

Choose the features while installing MariaDB on Windows 10

MySQL Client
MySQL Database service
HeidiSQL Third Party interface to connect to MariaDB

At the end of the video, I showed

How to create the database by using MySQL Client interace
How to connect to HeidiSQL and connect to MariaDB
How to use mysql -uroot -p to connect to MariaDB

How to find SQL Server Instance Name by using TSQL

Scenario:

Often as SQL Server DBA or SQL Server Developer we have to find the SQL Server instance name. Below query can be used to find out the SQL Server Instance name.

Solution:

You can use global variables @@servername and @@sqlservicename to find out the SQL Server Instance name.

SELECT @@servername AS servername, @@ServiceName AS sqlservicename

--If SQL Server is installed in Default
ServerName SQLServiceName
YourComputerName MSSQLSERVER

--If SQL Server is intalled as Named Instance
ServerName SQLServiceName
YourComputerName\InstanceName InstanceName

--If SQL Server is intalled in ClusterMode
ServerName SQLServiceName
NetworkName\InstanceName InstanceName

SSRS Error : The user or group name 'Group Name' is not recognized.

Scenario:

Today I got the request to provide permission to a AD Group on one of the SSRS report. When tried to provide browse or any other permissions such as Content Manager, My Reports, Publisher or Report Builder, got below error.

The user or group name 'Group Name' is not recognized.

Solution:

You need to go to Active Directory user and Groups and then check the properties of your Group.

Make sure Group Scope is Global or Universal and Group Type is security. I had it distribution and that is why SSRS was not able to recognized the AD group.

Once I changed the Group Type to Security, SSRS was able to recognize the AD group and was able to provide required browse permission.

SSRS Error Solution: The user or group name '' is not recognized.

How to Setup Peer To Peer Replication in SQL Server 2016 - SQL Server 2016 DBA Tutorial

Scenario:

You are working as SQL Server DBA. You are asked to setup Peer to Peer replication in SQL Server 2016. What steps you will follow to perform Peer to Peer Replication in SQL Server?

Solution:

As per Microsoft here is the definition

Peer-to-peer replication provides a scale-out and high-availability solution by maintaining copies of data across multiple server instances, also referred to as nodes. Built on the foundation of transactional replication, peer-to-peer replication propagates transactionally consistent changes in near real-time. This enables applications that require scale-out of read operations to distribute the reads from clients across multiple nodes. Because data is maintained across the nodes in near real-time, peer-to-peer replication provides data redundancy, which increases the availability of data.

In this video you will learn below items

How to setup Peer to Peer Replication in SQL Server 2016

In this video you will learn

1) Identifying databases to replicate

2) Synchronizing replication databases on all nodes

3) If changes happens after sync , taking backup again for the setup

4) All nodes are distributor or separate distributor

5) What is publisher, subscriber and distributor in Peer to Peer replication

6) What are steps to setup Peer to Peer Replication in SQL server 2016

Video Demo: Step by Step setup of Peer to Peer Replication in SQL Server 2016

How to Create Snapshot Replication in SQL Server - SQL Server 2016 DBA Tutorial

Scenario:

You are working as SQL Server DBA. You are asked to perform Snapshot Replication in SQL Server 2016. What steps you will follow to perform Snapshot Replication setup?

Solution:

Video demo explains all the important points those are required to perform Snapshot Replication in SQL Server 2016. Demo covers

What is Snapshot Replication in SQL Server
When you use Snapshot Replication
What are the reasons we need to use Snapshot Replication
How Snapshot Replication works
What are Snapshot Agent, Distribution Agent and Merge Agent
Go through Security Best Practices for Snapshot Replicaiton Setup
How to use Replication Monitor in SQL Server
How to use Replication Monitor in SQL Server for Snapshot Replication

How to setup Snapshot Replication in SQL Server 2016

How to create Merge Replication in SQL Server 2016 - SQL Server 2016 DBA Tutorial

Scenario:

How to create Merge Replication in SQL Server 2016?

Solution:

In this video demo, you will learn how to create Merge Replication in SQL Server 2016.

1. What is Merge Replication in SQL Server 2016?
2. How to setup Merge Replication in SQL Server 2016?
3. How to setup Merge Replication using separate Distributor?
4. What are security best practices of Merge Replication?
5. How to monitor Merge Replication using Replication Monitor?

How to setup Merge Replication in SQL Server 2016

How to setup Transactional Replication in SQL Server 2016 - SQL Server 2016 DBA Tutorial

Scenario:

How to setup Transactional Replication in SQL Server 2016?

Solution:

In this demo, you will learn how to setup Transactional Replication in SQL Server 2016. Demo covers below topics

1. What is Transactional Replication in SQL Server 2016?
2. How to setup Transactional Replication in SQL Server 2016 step by step?
3. What are the Security Best Practices of Replication?
4. How to Setup Transactional Replication in SQL Server 2016 using different Distributor?
5. How to monitor Transactional Replication using Replication Monitor?
6. How to Troubleshoot replication issues using Replication Monitor?
7. Resolution to the error “Server is not enabled as a publisher on the Distributor”

How to setup Transactional Replication in SQL Server 2016

Understanding Replication in SQL Server 2016 - SQL Server 2016 DBA Tutorial

Scenario :

What is Replication in SQL Server?

Solution :

This video demo can be used to Understanding Replication in SQL Server 2016. It explains all the components for Replication step by step.

Demo covers below points
1. What are replication components in SQL Server?
2. What is Publisher?
3. What is Distributor?
4. What is Subscriber?
5. Overview of Replication Agents
• Snapshot Agent
• Distribution Agent
• Merge Agent
• Log Reader Agent
• Queue Reader Agent
6. Replication Jobs on SQL Server 2016 Agent
7. How to troubleshoot Replication using Replication Agents Jobs?

What is Replication in SQL Server, What are important components of Replication in SQL Server.

How to find Number of Processors, Cores and Logical Processors on Machine

Scenario:
You need to find the Number of CPUs, Cores and Logical Processors on a computer, how would you do that?

Solution:

There are multiple ways you can find Number of CPUs, Number of Cores and Number of Logical Processors.

1) Go to Start and then in Run type msinfo32 and hit Enter.

You will see detail information about System. Look for Processor and you will be able to see information like below depending upon your computer configuration.

2) You can also go to Command Prompt and then paste below statement, you will get same information.

WMIC CPU Get DeviceID,NumberOfCores,NumberOfLogicalProcessors

How to Install SQL Server 2016 in Cluster Mode - SQL Server 2016 DBA Tutorial

Scenario:

You are working as SQL Server DBA, you are asked to install SQL Server 2016 in cluster mode, what steps you will take to perform the installation?

Solution:

In this video demo, you will learn all the steps you need to take to install SQL Server 2016 in cluster mode.

Video demo covers

What are pre-requisites of Installing SQL Server 2016 in cluster mode?
How to Prepare Cluster nodes to install SQL Server 2016 in cluster mode?
How to check SQL Server 2016 pre-requisites
What is shared storage and how to configure Shared Storage
Best practices to configure Shared Storage in SQL Server 2016
How to Add Shared Storage to the windows 2012 Server?
Best practices of installing SQL Server 2016 in cluster mode?

Part 1 - How to install SQL Server 2016 in Cluster Mode

Part 2- How to install SQL Server 2016 in Cluster Mode

How to setup Distributed Availability Group in SQL Server 2016 - SQL Server 2016 DBA Tutorial

Scenario:

You are working as SQL Server DBA and you are asked to setup Distributed Availability Group in SQL Server 2016, What steps would you follow?

Solution:

The video demo, will explain all the steps you need to take to configure Distributed Availability Group. Demo covers below items

What is Distributed Availability Group in SQL Server 2016?
What are pre-requisites of Distributed Availability Group in SQL Server 2016?
How to create availability groups on different clusters to use in Distributed AG?
How to create Availability Group with Automatic seeding
How to create Listener in Availability Group
How to setup Distributed Availability Group using two different listeners?
How to Join Secondary cluster in Distributed Availability Group?
How to check status of Distributed Availability Group
Troubleshooting Distributed Availability Group

Part 1

Part 2

Part 3

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.

What is Automatic Seeding?
What are the pre-requisites of Availability Group with Automatic Seeding?
How to prepare database to be added in Availability Group?
How to create Availability Group with Automatic Seeding using T-SQL?
What are the advantages of Availability Group with Automatic seeding?
What are the limitation of Availability Group with Automatic Seeding?
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

How to get list of all the tables with all the columns from SQL Server Databases on SQL Server Instance

Scenario:

Was working on a project and needed to get list of all the tables from all the databases with columns. Write below script. The script uses cursor to loop through all the databases to get the list of tables with columns. You can filter the databases on which you would like to run the query. I have ignored the system databases such as master, model, tempdb and msdb.

USE master
GO
--Declare Variables
DECLARE @DatabaseName AS VARCHAR(500)
--Create Temp Table to Save Results
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results
CREATE TABLE #Results (
    ServerName VARCHAR(128)
    ,DatabaseName VARCHAR(128)
    ,SchemaName VARCHAR(128)
    ,TableName VARCHAR(128)
    ,ColumnName VARCHAR(128)
     ,Data_Type VARCHAR(128)
   ,Is_Nullable VARCHAR(25)
   ,Character_Maximum_Length VARCHAR(10)

    )
DECLARE CUR CURSOR
FOR
--Filter the Database for which you would like to get all the tables with columns
SELECT '[' + NAME + ']' AS DBName
FROM sys.databases
WHERE NAME NOT IN (
         'master'
        ,'tempdb'
        ,'model'
        ,'msdb'
    
        )
OPEN Cur
FETCH NEXT
FROM Cur
INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
    --Build dynamic sql for each database 
    DECLARE @SQL VARCHAR(MAX) = NULL
    SET @SQL = 'Insert into #Results
      Select
      @@ServerName,
   Table_Catalog as DatabaseName,
   Table_Schema AS TableSchema,
   Table_Name AS TableName,
   Column_Name AS ColumnName
    ,Data_Type
   ,Is_Nullable
   ,Character_Maximum_Length
  From' + @DatabaseName + '.information_Schema.Columns 
  where Table_Name in (Select Table_Name from ' + @DatabaseName + '.information_schema.Tables
  where table_type=''BASE TABLE'')'
    EXEC (@SQL)
    PRINT @SQL
    FETCH NEXT
    FROM Cur
    INTO @DatabaseName
END
CLOSE Cur
DEALLOCATE Cur

SELECT *
FROM #Results
order by DatabaseName,TableName

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid namespace [0x8004100e]

Scenario: SQL Server 2016 Installed successfully as per Wizard but can't find SQL Server Service and can't connect to SQL Server from SSMS.

Solution:

Today I installed SQL Server 2016 on my 64 bit machine.

The file for installation that I used was aslso 64Bit. After installation I tried to connect to SQL Server Engine by using SSMS but could not find.

Next thing I did , I checked tried to open SQL Server configuration manager but got this error
"Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid namespace [0x8004100e]"

Then I went to Control Panel to take a look if SQL Server is installed on machine.

I found out the Size and Version was blank. Looks like the SQL server was not installed correctly. But Wizard should have given an error but it completed successfully.

When was doing research, people are talking about the file can be damaged if you have uninstalled and instance of SQL Server, that I have not done it. I did the fresh installation and it was first time.

"This problem occurs because the WMI provider is removed when you uninstall an instance of SQL Server. The 32-bit instance and the 64-bit instance of SQL Server share the same WMI configuration file. This file is located in the %programfiles(x86)% folder."

Anyways to fix this, you need to run below command on cmd after making sure you have below file on below path.

The Sqlmgmproviderxpsp2up.mof file must be present in the %programfiles(x86)%\Microsoft SQL Server\number\Shared folder.

Open cmd by going to Run in windows. I am using SQL Server 2016, so my number is 130 in below statement, your will be depending upon SQL version.
and paste below command.

mofcomp "%programfiles(x86)%\Microsoft SQL Server\130\Shared\sqlmgmproviderxpsp2up.mof"

Now you can open the SQL Server Configuration Manager without any problem but you will not the SQL server Service. You have to reinstall SQL Server.

I restarted the machine and then Reinstalled SQL Server. The installation completed successfully and was able to use SQL Server without any problem.

How to check if User is Enabled or Disabled in all the databases in SQL Sever

Scenario: How to check if the database user is enabled or disabled in multiple or all the databases in SQL Server

You have created a login and then user in multiple databases by using that login. You need to take a look if the user is enable or disable in those databases.

If the user is disabled in a database you will see a red arrow pointing downwards. I have disabled Aamir user as can be seen below by using

REVOKE CONNECT FROM Aamir

If you want to enable the user, you can use below statement

GRANT CONNECT TO UserName

But our goal is find out in all databases or in multiple database if the user is enabled or disabled. Also if the query does not return the Database name, that means the user does not exits in that database.

--Provide the user name that you would like to check if Enabled or Disabled in Databases
    DECLARE @UserName VARCHAR(128) 
    SET @UserName='Aamir' -- I have provided Aamir as my User Name
    

  IF OBJECT_ID('tempdb..##Temp_Table') IS NOT NULL
        DROP TABLE ##Temp_Table

    CREATE TABLE ##Temp_Table (
    ServerName VARCHAR(1000),
        DatabaseName VARCHAR(500)
        ,UserName VARCHAR(500)
        ,IsEnabled INT
        )
   
    DECLARE @SQLStatement VARCHAR(MAX)
    SET @SQLStatement='Insert into ##Temp_Table
Select @@ServerName AS ServerName,DB_Name() AS DatabaseName,name, hasdbaccess 
FROM sys.sysusers WHERE name='''''+@UserName+''''''

Print @SQLStatement
    DECLARE @DatabaseName AS VARCHAR(500)
    
--DECLARE CURSOR
    DECLARE CUR CURSOR
    FOR
    --Choose the DBs on which you would like to run the script
    SELECT NAME
    FROM sys.databases
    WHERE database_id > 4

--OPEN CURSOR
    OPEN CUR

--NEXT NEXT RECORD 
    FETCH NEXT
    FROM CUR
    INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @DBName AS NVARCHAR(500);

        SET @DBName = QUOTENAME(N'' + @DatabaseName + '');

        --USE Dynamic SQL To Change DB name and run Check If user Enabled or Disabled in Database
        EXEC (
                N'USE ' + @DBName + N'; EXEC('''+@SQLStatement+''');'
                );

        FETCH NEXT
        FROM CUR
        INTO @DatabaseName
    END

    CLOSE CUR

    DEALLOCATE CUR

    Select * From ##Temp_Table

Label

How To Move a MariaDB Data Directory to a New Location on Linux

MariaDB Administration Tutorial

Scenario:

Solution:

Scenario:

Solution:

Scenario:

Solution:

Scenario:

Solution:

Scenario:

Solution:

Scenario:

Solution:

Scenario :

Solution :

1) Go to Start and then in Run type msinfo32 and hit Enter.

2) You can also go to Command Prompt and then paste below statement, you will get same information.

Scenario:

Solution:

Scenario:

Solution:

Scenario:

Solution:

Scenario:

Solution:

Scenario:

Solution:

Scenario:

Solution :

Scenario:

Solution :

Scenario:

Solution:

Scenario:

Solution:

Scenario:

Solution:

Scenario:

Scenario:

Scenario:

Solution:

Scenario:

Solution:

Scenario:

Solution:

Scenario:

Solution:

Scenario:

Scenario: SQL Server 2016 Installed successfully as per Wizard but can't find SQL Server Service and can't connect to SQL Server from SSMS.

Solution:

Scenario: How to check if the database user is enabled or disabled in multiple or all the databases in SQL Server