Pages

Tuesday, 5 January 2021

Stellar Repair for MS SQL - Product Review

Stellar Data Recovery has an amazing product designed for the Microsoft SQL Server platform to provide data recovery solutions where databases or backups get corrupted, login passwords are unknown, and other all is lost scenarios like deletions. It’s inevitable and undeniable that most SQL Database administrators and developers would one day face one or more of these questions:

  • Do you have a corrupt database file and need to recover the data?
  • Do you have a corrupt backup file that you are unable to restore?
  • Are you missing records from a deletion of table, entries, and other database objects?
  • Did you forget or do not know the password to connect to your SQL Server instance?

If you need a resilient solution for any of these mentioned issues above or just one or two, then look no further than the Stellar Repair for MS SQL.

The software is available in three editions:

  1. Technician Edition: Database Repair
  2. Platinum Edition: Database and Backup Repair
  3. Toolkit Edition: Database and Backup Repair with Password Recovery

The Stellar Repair for MSSQL provides an a la carte way of shopping for your needed features. Instead of Stellar billing you for a one suite, the software has been priced based of the features. You could purchase a technician when looking into repairing a corrupt .MDF or Platinum to get the ability to repair backups or go for the Toolkit to have full access to all the above-mentioned features as well as reset SQL Server administrator and user passwords.

Some features you will notice with the SQL Repair software:

  • New and enhanced GUI.
  • Multiple DPI Supported (100%, 125%, 150%, 175%).
  • Saves the Scan Information.
  • Option to save repaired database to SQL Server Authentication.
  • Support for MS SQL Server 2019, MS SQL Server 2017, MS SQL Server 2016, MS SQL Server 2014, MS SQL Server 2012 R2, 2012, MS SQL Server 2008 R2, 2008, 2008 (64 bit), 2008 Express, MS SQL Server 2005, 2005 (64 bit), 2005 Express, MS SQL Server 2000, 2000 (64 bit), 7.0 and mixed formats.

The installation piece of the software is very easy and user friendly. The below steps would help you to successfully install the software.

  • Run the downloaded Stellar Repair for MSSQL.exe to open the setup dialog box.
  • Click next and accept the License Agreement to continue.
  • Browse and specify the location and folder for the binary installation file and click next to continue.
  • Select checkbox per installation requirements for the additional task screen.
  • Review your installation configurations and select back to modify any settings. Click start to install if ready.
  • Click Finish after a successful completion of the install.

 Launch the Stellar Repair for MS SQL from your start menu under programs or double click the desktop icon.

Quick Overview of Stellar Repair for MS SQL:

SQL Database Repair Software can help you fix the corruption and restore the data back in some cases even when the data is deleted. The easy to navigate GUI takes all the existing stress of dealing with a corrupt database.


The included deleted records functionality can assist in restoring deleted records in the MS SQL database (MDF) repaired file. By check-boxing the above highlighted [Include Deleted Records], the repair process will try to recover any deleted records (if found).


A left pane object explorer shows a tree map of all the database objects in the repaired database. You also have the functionality of being selective in your export/save of objects by check boxing the needed table(s). You can also directly view records in the tables to verify the consistency and data quality.

 


Below at the footer of the application is the Log report which provides a verbose log of the transactions that occur during the repair process. This is essential for troubleshooting and understanding the different processes that occurred during the repair.

REVIEW:  

Stellar Repair for MS SQL provides that enterprise-level SQL Server database repair solution that saves tremendous hours of administrative work of recovering a corrupt database. The entire suite called the Toolkit opens up all the amazing features of SQL Server password reset and SQL database backup recovery. The super-fast recovery algorithm can retrieve data from a corrupted database or backup file and create a new database or export data into other formats. The SQL user password reset tool allows the average user to change passwords without in-depth technical knowledge. I was not expecting such additional out-of-box functionalities such as multiple backup supports (full, differential, or transaction log). This is an all-in-one solution tool that I would recommend to anyone (technical and non-technical) that works with SQL Server databases.

Wednesday, 2 December 2020

SQL Database Corruption, how to investigate root cause?

Introduction:

In this article, we will discuss the MS SQL Server database corruption. 

So, first, we need to understand what the cause of corruption is. Usually, in all the scenarios of SQL Server database corruption, the main corruption cause is related to the IO subsystem level, which means that the root cause is a problem with the drives, drivers, and possibly even drivers. And while the specific root causes can vary widely (simply due to the sheer complexity involved in dealing with magnetic storage). The main thing to remember about disk systems is that any person in the IT knows that all major operating systems. It ships with the equivalent of a kind of Disk-Check utility (CHKDSK) that can scan for bad sectors, bad entries, and other storage issues that can infiltrate storage environments.

Summary:

If you are beginner to Microsoft SQL Server. You could do the following things to solve the database corruption. And these tricks can’t help you out:

  • Reopen SQL Server
    • It just holds up the issue and gives raise to the system to run through crash restoration on the databases. Not to mention, in most systems, you will not be able to do this right away and will hold up the issue further
  • Delete all the procedure cache
  • Separate and moving the Microsoft SQL server to a new server
    • When you do this you will feel pain because SQL Server will fail to attach on the second server and on your primary.  At this moment you have to look into "hack attach" SQL Server and I can understand it can be a very painful experience.

If you know what will be helpful to solve any problem or what can't be helpful. It requires that you have to be prepared every time for these kinds of problems.  It means that you have to create a database that is corrupt and try everything to recovery that database with the slightest data loss.

You may read this: How to Reduce the Risk of SQL Database Corruption

Root cause analysis:

Root cause analysis may be a crucial part of this method and should not be unmarked regardless of however you pass through the info. This can be a vital step in preventing the matter from occurring once more and doubtless earlier than you're thinking that. In my expertise, once corruption happens, it's absolute to happen once more if no actions area unit is taken to rectify the matter. To boot, this is often seemed to be worse the second time.

Now, I'd counsel, that though you think that you recognize the explanation for the corruption (E.G. power outage with no UPS) investigate the subsequent sources anyways. Perhaps the outage was simply helped and there have been warning signs occurring. To begin, I perpetually recommend these places to seem.

  • Memory and disk medicine to create certain there aren't any issues with the present hardware
  • SQL Server error logs
  • Windows event viewer
  • While rare, sit down with your vendors to examine if they need to have issues with the computer code you're using
  • Software errors, believe it or not, Microsoft has been known to cause corruption. See KB2969896. this is often wherever gap tickets with Microsoft also are helpful

The event viewer and SQL server error logs may be viewed along.

But, I suggest dividing these out to the system administrators as they regularly have more manpower on their team to review these. 

Helpful Tip:

In fact, even once knowing what the matter is, I forever counsel gap a price tag with Microsoft as a result of they're going to not solely provide an additional set of eyes on the problem however additionally their experience on the topic.to boot, Microsoft will and can assist you with the next steps to assist notice the foundation reason behind the matter and wherever the corruption originated from.

Corruption problems:

If the database is corrupt, it is possible to repair the database using SQL Recovery Software. This software will allow repairing the database in case of corruption.

Conclusion:

So finally, after this article, we learn many things about database corruption and how to resolve that corrupt database. Most of the things are too common, and now you can solve this kind of common corruption. With time when will you finish this series, the goal will be that when you find out you have corruption, it is coming from your alerts, not an end-user, and you will have a procedure to let your managers know where you sit and what the next steps are. Because of this, you will get a lot of benefits, and also it allows you to work without having someone breathing down your neck frequently.


Friday, 6 November 2020

[Solved] SQL Backup Detected Corruption in the Database Log

Summary: In this article, we will discuss about the ‘SQL Backup Detected Corruption in the Database Log’ error. It will also describe the reason behind the error and manual workarounds to resolve it. The article also explains an alternative solution that can be used to restore the database and its transaction log backup – when the manual solutions fail. 

When performing transaction log backup for a SQL database, to restore the database after network maintenance or in the event of a crash, you may find the backup job failed with the following error:

Backup failed for Server xxx (Microsoft.SqlServer.SmoExtended)

System.Data.SqlClient.SqlError: BACKUP detected corruption in the database log. Check the errorlog for more information. (Microsoft.SqlServer.Smo)


The error message clearly indicates that the transaction log is damaged (corrupted). Checking the SQL errorlog for more details on the error shows:

2020-11-01 13:30:40.570 spid62 Backup detected log corruption in database TestDB. Context is Bad Middle Sector. LogFile: 2 ‘D:\Data\TestDB_log.ldf’ VLF SeqNo: x280d VLFBase: x10df10000 LogBlockOffset: x10efa1000 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x280d LogBlock.StartLsn.
2020-11-01 13:30:40.650 Backup Error: 3041, Severity: 16, State: 1.
2020-11-01 13:30:40.650 Backup BACKUP failed to complete the command BACKUP DATABASE TestDB. Check the backup application log for detailed messages

However, the FULL database backup completed successfully and even running DBCC CHECKDB integrity check didn’t find any errors.

What Could Have Caused the SQL Transaction Log Backup to Fail?

A transaction log (T-log) backup allows restoring a database to a certain point-in-time, before the failure occurred. It does so by taking a backup of all the transaction logs created since the last log backup, including the corrupt portion of the T-log. This causes the backup to fail.

However, a FULL database backup only has to back up the beginning of the last active part of the T-log – at the time the backup is taken. Also, DBCC CHECKDB requires the same amount of log as the FULL database backup – at the time of the db snapshot was generated. This is why the full backup executed successfully and no errors were reported by DBCC CHECKDB.

Manual Workarounds to Backup Detected Log Corruption in SQL Database

Following are the manual workarounds you can apply to resolve the SQL backup log corruption issue:

Workaround 1: Change the SQL Recovery Model from FULL to SIMPLE

To fix the ‘SQL Server backup detected corruption in the database log’ issue, try switching the database to the SIMPLE recovery model.

Switching to SIMPLE recovery model will ignore the corrupted portion of the T-log. Subsequently, change the recovery model back to FULL and execute the backups again.

Here’s the steps you need to perform to change the recovery model:

Step 1: Make sure there are no active users by stopping all user activity in the db.

Step 2: Change the db from FULL to a SIMPLE recovery model. To do so, follow these steps:

  • Open SQL Server Management Studio (SSMS) and connect to an instance of the SQL Server database engine.
  • From Object Explorer, expand the server tree by clicking the server name.
  • Next, depending on the db you are using, select a ‘user database’ or choose a ‘system database’ by expanding System Databases.
  • Right-click the selected db, and then select Properties.
  • In the Database Properties dialog box, click Options under ‘Select a page’.

  • Choose the Simple recovery model from the ‘Recovery model’ list box, and then click OK

Step 3: Now set the db back to the FULL recovery model by following the same steps from 1 till 5 above. Then, select Full as your recovery model from the list box.

Step 4: Perform a FULL database backup again.

Step 5: Take log backups again.

Hopefully, performing these steps will help you perform the transaction log backup without any issue.

Note: This solution won’t be feasible if you’re using database mirroring for the database for which you have encountered the ‘backup detected log corruption’ error. That’s because, in order to switch to the SIMPLE recovery model you will need to break the mirror and then reconfigure the db which can take significant amount of time and effort. In this case, try the next workaround.

Workaround 2: Create Transaction Log Backup using Continue on Error Option

To complete executing the backup of T-log without any error, try running log backup of SQL database with the CONTINUE AFTER ERROR option. You can either choose to run the option directly from SSMS or by executing a T-SQL script.

Steps to run the ‘Continue on Error’ option from SSMS are as follows:

Step 1: Run SSMS as an administrator.

Step 2: From ‘Back Up Database’ window, click Options under ‘Select a page’ on the left panel. Then, select the ‘Continue on error’ checkbox under the Reliability section. 


Step 3: Click OK.

Now, run the log backup to check if starts without the backup detecting an error in SQL database.

Ending Note

The above-discussed manual solutions won’t work if the transaction log is missing or damaged, putting the database in suspect mode. In that case, you can try restoring the database from backups or run Emergency-mode repair to recover the db from suspect mode.

However, none of the above solutions might work in case of severe database corruption in SQL Server. Also, implementing the ‘Emergency-mode repair’ method involves data loss risk. But, using a specialized SQL database repair software such as Stellar Repair for MS SQL can help you repair a severely corrupted database and restore it back to its original state in just a few steps. The software helps in repairing both SQL database MDF and NDF files. Once the MDF file is repaired, you can create a transaction log file of the database and back it up without any encountering any error.

Saturday, 20 June 2020

Stairway to Microsoft Azure SQL Database Part 5 : Azure SQL serve Server Firewall and virtual Network Setting

In our last blog, we were discussing about Azure SQL server and we learned that Azure SQL server is a logical construct that acts as a central administrative point for the databases hosted under that server. One of the important aspects of the server is managing the connections. All these connection settings are found in the firewall and virtual network under the Security tab. Let us discuss various connectivity options in this blog.

Public Network Access: By default, Azure SQL server provides a public endpoint to access SQL Server through whitelisted IP addresses. You can access your database from anywhere in the world through the internet as long as your IP address is whitelisted. You can whitelist a single IP address or range of IP addresses using the option available under the page "Firewall and Virtual Network settings". As per the security best practices, you should never allow public endpoint to your database. To disable the public endpoint associated with your server, you can enable the "Deny Public network" option. Azure portal will not allow you to disable the public network option unless you have configured a private endpoint connection. This makes sense as without a private endpoint you will not able to connect the server once you disable the public network access. We will discuss private endpoints and its configuration in our next blog. You can whitelist the IP address in two-level, on the server level, and another one in the database level. When you whitelist the IP address from the portal, it will whitelist at the server level. You can find the whitelisted IP address information using the DMV sys.firewall_rule under the master database. You can also whitelist the IP address using the procedure sp_set_firewall_rule. To white list the IP in the database level, you can use the procedure sp_set_database_firewall_rule. Database firewall rule can be configured for the master database as well as for the user databases. Database level firewall setting makes more sense while using the contained user. The below diagram provide a better understanding of this setup. While establishing the connection, Azure SQL checks database level whitelisting first and if it is not available will check server-level whitelisting.
Azure Firewall Setup
Azure SQL Firewall 

Minimum TLS version: TLS is a cryptographic protocol that provides end to end communication security over the network. The minimal TLS version setting allows you to control the version of TLS used by the server. If you set the Minimum TLS version as '>1.2' and try to connect from the client with a TLS version less than 1.2, your connection will fail with an error "Login failed with invalid TLS version". If your applications are using the unencrypted connection, do not set any minimum TLS version. You can use the 'encrypt_option' column available in DMV sys.dm_exec_connections to check the connection from the applications is encrypted or not. Azure suggest to have   "Encrypt=True;TrustServerCertificate=False;" in the connection string. With this, the end to end communication between the server and client will be encrypted. By setting the TrustServerCertificate to false, it forces the client machine to verify the certificate provided by the server. That helps to avoid any kind of spoofing or man in the middle attack.

Connection Policy: Azure provides three connection policy to set. 
  1. Proxy
  2. Redirect 
  3. Default
These connection policies define the communication channel between the server and the client machine. When you try to connect Azure SQL server, the request first reach to Azure SQL gateway. Azure has a set of gateway IP addresses for each region which keeps listening on the port 1433. The number of gateway in each region depends on the workload in that region. For example, East US2 has 5 gateway IP addresses whereas Australia central region has only one gateway IP address. You can do telnet to these IP addresses through port number 1433. Once it reached the gateway, based on the connection policy setting, request forwarded to the actual SQL server node hosting your database or return the redirection information back to the client. You can do nslookup to your Azure SQL server and it provides you the detail of associated gateway. I did nslookup to one of my Azure SQL servers hosted in the US East2 region. It returned the one of the gateway IP address and its DNS name. Now I can do telnet to this IP address using the port number 1433. 



To establish the connection from your local machine or from the Virtual machine hosted in Azure, it should allow the outbound traffic to all the Azure gateway IP  address of that region through port number 1433. The gateway IP address of each region is published on their website. Let us look into each connection policy in detail.

        Proxy:  With this policy, when the client machine initiates the connection to Azure SQL server, first it will connect to one of the gateways in that region and forward the connection to the actual compute node in which your SQL database/server is hosted. In the below case (diagram), I am trying to connect to my server "mydbserver.database.windows.net". When I did the nslookup to my server name, it resolved to  52.167.104.0 which is one of the gateway IP addresses in the US East 2 region. When the connection is initiated from the client to the server, it will get connected to the gateway and gateway forward the connection to the node in which my database is hosted. In this case, the gateway act as a proxy between the client machine and the server. The client machine does not have any idea to which the connection is forwarded.

Proxy Connection



            Redirect:  With this connection policy, when the client initiates the connection, it will connect to the gateway and gateway return the redirection information to connect the node in which the database is hosted to the client. Using that information, the client directly connects to the node in which the database is hosted bypassing the gateway. Azure claims that this will reduce the latency. To make this work, the client machine :
  • Should allow outbound communication to the SQL database gateway IP addresses on port 1433 (This is for the initial connection to get the redirection information)
  • Should allow outbound communication to all Azure SQL IP addresses (nodes) in the region on a port range of 11000 to 11999 (All the SQL node in azure listen to one of port between 11000 and 11999). This can be easily done using the service tag. It is not practical to add numerous IP addresses of the node manually. 

In the below example, the client machine is initiating the connection to connect the server "Mydbserver.database.windows.net". The initial request will go to the gateway. On receiving the request, the gateway returns the redirection information (basically the name of the node in which the database is hosted), and the client uses that information to establish the direct communication with the node in which the database is hosted. There onwards all request in that connection goes directly to the node bypassing the gateway.

Redirection Connection 


    Default: This is the default setting when you create the Azure SQL server unless you explicitly change to Proxy or redirect. With this setting, all the connections initiated from the Azure use the redirect connection policy and use the proxy connection policy for all connections initiated from outside Azure.

Allow Azure Services and resources to access this server: When this setting is enabled, all the azure service and resources in that region can access this server. For example, if we have VM hosted in azure platform, that VM can access the SQL server without whitelisting the IP address of the VM. Keep in mind that, this is not restricting to the resource hosted in your account. Enabling this feature widely opens the communication from all the resources hosted in the Azure platform including the other customer's account also.

Connect  VNET/subnet: This is one of the options where you can attach the subnet to this sever. This allows all resources hosted in that subnet to connect to the Azure SQL server without whitelisting the IP address. Moreover, this enables communication through the Azure backbone networks. Microsoft suggests to use this feature to enable a secure and fast connection. Before attaching the subnet, note that we need to enable the service endpoint for the SQL server at the subnet level.
 
These are the various option available under the Firewall and Virtual network setting. In our next post, we will discuss enabling private endpoints.