Database snapshots are not accessible after an In-place upgrade

I worked on an issue where database snapshots were inaccessible after performing an In-place upgrade of SQL Server 2005 instance which has database snapshots to SQL Server 2008 R2. Upgrade completed successfully and all the SQL Server databases are accessible without any issues, however got the following error when I tried to access the database snapshots.

************************************************************************************************************************************************

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Cannot open database ‘Test_db_snapshot’ version 611. Upgrade the database to the latest version. (Microsoft SQL Server, Error: 946)

************************************************************************************************************************************************

Database Snapshots (http://msdn2.microsoft.com/en-us/library/ms175158.aspx) in SQL Server 2005 or later Enterprise Edition allow a read-only point-in-time view of data.

To understand this behavior, I had installed SQL Server 2005 and created a database snapshot. I then ran the upgrade to SQL Server 2008 R2 which completed successfully, after which I got the same error when I tried to access the snapshot database.

As the issue occurs after upgrading the SQL Server 2005 instance to SQL Server 2008 R2, I planned to run the SQL Server 2008 R2 upgrade advisor to test if it detects the issue.

I have installed new SQL Server 2005 instance and created a database snapshot. I then Installed and ran the SQL Server 2008 R2 upgrade advisor which generated an upgrade advisor report. From the report I could notice an error which says “Read-only databases cannot be upgraded” and under that when I clicked on “Show affected objects” got a pop up listing “Test_db_snapshot”.

Screenshot of the error generated by SQL Server 2008 R2 upgrade advisor.

SQLServer2008R2UpgradeAdvisor

SQLServer2008R2UpgradeAdvisor


SQL Server 2008 R2 Upgrade should be able to modify any of the existing objects on the instance while it is upgrading, but a database snapshot is a read-only, static view of a database (the source database), So the upgrade cannot make any changes to this database snapshot.
Before upgrading SQL Server instance, it is recommended to run the upgrade advisor to find out the objects that will get affected by upgrade and which need to be fixed before running the upgrade.

The report of the upgrade advisor will list out the issues & objects that will be affected by the upgrade from where we can find that “read-only databases cannot be upgraded” and when we click on “show affected objects”. We can find the database snapshots listed over there.
Delete database snapshots before upgrading the SQL Server 2005 to higher versions.

Happy Reading,
SQLServerF1 Team

 

What is SQL Server Consolidation and the Benifits of it

What is SQL Server Consolidation?
Consolidation is a process where we combine various units into more efficient and stable larger units. We here talk about Database(SQL Server) consolidation, which is one of the important components in the IT environment. Databases are used in many business systems and in different departments, so it is possible that we may easily lose control of the number of databases that need to be maintained, because each group may simply create their own database for their requirement alone. This leads to many servers, many database instances and databases. Thus databases are one of the prime candidates for consolidation.

Benefits of SQL Server Consolidation
Reduce cost – Consolidation reduces the cost greatly on Hardware, Software, labor time, licenses, hosting, etc.

Reduce management overhead – It reduces the overhead of maintaining and managing activities like server, SQL Server Patching, taking care of security, Performing and maintenance of backups, etc.

Increase resources utilization – It is possible that some machines with higher configuration are under utilized and are almost idle, so it was logical to just move the instances/databases. Upgrading to fewer machines and newer hardware allows for reductions in rack space, power, and cooling needs and allows utilizing the resources more efficiently.

Upgrade – In IT industry, on periodic basis old hardware is retired and replaced with newer hardware. Consolidation should come into picture during those time to bring efficiency.

Compliance – During the consolidation, documenting the process and reviewing the security will get the systems under compliance. Using new software features will bring environment to more unification.

Bring things under control – There are many clients who are unaware where their data is stored, used by whom, managed by whom, etc. Consolidation will get things under control with fewer machines and documentation.

What are the levels of SQL Server Consolidation?
SQL Server consolidation can be done at various levels as mentioned below

Database – At Database Level, multiple databases used by different application will be moved/hosted on a single SQL Server Instance.

Disadvantage with this approach would be managing security issues at instance level where users of one application should not be able to access data of other application, need to ensure that all applications use the shared resources like tempdb, hardware and software resources efficiently.

Instance – At Instance Level, multiple databases used by different application which are part of separate instances on their dedicated servers are moved/hosted on a single Server with multiple SQL Server Instances.

Disadvantage with this approach would be managing Security at OS and Network level. Need to ensure that each instance does not use all the available hardware resources, causing problems to other instances.

Virtualization – In Virtualization level, multiple physical servers are removed and are hosted on a single physical machine as Virtual Machine and share the Hardware resources.

Disadvantage with this approach would be managing Virtualization like ensuring that all VMs are granted appropriate hardware resources.

All these three levels can be mixed to get optimal SQL Server Consolidation for your environment.

Happy Reading,
SQLServerF1 Team

 

Missing SQL Server management Studio

I have installed SQL Server 2012 Evaluation Edition on my laptop which is Windows 7 Professional. Due to some issues, I had to uninstall the SQL Server for some time. After few days, I reinstalled the SQL Server 2012 Evaluation Edition using the same setup media which I used before to install on the same laptop. Installation completed successfully and I can see the SQL Server services from the services console, but the problem I was facing was that I could not see SQL Server Management Studio.

– I have looked at Start -> All Programs -> SQL Server 2012 -> Could not see SQL Server Management Studio

SSMS-Missing

SQL Server Management Studio Missing


– Tried to install the SQL Server Management Studio again using the Setup, but the setup wizard shows that SSMS Basic and Advanced are already installed.

– Searched for SQL Server Management Studio on the file system, but could not find it.
– Verified the configuration state from the registry(Start -> run -> regedit)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\ConfigurationState

Updated the “SQL_SSMS_Adv” key value from 3 to 1

– Started the repair of the SQL Server instance and Shared features which finished successfully, but still could not find the SSMS
– I ran SQL Server 2012 Setup Discovery Report which shows me that the Management Tools – Basic and Management Tools – Complete are present.
– Restarted my laptop.
– Started the installation of Shared features which completed successfully.

Now I was able to see SQL Server Management Studio and I could open and use it without any issues.

Happy Reading,
SQLServerF1 Team