Ali Shaik's Database Diary

  Differences between SQL Server and MongoDB backups and why it's important to know them? Here's a possible post: Feature SQL Server MongoDB Backup type Full, Differential, and Transaction Log Full and Incremental Backup size Usually larger due to transaction logs Smaller due to BSON format Backup format Binary, can be compressed and encrypted BSON, can be compressed Backup speed Slower due to transaction log processing Faster due to document-oriented storage Restore time Slower due to transaction log replay Faster due to simpler restore process Backup frequency Usually done daily Can be done more frequently due to faster backup process Backup tools Built-in tools such as SQL Server Management Studio, SQLCMD, PowerShell

  How to perform disaster recovery with two locations using SRDF:   Disaster recovery (DR) is an essential aspect of any IT infrastructure, especially for critical systems and applications such as SQL Server. There are many DR solutions available, but one of the most popular ones is SRDF, a data replication solution from Dell EMC. In this blog post, we'll discuss how to perform disaster recovery with two locations using SRDF and the responsibilities of the different teams involved in the process.   Identify the Primary and Secondary Locations: The first step is to identify which location is the primary data center and which is the secondary data center. For example, let's say that the primary location is in New York, and the secondary location is in Chicago.   Set up SRDF Replication: The storage team is responsible for configuring the storage arrays at both the New York and Chicago data centers for SRDF replication. This involves creating a replication relation

      A distributed availability group (AG) is a special type of availability group that spans two separate availability groups. Distributed availability groups are available starting with SQL Server 2016. The availability groups that participate in a distributed availability group don't need to be in the same location. They can be physical, virtual, on-premises, in the public cloud, or anywhere that supports an availability group deployment. The following figure shows a high-level view of a distributed availability group that spans two availability groups (AG 1 and AG 2), each configured on its own WSFC. The distributed availability group has a total of four replicas, with two in each availability group. Each availability group can support up to the maximum number of replicas, so a distributed availability can have up to 18 total replicas.       However, data movement is slightly different within distributed availability groups compared to a traditional availability

  reporting experiencing an issue whereby the “sa” account kept being locked out. In scenarios such as this the SQL Server Error Log is your friend, as it can be configured to record failed login attempts for an instance.  so instead we’re going to take a look at using the lesser known DMV  sys.dm_os_ring_buffers . Executing the query above produces a more readable result set such as the one below. You can see that in the query we joined our ring buffer data to the  sys.messages  catalog view in order to grab the  text  for the Error id. Splendid. Using this information we track down the precise source responsible for the Error: Login failed. ;WITH RingBufferConnectivity as (   SELECT         records.record.value('(/Record/@id)[1]', 'int') AS [RecordID],         records.record.value('(/Record/ConnectivityTraceRecord/RecordType)[1]', 'varchar(max)') AS [RecordType],         records.record.value('(/Record/ConnectivityTraceRecord/RecordTime)[1]', &#

As a SQL database administrator, you may need to use some Windows commands to perform certain tasks related to managing the SQL Server. Here are some common Windows commands that may be useful:  ipconfig: ipconfig: display the IP configuration for all network adapters on the server ipconfig /all: display detailed IP configuration for all network adapters, including DNS and DHCP information ipconfig /renew: renew the IP address lease for all network adapters ipconfig /release: release the IP address lease for all network adapters ping: ping <ip address>: check the connectivity to a specific IP address on the network ping <hostname>: check the connectivity to a specific hostname on the network ping -t <ip address>: continuously ping a specific IP address until stopped with Ctrl+C ping -a <ip address>: resolve the IP address to its hostname netstat: netstat: display all active network connections and ports on the server netstat -a: display a

  SQL Server 2022 has introduced a new Buffer Pool Parallel Scan capability that significantly improves the performance of operations that require scanning the buffer pool. The buffer pool is the memory area that SQL Server uses to cache data, and all pages must be copied into the buffer pool before they can be used in a query. Previously, operations that required scanning the buffer pool were slow, particularly on large memory machines. However, the new capability parallelizes buffer pool scans, utilizing multiple cores and resulting in faster processing times. The Buffer Pool Parallel Scan capability benefits both small and large database operations, making it a valuable addition for users of SQL Server. Additionally, it adds buffer pool scan diagnostics, improving supportability and providing insights into buffer pool scan events. This can help diagnose any issues related to buffer pool scans and optimize their performance. Customers running mission-critical OLTP, hosted service pro

1.What are the prerequisites for setting up AlwaysOn? 2.How do you set up replication in AlwaysOn environment? 3.How do you manage replication during Windows patching or failover if replication has been set up in AlwaysOn? 4.How do you sync logins in AlwaysOn? 5.How do you sync users in AlwaysOn secondary? 6.How do you add database files in AlwaysOn? 7.How do you perform an in-place upgrade of SQL Server in a AlwaysOn environment? 8.What is the procedure for SQL Server patching in AlwaysOn? 9.How do you failover a mirror server if replication has been set up? 10.What is the SPN concept in AlwaysOn? 11.What is file share in AlwaysOn? 12.How do you create multiple AlwaysOn listeners? 13.How do you check latency in AlwaysOn? 14.What is the command used to check latency in replication without using GUI? 15.What are DNS issues in AlwaysOn? 16.If a user is connecting to the primary and not able to connect to the secondary, and the secondary is in read-only mode, how do you fix the issue in A

  SQLCMD is a command-line utility provided by Microsoft SQL Server that enables you to connect to a SQL Server instance and run T-SQL commands and scripts from the command prompt. SQLCMD mode  can be used from SSMS as well  Here are some examples of how to use SQLCMD: Connect to a SQL Server instance using SQL Server authentication: sqlcmd -S MyServer -U MyUser -P MyPassword Run a script from a file: sqlcmd -S MyServer -U MyUser -P MyPassword -i C:\MyScript.sql Run a script and save the output to a file: sqlcmd -S MyServer -U MyUser -P MyPassword -i C:\MyScript.sql -o C:\MyOutput.txt Execute a single T-SQL command: sqlcmd -S MyServer -U MyUser -P MyPassword -Q "SELECT * FROM MyTable"

  Creating large indexes on production databases is a common task for database administrators. However, it can be a pain, especially when you need to create the index within a short window of time or manage the log growth. In such cases, you may need to change the recovery model to simple, take a full backup after changing the recovery model to full, and enable log dumps. All of these steps can be time-consuming and complex. Thankfully, SQL Server 2019 introduced a new feature called Resumable Online Index Creation that makes index creation easier and more manageable. This feature allows you to pause the index-building process, truncate or backup the log, and then resume the process in chunks. With Resumable Online Index Creation, you can create large indexes without using as much log space and without long-running transactions that block other maintenance activities. Resumable Online Index Creation supports the following scenarios: Resume an index creation operation after a failure, s

  Every database administrator faces CPU and memory issues frequently.  If you are really interested in knowing how the CPU processes and how memory is dependent on it, then you should go through this blog In SQL Server, the CPU and memory are two critical resources that work in tandem to ensure optimal performance of the database system. CPU is responsible for executing instructions and processing queries, while memory is responsible for storing data and executing instructions quickly. The relation between CPU and memory is that the CPU requires data from memory to perform tasks efficiently. When a query is executed in SQL Server, it requires memory to store data, and the CPU processes the query to retrieve and manipulate the data. Therefore, if there is insufficient memory available, the CPU has to wait for data to be loaded from the disk, leading to performance issues. Conversely, if the CPU is too slow, it will take longer to process queries and may cause the memory to be blocked u

  To get windows disk space utilization for single server. The script calculates the disk utilization, free space, and percent free for each disk on each server $disks = Get-WmiObject Win32_LogicalDisk -Filter "DriveType=3" foreach ($disk in $disks) {     $diskName = $disk.DeviceID     $diskSize = $disk.Size / 1GB     $diskFree = $disk.FreeSpace / 1GB     $diskUsed = $diskSize - $diskFree     $diskUtilization = $diskUsed / $diskSize * 100     $diskPercentFree = $diskFree / $diskSize * 100     $diskFreeFormatted = "{0:N2}GB" -f $diskFree     $diskSizeFormatted = "{0:N2}GB" -f $diskSize     Write-Host ("Disk {0}: Utilization = {1:N2}%, Free = {2} ({3:N2}%), Size = {4}" -f $diskName, $diskUtilization, $diskFreeFormatted, $diskPercentFree, $diskSizeFormatted) } For Multiple servers $servers = "server1", "server2", "server3" foreach ($server in $servers) {     Write-Host "Server: $server"     $disks = Get-WmiObj

Script to get path of MDF and LDF for a database: SELECT name AS [Database Name],        physical_name AS [MDF File Path],        (SELECT physical_name FROM sys.master_files WHERE database_id = DB_ID() AND type_desc = 'LOG') AS [LOG File Path] FROM sys.master_files WHERE database_id = DB_ID() AND type_desc = 'ROWS'; For user  databases : SELECT DB_NAME(database_id) AS [Database Name],        physical_name AS [MDF File Path],        (SELECT physical_name FROM sys.master_files WHERE database_id = DB_ID() AND type_desc = 'LOG') AS [LOG File Path] FROM sys.master_files WHERE database_id > 4 AND type_desc = 'ROWS';