Moving subscriber to a new server

Hi,
environment:
SQL Server 2000 EE, SP4, latest security update on win 2003. Using merge PULL
replication.one Publisher, 2 subscribers.
We have a need to move a DB with a pull subscription to a new
server. The new server will be setup on the same network with the same name as
the old one. (publisher on a different network)
after moving the database (detach,attach ,subscriptiom need to be deleted to
do this) and if the publisher has new data inserted during the move how can I
get the subscriber on the new server to synch with publisher and get the data
changes.
CTS DBA
hey can anybody please help?
I got till this Monday only to come up with the solution
CTS DBA
"CTS_DBA" wrote:

> Hi,
> environment:
> SQL Server 2000 EE, SP4, latest security update on win 2003. Using merge PULL
> replication.one Publisher, 2 subscribers.
> We have a need to move a DB with a pull subscription to a new
> server. The new server will be setup on the same network with the same name as
> the old one. (publisher on a different network)
> after moving the database (detach,attach ,subscriptiom need to be deleted to
> do this) and if the publisher has new data inserted during the move how can I
> get the subscriber on the new server to synch with publisher and get the data
> changes.
> --
> CTS DBA
|||Have a look at @.allow_subscription_copy which is a property of the
publication. It can be set to allow copying of subscriber databases. Can't
test your particular requirements at the moment, but please give it a go.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||thanks paul,
copying subscription works for dbs less than 2GB, mine is 12Gb.
I'm still testing this but looks like it's working,
synch the subscriber with the publication,
run the snapshot agant
make a copy of the db and move it to the new server
create a new subscription.
the new subscription will captures changes since the last back up
any body has any commnets?
"Paul Ibison" wrote:

> Have a look at @.allow_subscription_copy which is a property of the
> publication. It can be set to allow copying of subscriber databases. Can't
> test your particular requirements at the moment, but please give it a go.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>

Moving SQL Cluster to a new subnet

Hello All,
I need to move a SQL Server Cluster from one subnet to another subnet so I
need to update 5 cluster IP addresses (node a, node b, cluster, SQL Server
and MS DTC). Any help on how to do this would be appreciated.
I found article 230356 explaining how to change the IP address in the nodes
but it is still confusing because it says that "do not work ... if you change
the IP address from one subnet to abother subnet". Then Allan Hirts' book
'SQL High Availability' shows the same procedure (page 190) but says
'Warning: Do not do this unless your servers need to change subnets". Any
idea what the problem is here?
Looks like article 244980 describes how to change the IP address for SQL
Server.
I still need to know how to change the IP address for the cluster and for
MSDTC.
Any help on this?
Thanks,
Benjamin Nevarez
SQL Server Database Administrator
"=?Utf-8?B?QmVuamFtaW4gTmV2YXJleg==?="
<BenjaminNevarez@.discussions.microsoft.com> wrote in
news:782BC7F5-3D71-4543-860B-92F396F9115F@.microsoft.com:

> Looks like article 244980 describes how to change the IP address for
> SQL Server.
Yes, for a virtual server. Exactly what you need, as far as I can see. It
seems to be pretty detailed as well.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
|||The article can be very confusing. The key to remember is that your cluster
always needs a clear communications path for its private communications.
Everything else is optional. Of course, you may be offline for a while
during the transition. Here are a few absolutely key elements
Make sure you have the 'Public" interface enabled for private
communications. Then you can change the private network without losing the
cluster. Once the private side is stable, you can change the public side.
Don't forget the impact of changes on your SAN. It may not like having a
host change IP addresses.
Make sure to read this KB article as well.
How to change the network IP addresses of SQL Server virtual servers
http://support.microsoft.com/kb/244980/
Geoff N. Hiten
Microsoft SQL Server MVP.
"Benjamin Nevarez" <BenjaminNevarez@.discussions.microsoft.com> wrote in
message news:782BC7F5-3D71-4543-860B-92F396F9115F@.microsoft.com...
> Hello All,
> I need to move a SQL Server Cluster from one subnet to another subnet so I
> need to update 5 cluster IP addresses (node a, node b, cluster, SQL Server
> and MS DTC). Any help on how to do this would be appreciated.
> I found article 230356 explaining how to change the IP address in the
> nodes
> but it is still confusing because it says that "do not work ... if you
> change
> the IP address from one subnet to abother subnet". Then Allan Hirts' book
> 'SQL High Availability' shows the same procedure (page 190) but says
> 'Warning: Do not do this unless your servers need to change subnets". Any
> idea what the problem is here?
> Looks like article 244980 describes how to change the IP address for SQL
> Server.
> I still need to know how to change the IP address for the cluster and for
> MSDTC.
> Any help on this?
> Thanks,
> Benjamin Nevarez
> SQL Server Database Administrator
>

