Posts

Common Errors During SQL Server Installation on Windows Server

-
Common Errors During SQL Server Installation on Windows Server Computer domain controller error – This occurs when you're trying to install SQL Server on a domain controller, which is not supported for some editions. Missing Java error (PolyBase) – SQL Server 2016 and later require a supported version of the Java Runtime Environment (JRE) if you select the PolyBase feature. Error code 0x84B10001 or 0x851A001A – Usually means your Windows Server version is incompatible or missing required components like the .NET Framework. Setup fails with DLL errors – For example, if SqlCommonDll.dll fails to load, it could be due to corrupt installation files or insufficient permissions. Instance already exists – Trying to install another instance with the same name as an existing one. MSI errors like 1603 or 1706 – These are generic Windows Installer errors, often due to permissions, missing prerequisites, or incomplete installation files. Where to Check for Instal...
Post a Comment
Read more

SQL Server Service Account Password Rotation (Including SPN Updates and AAG Handling)

-
  Runbook: SQL Server Service Account Password Rotation (Including SPN Updates and AAG Handling) Environment Primary Server: ServerA Secondary Server: ServerB SQL Server: AlwaysOn Availability Group (AAG) with Automatic Failover Task Breakdown 1. Before Change Start Tasks ✅ 1.1 Raise Change Ticket Open a Change Request (CR) in ITSM Jira tool. Add details: SQL Service account password rotation. Update SPNs. Application validation. Attach: Impact Analysis Backout Plan 1.2 Get CAB Approval Submit CR for CAB review and approval. Get approvals from: CAB QA Application owners 1.3 Create New AD Service Account. Request AD team to create: Username: SQLService_ServerA Password: Strong and non-expiring. Permissions: Add to Domain Users Add to Local Administrators Group Command to Add Account to Local Admin Group: net localgroup administrators "DOMAIN\SQLService_ServerA" /add 1.4 Grant SQL Server Access Connect to SQL Server as sys...
Post a Comment
Read more

How to fix a slow query performance issue in SQL Server related to MAXDOP (Maximum Degree of Parallelism)?

-
Step-by-Step: Tune MAXDOP for Better Performance Check Current MAXDOP Setting : Run this: EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'max degree of parallelism'; Understand the Default : Default is  0 , meaning SQL Server uses all available CPU cores for parallel plans. This can cause  CPU pressure  or  CXPACKET waits . Determine Optimal MAXDOP : Follow Microsoft’s recommendation: If server has ≤8 logical CPUs → set MAXDOP = number of CPUs If >8 CPUs → set MAXDOP = 8 For  NUMA nodes , set MAXDOP = number of CPUs per NUMA node (but not more than 8) Set MAXDOP : For example, to set MAXDOP to 4: EXEC sp_configure 'max degree of parallelism', 4; RECONFIGURE; Test Performance : Run the slow query again and compare execution time. Check for reduced CXPACKET waits in  sys.dm_os_wait_stats . Optional – Use Query Hint (Scoped) : If you don’t want to change the server-level setting: SELECT * FROM YourTable OPTION (MAXDOP 2);
Post a Comment
Read more

What is the backup strategy you uses for point in time recovery in SQL Server if the database corrupted?

-
Lets say my  backup strategy is: 1. Full - Weekly backup in non-business hours (At Sunday at 12:00 AM) 2. Differential - Daily once in non-business hours. (At Daily at 12:00 AM) 3. Transactional Log - Every 15 minutes. And the last backup was done at Apr 05, 2025 12:00 AM Situation: Employee_DB corrupted at Apr 05, 2025 12:10 AM and database is not accessible to and table. Point in time recovery solution: 1st of all, I will check the current database mode and will try to bring online the corrupted data base. SELECT DATABASEPROPERTYEX ('Employee_DB', 'STATUS') Lets understand the database state or mode: 1. ONLINE - Online state means the database is running fine. No issue with database state. 2. SUSPECT - SUSPECT states means that the database is not available for user access. In this database state, the database recovery process has started but not completed successfully. database went in suspect mode due to DB file corruption, unavailable database files or impr...
Post a Comment
Read more

What are the System databases and it uses in SQL Server?

-
System data bases are defined by Microsoft and required for SQL  server to function. These data bases include the Master, Model, MSDB, Temp DB, Resource, and Distribution databases, Which are utilized in replication, as well as the ReportServer and ReportServerTempDB databases for reporting services. USES are: 1. Master database - PURPOSE; stores metadata for  the SQL Server instance, including system configuration settings, login accounts, and database information. SIGNIFICANCE;  The master database is essential for SQL Server to start  and function; without it, the server wont operate.  CONTENT; Contains information about linked servers, TCP endpoints, and master keys. 2. Model database - PURPOSE; serves as the template for all new databases created  on the SQL server instance. SIGNIFICANCE; configuring the model database  efficiently can help ensure consistent settings across newly created database. CONTENT; contains default configurations  and...
Post a Comment
Read more

Tell me Server level permission and DB level permission in SQL Server?

-
Image
There are two type of  security role  SERVER LEVEL and   DATABASE LEVEL. Server level Fixed Roles: Fixed server-level role Description sysadmin Members of the  sysadmin  fixed server role can perform any activity in the server. Important: permissions can't be denied to members of this role. serveradmin Members of the  serveradmin  fixed server role can change server-wide configuration options and shut down the server. securityadmin Members of the  securityadmin  fixed server role manage logins and their properties. They can  GRANT ,  DENY , and  REVOKE  server-level permissions.  securityadmin  can also  GRANT ,  DENY , and  REVOKE  database-level permissions if they have access to a database. Additionally,  securityadmin  can reset passwords for SQL Server logins. IMPORTANT:  The ability to grant access to the Database Engine and to configure user permissions allows the securit...
Post a Comment
Read more

How to take login and user backup in SQL Server?

-
In the SQL Server Management Studio, navigate to Security Logins, Right -click the Logins root folder and select New Logins. Right -clicks the Logins  root folder  and  select New Login folder and select New Login . On the General page, specify a login name in  the Login  name field. Select authentication mode.  Take a backup to take a back up of your data base, follow these steps , using SQL server Management Studio or Transact-SQL; For SSMS; 1. Launch SQL Server Management Studio and connect to your SQL Server instance. 2. Expand the databases node in Object Explorer. 3.Right click the data base, hover over Tasks , and select Backup.. 4. Under destination confirm that the path for your backup is correct. If you need to change the path, select remove  to remove the existing path, and then Add to type in a new path, You can use the ellipses to navigate to a specific file. 5. Select ok  to take a backup of your database.
Post a Comment
Read more