MIGRATING ON PREMISE TO AZURE

-- Migrating from .bak file and with large tables for .bacpac file.

 https://www.bizstream.com/blog/from-bak-to-bacpac-managing-azure-migrations-with-non-standard-backups/

POWERSHELL DBATOOLS MODULE

 1. Dba tools is a module which simplifies and automates many sql server tasks like backup,restores,migrations and replication management. Great for copying and automating synchronizaton for configurations,logins,mail setup,jobs etc.. This is not required to install on each server.

To install DBA-TOOLS 

Install-Module -Name dbatools

the commands are available online in dbatools.io

How to rebuild Master & MSDB database

Rebuilding MSDB database if its corrupted and dont have backup

Rebuilding MSDB can be achieved by using Instmsdb script which eliminate all the details stored  in MSDB for alerts, job, Maintainence plan, backup history,Policy based management settings, database mail, performance data warehouse etc.  
1. Stop the SQL Server Instance, SQL Agent, SSRS, SSIS and all other instance related to data store. 
2. In the CMD start the sql instance with below cmd 
NET START MSSQLSERVER /T3608
Trace flag 3608 prevents SQL Server from automatically starting and recovering any database except the master database.

3. In another CMD line detach the MSDB db 

SQLCMD -E -S <SERVERNAME> -dmaster -Q "Exec sp_detach_db msdb"

4. In the SQL data folder rename the existing msdb files to different name.

5. Using SQL Configuration manager start the SQL instance normally.

6.  In the CMD connect to sql server and exec the command 

SQLCMD -E<SERVERNAME> -i"C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Install\instmsdb.sql" -o"C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Install\instmsdb.out"

7. Check any errors logged in instmsdb file

8. Reapply if any service packs or hot fixes.

9. Recreate the jobs ,alerts  etc

10. Backup the msdb db. 

REBUILDING THE MASTER DATABASE

Prerequisites

1. Record all the server wide configuration values

select * from sys.configurations

2. Record all service packs and hotfixes applied to the instance of SQL Server and the current collation. You must reapply these updates after rebuilding the system databases.

SELECT

SERVERPROPERTY('ProductVersion ') AS ProductVersion,

SERVERPROPERTY('ProductLevel') AS ProductLevel,

SERVERPROPERTY('ResourceVersion') AS ResourceVersion,

SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime,

SERVERPROPERTY('Collation') AS Collation;

3. Record the current location of all data and log files for the system databases. Rebuilding the system databases installs all system databases to their original location. If you have moved system database data or log files to a different location, you must move the files again.

SELECT name, physical_name AS current_file_location

FROM sys.master_files

WHERE database_id IN (DB_ID('master'), DB_ID('model'), DB_ID('msdb'), DB_ID('tempdb'));

4. Locate the current backup of the master, model, and msdb databases.

5. If the instance of SQL Server is configured as a replication Distributor, locate the current backup of the distribution database.

6. Ensure you have appropriate permissions to rebuild the system databases. To perform this operation, you must be a member of the sysadmin fixed server role. For more information, see Server-Level Roles.

7. Verify that copies of the master, model, msdb data and log template files exist on the local server. The default location for the template files is C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\Templates. These files are used during the rebuild process and must be present for Setup to succeed. If they are missing, run the Repair feature of Setup, or manually copy the files from your installation media. To locate the files on the installation media, navigate to the appropriate platform directory (x86 or x64) and then navigate to setup\sql_engine_core_inst_msi\Pfiles\SqlServr\MSSQL.X\MSSQL\Binn\Templates.

To rebuild system databases for an instance of SQL Server:

1. Insert the SQL Server 2014 installation media into the disk drive, or, from a command prompt, change directories to the location of the setup.exe file on the local server. The default location on the server is C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Release.

2. From a command prompt window, enter the following command. Square brackets are used to indicate optional parameters. Do not enter the brackets. When using a Windows operating system that has User Account Control (UAC) enabled, running Setup requires elevated privileges. The command prompt must be run as Administrator.

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]

Troubleshoot CPU issue

 **Solving High CPU Utilization in SQL Server** 🔧

Recently, I encountered a high CPU utilization issue on one of our SQL Server instances, which was impacting performance and responsiveness. Here's how I approached and resolved the issue:

### **Symptoms:**
- Consistently high CPU usage
- Slow query performance
- Delays in application responses

### **Investigation Steps:**

1. **Identify the Culprit Queries:**
- Used **SQL Server Management Studio (SSMS)** to monitor active sessions and identify long-running queries.
- Ran the following query to find the top CPU-consuming queries:
```sql
SELECT TOP 10
qs.sql_handle,
qs.execution_count,
qs.total_worker_time AS CPU_Time,
qs.total_elapsed_time AS Total_Time,
qs.total_logical_reads AS Reads,
qs.total_logical_writes AS Writes,
SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) AS query_text
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY
qs.total_worker_time DESC;
```

2. **Analyze Query Execution Plans:**
- Analyzed execution plans for the top queries to identify any inefficiencies or missing indexes.

3. **Check Index Usage:**
- Used the **Database Engine Tuning Advisor** to suggest missing indexes and other optimizations.
- Verified the existing indexes and their usage patterns.

4. **Look for System Bottlenecks:**
- Monitored system resources (CPU, memory, disk I/O) to ensure there were no other bottlenecks.

### **Solutions Implemented:**

1. **Optimized Queries:**
- Refactored inefficient queries to reduce CPU load.
- Added necessary indexes based on execution plan analysis.

2. **Updated Statistics:**
- Ensured that statistics were up-to-date to help the SQL Server query optimizer make better decisions.
```sql
EXEC sp_updatestats;
```
3. **Index Maintenance:**
- Scheduled regular index maintenance tasks to rebuild and reorganize fragmented indexes.

4. **Resource Management:**
- Configured **Resource Governor** to manage workload distribution and prevent a single process from consuming too much CPU.

### **Results:**
- Significant reduction in CPU utilization
- Improved query performance and application responsiveness
- More stable and predictable system behavior

### **Takeaway:**
High CPU utilization in SQL Server can often be traced back to inefficient queries or poor indexing strategies. Regular monitoring and maintenance are key to maintaining optimal performance.

If you've faced similar issues, I'd love to hear about your experiences and solutions! Feel free to share your thoughts.

-- Database wise CPU stats

WITH DB_CPU_Stats
AS
(
SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName],
SUM(total_worker_time) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (
SELECT CONVERT(int, value) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid') AS F_DB
GROUP BY DatabaseID
)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
DatabaseName,
[CPU_Time_Ms],
CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
--WHERE DatabaseID > 4 -- system databases
--AND DatabaseID <> 32767 -- ResourceDB
ORDER BY row_num OPTION (RECOMPILE);