Moving sql 2000 to sql 2005- update query run slower

Earlier I posted this query in SQL Server Database Engine forum. I am reposting it here.
We have upgraded our database server from SQL 2000 to SQL 2005. To migrate the DB I attached the mdf file in SQL 2005. After migration website loads quicker than earlier but records updation ( no of records are 20k) got slowdown. I have found that update query run very slow in SQL 2005 as compared to SQL 2000. Although other select query run very faster. Query is called from ASP (active server page) page.

Changing of fill factor option from 0 to 70 also did not work. I also have set the competible leve to 90.

OLD Sever Config: dual xeon 1GHz, 512MB RAM, window 2000, MS SQL 2000

New server Config: Dual Core Xeon with 2 CPU, 10 GB RAM, window 2003, MS SQL 2005, SQL 2005 SP1

size of mdf file is 16GB

Rebuilting of index did not improved the performance.

Here is the code

SET rsMailQ = objConn.Execute("SELECT * FROM mailqueue WHERE date_sent IS NULL")

While NOT rsEmails.EOF

Set objMailer = Server.CreateObject("Persits.MailSender")
objMailer.Host = MailServer

objMailer.Charset = "UTF-8"
objMailer.ContentTransferEncoding = "Quoted-Printable"
objMailer.From = rsEmails("From")
objMailer.FromName = rsEmails("FromName")
objMailer.AddAddress rsEmails("To")
objMailer.Subject = rsEmails("Subject")
objMailer.Body = rsEmails("msg")

objMailer.Queue = True
objMailer.Send

strSQL = "UPDATE MailQueue SET date_sent = GETUTCDATE() " _
& "WHERE mail_queue_id = " & rsEmails("mail_queue_id")

Set rsUpdate = CreateObject("ADODB.Recordset")
rsUpdate.Open strSQL, objConn

Wend

Thanks in advance

Hi,

Have you update Statistics?

Is MDAC version are same on both the Servers and Client Machine?!

Have you check query running from QA ? What about Execution Plan of Both Servers? I would suggest you to refer relavent thread http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=17451 and some performance Tunning tips on SSP sote http://www.sql-server-performance.com/statistics_io_time.asp & http://www.sql-server-performance.com/transact_sql.asp

HTH

Hemantgiri S. Goswami

|||

Can you post the code for the creating the objConn, specifically are you using a client or server side cursor.

I also not your are not using the recordset returned. In which case you would be better of not storing the results in a recordset.

objConn.execute strSQL

Is this code running on a seperate machine.

What is the CPU usage on the 2 machines?

How many records are in the loop?

|||

Thanks Simon for your help.

Yes this code is running on seperate machine. New sql server ( i.e sql 2005) has better machine as mentioned in my question posted above. It is running slow even for 100 records, in live environment records will be arround 20k or more

here is the code to open connection

Set objConn = Server.CreateObject("ADODB.Connection")
'20030930 JDR 01 - Set 10 minute timeout
objConn.CommandTimeout = 600
objConn.Open(strConnection)

Thanks

|||

My problem got solved after using two connection objects one connection for reading and another for updating the records. Here is the new code...

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.CommandTimeout = 600
objConn.Open(strConnection)

Set objConn2 = Server.CreateObject("ADODB.Connection")
objConn2.CommandTimeout = 600
objConn2.Open(strConnection)

SET rsMailQ = objConn.Execute("SELECT * FROM mailqueue WHERE date_sent IS NULL")

While NOT rsEmails.EOF

Set objMailer = Server.CreateObject("Persits.MailSender")
objMailer.Host = MailServer

objMailer.Charset = "UTF-8"
objMailer.ContentTransferEncoding = "Quoted-Printable"
objMailer.From = rsEmails("From")
objMailer.FromName = rsEmails("FromName")
objMailer.AddAddress rsEmails("To")
objMailer.Subject = rsEmails("Subject")
objMailer.Body = rsEmails("msg")

objMailer.Queue = True
objMailer.Send

strSQL = "UPDATE MailQueue SET date_sent = GETUTCDATE() " _
& "WHERE mail_queue_id = " & rsEmails("mail_queue_id")

