I have a SQL 2000 database used in a data entry / transactional mode. We
have few users, but they do occassionally run reports which are server
intensive. It is a small business. We would like to begin using the data in
the database on the website. I'm wondering if I should replicate the DB on
the same server. Does that really provide any performance protection for the
data entry folks? We don't think the traffic on the Internet will be intense
to begin with because it will only be for members. Does a snapshot
replication make sense (we only need a refresh each night) or should I just
let the web hit the data entry db? Thanks
Hi,
I understand that some of your users occasionally ran reports on your
server. The operations impacted your server performance. You want to know
if you can replicate the DB on the same server to improve the performance.
If I have misunderstood, please let me know.
Generally producing report is CPU, memory and I/O intensive, so I recommend
that you replicate your database to a different server (dedicated for
report server) and have your web application use this report server for
producing report. Since reports are for history data, snapshot replication
is enough.
For implementing replication, please refer to:
Implementing Replication (Enterprise Manager)
http://msdn2.microsoft.com/en-us/library/aa216618(sql.80).aspx
Implementing Master-Slave Snapshot Replication Using SQL Server
http://msdn2.microsoft.com/en-us/library/ms998561.aspx
Planning for Snapshot Replication
http://msdn2.microsoft.com/en-us/library/aa237481(SQL.80).aspx
If you have any other questions or concerns, please let me know. Have a
nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||That answers my question. Thanks Charles!
"Charles Wang[MSFT]" wrote:
> Hi,
> I understand that some of your users occasionally ran reports on your
> server. The operations impacted your server performance. You want to know
> if you can replicate the DB on the same server to improve the performance.
> If I have misunderstood, please let me know.
> Generally producing report is CPU, memory and I/O intensive, so I recommend
> that you replicate your database to a different server (dedicated for
> report server) and have your web application use this report server for
> producing report. Since reports are for history data, snapshot replication
> is enough.
> For implementing replication, please refer to:
> Implementing Replication (Enterprise Manager)
> http://msdn2.microsoft.com/en-us/library/aa216618(sql.80).aspx
> Implementing Master-Slave Snapshot Replication Using SQL Server
> http://msdn2.microsoft.com/en-us/library/ms998561.aspx
> Planning for Snapshot Replication
> http://msdn2.microsoft.com/en-us/library/aa237481(SQL.80).aspx
> If you have any other questions or concerns, please let me know. Have a
> nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
>
>
>
>
>
sql
Showing posts with label wehave. Show all posts
Showing posts with label wehave. Show all posts
Friday, March 30, 2012
Monday, March 19, 2012
New Server Cannot 'See Out'
Hi All,
Not sure where to put this post, hope this is the right place.
Relatively new to SQL, not sure what info you need, so here we go: We
have a Windows 2000 Server running SQL 2000 ("Server1) in location 1.
This server is outside of the firewall.
We have a network setup in location 2 where we are doing development and
also have a server in that location (everything at location 2 is behind
the same firewall). There is a new server in location 2 running Windows
2003 Server and SQL Server 2005.
The problem is that Server2 cannot see Server1, and Server1 cannot see
Server2.
If, using Object Explorer, I click Connect > Database Engine, and put in
the details for Server2, I get an error that the database does not reply.
Also, if trying to connect from Server2 to Server1 (registering a db), I
get an access denied or does not exist error.
Any of the development machines at location 2 can see and connect to
Server1, but Server2 cannot. Also, any of the Dev machines can see and
connect to Server2 as well, but Server1 cannot.
1433 is not blocked in the firewall of location2, since all Dev machines
can access SQL on Server1.
I'm really at a loss, and any ideas would be most helpful.
Thanks,
MichaelIt sounds like port 1433 is blocked at the firewall. The firewall is between
Server1 and Location2, nothing is going through the firewall.
You need to open a port (the default is 1433) in the firewall to allow 2-way
tcp traffic, and then set a firewall rule that restricts traffic to just the
IP addresses of Server1 and Server2.
And if you want a bit more obscurity, you could even use a different port.
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Michael" <itsjustme@.nospam.nospam> wrote in message
news:OK6RHwkoGHA.516@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> Not sure where to put this post, hope this is the right place. Relatively
> new to SQL, not sure what info you need, so here we go: We have a Windows
> 2000 Server running SQL 2000 ("Server1) in location 1. This server is
> outside of the firewall.
> We have a network setup in location 2 where we are doing development and
> also have a server in that location (everything at location 2 is behind
> the same firewall). There is a new server in location 2 running Windows
> 2003 Server and SQL Server 2005.
> The problem is that Server2 cannot see Server1, and Server1 cannot see
> Server2.
> If, using Object Explorer, I click Connect > Database Engine, and put in
> the details for Server2, I get an error that the database does not reply.
> Also, if trying to connect from Server2 to Server1 (registering a db), I
> get an access denied or does not exist error.
> Any of the development machines at location 2 can see and connect to
> Server1, but Server2 cannot. Also, any of the Dev machines can see and
> connect to Server2 as well, but Server1 cannot.
> 1433 is not blocked in the firewall of location2, since all Dev machines
> can access SQL on Server1.
> I'm really at a loss, and any ideas would be most helpful.
> Thanks,
> Michael|||HI,
Thanks, but thats not it. Remember, we have numerous developer systems
sitting behind the same firewall, each has SQL 2005 developer version
installed, and they have perfect communication through the firewall with
Server1.
Arnie Rowland wrote:
> It sounds like port 1433 is blocked at the firewall. The firewall is betwe
en
> Server1 and Location2, nothing is going through the firewall.
> You need to open a port (the default is 1433) in the firewall to allow 2-w
ay
> tcp traffic, and then set a firewall rule that restricts traffic to just t
he
> IP addresses of Server1 and Server2.
> And if you want a bit more obscurity, you could even use a different port.
>|||Hi Michael,
Thank you for your posting!
Would you please try to add an alias of the Server1 in the server 2?
Please follow these steps:
1. Open Start Menu, All Programs, Microsoft SQL Server 2005, Configuration
Tools, SQL Server Configuration Manager.
2. In the left panel, click SQL Native Client Configuration, then
right-click Aliases, click New Alias.
3. In the Alias-New dialogbox, please type the Alias Name you want , and
type the Server1 name in the server section. Type 1433 in the Port No.
After that, please click OK.
4. Please try to connect to the Server 1 and let me know the result. Thank
you!
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Also, make sure you look at the network configuration tab (Right-click in
enterprise manager on the server in question, choose properties) of the
MSDE/SQL Server itself. Sometimes the named pipes and TCP/IP protocols do no
t
get added, and therefore nothing other than enterprise manager can connect!
James
"Wei Lu [MSFT]" wrote:
> Hi Michael,
> Thank you for your posting!
> Would you please try to add an alias of the Server1 in the server 2?
> Please follow these steps:
> 1. Open Start Menu, All Programs, Microsoft SQL Server 2005, Configuration
> Tools, SQL Server Configuration Manager.
> 2. In the left panel, click SQL Native Client Configuration, then
> right-click Aliases, click New Alias.
> 3. In the Alias-New dialogbox, please type the Alias Name you want , and
> type the Server1 name in the server section. Type 1433 in the Port No.
> After that, please click OK.
> 4. Please try to connect to the Server 1 and let me know the result. Thank
> you!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Wei Lu and James,
I followed your directions and created the Alias (and ensured the
protocols are present). I then logged into Server 2 and did exactly
these steps:
Start > All Programs > Mircosoft SQL Server 2005 > SQL Server Management
Studio.
Once it opened, I logged into the local instance of SQL Server. Then, I
did these steps:
In Object Explorer (along left side of screen, with only my local
instance showing), I clicked Connect > Database Engine. I then entered
the following information:
Server Name = 64.203.xxx.xxx
Authentication = SQL Server Authentication
username = xxx
password = xxx
On the Connection Properties tab, I tried it with default, and also
TCP/IP. Each time, I get the message below:
TITLE: Connect to Server
--
Cannot connect to 64.203.xxx.xxx
ADDITIONAL INFORMATION:
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: TCP Provider, error: 0 - A connection attempt
failed because the connected party did not properly respond after a
period of time, or established connection failed because connected host
has failed to respond.) (Microsoft SQL Server, Error: 10060)
Am I doing something wrong or missing something?
Thanks,
Michael
ps. Sorry for my delayed response, was out with a severe cold.
James Vickers wrote:[vbcol=seagreen]
> Also, make sure you look at the network configuration tab (Right-click in
> enterprise manager on the server in question, choose properties) of the
> MSDE/SQL Server itself. Sometimes the named pipes and TCP/IP protocols do
not
> get added, and therefore nothing other than enterprise manager can connect
!
> James
>
> "Wei Lu [MSFT]" wrote:
>|||Hi Michael,
Thank you for the update.
From your description, I suggest you to make sure you could connect to the
SQL Server 2000 now.
Here is an article for you to troubleshoot the connectivity issue:
827422 How to troubleshoot connectivity issues in SQL Server 2000
http://support.microsoft.com/defaul...kb;EN-US;827422
Hope this will be helpful!
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others: https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
You may want to check if client machine in location 1 can access server1
properly. Also, please note that if it is not default instance, you shall
configure a static port on server and open it via firewall.
INF: TCP Ports Needed for Communication to SQL Server Through a Firewall
http://support.microsoft.com/kb/287932/EN-US/
You may want to ping from server2 to server1, and use the following command
to check if port is opened properly
telnet <server1> 1433
If you have any update, please let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
Not sure where to put this post, hope this is the right place.
Relatively new to SQL, not sure what info you need, so here we go: We
have a Windows 2000 Server running SQL 2000 ("Server1) in location 1.
This server is outside of the firewall.
We have a network setup in location 2 where we are doing development and
also have a server in that location (everything at location 2 is behind
the same firewall). There is a new server in location 2 running Windows
2003 Server and SQL Server 2005.
The problem is that Server2 cannot see Server1, and Server1 cannot see
Server2.
If, using Object Explorer, I click Connect > Database Engine, and put in
the details for Server2, I get an error that the database does not reply.
Also, if trying to connect from Server2 to Server1 (registering a db), I
get an access denied or does not exist error.
Any of the development machines at location 2 can see and connect to
Server1, but Server2 cannot. Also, any of the Dev machines can see and
connect to Server2 as well, but Server1 cannot.
1433 is not blocked in the firewall of location2, since all Dev machines
can access SQL on Server1.
I'm really at a loss, and any ideas would be most helpful.
Thanks,
MichaelIt sounds like port 1433 is blocked at the firewall. The firewall is between
Server1 and Location2, nothing is going through the firewall.
You need to open a port (the default is 1433) in the firewall to allow 2-way
tcp traffic, and then set a firewall rule that restricts traffic to just the
IP addresses of Server1 and Server2.
And if you want a bit more obscurity, you could even use a different port.
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Michael" <itsjustme@.nospam.nospam> wrote in message
news:OK6RHwkoGHA.516@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> Not sure where to put this post, hope this is the right place. Relatively
> new to SQL, not sure what info you need, so here we go: We have a Windows
> 2000 Server running SQL 2000 ("Server1) in location 1. This server is
> outside of the firewall.
> We have a network setup in location 2 where we are doing development and
> also have a server in that location (everything at location 2 is behind
> the same firewall). There is a new server in location 2 running Windows
> 2003 Server and SQL Server 2005.
> The problem is that Server2 cannot see Server1, and Server1 cannot see
> Server2.
> If, using Object Explorer, I click Connect > Database Engine, and put in
> the details for Server2, I get an error that the database does not reply.
> Also, if trying to connect from Server2 to Server1 (registering a db), I
> get an access denied or does not exist error.
> Any of the development machines at location 2 can see and connect to
> Server1, but Server2 cannot. Also, any of the Dev machines can see and
> connect to Server2 as well, but Server1 cannot.
> 1433 is not blocked in the firewall of location2, since all Dev machines
> can access SQL on Server1.
> I'm really at a loss, and any ideas would be most helpful.
> Thanks,
> Michael|||HI,
Thanks, but thats not it. Remember, we have numerous developer systems
sitting behind the same firewall, each has SQL 2005 developer version
installed, and they have perfect communication through the firewall with
Server1.
Arnie Rowland wrote:
> It sounds like port 1433 is blocked at the firewall. The firewall is betwe
en
> Server1 and Location2, nothing is going through the firewall.
> You need to open a port (the default is 1433) in the firewall to allow 2-w
ay
> tcp traffic, and then set a firewall rule that restricts traffic to just t
he
> IP addresses of Server1 and Server2.
> And if you want a bit more obscurity, you could even use a different port.
>|||Hi Michael,
Thank you for your posting!
Would you please try to add an alias of the Server1 in the server 2?
Please follow these steps:
1. Open Start Menu, All Programs, Microsoft SQL Server 2005, Configuration
Tools, SQL Server Configuration Manager.
2. In the left panel, click SQL Native Client Configuration, then
right-click Aliases, click New Alias.
3. In the Alias-New dialogbox, please type the Alias Name you want , and
type the Server1 name in the server section. Type 1433 in the Port No.
After that, please click OK.
4. Please try to connect to the Server 1 and let me know the result. Thank
you!
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Also, make sure you look at the network configuration tab (Right-click in
enterprise manager on the server in question, choose properties) of the
MSDE/SQL Server itself. Sometimes the named pipes and TCP/IP protocols do no
t
get added, and therefore nothing other than enterprise manager can connect!
James
"Wei Lu [MSFT]" wrote:
> Hi Michael,
> Thank you for your posting!
> Would you please try to add an alias of the Server1 in the server 2?
> Please follow these steps:
> 1. Open Start Menu, All Programs, Microsoft SQL Server 2005, Configuration
> Tools, SQL Server Configuration Manager.
> 2. In the left panel, click SQL Native Client Configuration, then
> right-click Aliases, click New Alias.
> 3. In the Alias-New dialogbox, please type the Alias Name you want , and
> type the Server1 name in the server section. Type 1433 in the Port No.
> After that, please click OK.
> 4. Please try to connect to the Server 1 and let me know the result. Thank
> you!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Wei Lu and James,
I followed your directions and created the Alias (and ensured the
protocols are present). I then logged into Server 2 and did exactly
these steps:
Start > All Programs > Mircosoft SQL Server 2005 > SQL Server Management
Studio.
Once it opened, I logged into the local instance of SQL Server. Then, I
did these steps:
In Object Explorer (along left side of screen, with only my local
instance showing), I clicked Connect > Database Engine. I then entered
the following information:
Server Name = 64.203.xxx.xxx
Authentication = SQL Server Authentication
username = xxx
password = xxx
On the Connection Properties tab, I tried it with default, and also
TCP/IP. Each time, I get the message below:
TITLE: Connect to Server
--
Cannot connect to 64.203.xxx.xxx
ADDITIONAL INFORMATION:
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: TCP Provider, error: 0 - A connection attempt
failed because the connected party did not properly respond after a
period of time, or established connection failed because connected host
has failed to respond.) (Microsoft SQL Server, Error: 10060)
Am I doing something wrong or missing something?
Thanks,
Michael
ps. Sorry for my delayed response, was out with a severe cold.
James Vickers wrote:[vbcol=seagreen]
> Also, make sure you look at the network configuration tab (Right-click in
> enterprise manager on the server in question, choose properties) of the
> MSDE/SQL Server itself. Sometimes the named pipes and TCP/IP protocols do
not
> get added, and therefore nothing other than enterprise manager can connect
!
> James
>
> "Wei Lu [MSFT]" wrote:
>|||Hi Michael,
Thank you for the update.
From your description, I suggest you to make sure you could connect to the
SQL Server 2000 now.
Here is an article for you to troubleshoot the connectivity issue:
827422 How to troubleshoot connectivity issues in SQL Server 2000
http://support.microsoft.com/defaul...kb;EN-US;827422
Hope this will be helpful!
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others: https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
You may want to check if client machine in location 1 can access server1
properly. Also, please note that if it is not default instance, you shall
configure a static port on server and open it via firewall.
INF: TCP Ports Needed for Communication to SQL Server Through a Firewall
http://support.microsoft.com/kb/287932/EN-US/
You may want to ping from server2 to server1, and use the following command
to check if port is opened properly
telnet <server1> 1433
If you have any update, please let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
New Server
I have a win2000 server that is in need of replacement, we
have built a new server and now need to configure sql 2000.
From backups I retored all the databases including msdb.
How do I restore the Master database, as this has lots of
extra system SP that need on the new server.
I tried putting the Instance in single user mode and doing
a restore, which said it worked OK but when trying to
restart SQL it fail to start. Any ideas what I am doing
wrongWhat's the error in the SQL Server error log?
See if this helps too: http://vyaskn.tripod.com/moving_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Allan" <asql@.penstonall.co.uk> wrote in message
news:714601c52622$882fe170$a601280a@.phx.gbl...
I have a win2000 server that is in need of replacement, we
have built a new server and now need to configure sql 2000.
From backups I retored all the databases including msdb.
How do I restore the Master database, as this has lots of
extra system SP that need on the new server.
I tried putting the Instance in single user mode and doing
a restore, which said it worked OK but when trying to
restart SQL it fail to start. Any ideas what I am doing
wrong|||What was the reason to backup/restore the system DB instead of generating
the script for the SPs (extra system SP)
and running the script on the new server ?
If they are system SPs they should be common across all master DBs, right ?
Gopi
"Allan" <asql@.penstonall.co.uk> wrote in message
news:714601c52622$882fe170$a601280a@.phx.gbl...
>I have a win2000 server that is in need of replacement, we
> have built a new server and now need to configure sql 2000.
> From backups I retored all the databases including msdb.
> How do I restore the Master database, as this has lots of
> extra system SP that need on the new server.
> I tried putting the Instance in single user mode and doing
> a restore, which said it worked OK but when trying to
> restart SQL it fail to start. Any ideas what I am doing
> wrong
>|||Allan
From my experience i have learned that first of all script out all logins
(there are two SP provided by MS) and then jobs as well
There is no need to restore master,msdb databases , instead restore all user
databases and run the script generated by these SP's.
In the same way you can move all DTS packages (to save the as files). You
will not lost anything.
"Allan" <asql@.penstonall.co.uk> wrote in message
news:714601c52622$882fe170$a601280a@.phx.gbl...
> I have a win2000 server that is in need of replacement, we
> have built a new server and now need to configure sql 2000.
> From backups I retored all the databases including msdb.
> How do I restore the Master database, as this has lots of
> extra system SP that need on the new server.
> I tried putting the Instance in single user mode and doing
> a restore, which said it worked OK but when trying to
> restart SQL it fail to start. Any ideas what I am doing
> wrong
>
have built a new server and now need to configure sql 2000.
From backups I retored all the databases including msdb.
How do I restore the Master database, as this has lots of
extra system SP that need on the new server.
I tried putting the Instance in single user mode and doing
a restore, which said it worked OK but when trying to
restart SQL it fail to start. Any ideas what I am doing
wrongWhat's the error in the SQL Server error log?
See if this helps too: http://vyaskn.tripod.com/moving_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Allan" <asql@.penstonall.co.uk> wrote in message
news:714601c52622$882fe170$a601280a@.phx.gbl...
I have a win2000 server that is in need of replacement, we
have built a new server and now need to configure sql 2000.
From backups I retored all the databases including msdb.
How do I restore the Master database, as this has lots of
extra system SP that need on the new server.
I tried putting the Instance in single user mode and doing
a restore, which said it worked OK but when trying to
restart SQL it fail to start. Any ideas what I am doing
wrong|||What was the reason to backup/restore the system DB instead of generating
the script for the SPs (extra system SP)
and running the script on the new server ?
If they are system SPs they should be common across all master DBs, right ?
Gopi
"Allan" <asql@.penstonall.co.uk> wrote in message
news:714601c52622$882fe170$a601280a@.phx.gbl...
>I have a win2000 server that is in need of replacement, we
> have built a new server and now need to configure sql 2000.
> From backups I retored all the databases including msdb.
> How do I restore the Master database, as this has lots of
> extra system SP that need on the new server.
> I tried putting the Instance in single user mode and doing
> a restore, which said it worked OK but when trying to
> restart SQL it fail to start. Any ideas what I am doing
> wrong
>|||Allan
From my experience i have learned that first of all script out all logins
(there are two SP provided by MS) and then jobs as well
There is no need to restore master,msdb databases , instead restore all user
databases and run the script generated by these SP's.
In the same way you can move all DTS packages (to save the as files). You
will not lost anything.
"Allan" <asql@.penstonall.co.uk> wrote in message
news:714601c52622$882fe170$a601280a@.phx.gbl...
> I have a win2000 server that is in need of replacement, we
> have built a new server and now need to configure sql 2000.
> From backups I retored all the databases including msdb.
> How do I restore the Master database, as this has lots of
> extra system SP that need on the new server.
> I tried putting the Instance in single user mode and doing
> a restore, which said it worked OK but when trying to
> restart SQL it fail to start. Any ideas what I am doing
> wrong
>
New Server
I have a win2000 server that is in need of replacement, we
have built a new server and now need to configure sql 2000.
From backups I retored all the databases including msdb.
How do I restore the Master database, as this has lots of
extra system SP that need on the new server.
I tried putting the Instance in single user mode and doing
a restore, which said it worked OK but when trying to
restart SQL it fail to start. Any ideas what I am doing
wrong
What's the error in the SQL Server error log?
See if this helps too: http://vyaskn.tripod.com/moving_sql_server.htm
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Allan" <asql@.penstonall.co.uk> wrote in message
news:714601c52622$882fe170$a601280a@.phx.gbl...
I have a win2000 server that is in need of replacement, we
have built a new server and now need to configure sql 2000.
From backups I retored all the databases including msdb.
How do I restore the Master database, as this has lots of
extra system SP that need on the new server.
I tried putting the Instance in single user mode and doing
a restore, which said it worked OK but when trying to
restart SQL it fail to start. Any ideas what I am doing
wrong
|||What was the reason to backup/restore the system DB instead of generating
the script for the SPs (extra system SP)
and running the script on the new server ?
If they are system SPs they should be common across all master DBs, right ?
Gopi
"Allan" <asql@.penstonall.co.uk> wrote in message
news:714601c52622$882fe170$a601280a@.phx.gbl...
>I have a win2000 server that is in need of replacement, we
> have built a new server and now need to configure sql 2000.
> From backups I retored all the databases including msdb.
> How do I restore the Master database, as this has lots of
> extra system SP that need on the new server.
> I tried putting the Instance in single user mode and doing
> a restore, which said it worked OK but when trying to
> restart SQL it fail to start. Any ideas what I am doing
> wrong
>
|||Allan
From my experience i have learned that first of all script out all logins
(there are two SP provided by MS) and then jobs as well
There is no need to restore master,msdb databases , instead restore all user
databases and run the script generated by these SP's.
In the same way you can move all DTS packages (to save the as files). You
will not lost anything.
"Allan" <asql@.penstonall.co.uk> wrote in message
news:714601c52622$882fe170$a601280a@.phx.gbl...
> I have a win2000 server that is in need of replacement, we
> have built a new server and now need to configure sql 2000.
> From backups I retored all the databases including msdb.
> How do I restore the Master database, as this has lots of
> extra system SP that need on the new server.
> I tried putting the Instance in single user mode and doing
> a restore, which said it worked OK but when trying to
> restart SQL it fail to start. Any ideas what I am doing
> wrong
>
have built a new server and now need to configure sql 2000.
From backups I retored all the databases including msdb.
How do I restore the Master database, as this has lots of
extra system SP that need on the new server.
I tried putting the Instance in single user mode and doing
a restore, which said it worked OK but when trying to
restart SQL it fail to start. Any ideas what I am doing
wrong
What's the error in the SQL Server error log?
See if this helps too: http://vyaskn.tripod.com/moving_sql_server.htm
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Allan" <asql@.penstonall.co.uk> wrote in message
news:714601c52622$882fe170$a601280a@.phx.gbl...
I have a win2000 server that is in need of replacement, we
have built a new server and now need to configure sql 2000.
From backups I retored all the databases including msdb.
How do I restore the Master database, as this has lots of
extra system SP that need on the new server.
I tried putting the Instance in single user mode and doing
a restore, which said it worked OK but when trying to
restart SQL it fail to start. Any ideas what I am doing
wrong
|||What was the reason to backup/restore the system DB instead of generating
the script for the SPs (extra system SP)
and running the script on the new server ?
If they are system SPs they should be common across all master DBs, right ?
Gopi
"Allan" <asql@.penstonall.co.uk> wrote in message
news:714601c52622$882fe170$a601280a@.phx.gbl...
>I have a win2000 server that is in need of replacement, we
> have built a new server and now need to configure sql 2000.
> From backups I retored all the databases including msdb.
> How do I restore the Master database, as this has lots of
> extra system SP that need on the new server.
> I tried putting the Instance in single user mode and doing
> a restore, which said it worked OK but when trying to
> restart SQL it fail to start. Any ideas what I am doing
> wrong
>
|||Allan
From my experience i have learned that first of all script out all logins
(there are two SP provided by MS) and then jobs as well
There is no need to restore master,msdb databases , instead restore all user
databases and run the script generated by these SP's.
In the same way you can move all DTS packages (to save the as files). You
will not lost anything.
"Allan" <asql@.penstonall.co.uk> wrote in message
news:714601c52622$882fe170$a601280a@.phx.gbl...
> I have a win2000 server that is in need of replacement, we
> have built a new server and now need to configure sql 2000.
> From backups I retored all the databases including msdb.
> How do I restore the Master database, as this has lots of
> extra system SP that need on the new server.
> I tried putting the Instance in single user mode and doing
> a restore, which said it worked OK but when trying to
> restart SQL it fail to start. Any ideas what I am doing
> wrong
>
Subscribe to:
Posts (Atom)