Data Recovery Archive

« Older Entries
Newer Entries »

5 Steps to Evaluate SQL Server Policies on Multiple Instances through Central Management Server

Posted March 11, 2019 By AuthorVS1

The Article informs about the easy steps required to evaluate SQL Server Policies across multiple Instances using CMS.

While working with SQL Server, users/DBAs usually face problems with management of dozens of servers having different configuration. That situation arises when they have to check their databases on multiple SQL instances.

Evaluate SQL Server Policies on Multiple Instances

Since different organizations may use different configured Servers, it becomes a need to have a central policy management system. That’s why the Central Management Servers was introduced which could evaluate the policies against various SQL servers such as 2005, 2008, 2012, etc. 

In this article we will focus on easy steps to use Central management server feature to manage Multiple SQL server instances.

Policy Check across Multiple Instances 

Users first need to set up Central management Servers or Registered Servers and create a policy. Central management Server stores a list of names in hierarchal order in tables within that particular server and users can update or access the list through TSQL. It basically plays two roles, one is to manage and store all the logical servers and other is to evaluate policies. Using Central Management Servers, users can create what we call Server groups which help in easy administration and manual policy evaluation against each server groups.

So to check policies across various instances users can follow these simple steps.

Step 1

Users are required to open SQL SSMS i.e. Server Management Studio where they can find the option of ‘Registered Servers’ under view option in menu bar. Alternatively, users can also press Ctrl+Alt+G as keyboard shortcut.

Step 2

Registered Servers dialogue box appears where users can select Central management Servers and then SQLSRV1. It will display the option of Demo Servers enlisting all the available Servers like SQL2005, SQL2008, SQL2012, etc. There users can right click on the server group and select Evaluate Policies.

Step 3

In the Select Source Dialogue Box, users have to select the source where policy has been saved. It will require Login and Password of the account. Once the required fields are filled, one can press OK.

Step 4

In the Evaluate Policies dialogue box, select the policy which needs evaluation. There are two options, Policy Selection and Evaluation Results. The Policies along with categories will be mentioned in a table and users are required to check and uncheck the policies manually to determine the results. After selecting policies, users have to go to the option of Evaluation Results.

Step 5

In the results section, the selected policies will appear along with a table of target details. Simply click Evaluate and review the displayed results. In the target details, users can get all the information and evolution reports of the policies. There is also an option of exporting the results.

So this is how, users can evaluate a SQL Server Policy against multiple instances using Central Management Servers and Registered Servers. It’s an easy solution to the problem of evolution of policies on multiple servers as now users can easily monitor and reduce the unnecessary work of implementing the default traditional scripts on each instance.         

SQL Server administrators should always be prepared for situations where their recovery plans are put to test. Hence they should also keep a third party specialized SQL Server fix tool handy to deal with scenarios where normal recovery options do not give optimum results.

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including fix mdb and sql recovery software products. For more information visit https://www.datanumen.com/

Be the first to comment
Filed in SQL Server Database Recovery, SQL Server Solutions | Tagged: , , , , ,

How to Deal with an Insufficient Data Disk Space Issue in SQL Server

Posted March 8, 2019 By AuthorVS1

The following article lists multiple solutions for you to free up data disk space in different situations.

While working on SQL Server Database Engine, users often face the problems related to insufficient disk space, especially during the recovery process. SQL server sometimes might require extra disk space to recover files. In this article, we will try to solve the issue with effective approaches.

Insufficient Disk Space Issue in SQL Server

Disk Space Error

Database Engine shows errors like 1101 or 1105 in the cases where additional disk space is required for performing operations. If the database is online and the disk space gets filled, the database, however, remains online but the insertion of more data is not possible. If the same happens during recovery, Database engine labels it as pending resource. So it comes down to users to take steps to make space available for further operations.

Resolving the Disk Space Issue

You can take several actions to resolve the issue of disk space unavailability. Since the operations won’t take place further as long as there is no disk space so there has to be some user input to free up space.

The simplest approach would be to free disk space on the full disk. It could be done by removing indexes or tables which are unnecessarily acquiring space to lighten up the size on the disk. You should also take into account the error message and figure out the problem. Freeing up some space by removing needless tables can allow the files in the file group to function and work properly.

