Generate Attach and Detach Database Script for All User Databases on SQL Instance

Managing your SQL Server databases often requires attaching and detaching databases. Below is a T-SQL script to generate the attach and detach commands for all user databases on a SQL Server instance. This script can be used on SQL Server versions 2008, 2012, 2014, and 2016.

Script to Generate Attach and Detach Commands

SQL server SSIS Notes

 SQL server SSIS  Notes             Download 

How to move system databases from one location to another location in SQL server

Move tempdb:

1) first we have to know the location of tempdb data file and log file location by using below query
use tempdb
go
exec sp_helpfile
go

2) run the below query to save the sql server new location of tempdb data file and log file path

alter database tempdb modify file (name=tempdev, filename='E:\sysdbs\mdf\tempdb.mdf')
go
alter database tempdb modify file (name=templog, filename='E:\sysdbs\ldf\templog.ldf')
go

after execute the above query it shows message like the sql server save your catalog but you must restart the sql server

3) stop the sql server

4) start the sql server

5)verify the path for tempdb data file and log file by using below query
exec sp_helpfile
go

6) whenever restart the sql server new tempdb will be created so automatically the new tmpdb will created in new location  goto old location and delete the old tempdb files


move model :

1) first we have to know the location of tempdb data file and log file location by using below query
use model
go
exec sp_helpfile
go

2)run the below query to save the sql server new location of model data file and log file path

alter database model modify file (name=modeldev, filename='E:\sysdbs\mdf\model.mdf')
go
alter database model modify file (name=modellog, filename='E:\sysdbs\ldf\modellog.ldf')
go

after execute the above query it shows message like the sql server save your catalog but you must restart the sql server

3) stop the sql server

4)move the  model databse data file and log file from old location to new location

5) start the sql server

6)verify the path for model data file and log file by using below query
exec sp_helpfile
go


move msdb : 

1) first we have to know the location of msdb data file and log file location by using below query
use msdb
go
exec sp_helpfile
go

2)run the below query to save the sql server new location of msdb data file and log file path

alter database msdb modify file (name=msdbdata, filename='E:\sysdbs\mdf\msdbdata.mdf')
go
alter database msdb modify file (name=msdblog, filename='E:\sysdbs\ldf\msdblog.ldf')
go

after execute the above query it shows message like the sql server save your catalog but you must restart the sql server

3) stop the sql server

4)move the  msdb data file and log file from old location to new location

5) start the sql server

6)verify the path for model data file and log file by using below query
exec sp_helpfile
go

move master :

1) copy the startup paramaeter(sql server configuration manager---sql server services---sql server(mssqlserver)---properties---advanced---startup parameter)

-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

2) stop the sql server

3) move the master data file and log file from old location to new location

4)paste the startup parameter as a new location of master data fil;e and log file

(sql server configuration manager---sql server services---sql server(mssqlserver)---properties---advanced---startup parameter)

-dE:\sysdbs\mdf\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lE:\sysdbs\ldf\mastlog.ldf

apply ok

5)it shows one message like system save the ur request but you must restart the sql server to apply these rules

6) start the sql server

7)if run sql server there is no issues if sql server will not  work check the startup parameters(startup parameters are very senstive it doesnt have single extra collan or space etc)

8)check the new path of master database data file and log file by using below query

exec sp_helpfile

T-SQL TRANSACT STRUCTURED QUERY LANGUAGE

T-SQL

TRANSACT STRUCTURED QUERY LANGUAGE

            This is a 4^th generated intermediate language between user and SQL Server. Whenever user wants to interact with SQL Server, he has to interact with SQL Server through T-SQL. It includes the following Sub Languages

1. DDL (Data Definition Language)
2. DML (Data Manipulation Language)
3. DCL (Data Control Language)
4. TCL (Transaction Control Language)

Things to Observe:

1. While writing the Queries using T-SQL in SQL Server Management Studio we need not to follow any particular case. Because T-SQL is case insensitive language.
2. After writing the Query, we need to select that query using either mouse or keyboard.
3. Now Press F5 ( Execute Key).
4. Then the results are displayed in a separate window called Result window or Result Pane.
5. Use Ctrl+R to Hide/Show the Result window or Result Pane.
6. Use F8 for Object Explorer

