Unable to Open SQL Server Database Properties Window as it Returns Error

SQLServerF1

SQL Server Database Property page can be used to view or modify options for the database like changing the Collation, Recovery model, Compatibility level, Auto Close, Auto Create Statistics, Auto Shrink, etc. Sometimes you may receive below mentioned error when you right-click on the database and click on properties option.

Cannot show requested dialog. (SqlMgmt)
Property Owner is not available for Database ‘TestDB’. This property may not exist for this object, or may not be retrievable due to insufficient access rights.

This issue may not happen with all databases, as you may be able to open other databases properties on same SQL Server instance without any errors.

The reasons why this error may appear could be that there is no owner for this database either because the login has been deleted from the SQL Server or if it is a windows or domain account then the account may have been removed from the AD. Also it may be possible that SQL Server cannot validate this account due to permission or connectivity issues with AD.

As you are not able to use the GUI to check the database properties, so we cannot see if there is Database owner present for the database and if present then what is the name of that account. Other options to verify the owner of a database is by running the below query.

sp_helpdb [DBName]

Once you identify that there is no database owner or the login does not exist any more, you can run the below command to change the database owner to a different account.

Use [DBName]
GO
EXEC sp_changedbowner ‘SA’;

After running the above command you can Now you can now open the properties of the database without any error. Instead of ‘SA’ account you can choose any login which exists in the SQL Server instance and is valid, However it is important to note that after sp_changedbowner is executed, the new owner is known as the dbo user inside the database. The dbo has implied permissions to perform all activities in the database. Meaning the database owner can perform all operations inside the database inclusing dropping the database itself.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server Installation Wizard disappears After Setup Support Files

SQLServerF1

As a DBA it is common to perform installation of SQL Server and related components. Most of the times, the SQL Server installation progressed soothly. But sometimes when the SQL Server install fails it may get very difficult to identify the cause and then to find a solution.

One of the strange or weird issue one may face with the SQL Server instance is that when you start the SQL Server installation, you will be prompted to install Setup support file after that the Setup wizard provides further options to choose the instance name, location for data, log file and service accounts, etc. However you may notice that after the installation of setup support files, the setup window just disappears and does not show up again to proceed further with selecting other options.

Generally SQL Server setup logs are created under C:\program files\Microsoft SQL Server\100(different for each SQL version)\Setup Bootstrap\Log\ folder. But if the installation wizard disappears or fails after installation of Setup Support files or before that the the install log relate to this will be located at temp folder. You can get to this location by following below instructions.

Click on start->run and type %temp% and press enter (basically, go to the temp folder)
Here, look for SQLSetup.log and SQLSetup_1.log. Open the SQLSetup_1.log file.

Check these logs to see if you can find any specific errors or warnings. If you are unable to understand anything from those log files then you can do a quick search based on the error or warning messages in that log.

Coming back to the original issue of the SQL Server installation window getting disappeared after going through few installation wizard screens, you may try below steps to see if it resolves the issue.

– Open Control Panel – Add or Remove Programs and uninstall SQL Server Setup Support files. There may be multiple setup support files of multiple SQL Server instances, so remove them all.

– Logoff from the server, if possible reboot the server.

– Log back into the server and then start the SQL Server installation and now it should proceed further with the installation and should show the other options.

If above solution did not solve your problem with the SQL Server installation, then you can try the option of slip-streaming the SQL Server installation with latest Service Pack and then attempt the installation.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server Installation Failure due to Cabinet File (SQL.CAB) Error

SQLServerF1

As a DBA it is common to install SQL Server and related components. Most often or not the SQL Server installation is easy and completes without any errors. But sometimes when SQL Server installation fails, it makes it very difficult to identify the problem and to find a solution for the same.

There are various reasons why SQL Server installation fails and below is one such error which you may receive while trying to install SQL Server instance using the setup media from DVD mounted drive.

The following error has occured:
The cabinet file ‘Sql.cab’ required for this installation is corrupted and cannot be used. This could indicate a network error, an error reading from the CD-ROM, or a problem with this package.
Click ‘Retry’ to retry the failed action, or click ‘Cancel’ to cancel this action and continue setup.

Looking at the above error, first thing which can think about is that this error has something related to the corrupt media and some problem with the installation media. So, we can try below steps to see if we can get around the error.

– Launch the Setup by choosing “Run as Administrator”

– Copy the SQL Server Installation media to local disk instead of DVD drive or Network.

– Make sure the Setup media is not corrupt, one way to verify this is to try and install the SQL Server instance on another test server to see if the issue still persists.

– Try downloading the media again from the MSDN site using download manager.

– Extract the contents of SQL Server Media ISO file using MagicISO and then try to install from the extract media.

– Turn off or Uninstall any Anti-Virus software running on the system. Reboot of the server is required after making this change.

– Sometimes this error may be returned of the windows installer file is corrupt, so if above did not work, then it is good option to try and uninstall the Windows installer and reinstall it. Reboot the server and then attempt to install again.

Hopefully one of the above solution should help you in resolving this issue.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Snapshot Agent Fails while Generating Snapshot in SQL Server 2014

SQLServerF1

Replication in SQL Server is most widely used and most often of not the setup and configuration of Replication is quite simple. However there are many issues with the replication which causes problems for DBAs and with each release of newer versions new problems come up.

On such problem with replication comes with setting up replication on SQL Server 2014 instance. When you setup Merge replication and then try to initialize the replication by running the Snapshot agent, it may fail with below error.

There is already an object named “TestConstraint” in the database. Could not create constraint or index. See previous errors.

There is no special configuration with the replication, same database, same tables and same data on SQL Server 2008 R2 or 2012 instance works fine without any errors, this happens only with SQL Server 2014.

After looking at the code where it is failing, we noticed that it is trying to create system table MSmerge_conflict_publication_article where it fails. After digging and research found that in SQL Server 2014 the Snapshot Agent is trying to generate the create script to create the table MSmerge_conflict_publication_article with columns, it is also defining a constraint with same constraint name which already exists in the table as part of user table constraint. Here both the user table constraint name and system table constraint name are same thus causing this error.

Because of this change in SQL Server 2014, the merge replication can fail on tables which have constraints defined on them. This appear to be bug, but not seem to have any solution so far. Post talks about same problem and they have already tested installing SQL Server 2014 CU4, but the issue still persisted.

As of now one of the workaround is to drop all constraints before on the user tables which are replication and create them later. Other options which can be tried include initializing the Subscriber database using other methods such as Backup/Restore where we can avoid the Snapshot agent from running the scripts. Hopefully this issue will be fixed soon.

This is applicable on below versions of SQL Server

SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server 2012 Setup Failure due to Failure of InstallFeatureSpecificRules

SQLServerF1

Problem with installing SQL Server 2012 as it fails while checking some rules related Install Feature Specific Rules. Below is the error that occurred when the rule fails.

InstallFeatureSpecificRules: SQL Server 2014 Setup configuration checks for rules group ‘InstallFeatureSpecificRules’
RS_IsDotNet3_5Installed This rule determines if the Microsoft .NET Framework 3.5 Service Pack 1 is required on the system, based on the features you selected and whether Microsoft .NET Framework 3.5 Service Pack 1 is already installed.

Failed This computer does not have the Microsoft .NET Framework 3.5 Service Pack 1 installed. If the operating system is Windows Server 2008, download and install Microsoft .NET Framework 3.5 SP1 from http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=22. If the operating system is Windows 8.1, Windows Server 2012 R2, Windows 8, Windows Server 2012, Windows 7 or Windows Server 2008 R2, enable the .NET Framework 3.5 feature. A computer restart may be required after installing.

Sometimes, you may see that this rule fails even after you enabled the NET Framework 3.5. So, generally when this issue happens, check from Control Panel -> Programs and Features and verify if NET Framework 3.5 is enabled or not. If it is not enabled, then go ahead and enable it and reboot the server. If it is already enabled and if you are still hitting this error, then try to disable the NET Framework 3.5 and re-enable it back, and then reboot the server. If issue still persists then try to run repair of .NET Framework using the .NET Framework Repair Tool mentioned in the article

What are SQL Server Setup or Installation Rules?
SQL Server Setup validates your computer configuration before the Setup operation completes. During SQL Server Setup, the System Configuration Checker (SCC) scans the computer where SQL Server will be installed. The SCC checks for conditions that prevent a successful SQL Server setup operation. Before Setup starts the SQL Server Installation Wizard, the SCC retrieves the status of each item. It then compares the result with required conditions and provides guidance for removal of blocking issues.
The system configuration check generates a report which contains a short description for each executed rule, and the execution status. The system configuration check report is located at %programfiles%\Microsoft SQL Server\120(may be different for other versions)\Setup Bootstrap\Log\\

This is applicable on below versions of SQL Server

SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

AlwaysON Availability Group Does Not Failover to second Node Upon Stopping SQL Server Services

SQLServerF1

When SQL Server AlwaysON Availability Groups are setup in SQL Server 2012 or SQL Server 2014, DBAs perform various testing by shutting down one node and verifying that the Availability Group fails-over to the second node and another approach tried is manually stopping the SQL Server services and expect the Availability Group to failover to the second node, but sometimes you may see that the failover does not occur, instead the AlwaysON AG and IP address resources get into failed state. Manually failing over the AlwaysON AG to the second node however successfully brings the resources and Availability Group online and accessible.

This issue generally happens if the failover has been attempted multiple times which exceeds maximum number of failures supported for the availability group during a given time period. This is not specific to AlwaysON rather can also happens with SQL Server Failover Cluster instances as well.

The Default value for the maximum number of failures during this period is n-1, where n is the number of WSFC nodes and the default time period is six hours. If an availability group exceeds its WSFC failure threshold, the WSFC cluster will not attempt an automatic failover for the availability group. Furthermore, the WSFC resource group of the availability group remains in a failed state until either the cluster administrator manually brings the failed resource group online or the database administrator performs a manual failover of the availability group. Ref

With default values, Automatic failover only happens once during six hours. So, the solution to this problem is to change the failover-threshold values for a given availability group. We can use the WSFC Failover Manager Console and increase “Maximum Failures in the specified period” to higher value based on our requirement and reduce the “Period (Hours)”. For example, we can set the “Maximum Failures in the specified period” to a value like 10 and “Period (Hours)” to 1 hour, which means in one hour AlwaysOn Availability Group will be tried to failover for 10 times. If it fails for 10 times, then it will be left in failed state and DBA has to investigate based on the cause of the failure and fix it after which it can brought online manually. The values are to be set based on your requirement and may vary in different environments.

This is applicable on below versions of SQL Server

SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Tips and Process to Choose Best SQL Server Monitoring Products

SQLServerF1

In most of the organizations there are some monitoring tools which will monitor all the servers and sends alerts to the DBAs and other groups when a certain alert condition is met. It is very important to have some alert mechanism because it is not possible to manually monitor all the servers 24×7 and the problems can arise at any time. No matter how proactive a DBA team is, still there are unexpected problems with SQL Server either directly caused by SQL Server or due to issues related to Hardware, Network, Operating System or other applications or tools.

One of the important role of SQL Server DBA is to respond to alerts received and fix them in timely fashion based on the severity. Some very senior DBAs may be involved in making decisions on which monitoring product to be used or if a custom monitoring solution is to be developed. Most often or not most organizations prefer using third party monitoring tools to avoid unnecessary costs of developing and maintaining the in house tools.

There are various products or tools available in the market for monitoring SQL Server for various issues and raise alerts by sending E-Mails which later are converted into tickets for DBAs and other teams to handle and resolve the issues.

Below are some of the Tips or Best processes or Criteria that can be followed to determine which Third party monitoring tool best suits your environment.

precision of alerts – It is important to test and make sure that the monitoring tools detects the problems correctly and categorizes it into correct severity and sends the alerts without any fail. There should be mechanism which keep check for the issue periodically and raise the alerts again if it has not been resolved. Monitoring tool should not send false alerts or it should not keep sending alerts even after the issue is resolved.

Defining Alerts – There may be many predefined alerts provided by most of the monitoring tools, but it may not be useful for all organizations, so there should be options to choose which alerts to be activated and which can be left disabled. Also, there may be special requirement in some organizations to check and raise alerts for issues not defined in the monitoring tools, so the tools should have provision to define our own alerts too.

Types of Alerts – The alerts raised or checks performed should not be just limited to SQL Server, but it should also contains related alerts like Operating System, Network, Disk Space, Server Performance, Memory etc. SQL Server is dependent on the underlying server and OS, so the monitoring tool should also check and raise alerts for these issues too. The alerts should be categorized into different types like critical or warning and the alerts are to be raised accordingly and should be resent on a particular frequency based on the alert type.

Usability – Installing the tool, Configuring and managing it should be easy and clear instruction and documentation should be available. Also, it is important that the tool should allow different alerts to be sent to different groups. For example, OS, Network issues need to be routed to those groups along with SQL team and SQL Server issues to be routed only to the SQL teams.

Scalability – As organizations grow, there may be more number of servers implemented, thus can lead to the requirement of the monitoring tool to support large number of servers and should work seamlessly.

Reporting – There should be provision to have good reporting options where in the DBA and other teams can see the reports including current activity, and historical data. There should be provisions to create custom reports too.

Support – It is important to have a strong support for the product as if there is some issues with the product, then monitoring may be down for a particular server or for many servers leaving the environment exposed and can cause large outages.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

3rd Party Backup and Restore/Recovery Tools for SQL Server

SQLServerF1

One of the most important responsibility of SQL Server Database Administrators (DBAs) is planning, implementing and testing Backups and Restore/Recovery in SQL Server. There could be sudden disaster or corruption in which case the database needs to be restored and recovered with minimal data loss, thus it make the planning and testing of Backup and Restore process very important.

