Thursday, January 16, 2020

TEMPDB CORRUPTION:

1. If Tempdb corrupt instance wouldn't respond and it would be in hung state equal to crash.

2. To resolve, restart Sql server instance so that Tempdb files will be recreated.

Error number: 824 state: 19

Note:

1. If Tempdb log file is full then SQL Server instance did not allow any connections.

2. If model database is corrupted at the same time Tempdb corrupt, then even Tempdb did not create after restart.

Trace Flags:

-t: refereing startup
-d: fully qualified path of data file
-e: error file
-l: log file
-c: quick start of instance than regular process
-m: single user mode
-s: sqlserver.exe
-n: start instance name then the information will not trace in event viewer
-------------------------------------------------------------------------------------------------------------------------------
Trace Flag 3607:

Starts SQL Server without recovering any databases

Trace Flag 3608:

Starts SQL Server, recovering master only

User Database Status -32768:

Starts SQL Server without recovering the user database.

MSDB CORRUPT:

1) Verify the reason of failure in the error logs and troubleshoot accordingly. If database is really corrupt then look out for an available valid backup. If backup is available restore MSDB as a normal user database and it would be restored.

2) If backup is not available, then stop the instance and start the instance in /m and /t3608 startup parameters.

Net stop "SQL Server (MSSQLSERVER)"

Net start "SQL Server (instance name)" /t3608 /m

3) Connect to the Query window and detach MSDB database and delete the OS level files.

SP_detach_db 'MSDB'

NOTE: Remove MSDB data/log files from the path.

4) Execute the script in %Root Directory%\Install\instMSDB.sql file.

ISSUE: after msdb rebuild then Sql server agent not able to start? How to resolve

Solution:

sp_configure 'show advanced options', 1;

RECONFIGURE;

sp_configure 'Agent XPs', 0;
RECONFIGURE;

MODEL CORRUPTION:

Error: 5172, Severity: 16, State: 15.
Error message:

The header for file '\model.mdf' is not a valid database file header.

Solution:

Model database being one of the crucial database for new database creations and also for Tempdb recreation on every restart.

If model database is corrupt it is going to affect instance functionality.

Steps:

1) Verify if Model is corrupt or not in Event viewer and SQL Server Error Logs.

2) Confirm if a valid database backup exists or not using restore verify only/header only.

3) Start instance with Master database only by enabling the trace 3608.

Net start "SQL Server (MSSQLSERVER)" /t3608

4) Restore the Model database from backup.

Restore database model from disk=N'F:\Model.bak' WITH REPLACE

5) Start instance normally by removing trace 3608

Net stop "SQL Server (MSSQLSERVER)"
Net start "SQL Server (MSSQLSERVER)"

OTHER METHOD: Copy and paste model .mdf, .ndf files from other instance [Required to take instance offline]

Note:
From SQL SERVER 2008 onwards MS introduced TEMPLATE FOLDER WHICH contains fresh "MASTER, MODEL and MSDB" MDF and LDF files.
If any corruption for system database, then please use this template to bring the instance quickly and do the restore to get updated data.

SYSTEM DATABASE CORRUPTIONS

MASTER CORRUPT:


> Master is the most crucial database in an instance, if it is corrupt entire instance gets affected.
Master Corrupt


Error number: 3411

Error Message: Timely fashion error

112 error number for disk space

Partially corrupt:
1. If master database is corrupt, it is either completely corrupt or partially corrupt. If partially corrupt, instance will start with -m;-t3608 and if it is completely corrupt instance wouldn't start.
2. Put your instance in single user mode.
3. Restore master database WITH REPLACE option

Restore database master from disk=N'F:\Master.bak' WITH REPLACE

Completely Corrupt:

1) Master database doesn't start with /m /t3608 and hence we need to rebuild the master database.
Use command prompt and start rebuilding master database by enter into path of setup.exe [Path of SQL Server software]

2) Rebuild master