Note:

1. SQL SERVER can handle nearly 32767 Databases
2. Each Database can handle nearly 2 billion Database Objects.
3. Each Table can handle nearly 1024 columns
4. Each Table can handle nearly 1 million Rows.

SP in Fundamental level

SP in Fundamental level:

SP_RENAMEDB: Here SP stands for Stored Procedure. This stored procedure is used to change the name of the existing database.

Syntax: SP_RENAMEDB ‘OLD DATABASENAME’, ‘NEW DATABASENAME’

            E.g.: SP_RENAMEDB  ‘NRSTT’, ‘NRSTTS’

The above statement renames (changes the database name) NRSTT to NRSTTS

SP_RENAME: This stored procedure is used for changing the name of the table and for changing the name of the column

i. Syntax to change the name of the table

SP_RENAME ‘OLD TABLENAME’, ‘NEW TABLENAME’

E.g.      SP_RENAME ‘EMP’, ‘EMPLOY’

The above stored procedure changes the name of EMP table to EMPLOY

ii. Syntax to change the name of the column

SP_RENAME ‘TABLE.OLDCOLUMN NAME’, ‘NEW COLUMNNAME’

E.g.      SP_RENAME ‘STUDENT.ADR’, ‘ADDRESS’

The above stored procedure changes the name of ADR column to ADDRESS in STUDENT table.

SP_HELP: This stored procedure is used to display the description of a specific table.

Syntax: SP_HELP TABLENAME

E.g.: SP_HELP EMP

The above stored procedure displays the description of EMP table

SP_DATADASES: This Stored procedure displays the list of databases available in SQL Server.

Syntax: SP_DATABASES

SP_TABLES: This stored procedure displays the list of tables available in the current database.

Syntax: SP_TABLES

SP_HELPDB: This stored procedure is used to display the description of  master and log data file information of a specific database

Syntax: SP_HELPDB Database-Name

Ex: SP_HELPDB SAMPLE

SP_SPACEUSED: This stored procedure is used to find the memory status of the current database

Syntax: SP_SPACEUSED

Collation levels and default collation

Collation levels and default collation:

Collation can be set at 4 levels in SQL Server:
1.       Server
2.       Database
3.       Columns
4.       Expression

But collations are compared either at columns level or in expressions. So I am starting from lower to upper levels. The collations of upper two levels (server and database) are only helpful in providing default collation to columns and expressions.
Expression collation: All literals, variables and parameters and functions without any input parameters, by default get the collation of database.

Column collation: When we create a table we can specify a collation explicitly for all character data type (char, nchar, varchar, nvarchar, text, ntext) columns. If not specified then collation of database would be the default collation of a column. The collation of a column can be changed by using an ALTER TABLE statement similar to the following:

ALTER TABLE TestTab ALTER COLUMN CharCol CHAR(10) COLLATE Greek_CS_AI

Database collation: When we create a new database we can specify collation. If not specify then collation of model database is assigned as default collation. To know the collation of database use below statement:

SELECT DATABASEPROPERTYEX('testDB', 'Collation') SQLCollation

Database collation can be changed using the ALTER DATABASE statement as below.:

ALTER DATABASE myDB COLLATE Greek_CS_AI

When collation of database is changed it does not automatically change the collation of all columns of all tables. But the new collation would be the default collation for new columns created hence forth and for expressions.

Server collation: This is set during SQL Server installation. It’s the default collation for system databases. Because this is the collation of model database so this would be the default collation for all new databases that would be created on this server. The server collation can not be changed. To query the server collation use the below statement:

SELECT CONVERT (varchar, SERVERPROPERTY('collation'))

Executing a SQL statement from command prompt:

Executing a SQL statement from command prompt:

Sometimes we need to execute some –T-SQL script from command prompt. This is a common requirement when some t-sql scripts are needed to be scheduled by windows scheduler.

Here I will explain how we can execute T-SQL script from command prompt. Below I created an executable file (.bat) to create a backup of AdventureWorks database so that I can schedule this executable using Windows scheduler.

 Create a text file for example c:\sql.txt and write below backup script in it:

USE master

GO

BACKUP DATABASE AdventureWorks

TO DISK = 'D:\SQL_Backup\AdventureWorks.bak' WITH INIT

Create another batch file for example Backup.bat and write the below statement inside it:

sqlcmd -S ServerName -U yourLogin -P yourPassowrd -i c:\sql.txt

Now we can execute this Backup.bat file from command prompt or can schedule using windows scheduler. Thus we have scheduled a task that is not dependent on SQL server Agent service.

Find who dropped, created or altered the table, SP or any object

Find who dropped, created or altered the table, SP or any object

If any database object is dropped, created or altered accidentally, sometimes you may need information about:

Who dropped, created or altered the object? When culprit not accept the mistake.

When the object was dropped, created or altered? Especially drop time of table is required for point in time recovery from database backups.

These changes are not recorded in SQL Server Error Log but are recorded in default trace. If you have not disabled the default trace and started looking into the issue soon after change occurred you could get this information. The path of trace file is “C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Log\”. The folder MSSQL10.SQLEXPRESS may be different according to your SQL Server instance name. Here you would find 5 profiler trace files named as “log_1”, “Log_2”, etc. The file number is increased by time (in following example I used “log_16.trc”). Find the file that is most recently modified and use the below t-sql query to open that file:

SELECT ObjectName, DatabaseName, StartTime, NTDomainName, HostName, NTUserName, LoginName

FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Log\log_16.trc', default)

WHERE objectname is not null  

You can start a trace as default in two ways:

1. create a stored procedure that starts a trace# and then set that SP as startup as following:

CREATE PROC sp_StartTrace
AS
DBCC TRACEON (1204) 
GO

sp_procoption sp_StartTrace , 'STARTUP', 'ON'

2. Start SQL Server at Run window using NET START command with -T switch as below:
net start SQLSERVER -T 1204

SQL Server System Databases

SQL Server System Databases

• Master
• Purpose - Core system database to manage the SQL Server instance.  In SQL Server, the Master database is the logical repository for the system objects residing in the sys schema.  In SQL Server 2000 and previous editions of SQL Server, the Master database physically stored all of the system objects.
• Prominent Functionality
• Per instance configurations
• Databases residing on the instance
• Logins
• Linked\Remote servers
• Endpoints
• Additional Information
• The first database in the SQL Server startup process
• In SQL Server 2005, needs to reside in the same directory as the Resource database
• Resource
• Purpose - The Resource database is responsible for physically storing all of the SQL Server 2005 system objects. This database has been created to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database.
• Introduced in SQL Server 2005 to help manage the upgrade and rollback of system objects
• Prior to SQL Server 2005 the system related data was stored in the master database
• Read-only database that is not accessible via the SQL Server 2005 tool set
• The database ID for the Resource database is 32767
• The Resource database does not have an entry in master.sys.databases
• TempDB
• Purpose - Temporary database to store temporary tables (#temptable or ##temptable), table variables, cursors, work tables, row versioning, create or rebuild indexes sorted in TempDB, etc. Each time the SQL Server instance is restarted all objects in this database are destroyed, so permanent objects cannot be created in this database.
• Each time a SQL Server instance is rebooted, the TempDB database is reset to its original state
• Model
• Purpose - Template database for all user defined databases
• Additional Information
• User defined tables, stored procedures, user defined data types, etc can be created in the Model database and will exist in all future user defined databases
• The database configurations such as the recovery model for the Model database are applied to future user defined databases
• MSDB
• Purpose - Primary database to manage the SQL Server Agent configurations
• Prominent Functionality
• SQL Server Agent Jobs, Operators and Alerts
• SSIS Package storage in SQL Server 2005,2008r2 and 2012
• Additional Information
• Provides some of the configurations for the SQL Server Agent service.
• Distribution
• Purpose - Primary data to support SQL Server replication
• Prominent Functionality
• Database responsible for the replication meta data
• Supports the data for transaction replication between the publisher and subscriber(s)
•  
• ReportServer
• Purpose - Primary database for Reporting Services to store the meta data and object definitions
• ReportServerTempDB
• Purpose - Temporary storage for Reporting Services
• Prominent Functionality

How to Install SQL Server on Red Hat- Linux

The document for - how to  Install SQL Server on Red Hat -Linux       Download

Using Power shell script to get Disk space report for multible servers in HTML format via DB alert

The power shell script               Download

Please find the below mentioned sample screenshot disk space report :

Last cluster failover sql script to find out

--Last cluster failover sql script to find out

CREATE TABLE #ErrorLog(
LogDate DATETIME,

ErrorSource NVARCHAR(MAX),

ErrorMessage NVARCHAR(MAX)

)
CREATE TABLE #NumberOfLogs(