SQL Server ships with database backup capabilities, but it is on server to server basis and there is no central solution where backups and restores can be managed from a central server. Thus there are various Backup, Restore and Recovery tools for SQL Server are available in the market with various features. More information regarding choosing best backup/restore tool can be found in this article

Below are some of the popular Backup and Restore/Recovery 3rd party tools for SQL Server.

NetVault LiteSpeed from Quest – LiteSpeed tool is one of the most mature, feature-rich SQL Server backup products available. It is very widely used around the world by many organizations in almost all the industries. They also provide an excellent support and regular updates to their product. It provides integration with TSM, Adjustable Backup compression ratios, backup Encryption, • Central backup repository, supports Object level restores, etc. There are some discounts of about 20-20% offered for bulk licenses. Price is on higher side, but has many good features and is a reliable product.

SQL Safe from Idera – SQL Safe is cost effective with the features provided. This tool supports TSM integration, and is capable of object-level restores, central management for backups, etc. Object level recovery is not supported with this tool.

SQL Backup and HyperBac from RedGate – RedGate has two backup/restore products which are SQL Backup and HyperBac. same licensing applies for both the products. These are highly cost effective backup products available in the market and can be bundled with other tools in your environment. There are some limitations to the tools as it does not provide TSM integration.

There are other backup/restore tools available from CommVault which is Simpana, “SQL Server Backup” from DataBK.

What does SQL Server Backups Consists of? – SQL Server Backups copies the data or log records from a SQL Server database or its transaction log to a backup device, such as a disk, to create a data backup or log backup. This copy of SQL Server data can be used to restore and recover the data after a failure. A backup of SQL Server data is created at the level of a database or one or more of its files or filegroups. Table-level backups cannot be created. Recovery model of the database determines what kind of Log backups can be performed on a database, thus affects the restore process and amount of data that can be recovered after a failure.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Tips to Choose Best SQL Server Third-Party Backup and Restore Products or Tools

SQLServerF1

Backups in SQL Server are one of the most important responsibility of SQL Server Database Administrators (DBAs). There could be sudden disaster or corruption in which case the database needs to be restored and recovered with minimal data loss, thus it make the planning and testing of Backup and Restore process very important.

What are SQL Server Backups: – SQL Server Backups copies the data or log records from a SQL Server database or its transaction log to a backup device, such as a disk, to create a data backup or log backup. This copy of SQL Server data can be used to restore and recover the data after a failure. A backup of SQL Server data is created at the level of a database or one or more of its files or filegroups. Table-level backups cannot be created. Recovery model of the database determines what kind of Log backups can be performed on a database, thus affects the restore process and amount of data that can be recovered after a failure.

Although Backups and restores can be performed using SQL Server Management Studio (SSMS) and using T-SQL queries, but in many environments, to make the Backup and Restore process more manageable they use Third-Party Backup tools to perform backups and restores on SQL Server databases. Although there are many third-tools it is important to choose the tools which suits best for our environment.

Below are some of the parameters which can be used to determine the best Backup and Restore/Revoery tools for your environment.

Integration with TSM – One of the important part of a Backup/Testore product is its integration with the TSM which allows you to backup and restore directly to the TSM Server. Backing up directly to TSM allows saving disk space and IO bandwidth savings. Another benefit is that the direct restores can be performed on to other servers which saves time and space.

Backup Compression – Starting with SQL Server 2008, backup compression feature was introduced. However backup tools provide their own compression methods where in the backups can be further compressed and also backups are compressed on SQL Server versions which does not have this feature available.

Backup Encryption – Encryption of SQL Server Backups was introduced with SQL Server 2014, but there are many backup tools which provide custom encryption features which encrypts and stores the SQL Server backups.

Usability – Although various backup and restore tools provide variety of features, but it makes lot of difference when it comes to the ease at which DBAs can install, configure, manage and use these tools. If Backup tool installation or configuration is complicated then it will not be adopted by DBAs as they will not be comfortable using it or will have to spend lot of time on managing it.

Product Support – One of the key factor evaluated before making a decision to but the Backup or Restore tools is the level and kind of support option available for the product. Some of the tools which provide very good support often are mostly used in the organizations.

Price/Cost of the Product – Depending on the features provided the price or cost of the tools vary. This is a very important factor to decided weather to go ahead in buying the product. Over a period of time many new backup and restore tools were developed by various organizations which brought more competition this reduced the cost or price of these tools. Also various discounts are provided based on number of licenses brought and kind of servers the tools are being used in.

Degree of Restores – SQL Server provided Backups or restores to be performed at database, files or filegroup level, but the 3rd party backup and restore tools along with standard will also support object level restores, where in one can restore a particular table or stored procedure, which is more useful and helpful in Development or Test environments.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 
1 2 3 4 8