Replace SSMS 2005 with SSMS 2008

Ekrem Önsoy (ekrem-at-compecta.com) writes:
> I first wanted to share with you guys and confirm it.
>
> The SQL Server Instance on the hosting company is a 8.0.760. I tried to
> connect to another database on another web hosting company's SQL Server
> and it connects and expands the Databases node successfully and in this
> case, the SQL Server Instance's version is 9.0.3042.
>
> I believe there is some kinda problem with 8.0.x version. I'll report this
> issue now.

But not that my findings where the opposite: I got the error on SQL 2005,
not on SQL 2000. Anyway, I tracked it down to the dreaded AUTO_CLOSE
option. If the database is in AUTO_CLOSE, SSMS runs a query in that
database to get collation and some more information.

This is really bad: what if the server has many databases in auto-close,
a couple of thousand? Guess how long time it will take expand the Databases
node in Object Explorer.

And the fact that the exception is not handled properly, adds insult to
injury.

Turns out that this issue was reported just a few days ago,
https://connect.microsoft.com/SQLSer...dbackID=362646
so I did not have to submit a bug myself.


--
Erland Sommarskog, SQL Server MVP, esquel-at-sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Ekrem Önsoy (ekrem-at-compecta.com) writes:
> There's also another weird thing that, my test machine is Windows Vista
> Business Edition and I installed SQL Server 2008 Enterprise Edition on
> this OS. According to the system requirements of SQL Server 2008
> Enterprise Edition, I'm not supposed to be able to install this edition
> of SQL Server 2008 unless it's Enterprise Evaluation Edition.
>
> However, when I run SELECT SERVERPROPERTY('edition') or SELECT @@VERSION
> it says it's Enterprise Edition, it does not mention anywhere it's an
> Evaluation Enterprise Edition or something similar.
>
> Do you have any idea about this?

Oops!

I have Vista, but I have not downloaded Enterprise, so I am not going to
test, but it sounds like a goof in the Setup checks.


--
Erland Sommarskog, SQL Server MVP, esquel-at-sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Ekrem Önsoy (ekrem-at-compecta.com) writes:
> There's also another weird thing that, my test machine is Windows Vista
> Business Edition and I installed SQL Server 2008 Enterprise Edition on
> this OS. According to the system requirements of SQL Server 2008
> Enterprise Edition, I'm not supposed to be able to install this edition
> of SQL Server 2008 unless it's Enterprise Evaluation Edition.
>
> However, when I run SELECT SERVERPROPERTY('edition') or SELECT @@VERSION
> it says it's Enterprise Edition, it does not mention anywhere it's an
> Evaluation Enterprise Edition or something similar.
>
> Do you have any idea about this?

And this is RTM and not RC0?


--
Erland Sommarskog, SQL Server MVP, esquel-at-sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

This is abolutely the RTM Enterprise Edition from MSDN
(en_sql_server_2008_enterprise_x86_x64_ia64_dvd_x1 4-89207.iso)

--
Ekrem Önsoy




"Erland Sommarskog" wrote in message
news:Xns9B039CE333295Yazorman-at-127.0.0.1...
> Ekrem Önsoy (ekrem-at-compecta.com) writes:
>> There's also another weird thing that, my test machine is Windows Vista
>> Business Edition and I installed SQL Server 2008 Enterprise Edition on
>> this OS. According to the system requirements of SQL Server 2008
>> Enterprise Edition, I'm not supposed to be able to install this edition
>> of SQL Server 2008 unless it's Enterprise Evaluation Edition.
>>
>> However, when I run SELECT SERVERPROPERTY('edition') or SELECT @@VERSION
>> it says it's Enterprise Edition, it does not mention anywhere it's an
>> Evaluation Enterprise Edition or something similar.
>>
>> Do you have any idea about this?
>
> And this is RTM and not RC0?
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel-at-sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinf...ons/books.mspx
>

I'm reporting this as well.

--
Ekrem Önsoy




"Erland Sommarskog" wrote in message
news:Xns9B039AE02ED1EYazorman-at-127.0.0.1...
> Ekrem Önsoy (ekrem-at-compecta.com) writes:
>> There's also another weird thing that, my test machine is Windows Vista
>> Business Edition and I installed SQL Server 2008 Enterprise Edition on
>> this OS. According to the system requirements of SQL Server 2008
>> Enterprise Edition, I'm not supposed to be able to install this edition
>> of SQL Server 2008 unless it's Enterprise Evaluation Edition.
>>
>> However, when I run SELECT SERVERPROPERTY('edition') or SELECT @@VERSION
>> it says it's Enterprise Edition, it does not mention anywhere it's an
>> Evaluation Enterprise Edition or something similar.
>>
>> Do you have any idea about this?
>
> Oops!
>
> I have Vista, but I have not downloaded Enterprise, so I am not going to
> test, but it sounds like a goof in the Setup checks.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel-at-sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinf...ons/books.mspx
>

