1

Is there any major complication that one could run into while using the latest (developer) version of SSMS with older SQL Server 2005 instances?

Any way one could affect the data by using this version?

3 Answers 3

4

My motto is to always use the most recent version of the tools and always have tools >= highest engine you manage. Generally, most of everything in modern versions is backward compatible going back as early as 2005 instances.

There are a couple of exceptions, in addition to those others have mentioned:

  • For SQL Server 2000, I always install the 2000 client tools.
  • If you use DTS/SSIS you will want to have the tools relevant to the version of your packages. If you open a 2008 package in 2014 and save it, you will cause problems for other users still using older versions. This may also be true for maintenance plans.

The nice thing is that you can install both the 2005 version of the tools as well as the most recent version side-by-side. This way, if you come across any compatibility issues, you can fire up the older version.

In general, you should be able to use the newer version for just about everything, and not worry about any negative effects on your data (full disclosure though - I don't use the UI functionality for a whole lot, particularly the buggy designers).

2
  • A good set of considerations. May I ask what sort of tool would you consider over the functionality of SSMS?. Its a subjective matter, I know, but since its still in the span of the original question I would appreciate an alternative.
    – Nelson
    Feb 25, 2015 at 16:11
  • 1
    @ZQL Honestly I don't believe there is a good alternative unless maybe if you start looking into really pricy tools. I've just gotten accustomed to learning the T-SQL / DDL I need to run in a query window to get things done, since the UI isn't always ready early enough (Extended Events) or ever (service broker), and even when it is "ready" it's broken (just look at Connect for all the unresolved issues involving SSMS). Feb 25, 2015 at 18:32
2

I have been using it about a year now connecting also to SQL Server 2005. There are some features that don't work, at least following two I can remember now:

  • Connecting to profiler doesn't work. I noticed there is a patch for this, but it's not yet in the service pack. The error says "SQL Server Profiler cannot perform this action on database servers earlier than 2005. Please use SQL Server 2005 or later" even if the server is 2005.

  • Cannot view properties of an index in object explorer. Error is "Cannot read property IsSparse"

1
  • And I have connected SSMS 2014 to SQL Server 2000 and run scripts, etc. I don't rely on all the Enterprise Manager features working properly.
    – RLF
    Feb 25, 2015 at 13:52
2

That depends on your definition of major complication. Will it somehow cause your data to be affected in a negative way? No.

Would I recommend using SSMS 2014 to administrate a production 2005 instance? No, because you're going to run into a lot of issues when using the GUI to perform many types of operations. Even if they are supported in 2005, they may have been changed since then and using the updated GUI may not be viable.

Get used to seeing a lot of pop-ups like this (this is for a 2000 instance as conveniently our only 2005 instance is now acting up):

enter image description here

It works, but that doesn't mean it's a good idea.

1
  • 2
    To be fair, that error message is from trying to connect to 2000, not 2005. Feb 25, 2015 at 13:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.