Start /wait setup.exe /qb INSTANCENAME=sql2005 REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=Admin123
Net stop "SQL Server (instance name)"
Net start "SQL Server (instance name)" /m

4) Restore master database WITH REPLACE option


Restore database master from disk=N'F:\Master.bak' WITH REPLACE
























































Monday, January 13, 2020

LOG SHIPPING SCENARIOS:

1. Monitoring in Log-shipping

2. What are the log shipping jobs?

3. Failover

4. Switchover

5. What are the reasons log shipping fails? If fail what happened?

6. What are the common errors numbers will get in log shipping?

7. What is .tuf [Transaction undo file] purpose? Overview

8. If we delete .tuf file what happen to LS?

9. If we do insert, update, delete in primary server database, changes replicate to secondary?

10. If I shrink primary database what happen to log shipping in secondary db?

11. If I delete some records in primary database what happen to log shipping in secondary db?

12. If I do truncate in primary database what happen to log shipping in secondary db?

13. If I take a manual log backup for LS configured database will it impact secondary?

14. Adding multiple secondary server to the existing configuration? 15. Adding file to log shipping database?

16. Patch management process in LS instance?

17. Reasons a backup job, copy and restore job fails?

18. How to change recovery models in log shipping expect simple? Yes we can able to change ....Simple recovery not support for LS

19. If primary database log file full then what happen to secondary? How will we resolve?


SCENARIO: 1 MONITORING IN LOG-SHIPPING

1. Go to job view history and check status to monitor log shipping status. If all backup, copy and restore jobs are running then we can say your log shipping is in sync and working fine.

MSDB Tables:

PRIMARY SERVER TABLE:

1.dbo.log_shipping_primary_databases:

Number of databases are configured in logshipping in primary

Backup share local path

Backup share network path

Last transaction log backup and time stamp.

Monitor server ID

2.dbo.log_shipping_primaries:

primary_id

primary_server_name

primary_database_name

maintenance_plan_id

backup_threshold

threshold_alert

threshold_alert_enabled

last_backup_filename last_updated

planned_outage_start_time

planned_outage_end_time

planned_outage_weekday_mask


source_directory

3.dbo.log_shipping_primary_secondaries:

Secondary server instance name

Secondary server log shipping database name


SECONDARY SERVER TABLE:

1.dbo.log_shipping_secondary_databases

Secondary ls databases

Last restore file

Last restore date

Restore mode

Disconnect user option

2.dbo.log_shipping_secondary:

Primary server instance name

Primary database name

Network backup share

Copy share location

Monitor server

Last copied file

Last copied date

3.dbo.log_shipping_secondaries

primary_id

secondary_server_name

secondary_database_name

last_copied_filename

last_loaded_filename last_copied_last_updated


last_loaded_last_updated

secondary_plan_id

copy_enabled

load_enabled

out_of_sync_threshold

threshold_alert

threshold_alert_enabled

planned_outage_start_time

planned_outage_end_time

planned_outage_weekday_mask

allow_role_change


MONITOR SERVER TABLE:


1.dbo.log_shipping_monitor_primary:

Primary server
Primary dabase
Backup threshold
Last backup file
Last backup date

2.dbo.log_shipping_monitor_Secondary:

Primary server
Last copy file
Secondary server
Last restore file
Last restore date
Last copy date
Threshold

3.dbo.log_shipping_monitor_history_detail:

Backup ,copy and restore information maintain.

4.dbo.log_shipping_monitor_error_detail:

Stores error information.

5.dbo.log_shipping_monitor_alert:

Alert job id.

2. FAILOVER:::::: LOG SHIPPING IS MANUAL FAILOVER AND NO AUTOMATIC FAILOVER SUPPORTS


> This is done when the primary database is No longer available. It is not pre-planned.

> When primary database goes down my application cannot be able to connect and log shipping is no more exists.Need to bring secondary database online manually is called as “failover”

Steps:

1. Disable all backup, copy and restore jobs, alert job. Inform application to stop the app to avoid any user connections

2. Apply Tail log backup to recover active transactions in primary database...

3. Compare [BACKUP SHARE AND COPY SHARE] and move .trn files from primary to secondary copy folder.

Note 1: methods can be move the backup file

== Manually copy and paste from backup share to copy share by checking the time stamp and LSN number
== Or just run the copy job and which automatically copy to copy share. Please copy manually only TAIL LOG BACKUP
Note 2: methods TO RESTORE the backup file IN SECONDARY:
Note: Find what was the last restore backup file in Secondary
From msdb database, log shipping secondary tables we can get what was the last backup file restored...
Go to secondary server> MSDB> select * from sys. log_shippng_secondary_databases >> this table give data of last restored backup file.

4. Restore pending .trn backup files in secondary database with no recovery to recover the transactions (If tail log backup).

Note: Restore or copy by running manually or run the copy \restore jobs.

5. Restore last .trn backup file with recovery to get your secondary database up & running. If I have tail log then restore last TAIL log backup file with recovery

6. All login transfer to secondary database.

7. Inform to application team with new server instance name and database to start transactions.

Note: Log shipping supports manual fail-over method and does not support automatic fail-over.

3. HOW TO MOVE LOGINS IN LOG SHIPPING FROM PRIMARY TO SECONDARY SERVER DATABASE?

1. Whenever you create any login in Primary server then take the create login script in Primary server and execute in secondary server.

2. After login creation happen in secondary server.

3. But a user from primary server to secondary server is not possible to proceed manually. Due to secondary server database is in read-only\restoring state.

4. Wait or run manually for the backup\copy\restore job to run after login creation.

5. Once done, your secondary database gets added into user under database automatically.


6. Enable the login in secondary server.

4. SWITCH-OVER:

> This is done when both primary and standby databases are available. It is pre-planned.

> Switch-Over: Swapping the roles that means-- your primary become -secondary [ONLINE -Restoring or standby mode]


> Secondary becomes- Primary [From Restoring\stand by Online state]

DISASTER RECOVERY: [DR process] this concept used for high availability solutions to test both primary and secondary are working fine instead of waiting for any disaster. Can called as proactive check.

Every 6 Months or 1 Year DR Test happens. In real time down time is required.

Pre-step:

1. Disable all backup, copy and restore jobs, alert job

2. Take a t-log backup for Primary database with no recovery mode…

Backup log dbname to disk=’path’ with no recovery

After primary becomes secondary server. Role changed from ONLINE TO RESTORING MODE


3. Same like your fail-over operations; compare your backup and copy folders, move files

4. Restore secondary database by using t-log backup with recovery to change the role. Now secondary database will be in online state (Role swapped)


5. Start reverse configuring log shipping from secondary to primary server [switchover].

Post Activities:

> Inform application to point connection to the current primary which was previous secondary to test.


> If apps and users are able to work then DR test is successful.

SCENARIO: 5 WHAT ARE THE LOG SHIPPING JOBS?

Primary server: Backup job

Secondary server: Copy and restore jobs

Monitor Server: Alert job


Note: If monitor server is not included then alert job create in both primary and secondary

SCENARIO: 6 WHAT ARE THE REASONS LOG SHIPPING FAILS? IF FAIL WHAT HAPPENED ?

Reasons:
1. backup jobs fails
2. copy job fails
3. restore job fails
4. log backup fails
5. agent not working
6. disk space
7. network issue
8. permission issue
9. incorrect network path
10. domain issue

11. .tuf file deleted ---- restore job will fail
12. Database not available
13. Instance not available
14. Recovery model change

SCENARIO: 7 WHAT ARE THE COMMON ERRORS NUMBERS WILL GET IN LOG SHIPPING?

Error:
14420 [Primary server –backup job related]
And

14421 [Secondary server copy and restore jobs]



