Setting up AlwaysOn High Availability involves setting up… Windows Failover Cluster via Windows Failover Cluster Manager SQL Server vis SQL Server Management Studio Prerequisite Make sure database has a full backup and recovery mode set to Full. Make sure the SQL Server service on each node has the proper permission on the server. (recommend using... » read more
Using SQL Server Configuration Manager To disable AlwaysOn Connect to the Windows Server Failover Clustering (WSFC) node that hosts the SQL Server instance where you want to disable AlwaysOn Availability Groups. On the Start menu, point to All Programs, point to Microsoft SQL Server 2014, point to Configuration Tools, and click SQL Server Configuration Manager. In SQL Server Configuration Manager, click SQL Server... » read more
Error: Failed to open the Windows Server Failover Clustering registry subkey ‘HadrAgNameToIdMap’. (Microsoft SQL Server, Error: 41030) Fix: Disable Always On Available Groups and then Re-enable Always On Available Groups from SQL Server Configuration Manager. Using SQL Server Configuration Manager To disable AlwaysOn Connect to the Windows Server Failover Clustering (WSFC) node that hosts the... » read more
Error: When trying to create a new Available Group in SQL Server Always On, you get the following error message… Failed to create the Windows Server Failover Clustering (WSFC) resource with the name 'xxxxx' and type 'SQL Server Availability Group'. The resource type is not registered in the WSFC cluster. Fix: Register the resource type... » read more
Specify Name Enter name of availability group. Specify Databases Database must meet prerequisites requirements. Full recovery mode is required. Specify Replicas “Add Replica” as Secondary role Select “Automatic Failover” for each server Select “Synchronous Commit” for each server Select “Readable Secondary” for each server Select Data Synchronization “Full” – when you want the wizard to... » read more
Problem: SQL Server database stuck in RESTORING state Sometimes the database is in a restoring state after restarting the machine or for some other reason. It usually happens with big databases when a long transaction is in progress and an unexpected server shutdown or restart occurs. Fix: Sources: https://www.mssqltips.com/sqlservertip/5460/sql-server-database-stuck-in-restoring-state/
Error: When trying to create a new cluster in Failover Cluster Manager, trying to add a new server gives the following error… The computer "xxxxx" is joined to a cluster. The computer is cloning from an existing computer that was part of a cluster. Fix: Open up PowerShell (as administrator) on that computer and type... » read more
Problem: Unable to start SQL Server services. Error: Error 1069: The service did not start due to a logon failure. Fix: 1. Make sure service account running the service is part of the “Backup Operators” group on the local computer. Control Panel\User Accounts\User Accounts -> Manage User Accounts 2. Make sure the service account running... » read more
Use the following to check the current range for the partition. Example: Extend existing partition from 2018 to 2019. Add additional file group first, then modify partition schema and function. No need to modify partition table itself if the table is already setup to use partition. Step 1: Add Additional File Group Step 2: Add... » read more
REPLACE Option Impact REPLACE should be used rarely and only after careful consideration. Restore normally prevents accidentally overwriting a database with a different database. If the database specified in a RESTORE statement already exists on the current server and the specified database family GUID differs from the database family GUID recorded in the backup set,... » read more