SQLPlugger

Situation: Nowadays, most of the SQL Server environment is running on virtualized environment.  VMWare, Hyperviser, Xen, Elastic compute cloud etc. are the most popular virtualization platform available in the market. As per the recommended best practice, we should have separate dedicated drives for  SQLTemp, SQLData, SQLLog, SQLBinary & SQLBackups. All these drives should be presented as individual LUNs to provide the best I/O throughput to the SQL Server. In-spite of implementing these best practices, SQL Server frequently encounters disk I/O latencies issues in most of the environments. We can see following error in the SQL Server error logs: SQL Server has encountered xxx occurrence(s) of I/O requests taking longer than 50 seconds to complete on file [D:\MSSQL\MSSQL.1\MSSQL\Data\MyProdDB.mdf] in database [database].  The OS file handle is 0x00000000.  The offset of the latest long I/O is: 0x00000000000000 Solution: There are many...

Situation: One of my client has a scale-out deployment of SSRS 2014 reporting services. They have two SSRS servers that are connected with the single SQL DB Server. However, reporting services URL were not configured on NLBs, therefore, every time there is an issue with any of the report server, users need to use second server URL to view the business reports. Solution: Network load balancer(NLB) can be use for providing the high-availability across the SSRS deployment. NLB allows us to use single URL to access reports from any of the report server and also its provides load balancing across the report servers. NLB solution can be hardware based or it can be software based. Let's take an example of below typical NLB configuration for Reporting services: Two scale-out deployments of SSRS hosts SSRS01 & SSRS02 are connected with SQL Server DB server. Both host share the single reporting database through this DB server. Without the NLB configuratio...

Situation: Sometime as a Database administrator, we come across a problem with the SQL Server instance connectivity. Application team has reported a problem that they are unable to connect to the newly deployed SQL Server sever from their web application server. In this scenario, the application web server sits in the DMZ zone and SQL Server is residing in transit zone.  SQL server instance port number (other than default port 1433) is opened on the fireball to allow this communication. However, still application team is getting the connectivity issues. Solution: When we install a SQL Server instance then SQL Server Browser service is also get installed as part of the installation. If server has only default instance then browser service would remain in the disabled state. However, for named instance, it plays a significant role while connecting to the SQL Server instance. Browser service always listen on the UDP port 1434. When client requested to make connection with n...

Situation: SQL backups for large database are always be a challenging task. Depending upon the available system resources, a typical 5 TB database backup may take 6 - 7 hrs to complete. In addition to this, it is very difficult for DBAs to copy a single large backup file over the network. Solution: SQL Server provides a flexibility to split single backup file to multiple files. This would allow SQL server to split the workload by creating multiple threads. The advantages of this is backups will run faster and creates smaller files that can be easily copied across the network. Additionally, in most of the cases, Database administrator takes compressed backups which reduce the backup file size. However, for large database backups, overall backups time would be large as SQL server uses single thread to complete the backup operation. In a critical production environment it is always be a requirement to complete the backups before business starts next day. This would ensure the succe...