Skip to content

Home

  • Automate SQL Database Migration Using PowerShell from Source to Destination

    Make a quick fix for automating database migration across SQL Server versions using PowerShell and T-SQL scripting. Before moving on to a set of customised actions, I need to find several databases that can be moved using a standard procedure or at the very least a collection of standard activities. You could want to write general migration scripts and set up a system to automate their execution in these circumstances. A general method like this can be quite helpful if you’re going to iterate through several test sessions and want to migrate a fresh copy before each one.
    The following actions can be made to simplify the migrating process:

    1) The backup database from the Source Instance will be transferred.
    2) Restore the database that will be moved to the new instance.
    3) Refresh the allocation usage.
    4) Data purity can be used to check database consistency.
    5) Set the level of page verification.
    6) Set the database compatibility level to match the version of the destination instance (e.g. 120).
    7) Statistics should be updated.
    8) Remove any orphaned schemas (schemas automatically created for each database user to maintain compatibility but with no objects belonging to them).
    9) Fix Orphaned Users if any exists.
    10) Run Rebuild or re-organize all databases based on Migration Change window and the databases size.

    I’m automating the first two Point’s, databases today for backup and restore. I still have to finish automating the other points, but I’m working on it. I’m hoping that by sharing this backup and restore PowerShell script today, some of the DBAs may find it useful.
    You will use handlers that SMO will immediately call back when you execute backup and restore commands in T-SQL, and you will provide a straightforward progress notification similar to the ones offered by SSMS or sqlcmd. Please remember that the completion handler does nothing more than display the error.

    A handful of the PowerShell script’s functions include Get-SQLInstance, DatabaseFullBackup, and RestoreDatabaseFromFullBackup.

    Click the link below top open the PowerShell script that will implement backup/restore procedures for each database which we want to migrate to complete the migration :
    https://sqlserveradmintutorial.blogspot.com/2023/01/powershell-script-to-backuprestore.html

    A few variables in the script above, including SourceInstanceName, Destination Instance Name, Backup Path, DATA File Path, LOG File Path, and Databases Names, will need to be updated.
    The script will start taking database backups from the source instance and restoring them to the destination instance once all the parameters have been customised to your environment.

    Additionally, my destination instance doesn’t have any databases. Using the aforementioned PowerShell script, I’m currently backing up and restoring my “DB1,” “DB2,” and “DB3” databases. When I run the script, it first takes a backup of the first database, restores it to the destination instance once the backup is finished, and then takes a backup of the second database. It starts restoring the second database once the backup is finished.

    As soon as the DB1 Database backup is complete, the destination DB1 Database is instantly restored. After the restoration is complete, the backup for the following database (the DB2 database) is initiated, etc.,
    Migration of the databases I mentioned in the Databases Field was completed by the script.

    The migration of all three databases from the source instance to the destination instance has been completed successfully. Now all you have to do to migrate your given databases is to run the script in PowerShell ISE.

  • DISCONNECTED State Issue on Alwayson Secondary replica

    We had a strange issue with the Always on 3-Node replica today.

    I got onto the server to Implement a change, which was to apply a SQL Patch to SQL Server 2016 SP2.

    I have discovered that my AG’s health isn’t looking well though, and the databases in that secondary replica’s state are in RESTORING mode.

    If a secondary replica in an Always On availability group is in a DISCONNECTED state, it means that it is no longer able to communicate with the primary replica or other secondary replicas in the availability group. This can be caused by a number of issues, such as network connectivity problems, configuration issues, or resource contention on the secondary replica.

    Here are a few steps you can take to troubleshoot the issue:

    • Check the network connectivity: Make sure that the secondary replica has a stable and reliable network connection to the primary replica and other secondary replicas. Check for any network configuration issues or problems with the network equipment.
    • Check the availability group configuration: Make sure that the availability group configuration is correct and that the secondary replica is correctly configured to participate in the availability group.
    • Check the resource utilization: Make sure that the secondary replica has sufficient resources, such as CPU and memory, to keep up with the replication workload.
    • Check the error log: Check the SQL Server error log on the secondary replica to see if there are any error messages related to the disconnected state.
    • Check the Windows Application Event log: Check the Windows Event log for any events that may indicate the reason for the disconnected state.
    • Check the status of the synchronization: Check the status of the data synchronization between the primary and secondary replica. You can use the sys.dm_hadr_database_replica_states dynamic management view to check the synchronization status.
    • Check for any known bugs and troubleshoot accordingly: Check if there are any known bugs in the version of SQL Server you are running that could cause this issue.
    • Try to re-connect the replica: If all the above steps fail, you can try to manually force a reconnection between the primary and secondary replica using the ALTER AVAILABILITY GROUP command or using the SSMS GUI.
    • It’s important to note that some of these steps may require a downtime, so it’s recommended to have a plan in place before attempting any troubleshooting steps.

    So, googled for below error:

    Error Message 35201:

    When attempting to connect to availability replica ‘My replica’ with id [availability group id], a connection timeout occurred. Either a network or firewall problem, or the replica’s endpoint address is not the DB mirroring endpoint of the host server instance.
    There is no connection between this secondary replica and the primary replica. DISCONNECTED is the connected state.


    I found the following KB article on support.miscrosoft.com.

    https://support.microsoft.com/en-us/topic/kb3213703-fix-an-always-on-secondary-replica-goes-into-a-disconnecting-state-10131118-b63a-f49f-b140-907f77774dc2

    The SQL instances version, SQL 2016 SP2, made it useless for my current organization, though.

    As a result, we alerted the Windows Team, who are looking into it. We also discovered that some cluster issues had been recorded in Cluster Events on Windows Server Failover Cluster Manager.

    Due to the fact that the data is not being moved to one of the secondary replicas, we are receiving disk space alerts in the interim.

    In order to test if the issue was rectified, we requested permission from the Client to reboot the problematic secondary replica, as stated on the Microsoft page above.

    The customer provided his okay, we rebooted the replica because the secondary replica isn’t being used in any way, but the issue still exists.

    The SQL instances version, SQL 2016 SP2, made it useless for my current organization, though.
    As a result, we alerted the Windows Team, who are looking into it. We also discovered that some cluster issues had been recorded in Cluster Events on Windows Server Failover Cluster Manager.
    Due to the fact that the data is not being moved to one of the secondary replicas, we are receiving disk space alerts in the interim.
    In order to test if the issue was rectified, we requested permission from the Client to reboot the problematic secondary replica, as stated on the Microsoft page above.
    The customer provided his okay, we rebooted the replica because the secondary replica isn’t being used in any way, but the issue still exists.

  • How to fix Slow running queries on SQL Server

    As a DBA, we carry out all the fundamental checks, including examining CPU and memory consumption, any blockings, deadlocks, locks, fragmentation, statistics updating, and so forth, when a user approaches you and complains that his query is taking too long to finish. After completing all of these checks, if the user is still experiencing slowness, we must carry out the procedures listed below to diagnose or resolve SQL Server queries that are running slowly.

    Identify the slow-running queries: Find the slow-running queries by using SQL Server Management Studio (SSMS), which allows you to see the queries that are presently running and their current execution times. The SQL Server Profiler can be used to track down queries that are running more slowly than intended. You may use the profiler to filter and sort the traces depending on several factors including duration, CPU usage, reads, and writes.

    Analyze the query execution plan: After you’ve found the slow-running query, you can utilize the execution plan to learn more about how it’s being processed. The execution plan displays the steps that SQL Server takes to carry out the query, together with the operations’ projected prices and their respective orders. You can find out if there are any possible indexing, join, or other query-related performance problems by looking at the execution plan.

    Check for missing or outdated statistics: The query optimizer needs up-to-date information about the data distribution in the tables utilized in the query in order to produce effective execution plans. The optimizer might make less-than-optimal decisions and the query might execute more slowly than anticipated if the statistics are incomplete or out of date. To update the statistics, use either the UPDATE STATISTICS statement or the sp updatestats stored procedure.

    Check for blocking: When one query has a lock on a resource that another query needs to access, blocking occurs. Performance may be slowed as a result of the second query having to wait until the lock is released. To check for any potential blocking, utilize the sys.dm exec requests dynamic management view.

    Check for resource contention: If the query is running slowly, use Performance Monitor to look for resource contention, such as high CPU or memory utilization. The resources that are leading to the highest waits can also be identified using the dynamic management view sys.dm os wait stats.

    Check for hardware issues: Look for any hardware problems that might be slowing down the query, including disc constraints or high network latency. To evaluate disc performance and locate any bottlenecks, utilize tools like Perfmon, SQLIOSim, and SQLIO. 

    Optimize the query: Once the source of the poor performance has been found, you can improve the query by altering the query, the indexing, or other database infrastructure components. Adding or changing indexes, rewriting the query to employ more effective join methods, and using stored procedures rather than ad-hoc queries are all examples of common optimization strategies.

    Monitor Performance: You may find and fix slow-running queries before they become serious issues by routinely monitoring the performance of your SQL Server instances, including query performance. You may track the performance of your SQL Server instances and fix any issues using the built-in performance monitoring tools in SQL Server, including SQL Server Management Studio (SSMS), SQL Server Profiler, and dynamic management views (DMVs).

  • Server Side and SQL Side Requirements for SQL Server 2019

    SQL Server 2019 Requirements:

    the necessary conditions for installing SQL Server 2019 on Windows servers.

    Requirements for Hardware:

    • The disk space requirements for SQL Server will vary depending on the components you choose when installation, with a minimum of 6GB needed on the drive where SQL is being installed.
    • It needs at least 1GB of RAM, although Microsoft advises 4GB, and depending on the size of the database, we might need to increase the RAM for better performance.
    • Note that only x64 processors are supported for SQL Server installation. MS no longer provides support for x86.

    Requirements for Software:

    • Windows Server 2016 or Windows Server 2019 is the minimal operating system requirement for SQL Server 2019.
    • It needs the.NET Framework 4.6.1, which Windows Server 2016/Windows Server 2019 will have already installed.
    • Additionally, it is preferable to have.NET Framework 3.5 installed because Database Mail needs it.

    Supported Operating systems for SQL Server 2019:

    SQL Server 2019 Supported Editions

    EntDevStanWebExpress
    Windows Server 2019 EnterpriseYESYESYESYESYES
    Windows Server 2019 StandardYESYESYESYESYES
    Windows Server 2019 DatacenterYESYESYESYESYES
    Windows Server 2016 EnterpriseYESYESYESYESYES
    Windows Server 2016 StandardYESYESYESYESYES
    Windows Server 2016 DatacenterYESYESYESYESYES

    Support on Server Core Operating System:

    The following Windows Servers are compatible with installing SQL Server 2019 on Server Core:

    Windows Server 2016 Core Edition

    Windows Server 2019 Core Edition

    You may obtain the SQL Server 2019 Installation Media file from the following link:

    https://www.microsoft.com/en-in/evalcenter/evaluate-sql-server-2019

    Page File Configuration:

    Servers are configured by setting a pagefile with 1.5 times of memory (1.5*Physical Memory) to obtain a memory dump for upcoming examination.

    Disk Volumes Details:

    Disk performance must be taken into account for SQL and some applications that exhibit high I/O behavior. Data and log volumes must be on different disks or volumes.

    Example:

    VolumeDrive Mount PointSize in GBDisk Speed
    SQL DATAG:\SQLDATA250 GBFAST
    SQL LOGSG:\SQLLOGS300GBVERY FAST
    SQL TEMP LOGG:\SQLTEMPLOG50GBVERY FAST
    SQL TEMP DATAG:\SQLTEMPDATA50GBFAST
    SQL BACKG:\SQLBACK500 GBSTANDARD

    Note: Mount point mount points do not inherit from mount point root permissions. These root permissions must be manually copied and set to the mount points.

    Firewall Security:

    TCP/UDPPORTDescription
    TCP1433SQL Server/Availability Group listener (Default Port can be changed)
    TCP5022SQL Server DBM/AG endpoint (Default port can be changed)
    UDP1434SQL Server Browser
    UDP2382SQL Server Analysis Services Browser
    UDP2383SQL Server Analysis Services Listener
    UDP49152-65535Dynamic TCP\UDP (Defined Policy or by Company)
  • Troubleshooting steps for SQL Connectivity Issues

    You might have observed DBAs attempting to resolve SQL connectivity problems. So, I decided to post some tips here on how to fix SQL Server connectivity issues. Based on my experience, I’ve provided the essential actions to do below. The following are the things I look for while trying to connect to SQL Server but failing:

    1. SQL Server configuration Issue
    2. Firewall Issue
    3. Network Issue
    4. Logon Issue
    5. Configuration Issue in Application

    SQL Server configuration Issue:

    • Verify that the machine hosting SQL Server is up and listening on protocols.
    • In SQL Server Configuration Manager, protocols can be verified and enabled.
    • Named Pipes and TCP protocols must be enabled for SQL Server to connect remotely.
    • We may check whether the server is listening on protocols in the SQL Server Errorlog.
    • If the SQL instance is a named instance, make sure SQL Server Browser is operating on the machine.

    Firewall Issue:

    • Firewalls can prevent client and server SQL connection requests.
    • This can be fixed by disabling the firewall, however this is only a temporary solution because it can be permanently turned off at any time.
    • Adding SQL Server and SQL Browser to the Firewall Exception list will permanently cure issue.
    • Provide the port number in which SQL is listening while adding the exception for the SQL Server DB Engine.
    • When adding a SQL Browser exception, make sure to include UDP port 1434.

    Network Issue:

    • The Ping response from your source server to the target server is the first indicator of SQL connectivity.
    • From the client machine to the SQL Server box, you may also use Telnet to test the UDL.
    • Check nslookup; a misconfigured DNS entry may be the root of the SQL connectivity problem.

    logon Issue to SQL Server:

    • This is distinct from the examples mentioned above. Based on the error message we receive from the application team, this has to be troubleshot.
    • Due to a problem with login connectivity to SQL, each individual case must be examined.
    • If a login attempt is made to a SQL server and is unsuccessful, errors can be seen in the server’s event viewer and SQL error logs.
    • We can examine the logs and resolve the problem.

    Configuration Issue in Application:

    • An incorrect setting in the application can also cause a problem with SQL connectivity.
    • In the application configuration file, look for the connection string parameters.
    • Additionally, verify the login account application being used to connect to SQL.
    • If it is a SQL account, find out which authentication mode is enabled in the SQL instance properties.
    • Check the account’s permissions level as well.

    Incorrect connection string:

    An improper connection string is one of the most frequent reasons of problems with SQL connectivity. An improper server name, database name, or authentication technique could be at blame for this.

    Incorrect SQL Server instance name:

    In the connection string, the client must include the right SQL Server instance name. Make sure you are using the right instance name.

    These are a few typical reasons why SQL connectivity problems occur, but the precise reason can change based on the circumstance. You can pinpoint the issue’s root cause and choose the best course of action by using the troubleshooting procedures I discussed previously.

  • Some of the critical issues that a SQL Database Administrator (DBA) may face(Part-2)

    Continuation of Part-1

    Scalability: To make sure that the database can handle the increased load as the data and the number of users expand, a DBA may need to plan for and execute scalability solutions. It may be necessary to increase the number of physical resources, such as servers or storage, and to adopt technologies like sharding or replication to spread the workload across a number of servers.

    Scalability in SQL databases refers to the database’s capacity to manage growing volumes of data and users without experiencing performance degradation. Scalability in SQL databases can be attained in a number of methods, including:

    • Horizontal scaling: This entails expanding the database cluster’s machine count in order to enhance capacity.
    • Vertical scaling: Increasing the capacity of a single computer by adding more resources, such as CPU, memory, and storage.
    • Sharding: Data is divided into smaller portions and stored on different devices using a process known as sharding.
    • Replication: This entails making copies of the data on many machines so that another one may take over if one fails.
    • caching: To minimize the amount of disc reads, caching includes storing frequently requested data in memory.

    Additionally, the scalability of your database system matters. For instance, while certain database systems, like Amazon Aurora, Google Spanner, and CockroachDB, have built-in scalability, others, like MySQL and PostgreSQL, may need additional configuration to achieve the same level of scalability.

    High availability: To make sure that the database is highly accessible so that users can access it even in the event of hardware failures or other problems, DBAs may need to put solutions in place. In order to offer automatic failover in the event of a failure, this can involve building redundant hardware, such as numerous servers in a cluster, and technologies like replication or mirroring.

    In SQL databases, high availability means that the database may continue to function and be accessed by users notwithstanding a failure. In SQL databases, high availability can be attained in a number of ways, including:

    • Replication: If one system fails, another can take over and continue processing requests since there are numerous copies of the data on several machines.
    • Clustering: When several machines cooperate, they can each handle requests independently in the event that one fails.
    • Failover: Changing to a backup server while the primary server is down is known as failover.
    • Load balancing: By dividing the workload among several machines, the others may continue processing requests even if one fails.
    • Backup and recovery: It is possible to restore the data in the event of a failure by periodically backing up the data and having a recovery plan in place.

    The database system you use will determine the high availability solution; some, like Amazon Aurora, Google Spanner, and CockroachDB, are engineered to be highly available out of the box, while others, like MySQL and PostgreSQL, may need extra configuration to achieve high availability.

    Data migration: In the event of an upgrade or migration, for instance, DBAs may need to migrate data from one database to another. This may entail organizing and carrying out the migration, testing the transferred data, and resolving any problems that may come up.

    Data migration, often known as SQL database, is the process of moving data from one database system to another or from one version of a single database system to another. There are many reasons to carry out this practise, including:

    • upgrading the database system to a newer version
    • combining several datasets into one database
    • switching to a new database system
    • Hardware or network infrastructure changes

    The migration of a SQL database often comprises numerous processes, including:

    • Planning entails identifying the data that must be migrated, choosing the target database system and version, and developing a strategy for doing so.
    • Extraction: This entails taking the data from the source database system and changing it into a format that the target database system can import.
    • Loading: Adding the extracted data to the intended database system is known as loading.
    • Verification: Verification entails checking the accuracy and consistency of the data as well as if it has been effectively moved.
    • Maintenance: Updating the indexes, constraints, and other database objects is part of maintenance, which involves keeping up with the migrated data.

    The Data Transfer Assistant (DMA) for SQL Server is one of many tools that can assist in automating the migration process.

    Monitoring and problem-solving: DBAs must keep track of the database’s functionality and performance, locate problems, fix them, and take appropriate action to prevent further problems. This entails keeping track of important performance indicators, such as CPU and memory utilization, as well as locating and eliminating bottlenecks. It also entails integrating monitoring and alerting technologies, keeping a proactive eye out for prospective problems, and taking preventative measures as necessary.

    Utilizing a variety of tools and approaches, SQL database monitoring and problem-solving involves keeping track of the functionality and overall health of a SQL database as well as locating and fixing any potential problems. Among the crucial areas to keep an eye on and troubleshoot are:

    • Resource utilisation: This involves keeping an eye on the database server’s CPU, memory, disc, and network consumption and locating any bottlenecks that might be contributing to performance issues.
    • Database performance: This involves keeping track of the database’s own performance, including the number of connections, response time, and queries per second.
    • Performance of individual queries: This involves keeping track of each query’s execution time, number of returned rows, and number of disk reads.

    There are many tools available for monitoring and troubleshooting the SQL Database, including:

    • SQL Server Management Studio (SSMS)
    • SQL Server Profiler
    • Dynamic Management Views (DMVs)
    • Performance Monitor
    • SQL Server Error Logs

    SQL Server Agent Additionally, a lot of cloud service providers, like AWS, Azure, and Google Cloud, have their own SQL database monitoring and troubleshooting tools that may be utilized on those platforms.

    In order to be informed immediately of any potential concerns, it’s crucial to set up alerts and notifications.

  • Some of the critical issues that a SQL Database Administrator (DBA) may face(Part-1)

    Issues with database performance: A DBA may need to investigate and fix issues with slow query execution, high CPU utilization, and other performance-related issues. This may entail locating and improving queries that execute slowly, indexing tables, and modifying configuration options to enhance performance.

    Poor performance in a SQL database can be caused by a number of typical problems, including:

    • Insufficient hardware resources: The database server may not be able to handle the demand it is receiving if it does not have enough memory, disc space, or computing power.
    • Poorly optimized SQL queries: may take longer to execute and place extra stress on the database server if they are utilized to access the database.
    • Lack of indexes: If the database tables do not contain the necessary indexes, the database engine will have to scan the entire table to retrieve the requested data, which could cause query performance to sluggish.
    • Concurrent access: When several users or programmers attempt to access the database simultaneously, resource congestion and performance degradation may result.
    • Blocking and Deadlocks: When one transaction requests a lock type that is already being held by another transaction, the second transaction will be blocked. When it happens frequently, performance may suffer.
    • Data fragmentation: If a table’s data gets fragmented, the database engine will have to search through more data to find the needed information, which might result in subpar performance.
    • Out-of-Date Statistics: The query optimizer may provide suboptimal plans, which will have a negative impact on query performance, if the statistics it uses to construct execution plans are out-of-date.

    Data corruption: DBAs may be required to identify and fix problems with data corruption, which can happen as a result of hardware malfunctions, software flaws, or other reasons. Running diagnostic software to find corruption, recovering from backups, and manually fixing the data can all be part of this process.

    Corrupt of SQL data or databases can happen for a number of reasons, including:

    • Hardware failure: Data corruption may result from failing hardware, such as a hard drive, on which the database is stored.
    • Software flaws: Data corruption may result from flaws in the database management system or in an application that interacts with the database.
    • Power outage: If the power goes out while a database transaction is in progress, data corruption may result.
    • Malware or viruses: If the database server becomes infected, it may corrupt data.
    • Human error: Data corruption can occur if a user unintentionally edits or deletes data in the database.
    • Concurrent access: If there is insufficient concurrency management and numerous users or apps attempt to access the database simultaneously, data corruption may result.
    • Disk space: Data corruption might result from running out of disc space.
    • Database corruption can result in data loss and make the database unavailable. To lessen the effects of data corruption, it is crucial to develop a disaster recovery plan and perform regular backups. It is advised to use a repair tool offered by the database management system to rectify a corrupted database before attempting to restore it from a recent backup.

    Backup and recovery: DBAs are in charge of making sure that the database can be restored in the case of a catastrophe, and they may need to troubleshoot and fix backup and recovery-related problems. As part of this, frequent backups must be configured, tested to ensure they can be successfully restored, and disaster recovery plans must be made.

    A SQL database is backed up and recovered by making a copy of the database that can be used to restore the database to an earlier state in the event of data loss or corruption.

    A SQL database can be backed up using a number of different techniques, such as:

    • Full backup: This produces an exact duplicate of the whole database, including all of the data, structures, and log files.
    • Differential backup: This makes a copy of all database changes made since the last complete backup.
    • Transaction Log Backup: This makes a clone of the transaction log files, which serve as a record of all database modifications.
    • File or Filegroup Backup: This makes a copy of a database’s specified files or file groups.
    • To guarantee that they can be utilized to recover the database in the event of a disaster, it is crucial to plan routine backups and test them. In order to prevent against data loss due to hardware failure or other problems, it is also advised to keep backups in a different location or in the cloud.
    • You can utilize the restore feature offered by the database management system to recover a SQL database. The restoration of the database from a backup is followed by the application of any transaction log backups made after the full or differential backup was made.
    • These basic types of recovery models: Simple and Full. The recovery method may differ slightly based on the recovery model of the database. Additionally, a database can be swiftly recovered using advanced recovery methods like Point-in-time recovery, Database mirroring, and Always On Availability groups.

    Security: DBAs are responsible for making sure that the database is protected from hacking and that personal information is kept safe. This involves activities like creating user accounts, providing access, and keeping an eye out for security breaches. In order to protect the data, it also entails putting security measures in place like encryption, firewalls, and intrusion detection systems.

    Protecting sensitive information and guaranteeing the integrity and availability of the database require taking steps to secure a SQL Server and database. Some typical actions that can be made to secure a SQL Server and database are listed below:

    Authentication and Authorization: The SQL Server and database should be configured to utilize the most secure authentication mechanism available, such as Windows Authentication or Multi-factor Authentication. Authentication and Authorization: Use strong and unique credentials for the SQL Server and database.

    Encryption: To prevent unwanted access, encrypt sensitive data kept in the database, such as credit card numbers or personal data. Transparent Data Encryption (TDE), Always Encrypted, and Cell-level encryption are just a few of the encryption options that SQL Server supports.

    Firewall: Set the firewall up so that only traffic from reputable sources is permitted while blocking all other traffic. This may assist in limiting illegal database access.

    Auditing: Enable auditing on the SQL Server and database to keep track of and record any alterations to the database, including data edits, logins, and backups. This can aid in spotting security issues and addressing them.

    Least Privilege: Assign users and roles the minimal set of rights required for job completion. This may aid in limiting unauthorised access and information leaks.

    Patching and updating: Make sure you apply the most recent security patches and upgrades to the SQL Server and database software. This could aid in preventing known vulnerabilities.

    Backup and disaster recovery: Make regular database backups, test the backups to make sure they can be utilised to restore the database in the event of data loss or corruption, and implement disaster recovery procedures.

    Network segmentation: To isolate the database server from other systems and decrease the attack surface, segment the network and instal it in a different VLAN.

    It’s crucial to remember that maintaining security requires regular monitoring and updating in order to keep up with the evolving threat landscape.

  • Database for the Availability Group is Not Synchronizing / Recovery Pending State

    Database for the Availability Group is not synchronising and recovery is pending.

    This error message comes when SQL Server is unable to access the database log files of your availability group database or databases. Both the Primary and Secondary Replicas are susceptible to this issue.

    Problem on Primary Replica:

    To determine whether the database log file is there or not, check the SQL Server error log on Primary Replicas.
    The error code 9001 is also displayed in cases where the log file cannot be accessed.
    In order to resolve the issue on Primary Replicas, we should:

    1. Check the database files’ accessibility.
    2. Try suspending and restarting the availability group database; this might not fix the problem.
    3. Use the ALTER command listed below ALTER database DBNAME set ONLINE to make the database ACTIVE.
    4. Check the database status after a few minutes; it should now be GREEN and HEALTHY.

    If the problem recurs on the second copy:

    Check the SQL Server error log for any error messages to determine whether the database log file is accessible or unavailable. The error code 9001 is recorded if the log file cannot be accessed.
    The actions below should be followed to fix the issue on secondary replicas:

    1. Check the database files’ accessibility.
    2. Try suspending and resuming the availability group database; this might not fix the problem.
    3. We are unable to execute the Alter command to activate the Availability Group database since the secondary replica is being restored. On the sys.databases reports, the database status is ONLINE, though.
    4. Since the secondary replica SQL Instance will initiate the recovery process for all databases, we must RESTART it. Additionally, it will help with database synchronization with the primary replica and successful recovery of the problematic Availability Group database.
  • Steps to Follow for Best Practice After Installing SQL Server

    Post SQL Server Installation Steps :

    1. Add the group SQLServerMSSQLUser$ServerName$InstanceName to the Root path of drive(ex: D) with Full Control, and List Folder Contents Permissions for Data, Log and Tempdb Drives.
    2. Add the group SQLServerMSSQLUser$ServerName$InstanceName to SQLData folder on Data and Tempdb Drives with Full Control.
    3. Add the group SQLServerMSSQLUser$ServerName$InstanceName to SQLLogs folder on Log Drive with Full Control.
    4. Remove the Active Directory (AD) Service User Account from the Root Path. (This will decouples the Service Account explicitly and rely on the group) .
    5. Add the groups SQLServerMSSQLUser$ServerName$InstanceName, SQLServerSQLAgentUser$ServerName$InstanceName, or any other group accounts to Backup or sharing folders as needed.
    6. In Local Security Policy, add the group SQLServerMSSQLUser$ServerName$InstanceName to policies Lock Pages in Memory objects and Perform Volume Maintenance Tasks.
    7. Verify with Antivirus(Ex: Macaffe) team if they have excluded the folders Data, Log, Tempdb, any Backup file paths, and the SQL Server Binaries folders from AntiVirus Scans.
    8. Check\Verify backup is excluding mdf, ndf, and ldf files.
    9. Remove the Accounts Builtin\Administrators and Builtin\user logins.
    10. Enable the option All Login Auditing in the SQL Server Security Settings
    11. Enable the protocol TCP/IP and also change default port 1433.
    12. Enable the option remote DAC connections.
    13. Enable xp_cmdshell, OLE Automation and SQLCLR for the SQL Server Instance as required.
      a. Configure xp_cmdshell proxy account as needed.
    14. Enable the option Database Mail and configure default private and public accounts.
    15. Configure SQL Error Log retention with 99 log files
    16. Configure SQL Agent job to perform nightly .
    17. Configure SQL Server Maintenance Plan jobs for system and user database backups, index maintenance, CHECKDB, statistics updates, history cleanup and backup cleanup.
    18. Move the MSDB Database files to SQLData and SQLLogs .
    19. Reconfigure Tempdb Database with data files equal to 1/2-1/4 the physical CPU’s on the server based on load characteristics. Set all data files to equal size based on load characteristics in 4096MB increments for Datafiles, and 1024MB increments for Log files. Set AutoGrowth to 1024MB for data files and 512MB for Log file.
    20. Enable Trace Flag 1118 on SQL Server 2005/2008/2008R2/2012/2014 and 2016 for Tempdb.
    21. Change the recovery model for Model database to SIMPLE recovery, 2048MB as default datafile size and 1024MB as default logfile size. Set AutoGrowth to 1024MB for data files and 512MB for Log file.
    22. Set Max Server Memory based on RAM installed on Server and installation type (Latest\New Servers are all 64bit, but enable the option AWE as needed on 32 bit servers).
      a. 8GB RAM = 6144 Max Server Memory
      b. 16GB RAM = 12228 Max Server Memory
      c. 32GB RAM = 28672 Max Server Memory
      d. The above values are base values that can later be adjusted based on the Memory\Available MBytes counter value > 150 on the Server( based on your environment ).
      e. Memory configuration on clustered Instances should account for multiple instances on a one physical node in case of fail over.
      f. In general, we calculate Max. Server Memory using the formula: Max. Server Memory for a SQL Server Instance = (Total RAM available to the OS) – {(OS: memory pool, filesystem cache, PTE, desktop heap, Driver Images etc…) + (Non-buffer Pool region of SQL Server allocated to Multi Page Allocators, Worker Threads, COM, Extended SPs, Backup Buffers, CLR, Linked Server…) + (SQL Server Agent, Replication Agents, Bulk Copy, SSRS, SSAS, SSIS, and Full Text) + (Log shipping file copy depending on the size of log backups) + (Other SQL Server instances running in the box) + (Other applications running in the box (Antivirus, Monitoring Softwares, Compression softwares etc…) }
    23. Set max degree of parallelism sp_configure option based on the number of physical CPU cores installed and anticipated workload
      a. For OLTP servers, generally we set to 1/2 or 1/4 of the physical cores available on the server.
      b. we can Adjust up or down based on wait stats and load.
    24. Set cost threshold of parallelism sp_configure option based on the anticipated load.
      a. General default value of 5 is low for most OLTP workloads and should be increased.
      b. Base value of 20-25 used for most server installs.
    25. Add AD login (standard for environment and locked out in AD by default) for patching and emergency server access to Local Administrators Group.
    26. Set SA user password to standardized password that is changed quarterly on all servers and maintained in password safe.
    27. Install the SQL Server Performance Dashboard Reports
    28. Create default alerts for severities 16 through 25.
      SQL Server’s alerting system has the ability to notify operators whenever major things break inside the database. These include running out of space in log files, backup failures, failed logins and other things DBAs just need to be aware of. Don’t rely on this as your only SQL Server monitoring system, because it only sends alerts when it’s too late to take proactive action, but still, it’s better than nothing.

    The below script will set up an alert for severity 16. Copy this and repeat the same thing for 17-25, but change ‘Database Team’ to be the name of your default operator. Notice that @delay_between_responses is set to 60 – that means if it sends out an alert, it won’t repeat that same alert for 60 seconds. This is useful because when a database runs out of drive space, for example, all hell will break loose for a minute or two, and you don’t want hundreds of emails and pages per minute.

    USE [msdb]
    GO
    EXEC msdb.dbo.sp_add_alert @name=N’Severity 016′,
    @message_id=0,
    @severity=16,
    @enabled=1,
    @delay_between_responses=60,
    @include_event_description_in=1,
    @job_id=N’00000000-0000-0000-0000-000000000000′
    GO
    EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 016′, @operator_name=N’Database Team’, @notification_method = 7
    GO

  • SLIP STREAM SQL Server Installation (RTM + SP + Hotfix)

    Slipstream is a new installation method in SQL Server where SQL Server RTM + Service Pack + Hotfixes can be packaged together. This feature has been introduced from SQL Server 2008 SP1.

    I Just wanted to share Supporting Command Prompt Installation Scenarios please find below :

    • We can perform Installing, upgrading, or removing an instance and shared components of SQL Server on a local computer by using parameters and syntax specified at the command prompt.
    • We can also perform Installing, upgrading, or removing a FCI failover cluster instance.
    • We can perform SQL Server edition Upgrade from one edition to another edition of SQL Server.
    • Installing SQL Server instance on a local computer by using syntax and parameters mentioned in a configuration file. we can use this method to copy an installation configuration file to multiple computers for installation.

    This Command Prompt Installation is very helpful on Windows Server Core Machines  where we have only Command prompt Available to see after logging to Server , as the Server Core installation eliminates  services and other features  which are not required for the support .

    Below is how we should do to SLIP STREAM SQL 2014 and higher versions

    • Keep it ready with SQL Server 2014 media
    • Keep it ready with Needed SPs, CUs and Hotfixes that you want to Include
    • Keep all of the .exe files in one directory

    There are two types of slipstream methods.
    1)      Simple Slipstream

    2)      Advanced Slipstream

    Simple Slipstream (2014):
    This is command based installation which requires bit knowledge on installation parameters. Below are the parameters we have to know before starting with this installation.

          /Action =Install

          /UpdateEnabledTrue or 1    or    False or 0            

          /UpdateSource= MU   or    UNC path    or    .\Myupdates

    Advanced Slipstream (2014)

    As you guys helped by copying the SQL 2014 RTM Media and SP3 + KB on Server . I have created a New Folder called UPDATES inside SQL 2014 Media and copied both SP3 , KB [ here I think we need to follow Proper Naming Format for .exe files like SQLServer20XX-kbxxxxxx-Architecture.exe ( Hotfix ) and SQLServer20XXSPX-KBXXXXXXX-x64-ENU.exe ( Service Pack ) ]

    After performing above we need to do one more Important step is to Update DefaultSetup.ini File ( which exists in SQL 2014 Installation Media  ) by providing the Location of Patches  ( here we need to give Updates Folder Location )

    Specifying the location where SQL Server setup will obtain checking in Product Updates TAB in Installation

    UpdateSource = ” path of patches ” — It can be a local path or UNC shared path or if the patch folder is in Media folder it self we can give it like .\FolderName

    Now Once we Start Installation by initiating setup.exe and going through next steps we can see the difference at Product Update TAB

    By seeing this above can say  that we have Embed all the 3 ( Media + SP + Hotfix ) to make SLIP STREAM Installation ..

    Note : This SLIP STREAM Installation was introduced from SQL 2008 onwards but the process of doing Slip Stream in SQL 2008R2 is a bit more complicated compared with the latest releases

%d bloggers like this: