Changes to SQL Server 2016 Management Studio(SSMS)

With each new release of SQL Server, there are many new features that get introduced. Microsoft has put lot of effort in improving the SQL Server Management Studio as well with the new releases to support the new features that get introduced. Also, there has been lot of support improvements for Azure integration and management with SSMS. Previously there was not much, DBAs were able to make changes to Azure databases from SQL Server Management, but with the release of SSMS 2016, there has much much closer integration for managing SQL Azure instances and databases. For those who are not aware, we can download and install SQL Server Management Studio (SSMS) as separate individual download and install just SSMS on a client machine or on a server. Some of the new features support for SQL Server Management Studio(SSMS) include,

– Addition of human-readable error messages. With this, now when there is an error while performing an operation using SSMS, we get more meaningful error, which will be of great help in troubleshooting, instead of wondering or decoding on what the error is about, as like in some of the previous versions of SQL Server Management Studio.
– Stretch database wizard improvements which adds support for predicates.
– SQL Server 2016 management Studio now allows us to create ER Entity-Relationship Diagrams for SQL Azure Databases without the need for any external tool.
– Now we can see some new options when we right click on the SQL Azure database like “Open in Management Portal”, new “Reports” to view “Transaction Performance Analysis Overview”.
– New options supported when we right click on the user table like, Design(now we can design SQL Azure database tables from SSMS), option to choose selecting or edition top x rows, options for graphical interface to create and administer Full-Text indexes on SQL Azure database tables.

– Database properties now have additional properties or settings like, in the options tab, there is a section for Azure which has options which allows us to change the database edition, the Service Level Objective and the maximum database size.
– There have been many other options enabled in SQL Azure database and table properties which allows us to manage or make changes to database or tables of SQL Azure database and tables, just like regular on premise SQL Server database or table.
– Improvement in the AlwaysEncrypted Powershell commandlet to add key encryption APIs.
– Some known issues has been fixed, like to turn off IntelliSense in the SSMS toolbar, if it has been disabled in the Tools,Options dialog.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

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

 

How to Connect to AmazonRDS from SQL Server Management Studio?

Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the cloud. It provides cost-efficient, resizeable capacity for an industry-standard relational database and manages common database administration tasks. Although Amazon RDS is great for many applications to store its data, but has many limitations where many features cannot be used due to restricted permissions at file system level and on registry and OS level.

Although application connect to the database and works fine, it is important for DBAs or Developers to be able to query for data and to manage Databases and other objects places on AmazonRDS. There may be different ways to connect to Amazon RDS and manage or query data, but one of the easiest method and most popular and convenient way is to do it through well known tool which is SQL Server Management Studio(SSMS). We only need few basic details and then we should be good to connect to our database on AmazonRDS.

Firstly we need to create a security group from AmazonRDS web console, and then we must modify the DB instance to associate it with the security group. Once we are done with it, we can follow below steps to be able to connect to our database from SSMS

– On the Instances page of the AWS Management Console, we need to choose the arrow next to the DB instance to show the instance details. Make a note of the server name and port of the DB instance, which are displayed in the Endpoint field at the top of the panel, and the master user name, which is displayed in the Username field in the Configuration Details section.

– Launch Microsoft SQL Server Management Studio(SSMS). Then Connect to Server dialog box appears in the Server section “select Database Engine”. Now provide the server name of the DB instance then a comma(,) and port number. Example: EndPoint,Port

– Now choose SQL Server as authentication type as this is the only authentication method supported in AmazonDRS. Provide the user name and password and then click connect and you should be connected successfully, unless there are any port or firewall issues. Open a new query window and run few sample queries to make sure you can run queries.

Some of the known issues which you may face while trying to connect to AmazonRDS from SQL Server Management Studio are related to to your local firewall and the IP addresses you authorized to access your DB instance in the instance’s security group are not in sync. If you cannot send out or receive communications over the port you specified when you created the DB instance, you will not be able to connect to the DB instance. Check with your network administrator to determine if the port you specified for your DB instance is allowed to be used for inbound and outbound communication.or newly created DB instances, you must wait for the DB instance status to be “Available” before you can connect to the instance. Depending on the size of your DB instance, it can take up to 20 minutes before the instance is available.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2012
SQL Server 2008 R2

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

 

Unable to Connect to SQL Server Embedded edition Instance Used by SharePoint

On one of the servers, we started receiving alerts related to SQL Server instance MICROSOFT##SSEE. DBAs were not aware of this SQL Server instance as none of the DBAs have installed it on the server. We tried to connect to this SQL Server instance from SQL Server Management Studio, but it returned below error.

Cannot connect to SERVER\MICROSOFT##SSEE.
===================================
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)

After a quick search, found that this instance is related to Windows Internal Database. SSEE refers to SQL Server Embedded edition as well in previous versions. This is generally gets installed and used by Windows Server Update Services (WSUS) or Windows Sharepoint Services (WSS).

At times, there can be requirement to connect to this particular instance to manage or administer using SQL Server Management Studio (SSMS) by DBAs, however we cannot connect to it using TCP/IP or SharedMemory protocols, we would need to use NamePipes to connect to this instance.

We will need to use the below name in the server name box in SQL Server Management Studio (SSMS)
np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

If you do not have SQL Server Management Studio installed on the system, then best way to go would be to download and install SQL Server Express Edition Management Studio (SSMS). Once installed, we can use the above mentioned command to connect to this instance and administer it if required.

What is Windows Internal Database?
Windows Internal Database is a relational data store used by the following applications and services.
Active Directory Rights Management Services (AD RMS)
Windows Server Update Services
Windows System Resource Manager

These applications and services share a single instance of the data store. Windows Internal Database is installed by the first application or service that requires it. Because multiple applications and services use the same instance of Windows Internal Database, removing these applications and services does not remove the data store. If you have removed all applications and services that use Windows Internal Database, you can then remove it by using the Server Manager Remove Features Wizard.

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.

 

CTRL+R to Toggle the Results Pane Not Working Properly in SQL Server Management Studio (SSMS)

SQLServerF1

It is common for SQL Server DBAs to use SQL Server Management Studio (SSMS) for managing and administering SQL Server instances. SSMS offers variety of shortcuts to enable quick access to certain functions, one of such is the use of CTRL + R which can be used to Hide the results window or to show the results pane.

Recently, While I was using SQL Server 2014 Management Studio (SSMS) and when I tried to use the CTRL + R button, the results window was not getting cleared or not getting disappeared which ideally should. At the bottom left corner, I noticed a message that says “CTRL+R) was pressed. Waiting for second key of chord…”

The message displayed does not make any sense. After quick search, I found that this option can also be enabled from Menu options in SSMS. So, I clicked on “Window” menu option, but did not find the “Hide Results Pane” or the “Show Results Pane” options. After further research, found KB article which explains this issue to be caused because the .vssettings file for SQL Server Management Studio is corrupted.

However, this article says this is applicable for SQL Server 2012, but the issue happened for SQL Server 2014 SSMS. I went ahead and tried the below work around suggested in the above mentioned article and after which (CTRL + R) started working fine.

– Opened the SQL Server 2014 Management Studio.
– From the menu bar, clicked on “Tools”, and then clicked on Options.
– In the tree-view pane on the left side of the dialog box, clicked Keyboard.
– Clicked on “Reset” to reset the default keyboard mapping scheme.
– Next, In the dialog box that appeared, clicked on “Yes” and finally clicked on OK.
– Restarted SSMS and then I was able to use (CTRL + R) to toggle Results pane without any issues.

Use and Importance of SQL Server Management Studio
SQL Server Management Studio (SSMS) is used by DBAs and Developers for accessing, configuring, managing, development, and administering of SQL Server instances. SSMS combines the features of Enterprise Manager, Query Analyzer, and Analysis Manager, included in previous releases of SQL Server, into a single environment. In addition, SSMS works with all components of SQL Server such as Reporting Services and Integration Services. Developers get a familiar experience, and database administrators get a single comprehensive utility that combines easy-to-use graphical tools with rich scripting capabilities. SSMS combines a broad group of graphical tools with a number of rich script editors to provide access to SQL Server to developers and administrators of all skill levels.

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.

 

Using Lower Version SSMS to Connect to Higher Version of SQL Server Instance

SQLServerF1

SQL Server Management Studio (SSMS) is one of the most useful and important tool for SQL Server DBAs to manage the SQL Server instances. Without SQL Server Management Studio (SSMS), it will be very difficult for DBAs to perform their daily duties or troubleshooting any issues with SQL Server instances.

One of the doubt or question many DBAs or Developers have regarding the SQL Server Management Studio is that weather they can connect to a SQL Server Instance of higher version using the SQL Server Management Studio (SSMS) of lower version.

For Example, Can we cannot to SQL Server 2014 instance using the SQL Server 2012 SQL Server Management Studio (SSMS)?

Yes, we can definitely connect to lower higher version of SQL Server instance using the lower version SQL Server Management Studio (SSMS). In our example, we can certainly connect to SQL Server 2014 instance using the SQL Server 2012 SQL Server Management Studio (SSMS), however certain features cannot be used or does not work. With every version of SQL Server, new features are introduced, so the old SSMS cannot know about the new features, thus fails while accessing the new features from old Management Studio.

As a best practice, it is always good to use latest version to SSMS to connect to any version of SQL Server instance. Latest version of SSMS will have no problem connecting to lower versions of SQL Server instances. You can access all features of lower version SQL Server instance features using the higher version SQL Server Management Studio (SSMS). If there are any features which are retired or removed in the new version of SQL Server, still the Management Studio mostly will work when using those features for lower version SQL Server instances. In special cases, we may have to use lower version SSMS to connect to lower version SQL Server instances.

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 Management Studio (SSMS) Common Errors or Failures

SQL Server Management Studio (SSMS) by default gets installed on all servers where we install SQL Server database engine or related components. SQL Server DBA’s often use SSMS in order to manage the SQL Server instances for tasks like creating databases, creating logins, users and granting permissions, Creating maintenance plans or jobs, checking for any errors and troubleshoot various issues with SQL Server instance by running queries. SSMS has become one of the most important tool for a DBA to function their job. Most of the time, there wont be any errors with SQL Server Management Studio (SSMS), but at times, we may end up with some errors while using SSMS and we need to fix them quickly.

SQL Server Management Studio is dependent on many other components like Visual Studio, .Net Framework, etc, so changes to any of these components can also result in the failure or errors with SSMS.

Below are some of the common errors which we may see with SQL Server Management Studio (SSMS).
Error while trying to create new database diagram, which is crashing the SSMS with error
“Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (Microsoft.VisualStudio.OLE.Interop)”

You can resolve this issue by following below steps
– Copy and replace the DLL “dsref80.dll” in below path from a machine where SSMS is working fine in to the machine where the problem exists.
C:\Program Files (x86)\Common Files\microsoft shared\Visual Database Tools\dsref80.dll
– Replace all the files in below folder using the files from another server where SSMS is working fine.
C:\Program Files (x86)\Common Files\microsoft shared\MSDesigners8\
– Close and Open SSMS.

Error in SQL Server Management Studio: Failed to start debugger. Additional information: The EXECUTE permission was denied on the object ‘sp_enable_sql_debug’, database ‘mssqlsystemresource’, schema ‘sys’. (Microsoft SQL Server, Error: 229)
– You may receive above error message if you try to use the Debug button in SSMS, if it was by mistake, then no need to worry, but if you want to use the debug option in SSMS, then make sure below are true.
o SQL Server Management Studio must be running under a Windows account that is a member of the sysadmin fixed server roll.
o The Database Engine Query Editor window must be connected by using either a Windows Authentication or SQL Server Authentication login that is a member of the sysadmin fixed server role.
o The Database Engine Query Editor window must be connected to an instance of the SQL Server 2008 Database Engine. You cannot run the debugger when the Query Editor window is connected to an instance that is in single-user mode.

Error while trying to start SQL Server 2012 Management Studio (SSMS)
Cannot find one or more components. Please reinstall the application.
Exception has been thrown by the target of an invocation

– You may receive above errors while trying to open SSMS after you install Visual Studio 11. Visual studio might have been installed separately or may have been installed as part of some other software installation.
– To resolve this issue, follow the below steps
o Open registry editor from Start -> Run -> Type regedit
o In registry Editor navigate to HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio
o Backup and Delete the key 11.0_Config
o Start the SQL Server Management Studio and it should not open without any more errors.
– Restart ssms.exe

Operation not supported on SQL Server 2005
– You may received above error if you are using SQL Server 2005 Management Studio and connected to higher version of SQL instance like SQL 2012 or SQL 2008 R2 and then use a feature which was introduced in these higher versions. SSMS 2005 cannot be used to use features introduced in higher versions of SQL Server, you need to install and use higher version of SQL Server management Studio(SSMS)

microsoft .net framework error.unable to read the list of previously registered servers on this system.Re-register your servers in the registered servers windowvalue cannot be null.parameter name:viewinfo
(Microsoft.sqlserver.managment.sqlstudio.explorer)
Microsoft.SqlServer.Management.Sdk.Sfc.SfcSerializationException: Serialization operation on
ServerGroup[@Name=’SqlServerCompactEditionServerGroup’] has failed.
System.IO.DirectoryNotFoundException: Could not find a part of the path ‘c:\winnt\temp\qhn8zgxb.tmp’.
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)

– This error can occur if the user profile is corrupted. Normally we RDP to the server where SQL Server is installed using a domain account or local account and then open SSMS and then try to connect to SQL Server instance, if the profile of this domain or windows account is corrupt, then this error occurs.
– To resolve this error either create a new windows or domain account and login using the new account and connect to SSMS and then to SQL Server instance or repair the profile of the existing account.

The File %CommonDir%\dte80.olb could not be loaded. An attempt to repair this condition failed because the file could not be found. Please reinstall this program.”
The application cannot start

– This error can occur if one or more files related to SSMS is corrupt or missing. dte80.olb is a shared file used by the Visual Studio shell, Management Studio, Books Online, and the MSDN help viewer.
– To resolve this issue, either uninstall and reinstall the SSMS component or check if
C:\Program Files\Common Files\Microsoft Shared\MSEnv\dte80.olb file exists, if it does not exist, then copy this file from another server which has same SQL Server version and build, but better option is to reinstall the SSMS component.

Value cannot be null.
Parameter name: viewInfo (Microsoft.SqlServer.Management.SqlStudio.Explorer)

– There can be various reasons why you may see this error. Follow below to resolve this error
o Make sure that C:\ drive has enough free space.
o Launch SSMS by right click and choose run-as-administrator
o Create empty folder with name 2, if it does not exist in below path, so that you can navigate to below folders.
C:\Users\\AppData\Local\Temp\2
o Make sure from start>run>%temp% and start>run>%tmp% folders can be opened without any error, if it returns any error, contact your server administrator to fix the same.
o Apply latest Service pack or Cumulative Update
o Uninstall and reinstall SQL Server Management Studio

Evaluation Period has expired. For information how to upgrade your evaluation software please go to. Microsoft SQL Server Management Studio Complete (expires in x days)
– This error occurs, if you have installed SQL Server Management Studio or Database Engine using SQL Server Enterprise Evaluation Edition, which will expire after 180 days.
– You need to perform an Edition upgrade to an licensed version of SQL Server to fix the error.
– Sometimes, ever after successful edition upgrade, still you may receive this error, and to fix the error, set the CommonFiles registry key value to 3 under
HKLM\SOFTWARE\\Microsoft\Microsoft SQL Server\100\ConfigurationState and then rerun the edition upgrade.

Microsoft SQL Server Management Studio
Object reference not set to an instance of an object.
(Microsoft.VisualStudio.Platform.WindowManagement)

– Try below options to see if it resolves the error
o Repair the .net framework
o Open a command prompt by choosing run asa dministrator and then run below commands
C:\Program Files\Internet Explorer>regsvr32 /u ieproxy.dll
C:\Program Files\Internet Explorer>regsvr32 ieproxy.dll
C:\Program Files (x86)\Internet Explorer>regsvr32 /u ieproxy.dll
C:\Program Files (x86)\Internet Explorer>regsvr32 ieproxy.dll
regsvr32 actxprxy.dll
o Uninstall SSMS and reinstall SSMS

There are various other errors which cause failure of SSMS and will try to add as many as possible.

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.

 

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

 

Error while Editing the backup maintenance plan in SQL Server 2005

Recently while I was trying to edit a maintenance plan, when trying to edit the backup task using Management Studio resulted in an error. Below is the error message


TITLE: Microsoft SQL Server Management Studio
Cannot show the editor for this task.
Value of ’27/6/2014 12:00:00 AM’ is not valid for ‘Value’. ‘Value’ should be between ‘MinDate’ and ‘MaxDate’.

After some research, found that this has impacted other users as well and a connect bug Link has been filed for the same.

There was no fix released for this, but there is a work around available which resolved the issue and then I was able to edit the backup task in the maintenance plan.

Below is the work around

1. Modify Maintenance Plan.
2. Click On the backup task.
3. Click F4 button on the keyboard and you will find the properties window to the right corner of the SSMS.
4. Change the value in “expiredate” by adding 10 years or Clear out the value in “expiredate”

Backup Task Maintenance Plan Properties

Backup Task Maintenance Plan Properties


5. Save the maintenance plan.
6. Reopen the maintenance plan and now you should be able to edit the backup task.

Happy Reading,
SQLServerF1 Team