Set rsUpdate = CreateObject("ADODB.Recordset")
rsUpdate.Open strSQL, objConn2

Wend

Moving sql 2000 to sql 2005- update query run slower

Earlier I posted this query in SQL Server Database Engine forum. I am reposting it here.
We have upgraded our database server from SQL 2000 to SQL 2005. To migrate the DB I attached the mdf file in SQL 2005. After migration website loads quicker than earlier but records updation ( no of records are 20k) got slowdown. I have found that update query run very slow in SQL 2005 as compared to SQL 2000. Although other select query run very faster. Query is called from ASP (active server page) page.

Changing of fill factor option from 0 to 70 also did not work. I also have set the competible leve to 90.

OLD Sever Config: dual xeon 1GHz, 512MB RAM, window 2000, MS SQL 2000

New server Config: Dual Core Xeon with 2 CPU, 10 GB RAM, window 2003, MS SQL 2005, SQL 2005 SP1

size of mdf file is 16GB

Rebuilting of index did not improved the performance.

Here is the code

SET rsMailQ = objConn.Execute("SELECT * FROM mailqueue WHERE date_sent IS NULL")

While NOT rsEmails.EOF

Set objMailer = Server.CreateObject("Persits.MailSender")
objMailer.Host = MailServer

objMailer.Charset = "UTF-8"
objMailer.ContentTransferEncoding = "Quoted-Printable"
objMailer.From = rsEmails("From")
objMailer.FromName = rsEmails("FromName")
objMailer.AddAddress rsEmails("To")
objMailer.Subject = rsEmails("Subject")
objMailer.Body = rsEmails("msg")

objMailer.Queue = True
objMailer.Send

strSQL = "UPDATE MailQueue SET date_sent = GETUTCDATE() " _
& "WHERE mail_queue_id = " & rsEmails("mail_queue_id")

Set rsUpdate = CreateObject("ADODB.Recordset")
rsUpdate.Open strSQL, objConn

Wend

Thanks in advance

Hi,

Have you update Statistics?

Is MDAC version are same on both the Servers and Client Machine?!

Have you check query running from QA ? What about Execution Plan of Both Servers? I would suggest you to refer relavent thread http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=17451 and some performance Tunning tips on SSP sote http://www.sql-server-performance.com/statistics_io_time.asp & http://www.sql-server-performance.com/transact_sql.asp

HTH

Hemantgiri S. Goswami

|||

Can you post the code for the creating the objConn, specifically are you using a client or server side cursor.

I also not your are not using the recordset returned. In which case you would be better of not storing the results in a recordset.

objConn.execute strSQL

Is this code running on a seperate machine.

What is the CPU usage on the 2 machines?

How many records are in the loop?

|||

Thanks Simon for your help.

Yes this code is running on seperate machine. New sql server ( i.e sql 2005) has better machine as mentioned in my question posted above. It is running slow even for 100 records, in live environment records will be arround 20k or more

here is the code to open connection

Set objConn = Server.CreateObject("ADODB.Connection")
'20030930 JDR 01 - Set 10 minute timeout
objConn.CommandTimeout = 600
objConn.Open(strConnection)

Thanks

|||

My problem got solved after using two connection objects one connection for reading and another for updating the records. Here is the new code...

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.CommandTimeout = 600
objConn.Open(strConnection)

Set objConn2 = Server.CreateObject("ADODB.Connection")
objConn2.CommandTimeout = 600
objConn2.Open(strConnection)

SET rsMailQ = objConn.Execute("SELECT * FROM mailqueue WHERE date_sent IS NULL")

While NOT rsEmails.EOF

Set objMailer = Server.CreateObject("Persits.MailSender")
objMailer.Host = MailServer

objMailer.Charset = "UTF-8"
objMailer.ContentTransferEncoding = "Quoted-Printable"
objMailer.From = rsEmails("From")
objMailer.FromName = rsEmails("FromName")
objMailer.AddAddress rsEmails("To")
objMailer.Subject = rsEmails("Subject")
objMailer.Body = rsEmails("msg")

objMailer.Queue = True
objMailer.Send

strSQL = "UPDATE MailQueue SET date_sent = GETUTCDATE() " _
& "WHERE mail_queue_id = " & rsEmails("mail_queue_id")

Set rsUpdate = CreateObject("ADODB.Recordset")
rsUpdate.Open strSQL, objConn2

Wend