ID INT PRIMARY KEY NOT NULL,

LogDate DATETIME NOT NULL,

LogFileSize bigint
)
　
INSERT INTO #NumberOfLogs(ID,LogDate,LogFileSize)

EXEC master.dbo.xp_enumerrorlogs

DECLARE @ErrorLogID INT

DECLARE cNumberOfLogs CURSOR FOR

SELECT ID

FROM #NumberOfLogs

OPEN cNumberOfLogs

FETCH NEXT FROM cNumberOfLogs INTO @ErrorLogID

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO #ErrorLog(LogDate,ErrorSource,ErrorMessage)

EXEC sp_readerrorlog @ErrorLogID, 1, 'NETBIOS'

INSERT INTO #ErrorLog(LogDate,ErrorSource,ErrorMessage)

EXEC sp_readerrorlog @ErrorLogID, 1, 'SQL Server is terminating'

FETCH NEXT FROM cNumberOfLogs INTO @ErrorLogID

END

CLOSE cNumberOfLogs

DEALLOCATE cNumberOfLogs

SELECT LogDate, ErrorMessage FROM #ErrorLog

DROP TABLE #ErrorLog

DROP TABLE #NumberOfLogs

MySQL database migration from one server to another server.

MySQL database migration from one server to another server.

Connect to Testmysql01 using ssh

Take a dump of Test_database database

[root@Testmysql01 ~]# mysqldump -u root -p Test_database  > /opt/Test_database.sql

Copy the Dump file to Testmysql02

[root@Testmysql01 ~]# scp /opt/Test_database.sql root@Testmysql02.domain.com:/opt

[root@Testmysql01 ~]# Exit

Connect to Testmysql02 using ssh

connect to MySQL

[root@Testmysql02~]# MySQL -u root -p

Create Database and grant permission for the user

MySQL> create database Test_database;

MySQL> GRANT ALL ON Test_database.* TO testuser@'%' IDENTIFIED BY 'password';

MySQL> quit

Import Dump to new database

[root@Testmysql02~]# MySQL -u root -p Test_DBA < /opt/Test_DBA.sql

[root@Testmysql02~]# Exit

Connect to webserver using RDP.

Navigate to C:\inetpub\Test_database\Test_dat
abase\config

Edit file config.php and change 'dbhost' => in the file to Testmysql02.domain.com.

verify 'dbuser' => and 'dbpassword' => is defined correctly.

restart the site from IIS manager

connect to Test_database  site and verify everything is working fine.

Upgrade SQL Server 2014 Standard to Enterprise

Upgrade SQL Server 2014 Standard to Enterprise document..          Download

Script: list all Usernames, Roles for all the databases in SQL server

/**
Script: list all Usernames, Roles for all the databases.
and the database Roles that the user belongs to in all the databases.
Also, you can use this script to get the roles of one user in all the databases.

**/
USE MASTER
GO
BEGIN
DECLARE @SQLVerNo INT;
SET @SQLVerNo = cast(substring(CAST(Serverproperty('ProductVersion') AS VARCHAR(50)) ,0,charindex('.',CAST(Serverproperty('ProductVersion') AS VARCHAR(50)) ,0)) as int);

IF @SQLVerNo >= 9 
    IF EXISTS (SELECT TOP 1 *
               FROM Tempdb.sys.objects (nolock)
               WHERE name LIKE '#TUser%')
        DROP TABLE #TUser
