RSS

Category Archives: SQL Server 2008

SQl Server 2008

How to Upgrade to SQL Server 2017

One of the tasks that any DBA now can be asked to upgrade the production environment to the latest SQL Server version . and this task is more critical you can find below some tips and resources that can be helpful on this tasks

  1. How to decide on an upgrade
  • SQL Server 2000 and SQL Server 2005 are no longer supported by Microsoft and no updates are published. This lack of support means your databases may be vulnerable to security attacks that have been addressed by recent upgrades. Upgrade these database servers unless you can afford to lose them or allow the data they contain to fall into malicious hands.
  • Because these databases are no longer supported, it is highly likely that you are not in compliance with the data protection laws in your region.
  • Your company already uses Azure for other systems and may wish to migrate databases into Azure as well. You cannot migrate a database into Azure SQL Database from SQL Server 2000 or 2005.
  • You can upgrade a SQL Server 2005 database server to SQL Server 2017 directly. However, to upgrade from SQL Server 2000, you must first upgrade to SQL Server 2008.
  1. Discover your DB and Document anything (versions, features, editions , DB Size , DB Count , Configuration component, ..etc ) you can use (Microsoft Assessment and Planning (MAP) toolkit) in this part
  2. Data Migration Assistant (DMA) : supports versions of SQL Server from 2005 upwards. Using the assistant, you connect to the instance and select the database you plan to upgrade. You tell the assistant the target version of SQL Server and it will analyze the size and schema of the database. It will also make recommendations for your upgrade. After the analysis, DMA provides a compatibility report, which highlights breaking changes, behavioral changes, and deprecated features so these problems can be fixed.
  3. Database Experimentation Assistant (DEA) : To test your migration and upgrade process to test the DB performance and workload if we upgraded it You can also test performance on different hardware or with new features added

Introduction to upgrading SQL Server

Microsoft Assessment and Planning Toolkit MAP :

Microsoft® Data Migration Assistant DMA :

Microsoft® Database Experimentation Assistant DEA :

 
Leave a comment

Posted by on August 8, 2019 in General topics

 

Tags: ,

Learn about the Misconceptions around SQL Database Corruptions

SQL database corruption is the issue every database admin has to face at some point in their lifetime. But do you know what can cause you more trouble than a corrupt database? It is the misconceptions around SQL database corruption. These myths can actually worsen the situation for SQL database and its users. Therefore, it is crucial that users have the full knowledge of SQL Database corruptions and the myths and reality surrounding it.

Some Common Myths Regarding Database Corruptions

Let’s explore which ideas about database corruption are basically misconceptions.

  • Server Restart/ Reboot is the Quickest Method to Fix Corruption: This is one of the most prevailing misconceptions about SQL database corruption. SQL Server reboot or Windows Server restart will not fix the corruption, rather, it will possibly make the situation worse by putting it in Suspect mode. Detaching a database that is already in Suspect mode will make it impossible to fix the corruption ins future. A corrupt database page requires repairing or restoration, not reattaching.
  • Detaching and Re-attaching the Database will Resolve Corruption: This is another malpractice performed by many database admins. Instead of repairing, it will make the database recovery more difficult.
  • Repetitive Running of DBCC CHECKDB will Do Away With All Corruptions: In some extremely rare cases, re-running the DBCC command may repair one or two minor level corruption from SQL database. But this is not a recommended solution as the chance of success is very rare.
  • Applying Repair Command is harmless: If you believe that running commands to repair will not cause any harm to your database, you are wrong. If you are running DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS, it is possible that your database may face some significant amount of data loss. So, always keep a backup of your database to avoid this sort of situation.
  • To Execute Repair Commands, DBCC CHECKDB is Not Required: No, DBCC CHECKDB commands are used for the purpose of checking physical and logical integrity complete set of objects of any particular database. Before and after running the database repairing command, users have to run DBCC CHECKDB. It is not possible to repair database using manual command without running DBCC CHECKDB.
  • Running Repair Command Alone Assures Corruption Removal: Wrong! The success of Repair command in order to corruption removal depends on many factors. For this reason, its result will vary depending on the severity of the damages done to the database. So, it is mandatory to run DBCC CHECKDB command after repairing. It will help users to find out more critical corruptions and confirm the success of Repair command.
  • Incomplete Lengthy Operations Lead to Corruption: Many of us believe that if an operation is going on for a long period of time, interrupting it halfway will cause damage to the database. This is not true at all. Database corruption occurs only when there is a physical level corruption (e.g. bug, virus) in the Server.
  • DBCC CHECKDB is All-In-One Solution for All Corruptions: This is not quite the scene. The Repairing command of DBCC CHECKDB has only the limited capability when it comes to fixing corruption from SQL database. There are many corruptions that repair command is unable to fix, like PFS Page header corruption, Metadata corruption, Common value corruption, etc. If used in case of these corruptions, it will end up extract data in a lengthy process with no result. Moreover, REPAIR_ALLOW_DATA_LOSS command often leads to data loss. Restoration from last good backup is the most useful method in such cases.

Conclusion

There are numerous misconceptions around SQL database corruption in the industry. Here, we have focused on some of the most common myths regarding database corruption in SQL Server. We expect that the users will gain knowledge from this post and be able to take the right decision once the database becomes corrupt. SQL Server experts recommend having a regular backup of the entire database to avoid inconvenience during database corruption. In case any corruption persists in the database, then one can use SysTools MDF Recovery tool to repair corrupted MDF/NDF File of SQL version 2017, 2016 & all below versions.

 
Leave a comment

Posted by on February 13, 2019 in General topics

 

Method to Backup and Restore SQL Database

If you are an SQL user, you must back up your SQL database to save your business-critical data from getting lost/ deleted permanently. Only regular data backup can safeguard your data from various mishaps like end-user error, hardware issues, etc. Just by knowing the importance of backup is not suffice: users need to know the procedure in detail. This post will tell you some of the trusted methods to backup and restore database in SQL Server.

How to Backup and Restore Database in SQL Server

We will be providing you with a step-by-step guide to both the processes: SQL database backup and restore. Read the following segments to know more about it.

  • How to Backup SQL Database

Using this method of SQL Server Management Studio (SSMS), users can back up the database that can be used at any crucial moment.

a. Open SSMS and go to Object Explorer. Select the Servername and expand it.

b. Expand Databases to choose a user database. Or you can expand System Databases to choose any system database.

c. Right-click and hover your cursor on Tasks>> click Back up to get the Back Up Database dialog box.

d. Confirm the name of the database from the drop-down list of Database. If you want, choose another database from the list.

e. Choose Full from the drop-down list of Backup type. (You can also choose SIMPLE or BULK_LOGGED)

f. To create the copy-only backup, select the checkbox beside the same option.

g. Select the radio button beside Database under Backup component.

h. Under Destination section, choose your destination for backup from the Back up to drop-down list. Click Add to include extra backup objects and/or their destinations. You can also remove a mistakenly added backup destination by selecting it and clicking on Remove.

Note: To back up a transaction log, run this script. In this example,l get created in AllSalesWorks_FullRM_log1.

  • How to Restore SQL Server Database from Backup File

If the SQL database encounters some issues that cannot be resolved, the last good backup of the database can be restored to fix the problem. In this section, we will describe the process to restore SQL database from the backup file.

For Full Database Restoration

a. To execute this process, start SQL Server Management Studio (SSMS), select the option Databases.

b. Right-click on it and choose Restore Database to open a window.

c. Click on the radio button select From Device under Source for restore section and then click on the Browse icon.

d. Specify Backup window will open. Click on Add and go to the location where the flat files are stored. Select the first backup file from the list as it is the Full backup file.

e. Click on OK. The file will get added to the Specify Backup window. Click on OK again.

f. Choose the target database where you want to restore the backup file by going to the section “Destination for restore.” Under “Select the backup sets to restore” section, check mark the database file chosen earlier.

g. Select Options from the left pane and then do these steps:

  • Check the box beside Overwrite the existing database (WITH REPLACE) under ‘Restore potions’ section.
  • Under ‘Recovery state’ section, choose Leave the database non-operational, and do not roll back uncommited transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)” radio button.

h. Click OK and the restoration will be done.

I. Perform these steps for every incremental backup file, which includes the .tm file. When you reach a point when you have to restore the point-in-time file, follow this second method after “Restoring” message appears.

For Point-in-Time File Restoration

These steps need to be performed for the restoration of last incremental file that contains the point-in-time:

a. Start SQL Server Management Studio to right-click on Databases>> click Restore Database.

b. Choose From Device option under Source for restore section and click Browse.

c. On the next window, click on Add to go to the location of the incremental backup file with the point-in-time that you want to restore. Select that file and click OK.

d. Click on OK again. Select the added backup file in Select the backup sets to restore segment.

e. Choose the destination database in Destination for restore section. Click on Browse button next to “To a point in time” to make ‘Point in time restore’ window appear.

f. Enable ‘a specific date and time’ option and select the date and time.

g. Click on OK>> Options (left panel)>> Overwrite the existing database>> Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH RECOVERY)

h. Click OK to perform the process. In the restored database, you will only view the changes done to a particular point-in-time.

SQL Backup Recovery: The Ultimate Way to Restore Healthy and Corrupt BAK File

As we can see, the manual restoration process is a long one. Moreover, this process does not support restoring data from corrupt BAK file. To avoid these problems, SQL bak File Repair is a reliable solution. In some easy steps, users can restore their corrupt or healthy .bak file to SQL database.

a. Launch the tool and Add BAK file.

b. The software will resolve all the issues and offer full Preview. Click on Export.

c. You can export as SQL Server Database or as .csv script.

Concluding Words

In case you are wondering about the process to backup and restore SQL database, here are some of the approaches you can follow. All the methods mentioned here have been tested and users can execute them without any worry. If your backup file is damaged or you prefer an easy way of restoration, you must go for SQL Backup Recovery Tool.

 
Leave a comment

Posted by on January 8, 2019 in General topics

 

How to create a SQL Database in Azure

How to create a SQL Database in Azure

(A Step by step procedure)

I am writing this blog and explaining how to create a database in Azure. As we know that Azure is a cloud computing service provided by Microsoft and is becoming popular in the world. As a DBA we need to know how to work in a cloud computing environment and therefore for the ease I providing a step by step solution for creating a database in Azure.

sql-database-windows-azure

Explanation:

First of all we need to login to Azure portal and need to check for SQL Database options as it will be on the left hand side as given in picture below.

SQL Database Options

As soon as we click on SQL database option, a new windows will open up as given in picture below:

pic 1

Here we can see in this picture that there is no database available. Now we have to create a new database and for that we need to click on Add button on the top as shown in picture.

As we click on add button so it will display a new windows which requires the information to fill in to create a database.

pic 2

pic 3

Here we need to provide the information as database name, subscription, resource group (if existing then use that and no need to create a new resource group for each database), another option here elastic pool which is already discussed in my previous blog , go through if not read

https://mirzahusain.wordpress.com/2018/08/06/sql-database-as-a-service-in-azure/

As this is a test database which I have created so not using elastic pool and simple creating a standalone single database in Azure. Rest settings pricing and collation we need to set here as per our requirements or choose default.

One more thing which is important that is location we need to choose as I have chosen East US , you may choose as per your company requirement and policies, for the test purpose you may choose any location and just try and hit.

pic 4

Check and fill all the option carefully as shown in the above picture and then hit the create button.

The deployment will start to create the database. I have created here the database named as “MirzaDB”. You would be getting the alert in the alert section as soon as deployment succeeded.

pic 5

Now database has been created and we can explore it by checking its size and other details as below in different pictures.

pic 6

Looking forward your likes & comments!

Mirza Husain

 

 

 

Tags: , , , , , , , , , , , , ,

How to Fix Microsoft SQL Server Error 926 Instantly

Microsoft SQL server is the most popular database management system. It provides flexibility to the database administrator for managing the database. But, sometimes it creates numerous errors in different stages while working with the SQL server database. SQL database suspect error 926 is one of them, which creates hurdles in SQL transaction. Sometimes, it also vanish the smooth working of SQL server as it makes database inaccessible for the user.

Hence, this article will tell you the best solution on how to fix Microsoft SQL Server error 926. Before going towards the solution, let us discuss the symptoms and reasons behind SQL Server error 926.

SQL Server Error 926 – Find Out Reasons

  • The SQL database might be marked as suspected
  • When malware defect occurs in the hardware of the machine<
  • If corruption found in the header part of the system file
  • Sudden shut down of the server application also leads the error

How to Fix Microsoft SQL Server Error 926

You can resolve SQL database suspect error 926 with help of any of the solution discussed below:

Solution # 1: Resolve SQL Error 926 Using Server Management Studio

  1. Launch the SQL Server Management Studio on your computer system.
  2. After that, go to the Object Explorer >> Server Instance >> Stop option.
  3. Now, minimize the management studio & open the Control Panel on the system.
  4. After that, change the view screen to Large icon & click on the Administrative Tools.
  5. In this step, select the Services option to proceed further.
  6. Here, search for the SQL Server (MSSQLSERVER), right-click on it & choose Stop option.
  7. Close the control panel window & launch My Computer or Computer on Windows. In the computer, go to the location where SQL data has been saved.
  8. Select MSDBDATA and MSDBlog files and move them to another location.
  9. Now, go to the location where you had moved the file in the previous step and copy them.
  10. In this step, restore the management studio window & go to the Object Explorer section. Now, right-click on the Server Instance & choose Start option.
  11. Finally, click on the Refresh button of the Management studio in order to update the changes.

Limitations of the Manual Approach

The above discussed manual method involves a lot of brainstorming effort and it is a time consuming process.To fix SQL Server error 926, there is a long and complex command to be executed. There might be the possibility that you might make mistake during command execution. It also takes a lot of time to remove the error Microsoft SQL error 926 and to recover the SQL server database. Moreover, the manual procedure is completely unsuitable for the non-technical or less experienced users because this process contains the totally technical term. Thus, to replace the manual approach, an effortless approach is described which overcomes all such consequences of the manuals.

Solution # 2: Fix Microsoft SQL Server Error 926

To overcome all such drawbacks, users are suggested to take help from a third-party solution named as SQL MDF Repair Tool. The tool repairs corrupt MDF / NDF SQL server database file of SQL Server version 2017 & all below version. The software gives you two different options for exporting. One can export recovered database in SQL server database and as .sql script. Moreover, by using this Professional software, Microsoft SQL Server error 926 can be easily fixed without any hassle.

Final Words

In this article, we have discussed about SQL server error 926 and different methods to fix this error. The manual approach is available to fix SQL error 926 Suspect but it has many consequences. Hence, in order to perform a simple, easy and effortless recovery procedure, users can use SQL recovery tool. It is the easiest approach to remove SQL Database suspect error 926

 
Leave a comment

Posted by on September 19, 2018 in DB corruption

 

SQL Server Torn Page Repair: Without Worrying About Data Loss or Corruption

It can often be bugs and errors in fetching data from the server. This can leave system administrators baffled as all work comes to a standstill. Incomplete or incorrect transaction cause confusion among employees working on the different sections of a database.

One such frequent issue is the SQL Server torn page error. It can be quite a hassle for a non-technical user facing such a situation. There are ways to fix it easily without worrying about any data loss. This post covers all there is to know about this topic. Continue reading to find out more.

What is the Torn Page in SQL Server?

It is the inability of the server to fetch a particular data during a transaction. It is caused when an Input/Output header tries to access a page that was written incorrectly to the disk. It reports a message saying ‘I/O error (torn page) detected during read’. The reason for this can be primarily contributed to power failure causing partial writes. Other factors include damaged disk or other hardware to which data is being written to. If a torn page is detected by SQL Server, it will sever all connections as the requested data is inaccessible. During the restore process, detecting a torn page sends the database into SUSPECT mode.

sql server torn page repair

Read the rest of this entry »

 

Query Store for Solving Query Performance Regressions

Query Store for Solving Query Performance Regressions

Query performance is a very important area of SQL server. We always have badly performance queries around.

Query store is the newest tool for tracking and resolving performance problems in SQL server.

In this article, we are going to have a look at some practical uses of SQL server Query store.

What is Query Store?

The query store has been described by Microsoft as a ‘flight data recorder’ for SQL server queries. It tracks the queries run against the database, recording the details of the queries and their plans and their runtime characteristics. Query store is per database feature and runs automatically. Once turned on, nothing further needs to be done to get it to track data. It runs in the background collecting data and storing it for later analysis.

Query store is available in SQL Server 2016 and later, and Azure SQLDB v12 and later. It is available in all editions of SQL server, even in Express edition.

How is Query store different from other tracking options?

We have had query performance tracking for some time though in the form of dynamic management views. Mostly, sys.dm_exec_query_stats and sys.dm_exec_query_plan and tracing tools like SQL server profiler and extended events.

So, what makes Query Store different? Let me start answering that by describing a scenario that I encountered a couple of years ago.

A particular critical system was suddenly performing badly. It had been fine the previous week and there have been no extended events sessions or profiler traces running historically. The admin had restarted the server when the performance problem started, just to make sure it was not something related to a pending reboot.

As such, there was no historical performance data at all and solving the problem of what happened, why the query performance is different this week was extremely difficult.

1

Read the rest of this entry »

 

Tags: ,

 
%d bloggers like this: