?
Solved

SQL Server 2005 - Create Server and Register?

Posted on 2006-05-02
18
Medium Priority
?
502 Views
Last Modified: 2008-02-01
Hello,

  I just migrated to SQL Server 2005 and was pleased to see that my old SQL 2000 Server Instance was backward compatable. I can actually view/edit/etc from the Studio Manager. Cool! I like backwards compatability.

  However, I would like to start a new 2005 server instance and have it be registered. How can I accomplish this?

  In the Studio Manager, in the "Registered Servers" window, it seems like all I can do is add a previously registered server instance (such as my SQL Server 2000 instance I mention above). There isn't a choice that I can see anywhere for creating a new one. Since all the 2005 client tools are brand-spankin' new and are totally diffent than the old enterprise manager, I'm not sure what to do. Maybe I am looking in the wrong place? What sequence of mouse clicks brings up a new server creator?




 
0
Comment
Question by:sapbucket
  • 5
  • 4
  • 4
  • +1
14 Comments
 
LVL 20

Expert Comment

by:Sirees
ID: 16589764
Right click on database engine New ->Server Registration
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 16589861
Or Right-click on the Server name - the drop down menu has Register.
0
 
LVL 3

Author Comment

by:sapbucket
ID: 16590402
While I am able to do what you mention, it still does not connect. Perhaps a discussion of what I am clicking will illuminate what I am doing wrong:

1.) start MS SQL Management Studio.
2.) Right click "Database Engine" and select new->Server Registration
3.) The wizard that appears I do the following:
 a.) under server name I type PAVILION\SQLEXPRESS
 b.) under authentication I select "Windows Authentication" (I am logged in as admin)
 c.) select "Save"
4.) I now see the server in the Registered Services list, but it is not started (no green "play button" arrow - just a white ball)
5.) I right click on PAVILION\SQLEXPRESS in the Registered Services list, and see that all the start/stop/pause/restart choices are disabled (greyed out).

And that is as far as I can get. I cannot start the registered server instance. Is there something I am missing? Having the server service appear in the Registered Services list is a great start - but doesn't really get me anywhere if I can't start it.

Thanks for the help so far...
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 3

Author Comment

by:sapbucket
ID: 16590543
I noticed that when I right click on PAVILION\SQLEXPRESS in the Registered Servers List and select Connect->Object Browser I get the following error:

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

An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------


However, a remote connection is not what I am doing at all. I am connecting using Windows Auth and the server resides on my cpu. It probably can't connect because it isn't started.
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 16590642
Check the SQL Server surface configuration.
0
 
LVL 3

Author Comment

by:sapbucket
ID: 16590776
Where is that?
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16596566
go to sql server configuration manager you can find it in

control panel ==> administrative tool ===> computer management ==> services and application
enable tcp ip for sql sever network  and sql server client configuration
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 16596820
It is under Programs - Microsoft SQL Server 2005 - Configuration Tools - SQL Server configuration manager
0
 
LVL 3

Author Comment

by:sapbucket
ID: 16598740
ok maybe I am taking this question down the wrong path.

I'll keep this as simple as I can.

<add key="hibernate.connection.connection_string" value="Data Source=SONYLAPTOP\SQLEXPRESS;initial catalog=MyDatabase;User Id=MyUserId;Password=MyPassWord" />


see the "Data Source = SONYLAPTOP\SQLEXPRESS"?

well that's the data source i want to connect to. before i can do that, I have to setup the data source. That means creating it, registering it, and starting it.

How can this be accomplished using SQL SERVER 2005? (If this is difficult - I did appoint 500 points for a solution!)
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16620447
for all connecting strings you can check at
www.connectionsting.com 

Use this connection string instead and try

<add key="hibernate.connection.connection_string" Provider=SQLOLEDB.1;value="Data Source=SONYLAPTOP\SQLEXPRESS;initial catalog=MyDatabase;User Id=MyUserId;Password=MyPassWord;Integrated Security=SSPI;Persist Security Info=False" />
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16724671
hi,
did you get your solution?
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 1000 total points
ID: 16929568
>>well that's the data source i want to connect to. before i can do that, I have to setup the data source. That means >>creating it, registering it, and starting it.
>>How can this be accomplished using SQL SERVER 2005?

You have to install sql server 2005 on a machine and this data sourse is the sql server machine name

In the below
SONYLAPTOP\SQLEXPRESS

SONYLAPTOP  is the sql server name
SQLEXPRESS  is the instance name

if you are usinf default instance then the data source will be simply

"Data Source = SONYLAPTOP"



0
 
LVL 3

Author Comment

by:sapbucket
ID: 16930391
Aha! Now I think we are getting somewhere.

How do I create a new "instance"?

So,

 SONYLAPTOP\MYNEWINSTANCE would be the data source I want to create.

It should be really easy. For some reason I can't figure out some supposedly intuitive sequence of mouse and keyboard clicks to do this.

Many thanks for the help
0
 
LVL 27

Assisted Solution

by:ptjcb
ptjcb earned 1000 total points
ID: 17126257
How do I create a new "instance"?

Rerun the installation. That is the only way to create an instance.  Run setup again and specify a different instance name.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question