ELSE
IF @SQLVerNo = 8
BEGIN
    IF EXISTS (SELECT TOP 1 *
               FROM Tempdb.dbo.sysobjects (nolock)
               WHERE name LIKE '#TUser%')
        DROP TABLE #TUser
END

CREATE TABLE #TUser (
    ServerName    varchar(256),
    DBName        SYSNAME,
    [Name]        SYSNAME,
    GroupName     SYSNAME NULL,
    LoginName     SYSNAME NULL,
    default_database_name  SYSNAME NULL,
    default_schema_name    VARCHAR(256) NULL,
    Principal_id  INT,
    [sid]         VARBINARY(85))

IF @SQLVerNo = 8
BEGIN
INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT 
   @@SERVERNAME,
   ''?'' as DBName,
   u.name As UserName,
   CASE WHEN (r.uid IS NULL) THEN ''public'' ELSE r.name END AS GroupName,
   l.name AS LoginName,
   NULL AS Default_db_Name,
   NULL as default_Schema_name,
   u.uid,
   u.sid
FROM [?].dbo.sysUsers u
   LEFT JOIN ([?].dbo.sysMembers m 
   JOIN [?].dbo.sysUsers r
   ON m.groupuid = r.uid)
   ON m.memberuid = u.uid
   LEFT JOIN dbo.sysLogins l
   ON u.sid = l.sid
WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1
   /*and u.name like ''tester''*/
ORDER BY u.name
'
END

ELSE 
IF @SQLVerNo >= 9
BEGIN
INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT 
   @@SERVERNAME,
   ''?'',
   u.name,
   CASE WHEN (r.principal_id IS NULL) THEN ''public'' ELSE r.name END GroupName,
   l.name LoginName,
   l.default_database_name,
   u.default_schema_name,
   u.principal_id,
   u.sid
FROM [?].sys.database_principals u
   LEFT JOIN ([?].sys.database_role_members m
   JOIN [?].sys.database_principals r 
   ON m.role_principal_id = r.principal_id)
   ON m.member_principal_id = u.principal_id
   LEFT JOIN [?].sys.server_principals l
   ON u.sid = l.sid
WHERE u.TYPE <> ''R''
   /*and u.name like ''tester''*/
order by u.name
'
END

SELECT *
FROM #TUser
ORDER BY DBName,
 [name],
 GroupName

DROP TABLE #TUser
END


/** end of script **/

Script to get disabled jobs list in SQL Server

Script to get disabled jobs list in SQL Server

Backup, Restore percentage status script

--Backup, Restore percentage status script

SQL Server Database backup verify script

--Verify Backup is completed or not

Clear/Remove SQL Server Services from Services.msc

Problem:-  One of my friend was installing cluster SQL Server, installation was stopped in the middle of something and he was not able to remove the SQL Server from add/remove programs or SQL Server setup. He has deleted the registry, folder manually but still he can see the SQL Server Services in Services.msc. He called me for the issue, I have provided him the below solution and it will work.

Solution:

1) Go to command prompt and use SC command to delete service.

sc delete sqlserveragent

sc delete mssqlserver

2) You have to also delete the services registry entry using regedit

HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services

Grant view defination access for all Stored Procedures in SQL server using SQL script

Grant view defination access for all Stored Procedures in SQL server using SQL script

Configuring Database Mirroring in SQL Server

Database mirroring is a solution for increasing database availability. Here’s a step-by-step guide to configure mirroring in SQL Server:

Step 1: Take a Full Backup of the Principal Database

sql
BACKUP DATABASE [Database_Name] TO DISK = 'Path\Backup.bak';

Step 2: Take a Transaction Log Backup of the Principal Database

sql
BACKUP LOG [Database_Name] TO DISK = 'Path\Backup.trn';

Step 3: Remove the Recovering State of the Mirror Database (if applicable)

sql
RESTORE DATABASE [Database_Name] WITH RECOVERY;

Step 4: Ensure the Backup Folder on the Principal Server is Shared and Accessible from the Mirror Server