If the error is occurring during the recovery process of the database, then you must attempt SQL Server recovery of the database after resolution of the error to avoid data loss.

Adding Files to a Different Disk

If the Database engine shows an error of disk space unavailability then you can try adding files on a different disk using Transact-SQL. It should solve the disk space issue as you are now using disk space of different disk to add files. You can try adding files to different disk with the help of ALTER DATABASE command and defining ‘filegroup’ name.

Alternatively, you can also move the databases by specifying a new location. For this use option, FILENAME Clause of ALTER DATABASE statement and move the Full-text catalog files, logs, and Data to a new location. 

You can also use SQL Server Management Studio to add files on some other disk and that can free up space. There is an option of Object Explorer in SQL management Studio where users can specify Autogrowth column i.e. file growth and users can also specify a maximum size limit for files according to the available disk space.

Increasing File Size and Recovery of Database

Once the Autogrow is disabled and the database is online, and there is available disk space then users can manually try increasing the file size so that a single growth increment can be produced.

Users can also try enabling Autogrow with the help of Alter Database statement to fix a non-zero increment in Filegrowth Option. So now users can simply increase the MAXSIZE value to resolve the issue for both the cases. 

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including mdb fix and sql recovery software products. For more information visit https://www.datanumen.com/

Be the first to comment
Filed in SQL Server Database Recovery, SQL Server Solutions | Tagged: , , , , ,

6 Crucial Indexing Best Practices and Priorities in SQL Server that You Must Keep in Mind

Posted March 5, 2019 By AuthorVS1

This article explains the relevance and importance of indexing in SQL Server along with explaining the best practices and priorities to follow when creating indexes in SQL Server.

Understanding of Indexes and their practices is essential if you are working with SQL Servers and database management. Indexes speed up the search process in the database up to a great extent as it can effectively find all relevant rows matching columns from your query. There are two types of indexes, Clustered Index and Heap Index, and it’s up to the user to decide which they wish to use. So it’s important to have a clear idea about the data access code to utilize the indexes in an efficient way. There are traditional as well as nontraditional ways to approach the indexing in SQL and it depends on the nature of database and production goals to decide the Indexing techniques.

Indexing Best Practices and Priorities in SQL Server

The following article explains the process of Indexing and provides a guide to best practices and priorities to follow.

Best Practices for Indexing

Here are a few practices that users should keep in mind while indexing in SQL-

  1. It’s important to understand how Data is accessed and maintained, which includes the activities like INSERT, DELETE, UPDATE, and then move to Indexing. There is a need of prioritizing as the data may not be accessed in the same manner each time.
  2. You should use T-SQL Code to build your indexing strategy and start with functional areas. Also, try having a process that can analyze the indexes. 
  3. Try having an index with multiple columns if that helps in improvement of data access but try to avoid bookmark lookup operations.
  4. Use only the needy indexes as unnecessary indexes can cause storage problems and improvement issues. Avoid using multiple repeating indexes.   
  5. The order of the Indexes sometimes plays a vital role and can make a good difference in the database (ASC or DESC order)
  6. Index selection is equally important so users should identify a clustered index for the tables. On the basis of Data Access, you can fine tune or even eliminate them during the validation and testing phases. 

Priorities to keep in mind while Indexing

Users should have a proper priority list of indexing while working on SQL Server. Considering your enterprises’ development plan, you should build a proper indexing strategy and database design otherwise it would be hard to maintain the database in the production stage.   

It is advised to review the index properly and it should have priority when there is some performance glitch. In most of the cases, if you change or add index and review and test them, you can solve the issues related to performance and they might even speed up the processing too.

It’s true that if changes are made in an application then Indexes also need to be changed. So users should review the access code with indexing in focus and apply changes to the indexing strategy. 

In a nutshell, Indexes are one of the most important components of SQL server database and largely affects the performance so users should be informed and aware about the Indexing practices and techniques.

Companies using SQL Server as backend in their line of business applications should always include tool that can repair SQL Server in their backup and recovery strategy. Such foresight can go a long way in preventing data loss in the event of a database crash.

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including mdb recovery and sql recovery software products. For more information visit https://www.datanumen.com/

Be the first to comment
Filed in SQL Server Database Recovery, SQL Server Solutions | Tagged: , , , , ,
« Older Entries
Newer Entries »