Showing posts with label dear. Show all posts
Showing posts with label dear. Show all posts

Friday, March 23, 2012

many tables vs one big table

Dear experts,

If I have a number of related groups of data (e.g., stock prices for
different stocks) is it better to put them into many tables (e.g., one
table per stock) or into one big table (e.g., a single table where one
column is the stock id other columns are the price and other data)?

The single table approach with a clustered primary key including the
stock id and other information that is different for each stock seems
cleaner to me. But I worry that having a single table could hurt
preformance when the database gets very large. The clustered primary
key should make queries on the single table fast, but I'm worried about
inserts. According to my vague understanding of a clustered primary
key, records are physically stored on disk based on the primary key. So
if I insert a record for a given stock does the database have to
physicall "move down" all the records that are below that or does it do
something smarter?

To summarize:

1. Should I use a single table or many tables?

2. If using a single table, how are inserts handled by the DB to
prevent inserts from getting slower and slower as more items are added
to the DB?

Thanks in advance for your help and advice.Use a single table, not many tables. Use a multi-column key that
includes the stock. When a new stock comes along it should be a user
transaction to add a row to the Stocks master table, not a database
administrator's job to add tables. The database design should be
stable, not in a constant state of change.

Performance of INSERTS does not change much between inserting to a
table with 10000 rows or 10000000 rows.

Roy Harvey
Beacon Falls, CT

On 2 Jan 2007 08:57:21 -0800, "Emin" <emin.shopper@.gmail.comwrote:

Quote:

Originally Posted by

>Dear experts,
>
>If I have a number of related groups of data (e.g., stock prices for
>different stocks) is it better to put them into many tables (e.g., one
>table per stock) or into one big table (e.g., a single table where one
>column is the stock id other columns are the price and other data)?
>
>The single table approach with a clustered primary key including the
>stock id and other information that is different for each stock seems
>cleaner to me. But I worry that having a single table could hurt
>preformance when the database gets very large. The clustered primary
>key should make queries on the single table fast, but I'm worried about
>inserts. According to my vague understanding of a clustered primary
>key, records are physically stored on disk based on the primary key. So
>if I insert a record for a given stock does the database have to
>physicall "move down" all the records that are below that or does it do
>something smarter?
>
>To summarize:
>
1. Should I use a single table or many tables?
>
2. If using a single table, how are inserts handled by the DB to
>prevent inserts from getting slower and slower as more items are added
>to the DB?
>
>Thanks in advance for your help and advice.

|||Emin (emin.shopper@.gmail.com) writes:

Quote:

Originally Posted by

If I have a number of related groups of data (e.g., stock prices for
different stocks) is it better to put them into many tables (e.g., one
table per stock) or into one big table (e.g., a single table where one
column is the stock id other columns are the price and other data)?


There are two reasons why you would have more than one table:

1) Information is so different for different (groups of) stocks,
that you get different set of columns.

2) The table is really big. Then you would might make into a partioned
view or table, based on condition. But that would not be one table
per stock id.

So, in the logical design, that's one table.

Quote:

Originally Posted by

The single table approach with a clustered primary key including the
stock id and other information that is different for each stock seems
cleaner to me. But I worry that having a single table could hurt
preformance when the database gets very large. The clustered primary
key should make queries on the single table fast, but I'm worried about
inserts. According to my vague understanding of a clustered primary
key, records are physically stored on disk based on the primary key. So
if I insert a record for a given stock does the database have to
physicall "move down" all the records that are below that or does it do
something smarter?


If the PK is monotonically ascending new rows are just added at the
end, all continues smoothly. If the PK is not ascending, but for instance
a random GUID, then you will get a lots of page splits, and that
takes extra power. But the answer in this case is that you should
not cluster on the GUID, but on something else. It's perfectly possible
to have the clustered index on some other column(s).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 2 Jan 2007 08:57:21 -0800, Emin wrote:

(snip)

Quote:

Originally Posted by

>if I insert a record for a given stock does the database have to
>physicall "move down" all the records that are below that or does it do
>something smarter?


Hi Emin,

Roy answered most of your questions, but was friendly enough to leave
this bit for me to comment on.

Yes, it does something smarter. Rows are *NOT* physically stored in the
order imposed by the clustered index. The clustered index imposes a
*logical* ordering on the data, which is implemented by pointers that
form a logical chain of database pages.

If a row in inserted, SQL Server first navigates the clustered index to
find the correct location (database page). Then, there are two
possibilities:

1. There's still space left on the page. The row is added and the insert
is finished.

2. There's not enough free space on the page. A page split occurs: half
of the data on the page is moved to a newly allocated page and pointers
are updated to reflect the location of the new page in the chain. After
that, the new row is inserted.

If your database has to process *LOTS* of inserts (in the order of
thousands per second or more), it makes sense to define your clustered
index such that new rows are always inserted at the logical end of the
pointer chain. In that case, page splits will never happen.
For lower amounts of inserts, the overhead of a page split is
insignificant.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Dear Roy, Erland, and Hugo,

Thank you very much for your excellent responses. It sounds like a
single logical table is the way to go since the frequency of inserts is
not very high. If necessary, where would I get more information about
low level details like page splits (e.g., how to control the page size,
how to determine when and how many page splits are occuring, etc.)?

Thanks again,
-Emin

On Jan 2, 5:53 pm, Hugo Kornelis <h...@.perFact.REMOVETHIS.info.INVALID>
wrote:

Quote:

Originally Posted by

On 2 Jan 2007 08:57:21 -0800, Emin wrote:
>
(snip)
>

Quote:

Originally Posted by

if I insert a record for a given stock does the database have to
physicall "move down" all the records that are below that or does it do
something smarter?Hi Emin,


>
Roy answered most of your questions, but was friendly enough to leave
this bit for me to comment on.
>
Yes, it does something smarter. Rows are *NOT* physically stored in the
order imposed by the clustered index. The clustered index imposes a
*logical* ordering on the data, which is implemented by pointers that
form a logical chain of database pages.
>
If a row in inserted, SQL Server first navigates the clustered index to
find the correct location (database page). Then, there are two
possibilities:
>
1. There's still space left on the page. The row is added and the insert
is finished.
>
2. There's not enough free space on the page. A page split occurs: half
of the data on the page is moved to a newly allocated page and pointers
are updated to reflect the location of the new page in the chain. After
that, the new row is inserted.
>
If your database has to process *LOTS* of inserts (in the order of
thousands per second or more), it makes sense to define your clustered
index such that new rows are always inserted at the logical end of the
pointer chain. In that case, page splits will never happen.
For lower amounts of inserts, the overhead of a page split is
insignificant.
>
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

|||One more question, is there a process similar to defragmentation that I
should run periodically to collect split pages together?

Thanks again,
-Emin

On Jan 3, 1:42 pm, "Emin" <emin.shop...@.gmail.comwrote:

Quote:

Originally Posted by

Dear Roy, Erland, and Hugo,
>
Thank you very much for your excellent responses. It sounds like a
single logical table is the way to go since the frequency of inserts is
not very high. If necessary, where would I get more information about
low level details like page splits (e.g., how to control the page size,
how to determine when and how many page splits are occuring, etc.)?
>
Thanks again,
-Emin
>
On Jan 2, 5:53 pm, Hugo Kornelis <h...@.perFact.REMOVETHIS.info.INVALID>
wrote:
>

Quote:

Originally Posted by

On 2 Jan 2007 08:57:21 -0800, Emin wrote:


>

Quote:

Originally Posted by

(snip)


>

Quote:

Originally Posted by

Quote:

Originally Posted by

>if I insert a record for a given stock does the database have to
>physicall "move down" all the records that are below that or does it do
>something smarter?Hi Emin,


>

Quote:

Originally Posted by

Roy answered most of your questions, but was friendly enough to leave
this bit for me to comment on.


>

Quote:

Originally Posted by

Yes, it does something smarter. Rows are *NOT* physically stored in the
order imposed by the clustered index. The clustered index imposes a
*logical* ordering on the data, which is implemented by pointers that
form a logical chain of database pages.


>

Quote:

Originally Posted by

If a row in inserted, SQL Server first navigates the clustered index to
find the correct location (database page). Then, there are two
possibilities:


>

Quote:

Originally Posted by

1. There's still space left on the page. The row is added and the insert
is finished.


>

Quote:

Originally Posted by

2. There's not enough free space on the page. A page split occurs: half
of the data on the page is moved to a newly allocated page and pointers
are updated to reflect the location of the new page in the chain. After
that, the new row is inserted.


>

Quote:

Originally Posted by