Indeed the logical error you found can be a hell of a problem for most of
us. As we all know that a default SQL Server 2005 Express Edition database
will be in AUTO_CLOSE property set True it's going to be a bad really bad
trouble. Hopefully they'll release a fix for these bugs.

I tried to reproduce the error you get and I got the following one:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request.
(Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click:
http://go.microsoft.com/fwlink?ProdN...r&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)

------------------------------

The server principal "test" is not able to access the database
"AW_TranRepl_Test" under the current security context. (Microsoft SQL
Server, Error: 916)

------------------------------

When setting AUTO_CLOSE property to false, no problem.


You know, in my case, it seems SSMS 2008 is tring to reach to every database
to list and get some info about them when clicking Databases even if the
ones that my Login is not mapped. So I get this invalid user error when I
expand the Databases node. I'm not sure what SSMS 2008 is trying to do
behind the scenes. I must run the SQL Profiler against a SQL Server 2000
instance in a test environment and see what happens.

Also, yesterday I was testing Resource Governor and I somehow cause SSMS to
crash but I could not reproduce it. It seems we'll have some problems with
this feature too. My test was generally about changing a workgroup's
resource pool while a connexion was open and moving the workgroup of this
connection to another resource pool while Resource Governor disabled. I'll
work on it later again to push and cause it to crash.

--
Ekrem Önsoy



"Erland Sommarskog" wrote in message
news:Xns9B039A63691A0Yazorman-at-127.0.0.1...
> Ekrem Önsoy (ekrem-at-compecta.com) writes:
>> I first wanted to share with you guys and confirm it.
>>
>> The SQL Server Instance on the hosting company is a 8.0.760. I tried to
>> connect to another database on another web hosting company's SQL Server
>> and it connects and expands the Databases node successfully and in this
>> case, the SQL Server Instance's version is 9.0.3042.
>>
>> I believe there is some kinda problem with 8.0.x version. I'll report
>> this
>> issue now.
>
> But not that my findings where the opposite: I got the error on SQL 2005,
> not on SQL 2000. Anyway, I tracked it down to the dreaded AUTO_CLOSE
> option. If the database is in AUTO_CLOSE, SSMS runs a query in that
> database to get collation and some more information.
>
> This is really bad: what if the server has many databases in auto-close,
> a couple of thousand? Guess how long time it will take expand the
> Databases
> node in Object Explorer.
>
> And the fact that the exception is not handled properly, adds insult to
> injury.
>
> Turns out that this issue was reported just a few days ago,
> https://connect.microsoft.com/SQLSer...dbackID=362646
> so I did not have to submit a bug myself.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel-at-sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinf...ons/books.mspx
>

Ekrem Önsoy (ekrem-at-compecta.com) writes:
> You know, in my case, it seems SSMS 2008 is tring to reach to every
> database to list and get some info about them when clicking Databases
> even if the ones that my Login is not mapped. So I get this invalid user
> error when I expand the Databases node. I'm not sure what SSMS 2008 is
> trying to do behind the scenes. I must run the SQL Profiler against a
> SQL Server 2000 instance in a test environment and see what happens.

That may not help. When I first debugged this with Profiler, I saw
that SSMS 2008 ran a query to get database information, but there were
no NULL columns. Nevertheless it ran a query against my auto_close database
to get version and collation. It did not make sense. However, when I traced
it against SQL 2008, the result set for all databases was much wider and
included the collation (which was NULL for the closed databases).

--
Erland Sommarskog, SQL Server MVP, esquel-at-sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

> That may not help. When I first debugged this with Profiler, I saw
> that SSMS 2008 ran a query to get database information, but there were
> no NULL columns. Nevertheless it ran a query against my auto_close database
> to get version and collation. It did not make sense. However, when I traced
> it against SQL 2008, the result set for all databases was much wider and
> included the collation (which was NULL for the closed databases).

Yes, I think this is because much more information is included in Object
Explorer Details if the targeted server is 2008.

I complained about this one too, that this info should be N/A or something,
or not be checked at all, instead of returning NULL, when the GUI apparently
can't handle NULL values.

I agree with Erland's suggestion, especially for SQL Server 2000 tools. From this feedback, http://connect.microsoft.com/SQLServ...dbackID=352031,
we can find that our product team recommends that you run Enterprise Manager side-by-side with SSMS 2008 for managing 2000 processes.

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg-at-microsoft.com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...#notifications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Thanks to all for your comments, I've installed SSMS 2008, and left SSMS 2005
in place.

"TomT" wrote:

> Are there any problems or issues in replacing just SSMS 2005 with the new
> version - I'd like to get familiar with it, but we have not yet upgraded our
> databases to 2008 (they are, except for 2, 2005).
>
> I'm hoping to do this only on my client/dev system, which I use to connect
> to our SQL 2005 servers.
>
> Thanks