Step 5: Restore the Full Backup to the Mirror Server with the NORECOVERY Option

sql
RESTORE DATABASE [Database_Name] FROM DISK = N'\\Principal\Backup\Backup.bak' WITH NORECOVERY;

Step 6: Restore the Transaction Log Backup to the Mirror Server with the NORECOVERY Option

sql
RESTORE LOG [Database_Name] FROM DISK = N'\\Principal\Backup\Backup.trn' WITH FILE = 1, 'NORECOVERY, NOUNLOAD, STATS = 10;

Step 7: Configure Database Mirroring from the Principal Server

1. Right-click the database.
2. Choose "Tasks" > "Mirror" or "Properties" > "Mirroring".

Step 8: Launch the Configure Database Mirroring Security Wizard

• Click the "Configure Security" button.
• Click "Next" if the intro screen appears.

Step 9: Skip Witness Server Configuration

• Select "No" for "Do you want to configure security to include a witness server instance".
• Click "Next" to continue.

Step 10: Configure the Principal Server Instance

• Create an endpoint named "Mirroring" with a Listener Port of 5022.
• Click "Next" to continue.

Step 11: Configure the Mirror Server Instance

• Click "Connect..." and select the mirror server.
• Click "Next" to continue.

Step 12: Service Accounts Configuration

• Leave the fields blank if all server instances use the same domain account.
• Click "Next".

Step 13: Complete the Wizard

• Review the summary of the configuration.
• Click "Finish".

Step 14: Start Mirroring

• Click "Start Mirroring".
• If everything is set up correctly, database mirroring will start successfully and will be fully synchronized.

Backout Plan

1. Remove the mirror configuration.
2. Set the mirror database with RECOVERY.

Test Plan

Verify that Both Servers are Listening on the Same Port

sql
SELECT type_desc, port FROM sys.tcp_endpoints;

Verify Database Mirroring Status on Both Servers

sql
SELECT state_desc FROM sys.database_mirroring_endpoints;

• The state_desc column on both the Principal and Mirror server should be STARTED.

Verify that Roles are the Same on Both the Principal and Mirror Server

sql
SELECT role FROM sys.database_mirroring_endpoints;

By following these steps, you can configure database mirroring in SQL Server to ensure high availability and disaster recovery.

Differences Between Log Shipping, Mirroring, and Replication in SQL Server

Log Shipping

Log Shipping automatically sends transaction log backups from one database (primary database) to another database (secondary database) on a different server. An optional third server, known as the monitor server, records the history and status of backup and restore operations and can raise alerts if these operations fail to occur as scheduled.

Mirroring

Database mirroring is a software solution primarily designed to increase database availability. It maintains two copies of a single database on different server instances of SQL Server Database Engine.

Replication

Replication is a set of technologies for copying and distributing data and database objects from one database to another, then synchronizing between databases to maintain consistency. Using replication, data can be distributed to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.

Components

• Log Shipping: Primary server, secondary server, and monitor server (optional).
• Mirroring: Principal server, mirror server, and witness server (optional).
• Replication: Publisher, subscribers, distributor (optional).

Data Transfer

• Log Shipping: Transaction logs are backed up and transferred to the secondary server.
• Mirroring: Individual transaction log records are transferred using TCP endpoints.
• Replication: Changes are tracked/detected (either by triggers or log scanning) and shipped.

Server Limitation

• Log Shipping: Configurable as one-to-many (one primary server to many secondary servers) or many-to-one (multiple primary databases on different servers to one secondary server).
• Mirroring: One-to-one (one principal server to one mirror server).
• Replication: Various topologies including central publisher/distributor with multiple subscribers, central distributor with multiple publishers and subscribers, and mixed topologies.

Types of Failover

• Log Shipping: Manual.
• Mirroring: Automatic or manual.
• Replication: Manual.

Database Access

• Log Shipping: Secondary database can be used for reporting in STANDBY mode.
• Mirroring: Mirrored database can only be accessed using a snapshot.
• Replication: Subscriber database is open to reads and writes.

Recovery Model

