How to remove and move the Distribution database?

We have 3 servers A, B and C.
There were databases on Server B being replicated in C.
The Distribution database is located in server A.
I have been told that the server A is going away.
Question: How do I move the Distribution database from Server A into one of
the other servers?
Do I need to redo the Replication Setup on Servers B and C?
Thanks,
Suresh Kumar
Disclaimer: I tried posting this message in the Replication group but there
were no replies and hence I trying my luck here.Kumar,
I'm trying to envision here because I don't have experience with that
particular case.
I don't think you need to redo the replication. You only need to make sure
the new distribution server is IDENTICAL to the old one. So how do you
mirror the distribution server?
1. Pause the publisher (B). Make sure all transactions are commited. Stop
replication by stopping all agents (log reader, distribution agents,
snapshot agents, etc.) so that there is no open transactions.
2. Backup master, msdb, distribution db on the distribution server. Script
out logins;
3. Bring down the distriubition server;
4. Turn on the new server. Make sure it is set up exactly the same as the
old distribution server. Rename it to A. check IP address, DNS entries, etc.
5. Install SQL server with same version and service packs. Rename the SQL
server name to what is was called on A.
6. Restore master, msdb and distribution databases; Don't forget to run the
login script to recreate them on the new server.
7. At this point, the new server looks the same as the old one. All
replication related tables are there...Neither the publisher nor subscriber
will notice that the server is no longer what is was;
8. Restart replication agents. You should rock...
Richard
"Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
news:eaim$%23EWDHA.1204@.TK2MSFTNGP12.phx.gbl...
> We have 3 servers A, B and C.
> There were databases on Server B being replicated in C.
> The Distribution database is located in server A.
> I have been told that the server A is going away.
> Question: How do I move the Distribution database from Server A into one
of
> the other servers?
> Do I need to redo the Replication Setup on Servers B and C?
> Thanks,
> Suresh Kumar
> Disclaimer: I tried posting this message in the Replication group but
there
> were no replies and hence I trying my luck here.
>|||Suresh,
Please reply to the group.
It seems your plan becomes more difficult. If you move distribution db to
either B or C, then both server need to reconfigure 'cause they now have to
repoint to the new distribution server. The system tables involved in
replications are not easy to modify to reflect the change. Although it might
be doable, it takes pain to get everything seamless. Considering the effort
and difficulty, you'd rather script out the replication, sever it, and set
up from scratch. Once you get publisher, distributor, and subscriber in
place, you can change the server name in the script and apply on the
publisher, or even do it manually.
Richard
"Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
news:eaim$%23EWDHA.1204@.TK2MSFTNGP12.phx.gbl...
> We have 3 servers A, B and C.
> There were databases on Server B being replicated in C.
> The Distribution database is located in server A.
> I have been told that the server A is going away.
> Question: How do I move the Distribution database from Server A into one
of
> the other servers?
> Do I need to redo the Replication Setup on Servers B and C?
> Thanks,
> Suresh Kumar
> Disclaimer: I tried posting this message in the Replication group but
there
> were no replies and hence I trying my luck here.
>|||Richard,
Thanks for your input.
Lucky for me the Replication database has not gone to production yet.
Hence, as per your suggestion, I was able to tear it down and rebuild it
from scratch.
My 2 questions now are:
- There is still an old Distribution database in Server A. How do I delete
it. The Delete is dimmed out under Replication/Distributor properties even
though there are no subscriptions to that database. Can I just delete the
old Ditribution database on Server A (by deleting thru DB properties)
- The Publication and distribution are now on Server B. I am not able to do
pull subscriptions from Server C unless I allow 'Anonymous' subscriptions
for the Publications in B. What are the implications of allowing Anonymous
subsriptions? Does this make us more vulnerable from the securities point of
view?
Thanks,
Suresh Kumar
"Richard Ding" <dingr@.cleanharbors.com> wrote in message
news:%237nfTHHWDHA.652@.tk2msftngp13.phx.gbl...
> Suresh,
> Please reply to the group.
> It seems your plan becomes more difficult. If you move distribution db to
> either B or C, then both server need to reconfigure 'cause they now have
to
> repoint to the new distribution server. The system tables involved in
> replications are not easy to modify to reflect the change. Although it
might
> be doable, it takes pain to get everything seamless. Considering the
effort
> and difficulty, you'd rather script out the replication, sever it, and set
> up from scratch. Once you get publisher, distributor, and subscriber in
> place, you can change the server name in the script and apply on the
> publisher, or even do it manually.
>
> Richard
> "Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
> news:eaim$%23EWDHA.1204@.TK2MSFTNGP12.phx.gbl...
> > We have 3 servers A, B and C.
> > There were databases on Server B being replicated in C.
> > The Distribution database is located in server A.
> >
> > I have been told that the server A is going away.
> >
> > Question: How do I move the Distribution database from Server A into one
> of
> > the other servers?
> > Do I need to redo the Replication Setup on Servers B and C?
> >
> > Thanks,
> > Suresh Kumar
> >
> > Disclaimer: I tried posting this message in the Replication group but
> there
> > were no replies and hence I trying my luck here.
> >
> >
>|||Hari,
Thanks for your input.
Suresh
<hkvats_1999@.yahoo.com> wrote in message
news:68c8c86d.0308040059.7e3e8d5e@.posting.google.com...
> Hi Suresh,
> You need to follow the complete process for Disabling Distributor.
> --Deletion of Distribution Database
> Execute sp_dropdistributiondb to delete a distribution database.
> --Disable distributors
> Execute sp_dropdistributor with @.no_checks=1
> 'Anonymous' subscriptions
> ==================================================================> Anonymous subscriptions to allow Subscribers unknown to the Publisher
> to subscribe to this publication and not able to change this after the
> publication is created.
> I suggest you go thru with detailed description on this in BOOKS
> ONLINE Topic titled =============="Anonymous Subscriptions"
>
> regards
> Hari Sharma, India

How to register remote server? Authentication method is grayed out!

In SQL Server management Studio. I was trying to add remote server, so it would show up in registered servers. The authentication method is grayed out!

The only option to connect to the server is by clicking connect button. It remebers the old settings. It also shows me all server databases, even I have selected my database in options...

Can you please describe exact steps that you are following. Also please make sure that the server type is 'Database Engine'.|||

I think the reason is that I have SQl Server Express installed, but SQL Server 2005 interface. Do you know how to upgrade it to full version? I have DVD, but got some errors while running setup again "Can not read from device...". Thanks

|||

You may have a problem with your DVD disk or device. You may want to check with product support to resolve this issue.

Note this should just work, you should not need to install another version.

|||SQL Server Management Studio supports connecting to SQL Express. Are you trying to connect from the same machine or from a remote box? DO you experience this problem for this instance only?|||Same machine, I have realased I did not upgrade the original 2000 version! It asks me about service packs, however I have XP SP 2 already installed...|||

Could you send a screen shot of the authentication dialog to my email address when it is in the state that you descibe?

Also, please provide step by step explanation how it gets into this state (please also send this as a reply to this posting). Example:

1. Start SQL Server Mananagement Studio

2. Right click Database Engine in the Registered Servers dialog

etc. -- you get the picture...

Note that you have to remove the 'online' part from my email address.

How to register remote server? Authentication method is grayed out!

In SQL Server management Studio. I was trying to add remote server, so it would show up in registered servers. The authentication method is grayed out!

The only option to connect to the server is by clicking connect button. It remebers the old settings. It also shows me all server databases, even I have selected my database in options...

Can you please describe exact steps that you are following. Also please make sure that the server type is 'Database Engine'.|||

I think the reason is that I have SQl Server Express installed, but SQL Server 2005 interface. Do you know how to upgrade it to full version? I have DVD, but got some errors while running setup again "Can not read from device...". Thanks

|||

You may have a problem with your DVD disk or device. You may want to check with product support to resolve this issue.

Note this should just work, you should not need to install another version.

|||SQL Server Management Studio supports connecting to SQL Express. Are you trying to connect from the same machine or from a remote box? DO you experience this problem for this instance only?|||Same machine, I have realased I did not upgrade the original 2000 version! It asks me about service packs, however I have XP SP 2 already installed...|||

Could you send a screen shot of the authentication dialog to my email address when it is in the state that you descibe?

Also, please provide step by step explanation how it gets into this state (please also send this as a reply to this posting). Example:

1. Start SQL Server Mananagement Studio

2. Right click Database Engine in the Registered Servers dialog

etc. -- you get the picture...

Note that you have to remove the 'online' part from my email address.

How to register remote server? Authentication method is grayed out!

In SQL Server management Studio. I was trying to add remote server, so it would show up in registered servers. The authentication method is grayed out!

The only option to connect to the server is by clicking connect button. It remebers the old settings. It also shows me all server databases, even I have selected my database in options...

Can you please describe exact steps that you are following. Also please make sure that the server type is 'Database Engine'.|||

I think the reason is that I have SQl Server Express installed, but SQL Server 2005 interface. Do you know how to upgrade it to full version? I have DVD, but got some errors while running setup again "Can not read from device...". Thanks

|||

You may have a problem with your DVD disk or device. You may want to check with product support to resolve this issue.

Note this should just work, you should not need to install another version.

|||SQL Server Management Studio supports connecting to SQL Express. Are you trying to connect from the same machine or from a remote box? DO you experience this problem for this instance only?|||Same machine, I have realased I did not upgrade the original 2000 version! It asks me about service packs, however I have XP SP 2 already installed...|||

Could you send a screen shot of the authentication dialog to my email address when it is in the state that you descibe?

Also, please provide step by step explanation how it gets into this state (please also send this as a reply to this posting). Example:

1. Start SQL Server Mananagement Studio

2. Right click Database Engine in the Registered Servers dialog

etc. -- you get the picture...

Note that you have to remove the 'online' part from my email address.

How to reference different databases on different servers in one query

Hi,

I'm a novice to SQL Server, though I have some experience in Access.

My problem is this:

I have a table on my local database that I want to insert into a remove database on a different server

What are the steps neccessary?

My local server name is 'HOME\SQLEXPRESS' Local database: 'MyDBTest', table name: 'dbo.Courses' (the fields are: ID, CourseName, TeacherID)|||Take a look in Books On Line at a topic called Linked Servers. Although I am not 100% sure that they are supported in Express|||Using linked servers (first create it using te linked server procedures for that) you can issue this query:

SELECT *
FROM [Linkedservername].[ku.univ].dbo.Courses

HTH, Jens Suessmeyer,

http://www.sqlserver2005.de
|||

I think I'm not using the right syntax

I've created the linked server by:

sp_addlinkedserver @.server='REMOTESRV', @.provider='SQLOLEDB',@.srvproduct='',@.datasrc='129.113.271.58\ku.univ'

The log was: Command(s) completed successfully.

I then tried to issue an SQL Statement as you have adviced:

SELECT *

FROM [REMOTESRV].[ku.univ].dbo.Courses

but the log was:

OLE DB provider "SQLNCLI" for linked server "REMOTESRV" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "REMOTESRV" returned message "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.".

Msg 65535, Level 16, State 1, Line 0

SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

I know that the server allows remote connections: when I connect to it using Object browser I can issue INSERT queries - no problem

Maybe I have a problem with syntax

Thanx a lot

|||Check out the 'Linked Servers' topic in the Books Online|||

Hi,

this here:

,@.datasrc='129.113.271.58\ku.univ'

Should be the instance to connect to, so normally this has to be written in the syntax of:

Servername\Instancename,Portnumber (where as the servername can be exchanged by the ip adress and the portnumber (can be left out if you didn′t choose another one that the default one [or you have SQl Server 2005 at the other end using dynamic port and installed the SQL Server Browser]) and the instancename (which can be left out if you choose the default instance on the server.

So in your case for the default port and the default instance, this should be just:

,@.datasrc='129.113.271.58'

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi, Jens

Thanx a lot for helping, I've changed the @.datasrc as you wrote but got a different error this time:

OLE DB provider "SQLNCLI" for linked server "remotesrv2" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "remotesrv2" returned message "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.".

Msg 53, Level 16, State 1, Line 0

Named Pipes Provider: Could not open a connection to SQL Server [53].

I'd really appreciate your help on this one.

Thanx in advance

|||

Other Ideas, anybody?

I'm pretty stuck here, I've read the documentation, did as it said but still grt an error.

Thanx!

|||

http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx

HTH

How to reference different databases on different servers in one query

Hi,

I'm a novice to SQL Server, though I have some experience in Access.

My problem is this:

I have a table on my local database that I want to insert into a remove database on a different server

What are the steps neccessary?

My local server name is 'HOME\SQLEXPRESS' Local database: 'MyDBTest', table name: 'dbo.Courses' (the fields are: ID, CourseName, TeacherID)

The remote SQL Server IP is: 129.113.271.58 (I've changed the real IP) The name of the database on this remote server is: 'ku.univ', and the remote table name I would like to update is 'dbo.Courses' (the table has the same fields as the local one).

I use SQL Server Express Edition

Please help!!

Thank you in advance

Michael

Any ideas?

I'm really stuck here - I am able to connect to the remote server and browse the database, but how do I make an INSERT query to the remote table from my local table?

In Access you could just put any table (local and linked) in one query - there has to be a way to do it in SQL Server

Thanx

|||

There are a couple of options for you. You could use BCP to export the table on the local server to a text file, then use the same utility to insert into the remote server. You can also use a linked server.

To setup a linked server run these commands from sqlcmd, replace the appropriate parameters:

exec sp_addlinkedserver 'RemoteServerName', N'SQL Server'
exec sp_addlinkedsrvlogin 'RemoteServerName',@.locallogin=NULL /*any login*/, @.rmtuser = 'remoteusername', @.rmtpassword='rmtpassword'

Then you will be able to run a query such as this when connected to your local server:

insert [remoteserver].[ku.univ].[dbo].[Tableneme] (columns...)

select columns... from localtable

I'm assuming here that the remote server is also SQL Server. If not you'll need to modify the sp_addlinkedserver call to specify the proper connection string.

How to record if a Linked Server is offline?

I created the below script to track my linked servers. I set it up that it
would record a record for each server along with all its corresponding
databases. Unfortunately, I have not been able to successfully track a
server which is offline.
Currently, I am using cmdshell to ping linked servers and return any that
have a 'timed out' string. I feel that there must be a better way to see if
a linked server is offline via TSQL.
Thanks in Advance.
Drake
DECLARE @.DatabaseName VARCHAR(50)
,@.DatabaseNameString VARCHAR(200)
,@.DBString VARCHAR(200)
,@.Server VARCHAR(100)
,@.SQLcmd NVARCHAR(500)
,@.Error int
,@.Errormsg varchar(100)
--BEGIN TRAN
--SET NOCOUNT ON
DECLARE SERVERCRSOR CURSOR FOR
select srvname from sysservers order by srvname
OPEN SERVERCRSOR
FETCH NEXT FROM SERVERCRSOR INTO @.Server
WHILE @.@.FETCH_STATUS = 0
BEGIN
BEGIN
set @.SQLcmd = 'IF EXISTS(select name from [' + @.Server +
'].master.dbo.sysdatabases)
DECLARE STATUSCRSOR CURSOR FOR SELECT Name FROM [' + @.Server +
'].master.dbo.sysdatabases'
EXEC sp_executesql @.SQLcmd ,N'@.Server varchar(255)' ,@.Server
select @.Error = @.@.Error
if @.Error <> 0
begin
set @.Errormsg = 'Linked SQL Server does not exist or access denied.'
Insert into SQLMONITOR.dbo.tblSQLServerStatus (szLinkedServer_nm,
szDatabases_nm, dtStatusdate_dt)
Select @.Server, '(' + @.Errormsg + ')', getdate() DateStatus
raiserror ('test',1,1)
end
OPEN STATUSCRSOR
FETCH NEXT FROM STATUSCRSOR INTO @.DatabaseName
Select @.DBString = @.DatabaseName
WHILE @.@.FETCH_STATUS = 0
BEGIN
Select @.DBString = @.DBString + ',' + @.DatabaseName
FETCH NEXT FROM STATUSCRSOR INTO @.DatabaseName
Select @.DatabaseNameString = @.DBString
END
Insert into SQLMONITOR.dbo.tblSQLServerStatus (szLinkedServer_nm,
szDatabases_nm, dtStatusdate_dt)
Select @.Server, '(' + @.DatabaseNameString + ')', getdate() DateStatus
END
FETCH NEXT FROM SERVERCRSOR INTO @.Server
CLOSE STATUSCRSOR
DEALLOCATE STATUSCRSOR
END
CLOSE SERVERCRSOR
DEALLOCATE SERVERCRSOR
--COMMIT
GOYou could try to do a select count(*) from <<linked
server>>.pubs.dbo.authors and see if anything comes back.|||I need to clarify my above response - I have a job that contains 3
steps. Step #1 contains the SELECT statement against the linked
server. Step #1, on success, goes to step #2 which handles some
processing on the remote server. Step #1, on failture, goes to step
#3 which informs me that the linked server is not available.
Make sure that step #2 skips step #3.

How to record if a Linked Server is offline?

I created the below script to track my linked servers. I set it up that it
would record a record for each server along with all its corresponding
databases. Unfortunately, I have not been able to successfully track a
server which is offline.
Currently, I am using cmdshell to ping linked servers and return any that
have a 'timed out' string. I feel that there must be a better way to see if
a linked server is offline via TSQL.
Thanks in Advance.
Drake

DECLARE @.DatabaseName VARCHAR(50)
,@.DatabaseNameString VARCHAR(200)
,@.DBString VARCHAR(200)
,@.Server VARCHAR(100)
,@.SQLcmd NVARCHAR(500)
,@.Error int
,@.Errormsg varchar(100)
--BEGIN TRAN
--SET NOCOUNT ON
DECLARE SERVERCRSOR CURSOR FOR
select srvname from sysservers order by srvname
OPEN SERVERCRSOR
FETCH NEXT FROM SERVERCRSOR INTO @.Server
WHILE @.@.FETCH_STATUS = 0
BEGIN
BEGIN
set @.SQLcmd = 'IF EXISTS(select name from [' + @.Server +
'].master.dbo.sysdatabases)
DECLARE STATUSCRSOR CURSOR FOR SELECT Name FROM [' + @.Server +
'].master.dbo.sysdatabases'
EXEC sp_executesql @.SQLcmd ,N'@.Server varchar(255)' ,@.Server
select @.Error = @.@.Error
if @.Error <> 0
begin
set @.Errormsg = 'Linked SQL Server does not exist or access denied.'
Insert into SQLMONITOR.dbo.tblSQLServerStatus (szLinkedServer_nm,
szDatabases_nm, dtStatusdate_dt)
Select @.Server, '(' + @.Errormsg + ')', getdate() DateStatus
raiserror ('test',1,1)
end
OPEN STATUSCRSOR
FETCH NEXT FROM STATUSCRSOR INTO @.DatabaseName
Select @.DBString = @.DatabaseName
WHILE @.@.FETCH_STATUS = 0
BEGIN
Select @.DBString = @.DBString + ',' + @.DatabaseName
FETCH NEXT FROM STATUSCRSOR INTO @.DatabaseName
Select @.DatabaseNameString = @.DBString
END
Insert into SQLMONITOR.dbo.tblSQLServerStatus (szLinkedServer_nm,
szDatabases_nm, dtStatusdate_dt)
Select @.Server, '(' + @.DatabaseNameString + ')', getdate() DateStatus
END
FETCH NEXT FROM SERVERCRSOR INTO @.Server
CLOSE STATUSCRSOR
DEALLOCATE STATUSCRSOR
END
CLOSE SERVERCRSOR
DEALLOCATE SERVERCRSOR
--COMMIT
GO

You could try to do a select count(*) from <<linked
server>>.pubs.dbo.authors and see if anything comes back.
|||I need to clarify my above response - I have a job that contains 3
steps. Step #1 contains the SELECT statement against the linked
server. Step #1, on success, goes to step #2 which handles some
processing on the remote server. Step #1, on failture, goes to step
#3 which informs me that the linked server is not available.
Make sure that step #2 skips step #3.

How To Query A Linked Server

Hey all. I'm having a slight problem and was hoping I could use
linked servers to solve it. Basically I've developed a web app
locally using SQL 2005 Express and need to import the data to a web
hosting facility SQL 2005 server. I can see the production server in
management studio and have set up a linked server. However I'm not
sure how to query the database and then ultimately select from my
local db and insert it into the new db tables.
First problem is the linked server address. It's the following:
sqlXXXX.mssqlservers.com
When I query the linked server I get this.
The object name 'sqlXXXX.mssqlservers.com.MyDB.dbo.MyTable' contains
more than the maximum number of prefixes. The maximum is 3.
Is the linked server route the best way for me to import this data? I
need to get the data in ASAP for this client.
Thanks.Hi,
Use: '[sqlXXXX.mssqlservers.com].MyDB.dbo.MyTable'
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
<rh1200la@.gmail.com> wrote in message
news:1190655841.782199.125430@.19g2000hsx.googlegroups.com...
> Hey all. I'm having a slight problem and was hoping I could use
> linked servers to solve it. Basically I've developed a web app
> locally using SQL 2005 Express and need to import the data to a web
> hosting facility SQL 2005 server. I can see the production server in
> management studio and have set up a linked server. However I'm not
> sure how to query the database and then ultimately select from my
> local db and insert it into the new db tables.
> First problem is the linked server address. It's the following:
> sqlXXXX.mssqlservers.com
> When I query the linked server I get this.
> The object name 'sqlXXXX.mssqlservers.com.MyDB.dbo.MyTable' contains
> more than the maximum number of prefixes. The maximum is 3.
> Is the linked server route the best way for me to import this data? I
> need to get the data in ASAP for this client.
> Thanks.
>