SENEARIO: 8 WHAT IS .TUF [TRANSACTION UNDO FILE] PURPOSE? OVERVIEW


TUF File (Transaction UNDO file): Contains only uncommitted transactions and create in secondary server but not in primary...ONLY SECONDARY DB IS IN STANDBY MODE

A .TUF file is a Microsoft SQL Server Transaction Undo file.

The TUF file basically contains the information with respect to any modifications that were made as part of incomplete transactions at the time the backup were performed.

> This file contains uncommitted transactions

> This file creates only in STANDBY mode

> This file resides always under secondary server> copy folder

Note: If .tuf deleted, log shipping is not going to work.

Is there any way to re-create .tuf file... i have configured log shipping but unfortunately tuf file has been removed and now log shipping has been stop and we are unable to up the same.?

Ans: Impact is only restore job failed in secondary. We have reconfigure the log shipping one more time to re-create .TUF file

Note: If I delete .TUF file then impact to restore job but not copy and backup jobs.



  • .Tuf file is updates dynamically whenever any log backup file restore
  •  .TUF file only creates in secondary server > copy folder>
  • No .tuf file creates in “NORECOVERY “mode. >.WRK [work file]: Work file creates in secondary server and the main purpose is it contains copy job information. This file used by only log shipping copy job

WORK FILE [.WRK]:- To manage the file copy process from Primary server to Secondary server, .WRK files temporarily generated.

Means, The .wrk file got generate when the transaction log backups files are being copied from the backup location (Commonly at Primary Server end) to the secondary server by the agent job named as LS-Copy on the secondary, and when file copied completely at secondary server, they renamed to the .trn extension.

The temporary naming using the .wrk extension indicates/ensure that the files will not picked up by the restore job until successfully copied.

Wrk file creates in both no recovery and standby mode

SENEARIO: 9 IF I DELETE SOME RECORDS IN DATABASE WHAT HAPPEN TO LOG SHIPPING IN SECONDARY DB

Yes Secondary server database records will be deleted

SENEARIO: 10 IF I TRUNCATE SOME RECORDS IN DATABASE WHAT HAPPEN TO LOG SHIPPING IN SECONDARY DB

Yes Secondary server database records will be truncated

SCENARIO: 11 IF I SHRINK PRIMARY DATABASE WHAT HAPPEN TO LOG SHIPPING IN SECONDARY DB?

Shrinking: if u use shrinking database release spaces in os to your disk
If we perform shrink in primary database automatically shrinking replicate to secondary database as well...No impact to log shipping configuration














Troubleshooting Log Shipping:

1) Jobs disabled can be a cause for LS failure.

2) Backup Share permission issues.

3) Space issues in the backup share/local copy location.

4) SQL Server Agents stopped at Primary/Standby/Monitor.

5) Manual log backup can cause LS break.

6) Recovery Model changes from Full/Simple.

7) Backup/Copy/Restore Job owner change can cause permission issues and break LS.

8) Network Issues with Backup Share.

9) WITH RECOVERY statement fired at Standby server can bring secondary database ONLINE breaking LS.

10) Service Account changes can lead to permission issues.

11) Log backups getting corrupted

12) Backup schedule is changed can cause lot of delay which might raise an alert.

Friday, January 10, 2020

HIGH AVIALABILITY

High Availability


  • High Availability: SQL Server provides several options for creating high availability for a server or database.
  • HA is to continue operations when a component fails. This is usually a hardware component like a CPU, Power supply, Disk failure, Memory failure or the complete server.
  •  With HA there is usually no loss of service when a component fails.

The high-availability options include the following:


  • LOG SHIPPING ---- Database Level 


  •  DB MIRRORING ---- Database Level 
  •  REPLICATION ---- Table Level
  •  CLUSTERING ---- Instance level

LOG SHIPPING:

What is Log Shipping? 

Log Shipping Definition:


Log Shipping is used to synchronize the Distributed Databases. Synchronize the database by copying Transaction logs, Backing up, Restoring data. SQL Server used SQL Server Job Agents for making those processes automatic.
Or
• It automatically sends transaction log backups from one database (Known as the primary database) to a database (Known as the Secondary database) on another server. An optional third server, known as the monitor server, records the history and status of backup and restore operations. The monitor server can raise alerts if these operations fail to occur as scheduled.
Or
• Shipping the transaction log backups from one server to another server called "Logshipping"


The main functions of Log Shipping are as follows:

• Backing up the transaction log of the primary database

• Copying the transaction log backup to each secondary server

• Restoring the transaction log backup on the secondary database

Log shipping pre-requisites:

1. Minimum 2 Sql server instances and 3 if we include monitor as well

2. Create database in primary server

3. Recovery model should be "FULL \BULK LOGGED"

4. SQL Server version should same and edition also same.

5. Create backup share in primary and provide read\write permissions

6. Create a copy share in secondary server and provide minimum read permissions.

7. SQL Service account should have the permissions on backup and copy share.

8. SQL Server services should run on domain level accounts.

9. Should be any edition for log shipping except express edition [No agent service]

10. SQL Server agent should be up and running fine.

11. Collation settings should be same.

Log shipping terminologies (or) Components:


  •  For implementing Log Shipping, we need the following components - Primary Database Server, Secondary Database Server, and Monitor Server.
  •  Primary Database Server: Primary Sever is the Main Database Server or SQL Server Database Engine, which is being accessed by the application. Primary Server contains the Primary Database
  • Secondary Database Server: Secondary Database Server is a SQL Server Database Engine or a different Server that contains the backup of primary database. We can have multiple secondary severs based on business requirements.
  • It is a copy of primary database and it is restoring\stand_by (read_only) mode.
  • Maximum we can add 25 Secondary Servers.
  •  Monitor Server: Monitor Server is a SQL Server Database Engine which Track the Log Shipping process.
Note: If monitor server is included in LS configuration then alert job gets created in monitor server itself if monitor server is not included then alert job created in both primary and secondary server.

Log shipping Architecture:


Backup job:

A SQL Server Agent job that performs the backup operation.

Copy job:

A SQL Server Agent job that copies the backup files from the primary server to the secondary server.

Restore job:

A SQL Server Agent job that restores the copied backup files to the secondary database.

Alert job:

A SQL Server Agent job that raises alerts for primary and secondary databases when a backup or restore operation does not complete successfully within a specified threshold.

Architecture Points:

1. This is one of the database level HA option in SQL Server.

2. Initial full backup of primary database directly restore to secondary server.

3. Backup job take the transactional log backup of primary database into backup share.

4. From backup share copy job picks the t-log backups and copy to secondary server > copy folder.

5. From copy folder restore job picks the backup file and restore in secondary database

This is the continues process where Log shipping works.

Log shipping configuration steps:

1. Create backup, copy share and provide read_write permissions

2. Go to primary configure backup Job by providing local and network path.

In real time always use network path to place the backup files.

Note: Taking backup into local server is very fast compare to network path.

3. Connect to secondary instance > then provide copy share path and restore database mode.

1. No recovery: No users can able to access database

2. Standby: Database in read only mode and users can able to read the data.

Note: In log shipping secondary server database can be used for "reporting purpose".

4. Add monitor server if need and click OK

5. Verify log shipping status.

Advantages:

• Data Transfer: T-Logs are backed up and transferred to secondary server

• Transactional Consistency: All committed and un-committed are transferred

• Server Limitation: Can be applied to multiple stand-by servers

• Secondary database mode: Stand-by mode [Read_only]...Useful for reporting purpose

• Recovery model supports Full and bulk-logged…database to simple recovery will cause log shipping to stop functioning

• Edition is not necessary to be same for both primary and secondary servers.

Disadvantages:

• Failover: Manual

• Failover Duration: Can take more than 30 mins

• Role Change: Role change is manual

• Client Re-direction: Manual changes required