• Log Shipping: Supports both Bulk Logged and Full Recovery Models.
• Mirroring: Supports only Full Recovery Model.
• Replication: Supports Full Recovery Model.

Restoring State

• Log Shipping: Can be completed using either NORECOVERY or STANDBY option.
• Mirroring: Can be completed using NORECOVERY.
• Replication: Can be completed using RECOVERY.

Backup/Restore

• Log Shipping: Can be done manually or through Log Shipping options.
• Mirroring: Must be done manually by the user.
• Replication: Create an empty database with the same name.

Monitor/Distributor/Witness

• Log Shipping: The monitor server should be separate from the primary or secondary servers. If not used, alert jobs are created locally on the primary and secondary servers.
• Mirroring: The principal server cannot act as both principal and witness server.
• Replication: The publisher can also be the distributor.

Types of Servers

• Log Shipping: All servers must be SQL Server.
• Mirroring: All servers must be SQL Server.
• Replication: The publisher can be an Oracle Server.

SQL Server Agent Dependency/Jobs

• Log Shipping: Yes, involves four jobs handled by SQL Server Agent jobs (backup, copy, restore, alert).
• Mirroring: Independent of SQL Server Agent.
• Replication: Yes, involves Snapshot Agent, Log Reader Agent, and Distribution Agent (transactional replication), and Merge Agent (merge replication).

Requirements

Log Shipping:

• Same logical design and collation settings for involved servers.
• Databases must use Full or Bulk-Logged Recovery Models.
• SQL Server Agent should start automatically.
• Sysadmin privileges are required on each server.

Mirroring:

• No differences in system collation settings.
• Identical local Windows groups and SQL Server login definitions on both servers.
• Same external software components installed on both servers.
• Identical SQL Server software versions on both servers.
• Identical global assemblies on both servers.
• Matching certificates and keys for authentication and encryption.

Replication:

• No differences in system collation settings.
• Identical local Windows groups and SQL Server login definitions on both servers.
• Same external software components installed on both servers.
• CLR assemblies deployed on the publisher must also be deployed on the subscriber.
• SQL Agent jobs and alerts required on the subscriber server.
• Matching certificates and keys for authentication and encryption.

Using with Other Features or Components

• Log Shipping: Can be used with Database Mirroring and Replication.
• Mirroring: Can be used with Log Shipping, Database Snapshots, and Replication.
• Replication: Can be used with Log Shipping and Database Mirroring.

DDL Operations

• Log Shipping: DDL changes are applied automatically.
• Mirroring: DDL changes are applied automatically.
• Replication: Only DML changes to published tables are replicated.

Database Limit

• Log Shipping: No limit.
• Mirroring: Generally recommended to have up to 10 databases per server.
• Replication: No limit.

Latency

• Log Shipping: Data transfer latency >1 minute.
• Mirroring: No data transfer latency.
• Replication: Latency can be as low as a few seconds.

Committed/Uncommitted Transactions

• Log Shipping: Both committed and uncommitted transactions are transferred to the secondary database.
• Mirroring: Only committed transactions are transferred to the mirror database.
• Replication: Only committed transactions are transferred to the subscriber database.

Primary Key

• Log Shipping: Not required.
• Mirroring: Not required.
• Replication: All replicated tables must have a primary key.

New Created Database & Stored Procedure

• Log Shipping: Monitoring and history information stored in msdb, accessed using log shipping stored procedures.
• Replication: Creates new stored procedures (three per table) and a distribution database. A rowguid column is created.

Individual Articles

• Log Shipping: No, the whole database must be selected.
• Mirroring: No, the whole database must be selected.
• Replication: Yes, including tables, views, stored procedures, and other objects. Filters can restrict columns and rows sent to subscribers.

FILESTREAM

• Log Shipping: Supports FILESTREAM.
• Mirroring: Does not support FILESTREAM.
• Replication: Supports FILESTREAM.

Database Name

• Log Shipping: Secondary database can have a different name from the primary database.
• Mirroring: Must have the same name.
• Replication: Must have the same name.

Database Availability