If your database has to process *LOTS* of inserts (in the order of
thousands per second or more), it makes sense to define your clustered
index such that new rows are always inserted at the logical end of the
pointer chain. In that case, page splits will never happen.
For lower amounts of inserts, the overhead of a page split is
insignificant.


>

Quote:

Originally Posted by

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

|||Emin (emin.shopper@.gmail.com) writes:

Quote:

Originally Posted by

One more question, is there a process similar to defragmentation that I
should run periodically to collect split pages together?


Yes, DBCC DBREINDEX on SQL 2000. The preferred syntax on SQL 2005 is
ALTER INDEX WITH REBUILD or something like that.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 3 Jan 2007 10:42:46 -0800, Emin wrote:

Quote:

Originally Posted by

If necessary, where would I get more information about
>low level details like page splits (e.g., how to control the page size,
>how to determine when and how many page splits are occuring, etc.)?


Hi Emin,

For that kind of information, the only serious resource are the books
written by Kalen Delaney. Quite advanced stuff, definitely not entry
level, but that's unavoidable if you want to know about page splits.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Wednesday, March 21, 2012

Manualy Create IDENTITY Column inside ControlFlow

Dear Friends... I'm having a problem...

I want to manually create the identity column for a table...

I have some dataflws, and in each dataflow I insert values in this table...

I need to start the controlflow in a SQL task to get the last ID and save it in a global variable with name D_INST_IDENTITY.

And in each dataflow I have a script component transform, to get the ID... using a local variable COUNTER! and for each row I increment this value...

Until this step there is no problem... the problem starts here...:

I need to refresh the global variable in the final of each dataflow in order that in the next sequence dataflow I have D_INST_IDENTITY refreshed......

D_INST_IDENTITY = D_INST_IDENTITY + COUNTER

How can I do it? I have a RowCount transform next the script component, but generates errors...

What do you think I can do it?
Thanks!!

Can you not just use an Execute SQL Task to get the current max value in the target table and store is in a variable?

-Jamie

|||

I can do it... but I have some dataflows, and each dataflow insert in this same table... so, In the finaly of each sequence dataflow, i need tro refresh the identity value... in the begin of each dataflow I need to get this IDENTITY...

Example

1. Dataflow

Return the Initial value of IDENTITY_variable from SQL Task

Refresh Identity_variable

2. Dataflow

Read Identity_variable

Refresh Identity_variable

3. Dataflow

Read Identity_variable

Refresh Identity_variable

|||

And In the SQL Task I use:

SELECT MAX(INST_ID)+1 FROM Instrumento

But if teh table is empty returns me an error... Is this statment that is usually used?

|||

PedroCGD wrote:

I can do it... but I have some dataflows, and each dataflow insert in this same table... so, In the finaly of each sequence dataflow, i need tro refresh the identity value... in the begin of each dataflow I need to get this IDENTITY...

You don't do it IN the data-flow, you do it BEFORE the data-flow

PedroCGD wrote:

Example

1. Dataflow

Return the Initial value of IDENTITY_variable from SQL Task

Refresh Identity_variable

2. Dataflow

Read Identity_variable

Refresh Identity_variable

3. Dataflow

Read Identity_variable

Refresh Identity_variable

So you put an Execute SQL Task before each data-flow. Is there a problem with doing that?

(There are actually some cleverer ways of doing it but for now - let's keep it simple.)

-Jamie

|||

PedroCGD wrote:

And In the SQL Task I use:

SELECT MAX(INST_ID)+1 FROM Instrumento

But if teh table is empty returns me an error... Is this statment that is usually used?

It really helps if, when you get an error, you tell us the error emssage.

I'm pretty sure I can guess what it is though. Try this:

SELECT ISNULL(MAX(INST_ID), 0) +1 FROM Instrumento

-Jamie

|||

Dear Jamie,

In order I have teh best performance, I avoid to use multiple SQL tasks. Imagin that I have 20 dataflows? Why I need to execute a query in database for each dataflow, if I have a counter to automatically give me the IDENTITY column? Do you think is more consistent using SQL Task?

I have found the solution to read a global variavel, and change it inside the script component transform...

I initialize in the Script Component properties the variable INST_IDENTITY as ReadWritevariables, and in order to avoid errors inside the PreExecute method when I read the value, I changed the code as you can see:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Dim counter As Integer

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

counter = counter + 1

Row.TesteKey = counter

End Sub

Public Overrides Sub PreExecute()

Dim vars As IDTSVariables90 = Nothing

Me.VariableDispenser.LockForRead("User::INST_IDENTITY")

Me.VariableDispenser.GetVariables(vars)

counter = CType(vars(0).Value, Integer)

vars.Unlock()

MyBase.PreExecute()

End Sub

Public Overrides Sub PostExecute()

Me.ReadWriteVariables("INST_IDENTITY").Value = counter

MyBase.PostExecute()

End Sub

Protected Overrides Sub Finalize()

MyBase.Finalize()

End Sub

End Class

I was having errors if I read the value of a ReadWriteVariables inside the PreExcute Method, so, using VariableDispenser I can do it!!

Thanks!!

|||

PedroCGD wrote:

I have found the solution to read a global variavel, and change it inside the script component transform...

Cool! That is the "other way" that I alluded to earlier.

-Jamie

Saturday, February 25, 2012

Managing 2000 and 2005

Dear all
I have now a default instance of SQL Server 2005 running on one PC and an
old default instance of SQL Server 2000 running on another PC.
I would like to administer both SQL Servers with the new SQL Server 2005
Management Studio. However, I am unable to register the SQL Server 2000 in
the SQL Server 2005 Management Studio.
Any help is appreciated.
Best regards
Adrian
Firewalls?
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4525139C.1010502@.realsqlguy.com...
> Adrian wrote:
> In the connection dialog in Management Studio, try specifying the server
> name like this:
> serverDNSname,2433
> And in the Options of that connection dialog, make sure the "Encrypt
> Connection" checkbox is marked...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Deal all
Thank you very much for the excellent and very quick help.
Yes, the connection is established.
The trick is:
i) to follow this advice:
> In the connection dialog in Management Studio, try specifying the server
> name like this:
> serverDNSname,2433
but
ii) not to follow this one:
> And in the Options of that connection dialog, make sure the "Encrypt
> Connection" checkbox is marked...
Now my sleep will be peaceful.
Best regards
Adrian
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4525139C.1010502@.realsqlguy.com...
> Adrian wrote:
> In the connection dialog in Management Studio, try specifying the server
> name like this:
> serverDNSname,2433
> And in the Options of that connection dialog, make sure the "Encrypt
> Connection" checkbox is marked...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||You should be able to.
What problems are you seeing?
"Adrian" <adrian@.iai.uni-bonn.de> wrote in message
news:Ofpo5yH6GHA.4620@.TK2MSFTNGP02.phx.gbl...
> Dear all
> I have now a default instance of SQL Server 2005 running on one PC and an
> old default instance of SQL Server 2000 running on another PC.
> I would like to administer both SQL Servers with the new SQL Server 2005
> Management Studio. However, I am unable to register the SQL Server 2000 in
> the SQL Server 2005 Management Studio.
> Any help is appreciated.
> Best regards
> Adrian
>
|||Adrian wrote:
> Dear all
> I have now a default instance of SQL Server 2005 running on one PC and an
> old default instance of SQL Server 2000 running on another PC.
> I would like to administer both SQL Servers with the new SQL Server 2005
> Management Studio. However, I am unable to register the SQL Server 2000 in
> the SQL Server 2005 Management Studio.
> Any help is appreciated.
> Best regards
> Adrian
>
"Unable to register" means what exactly? Are you getting an error
message? Management Studio is perfectly capable of working with a 2000
server, I do it all day long every day...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4525017F.8020007@.realsqlguy.com...
> Adrian wrote:
> "Unable to register" means what exactly? Are you getting an error
> message? Management Studio is perfectly capable of working with a 2000
> server, I do it all day long every day...
Well, it is great to hear it. So I just need to figure out how.
I can connect to the SQL Server 2000 with the sqlcmd program within an eye's
blink. However, when I enter precisely the same connect data to the
register-dialog of the Management Studio it returns after quite a long time
a message with the error 10061.
So what am I doing wrong?
Adrian

>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||You should be able to connect to a SQL Server 2000 box from the SQL Server
2005 tools (including SSMS, Profiler, and the Replication Monitor
(sqlmonitor).
Keith Kratochvil
"Adrian" <adrian@.iai.uni-bonn.de> wrote in message
news:Ofpo5yH6GHA.4620@.TK2MSFTNGP02.phx.gbl...
> Dear all
> I have now a default instance of SQL Server 2005 running on one PC and an
> old default instance of SQL Server 2000 running on another PC.
> I would like to administer both SQL Servers with the new SQL Server 2005
> Management Studio. However, I am unable to register the SQL Server 2000 in
> the SQL Server 2005 Management Studio.
> Any help is appreciated.
> Best regards
> Adrian
>
|||Adrian wrote:
> Well, it is great to hear it. So I just need to figure out how.
> I can connect to the SQL Server 2000 with the sqlcmd program within an eye's
> blink. However, when I enter precisely the same connect data to the
> register-dialog of the Management Studio it returns after quite a long time
> a message with the error 10061.
> So what am I doing wrong?
That error indicates that the connection was refused by the server. Is
SQL Server using TCP/IP? Listening on what port?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45250DE5.8030609@.realsqlguy.com...
> Adrian wrote:
> That error indicates that the connection was refused by the server. Is
> SQL Server using TCP/IP? Listening on what port?
Yes, it listens on TCP/IP 2433 and requires encryption. The client libraries
on the SQL Server 2005 PC are set appriopriately, so sqlcmd has no problems
connecting to it. It is only the Management Studio that makes me ...
aaarrrgh.
Any ideas?
Adrian

>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Adrian wrote:
> Yes, it listens on TCP/IP 2433 and requires encryption. The client libraries
> on the SQL Server 2005 PC are set appriopriately, so sqlcmd has no problems
> connecting to it. It is only the Management Studio that makes me ...
> aaarrrgh.
> Any ideas?
>
In the connection dialog in Management Studio, try specifying the server
name like this:
serverDNSname,2433
And in the Options of that connection dialog, make sure the "Encrypt
Connection" checkbox is marked...
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Managing 2000 and 2005

Dear all
I have now a default instance of SQL Server 2005 running on one PC and an
old default instance of SQL Server 2000 running on another PC.
I would like to administer both SQL Servers with the new SQL Server 2005
Management Studio. However, I am unable to register the SQL Server 2000 in
the SQL Server 2005 Management Studio.
Any help is appreciated.
Best regards
AdrianYou should be able to.
What problems are you seeing?
"Adrian" <adrian@.iai.uni-bonn.de> wrote in message
news:Ofpo5yH6GHA.4620@.TK2MSFTNGP02.phx.gbl...
> Dear all
> I have now a default instance of SQL Server 2005 running on one PC and an
> old default instance of SQL Server 2000 running on another PC.
> I would like to administer both SQL Servers with the new SQL Server 2005
> Management Studio. However, I am unable to register the SQL Server 2000 in
> the SQL Server 2005 Management Studio.
> Any help is appreciated.
> Best regards
> Adrian
>|||Adrian wrote:
> Dear all
> I have now a default instance of SQL Server 2005 running on one PC and an
> old default instance of SQL Server 2000 running on another PC.
> I would like to administer both SQL Servers with the new SQL Server 2005
> Management Studio. However, I am unable to register the SQL Server 2000 in
> the SQL Server 2005 Management Studio.
> Any help is appreciated.
> Best regards
> Adrian
>
"Unable to register" means what exactly? Are you getting an error
message? Management Studio is perfectly capable of working with a 2000
server, I do it all day long every day...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4525017F.8020007@.realsqlguy.com...
> Adrian wrote:
> "Unable to register" means what exactly? Are you getting an error
> message? Management Studio is perfectly capable of working with a 2000
> server, I do it all day long every day...
Well, it is great to hear it. So I just need to figure out how.
I can connect to the SQL Server 2000 with the sqlcmd program within an eye's
blink. However, when I enter precisely the same connect data to the
register-dialog of the Management Studio it returns after quite a long time
a message with the error 10061.
So what am I doing wrong?
Adrian

>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||You should be able to connect to a SQL Server 2000 box from the SQL Server
2005 tools (including SSMS, Profiler, and the Replication Monitor
(sqlmonitor).
Keith Kratochvil
"Adrian" <adrian@.iai.uni-bonn.de> wrote in message
news:Ofpo5yH6GHA.4620@.TK2MSFTNGP02.phx.gbl...
> Dear all
> I have now a default instance of SQL Server 2005 running on one PC and an
> old default instance of SQL Server 2000 running on another PC.
> I would like to administer both SQL Servers with the new SQL Server 2005
> Management Studio. However, I am unable to register the SQL Server 2000 in
> the SQL Server 2005 Management Studio.
> Any help is appreciated.
> Best regards
> Adrian
>|||Adrian wrote:
> Well, it is great to hear it. So I just need to figure out how.
> I can connect to the SQL Server 2000 with the sqlcmd program within an eye
's
> blink. However, when I enter precisely the same connect data to the
> register-dialog of the Management Studio it returns after quite a long tim
e
> a message with the error 10061.
> So what am I doing wrong?
That error indicates that the connection was refused by the server. Is
SQL Server using TCP/IP? Listening on what port?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45250DE5.8030609@.realsqlguy.com...
> Adrian wrote:
> That error indicates that the connection was refused by the server. Is
> SQL Server using TCP/IP? Listening on what port?
Yes, it listens on TCP/IP 2433 and requires encryption. The client libraries
on the SQL Server 2005 PC are set appriopriately, so sqlcmd has no problems
connecting to it. It is only the Management Studio that makes me ...
aaarrrgh.
Any ideas?
Adrian

>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Adrian wrote:
> Yes, it listens on TCP/IP 2433 and requires encryption. The client librari
es
> on the SQL Server 2005 PC are set appriopriately, so sqlcmd has no problem
s
> connecting to it. It is only the Management Studio that makes me ...
> aaarrrgh.
> Any ideas?
>
In the connection dialog in Management Studio, try specifying the server
name like this:
serverDNSname,2433
And in the Options of that connection dialog, make sure the "Encrypt
Connection" checkbox is marked...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Firewalls?
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4525139C.1010502@.realsqlguy.com...
> Adrian wrote:
> In the connection dialog in Management Studio, try specifying the server
> name like this:
> serverDNSname,2433
> And in the Options of that connection dialog, make sure the "Encrypt
> Connection" checkbox is marked...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Deal all
Thank you very much for the excellent and very quick help.
Yes, the connection is established.
The trick is:
i) to follow this advice:
> In the connection dialog in Management Studio, try specifying the server
> name like this:
> serverDNSname,2433
but
ii) not to follow this one:
> And in the Options of that connection dialog, make sure the "Encrypt
> Connection" checkbox is marked...
Now my sleep will be peaceful.
Best regards
Adrian
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4525139C.1010502@.realsqlguy.com...
> Adrian wrote:
> In the connection dialog in Management Studio, try specifying the server
> name like this:
> serverDNSname,2433
> And in the Options of that connection dialog, make sure the "Encrypt
> Connection" checkbox is marked...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Managing 2000 and 2005

Dear all
I have now a default instance of SQL Server 2005 running on one PC and an
old default instance of SQL Server 2000 running on another PC.
I would like to administer both SQL Servers with the new SQL Server 2005
Management Studio. However, I am unable to register the SQL Server 2000 in
the SQL Server 2005 Management Studio.
Any help is appreciated.
Best regards
AdrianYou should be able to.
What problems are you seeing?
"Adrian" <adrian@.iai.uni-bonn.de> wrote in message
news:Ofpo5yH6GHA.4620@.TK2MSFTNGP02.phx.gbl...
> Dear all
> I have now a default instance of SQL Server 2005 running on one PC and an
> old default instance of SQL Server 2000 running on another PC.
> I would like to administer both SQL Servers with the new SQL Server 2005
> Management Studio. However, I am unable to register the SQL Server 2000 in
> the SQL Server 2005 Management Studio.
> Any help is appreciated.
> Best regards
> Adrian
>|||Adrian wrote:
> Dear all
> I have now a default instance of SQL Server 2005 running on one PC and an
> old default instance of SQL Server 2000 running on another PC.
> I would like to administer both SQL Servers with the new SQL Server 2005
> Management Studio. However, I am unable to register the SQL Server 2000 in
> the SQL Server 2005 Management Studio.
> Any help is appreciated.
> Best regards
> Adrian
>
"Unable to register" means what exactly? Are you getting an error
message? Management Studio is perfectly capable of working with a 2000
server, I do it all day long every day...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4525017F.8020007@.realsqlguy.com...
> Adrian wrote:
>> Dear all
>> I have now a default instance of SQL Server 2005 running on one PC and an
>> old default instance of SQL Server 2000 running on another PC.
>> I would like to administer both SQL Servers with the new SQL Server 2005
>> Management Studio. However, I am unable to register the SQL Server 2000
>> in the SQL Server 2005 Management Studio.
>> Any help is appreciated.
>> Best regards
>> Adrian
> "Unable to register" means what exactly? Are you getting an error
> message? Management Studio is perfectly capable of working with a 2000
> server, I do it all day long every day...
Well, it is great to hear it. So I just need to figure out how.
I can connect to the SQL Server 2000 with the sqlcmd program within an eye's
blink. However, when I enter precisely the same connect data to the
register-dialog of the Management Studio it returns after quite a long time
a message with the error 10061.
So what am I doing wrong?
Adrian
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||You should be able to connect to a SQL Server 2000 box from the SQL Server
2005 tools (including SSMS, Profiler, and the Replication Monitor
(sqlmonitor).
--
Keith Kratochvil
"Adrian" <adrian@.iai.uni-bonn.de> wrote in message
news:Ofpo5yH6GHA.4620@.TK2MSFTNGP02.phx.gbl...
> Dear all
> I have now a default instance of SQL Server 2005 running on one PC and an
> old default instance of SQL Server 2000 running on another PC.
> I would like to administer both SQL Servers with the new SQL Server 2005
> Management Studio. However, I am unable to register the SQL Server 2000 in
> the SQL Server 2005 Management Studio.
> Any help is appreciated.
> Best regards
> Adrian
>|||Adrian wrote:
> Well, it is great to hear it. So I just need to figure out how.
> I can connect to the SQL Server 2000 with the sqlcmd program within an eye's
> blink. However, when I enter precisely the same connect data to the
> register-dialog of the Management Studio it returns after quite a long time
> a message with the error 10061.
> So what am I doing wrong?
That error indicates that the connection was refused by the server. Is
SQL Server using TCP/IP? Listening on what port?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45250DE5.8030609@.realsqlguy.com...
> Adrian wrote:
>> Well, it is great to hear it. So I just need to figure out how.
>> I can connect to the SQL Server 2000 with the sqlcmd program within an
>> eye's blink. However, when I enter precisely the same connect data to the
>> register-dialog of the Management Studio it returns after quite a long
>> time a message with the error 10061.
>> So what am I doing wrong?
> That error indicates that the connection was refused by the server. Is
> SQL Server using TCP/IP? Listening on what port?
Yes, it listens on TCP/IP 2433 and requires encryption. The client libraries
on the SQL Server 2005 PC are set appriopriately, so sqlcmd has no problems
connecting to it. It is only the Management Studio that makes me ...
aaarrrgh.
Any ideas?
Adrian
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Adrian wrote:
> Yes, it listens on TCP/IP 2433 and requires encryption. The client libraries
> on the SQL Server 2005 PC are set appriopriately, so sqlcmd has no problems
> connecting to it. It is only the Management Studio that makes me ...
> aaarrrgh.
> Any ideas?
>
In the connection dialog in Management Studio, try specifying the server
name like this:
serverDNSname,2433
And in the Options of that connection dialog, make sure the "Encrypt
Connection" checkbox is marked...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Firewalls?
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4525139C.1010502@.realsqlguy.com...
> Adrian wrote:
>> Yes, it listens on TCP/IP 2433 and requires encryption. The client
>> libraries on the SQL Server 2005 PC are set appriopriately, so sqlcmd has
>> no problems connecting to it. It is only the Management Studio that makes
>> me ... aaarrrgh.
>> Any ideas?
> In the connection dialog in Management Studio, try specifying the server
> name like this:
> serverDNSname,2433
> And in the Options of that connection dialog, make sure the "Encrypt
> Connection" checkbox is marked...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Deal all
Thank you very much for the excellent and very quick help.
Yes, the connection is established.
The trick is:
i) to follow this advice:
> In the connection dialog in Management Studio, try specifying the server
> name like this:
> serverDNSname,2433
but
ii) not to follow this one:
> And in the Options of that connection dialog, make sure the "Encrypt
> Connection" checkbox is marked...
Now my sleep will be peaceful.
Best regards
Adrian
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4525139C.1010502@.realsqlguy.com...
> Adrian wrote:
>> Yes, it listens on TCP/IP 2433 and requires encryption. The client
>> libraries on the SQL Server 2005 PC are set appriopriately, so sqlcmd has
>> no problems connecting to it. It is only the Management Studio that makes
>> me ... aaarrrgh.
>> Any ideas?
> In the connection dialog in Management Studio, try specifying the server
> name like this:
> serverDNSname,2433
> And in the Options of that connection dialog, make sure the "Encrypt
> Connection" checkbox is marked...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com