• Log Shipping: In standby mode: read-only database. In restoring mode with NORECOVERY: restoring state.
• Mirroring: In recovery state, no user operations allowed. Can take a snapshot.
• Replication: Snapshot replication (read-only), other types (databases are available).

Warm/Hot Standby Solution

• Log Shipping: Provides a warm standby solution with multiple copies of a database and requires manual failover.
• Mirroring: Provides a hot standby server with automatic failover when synchronized, otherwise a warm standby server with possible data loss.
• Replication: Provides a warm standby solution with multiple copies of a database and requires manual failover.

System Data Transferred

• Log Shipping: Mostly.
• Mirroring: Yes.
• Replication: No.

System Databases

• Mirroring: Cannot mirror Master, msdb, tempdb, or model databases.

Mode or Types

Log Shipping:

• Standby mode (read-only): disconnect users when restoring backups.
• No recovery mode (restoring state): user cannot access the secondary database.

Mirroring:

• High-safety mode: supports synchronous operation.
• High-performance mode: runs asynchronously.
• High-safety mode with automatic failover.

Replication:

• Snapshot replication.
• Transactional replication.
• Transactional publication with updatable subscriptions.
• Merge publication.
• Pull/push subscription.

How to Skip the Reboot While Installing SQL Server or Applying Patches Using PowerShell or Command Prompt

How to Skip the Reboot While Installing SQL Server or Applying Patches Using PowerShell or Command Prompt

When installing SQL Server or applying patches, sometimes a reboot is initiated automatically. To avoid this, you can use specific parameters or switches in PowerShell or Command Prompt. Here's how you can skip the reboot:

Using Command Prompt

To install SQL Server without rebooting, use the following command with the /SkipRules and /Action parameters:

SQL Server DBA Responsibilities

A Database Administrator (DBA) is responsible for designing, implementing, and maintaining the database system, as well as establishing policies and procedures for the management, security, maintenance, and use of the database management system. Below are the key responsibilities of a SQL Server DBA.

SQL Server DBA Responsibilities

1. Installing or Upgrading SQL Server

   • DBAs are responsible for installing SQL Servers or upgrading to a newer version. This involves understanding the differences between various SQL Server editions and ensuring the correct edition is installed. Knowledge of the required licenses is also essential.

2. Patching SQL Servers

   • After installation, DBAs must ensure that SQL Servers are properly patched with the latest Service Packs and cumulative updates to maintain security and performance.

3. Database Server Health Monitoring

   • One of the primary responsibilities is to monitor the health of the database server, ensuring optimal utilization of resources such as the processor and memory.

4. Storage Availability

   • DBAs need to ensure there is sufficient disk space for database growth. This involves regular monitoring and planning for future storage needs.

5. Performance Tuning & Optimization

   • Regular performance tuning and optimization are necessary to maintain database efficiency and speed.

6. Securing the SQL Server Database

   • Data security is crucial. DBAs must protect the database from unauthorized access by assigning appropriate permissions to users.

7. Backups and Restoration

   • Regular backups are vital to prevent data loss. In the event of a database corruption or server failure, DBAs must restore the database with minimal data loss and downtime.

8. Data Transfer

   • In heterogeneous environments, DBAs often need to import or export data to and from different formats (e.g., SQL Server to/from Oracle, Sybase, text files, CSV format).

9. Disaster Recovery

   • DBAs must plan and implement strategies for disaster recovery to ensure business continuity in the event of a database or server failure.

10. Deployment of SQL Scripts

    • DBAs are responsible for deploying SQL code and scripts to the production environment, ensuring they are tested and safe to execute.

11. Maximizing Uptime

    • Ensuring that SQL Servers have minimal downtime is critical. DBAs must implement strategies to achieve high availability and minimal impact on business operations.

12. Documentation

    • Proper documentation is necessary to keep track of configurations, procedures, and changes. This is helpful for new DBAs and during emergencies.

13. Communication

    • Effective communication skills are essential, as DBAs interact with various teams to ensure smooth database operations.

Being a SQL Server DBA involves a wide range of responsibilities, each crucial to maintaining a healthy, secure, and efficient database environment. Proper management of these responsibilities ensures that the database systems support business needs effectively.