Adding 2nd instance of SQL

I am adding a 2nd instance of SQL on a SQL SP4 box. Can someone please
clarify a few things for me?
1. Can this be done during productions hours - does it requre a reboot?
2. Does it actually install another full version of SQL - I only have a CD
that has SQL 2000 on it so I would have to apply the SP 4 after the fact -
will this affect the production instance default instance)
3. Does it use a different service account to start?
4. Can it use a second bound IP address?
Any other tips I should be aware of? Thanks for your time.
Hi
"Bad Beagle" wrote:

> I am adding a 2nd instance of SQL on a SQL SP4 box. Can someone please
> clarify a few things for me?
> 1. Can this be done during productions hours - does it requre a reboot?
It should not require a re-boot, but it would not be a good idea to do it on
a production server whilst it is being used.

> 2. Does it actually install another full version of SQL - I only have a CD
> that has SQL 2000 on it so I would have to apply the SP 4 after the fact -
> will this affect the production instance default instance)
Yes you would need to apply a service pack separately.

> 3. Does it use a different service account to start?
This is however you configure it, the can be the same if required.

> 4. Can it use a second bound IP address?
You could do this via a firewall and filter on tcp/ip ports or possibly by
using the TCP/IP filtering in the advanced options of the network connection.
> Any other tips I should be aware of? Thanks for your time.
>
>
Having a second instance will use resources on this machine, you should not
(really) have a second instance on a production server if you can avoid it,
and certainly test/development instances should be kept separate.
John
|||Hello,
Thank you for posting. Also, thanks very much to John for his contribution.
It seems that John has essentially answered all the questions appropriately.
You can install the instance without re-booting the system. Application of
SP4 does require a reboot. I would also recommend that, regardless, you do
reboot the system simply to refresh services and what not. You can
install the instance followed by SP4 then reboot.
My understanding is that the root of this question is "Can you do the
installation on a production server while it is being used?" While it
should work without issue, there are a number of reasons not to attempt
this. First, if something does go wrong, you may unexpectedly bring down
your production server while people are using it, thus potentially losing
data and incurring user and executive wrath: something we all generally
want to avoid. Secondly, and particularly with SP updates, installations
have been known to fail silently and return a successful message without
properly updating services. It's very much recommended to do any such type
of major administration while the database is not being accessed.
You can configure the service account either during the installation of the
SQL Server, or you can update it later through SQL Enterprise manager. As
John mentioned, you can set it to a different account or the same account
depending on your requirements.
You can bind each instance that you install to a separate port, but you can
not specify an IP address for each instance. Instead, the port for the
TCP/IP network protocal is dynamically configured during installation. As
John suggests you can manage this using NAT at your Router/Firewall. To
check/change the port for the instance, you can open SQL Enterprise Manger,
Right click the instance you want to change, select Properties > Network
Configuration... > TCP/IP > Properties...
John also brings up a very good point about the performance implications of
running an extra instance on a production server. Basically, when you add
an instance, all the following are replicated, taking up additional
resources:

> SQL Server database engine
> System and user databases.
> The SQL Server and SQL Server Agent services.
> The registry keys associated with the database engine and the SQL Server
and SQL Server Agent services.
> Network connection addresses
(*see:
http://msdn.microsoft.com/library/de...us/architec/8_
ar_cs_9i5u.asp)
Please also review the BOL section on 'multiple instance of SQL Server' in
the index for BOL. See particularly the sections "best practices', 'memory
usage', and 'when to use' to better determine if installing separate
instances is actually in your best interest.
Hope this helps!
Sincerely,
Dana Brash [MSFT]
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.
--
>From: "Bad Beagle" <maxwelli@.nospam.postalias>
>Subject: Adding 2nd instance of SQL
>Date: Fri, 13 Jan 2006 07:29:45 -0700
>Lines: 12
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <eScgO3EGGHA.3944@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: 199.213.91.1
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msft ngp13.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:417520
>X-Tomcat-NG: microsoft.public.sqlserver.server
>I am adding a 2nd instance of SQL on a SQL SP4 box. Can someone please
>clarify a few things for me?
>1. Can this be done during productions hours - does it requre a reboot?
>2. Does it actually install another full version of SQL - I only have a
CD
>that has SQL 2000 on it so I would have to apply the SP 4 after the fact -
>will this affect the production instance default instance)
>3. Does it use a different service account to start?
>4. Can it use a second bound IP address?
>Any other tips I should be aware of? Thanks for your time.
>
>
|||Hi,
Just want to say Hi, and I was wondering how everything is going. If
anything is unclear, please let me know. It is my pleasure to be of
assistance.
Hope this helps!
Sincerely,
Dana Brash
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/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
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.
--
>From: "Bad Beagle" <maxwelli@.nospam.postalias>
>Subject: Adding 2nd instance of SQL
>Date: Fri, 13 Jan 2006 07:29:45 -0700
>Lines: 12
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <eScgO3EGGHA.3944@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: 199.213.91.1
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msft ngp13.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:417520
>X-Tomcat-NG: microsoft.public.sqlserver.server
>I am adding a 2nd instance of SQL on a SQL SP4 box. Can someone please
>clarify a few things for me?
>1. Can this be done during productions hours - does it requre a reboot?
>2. Does it actually install another full version of SQL - I only have a
CD
>that has SQL 2000 on it so I would have to apply the SP 4 after the fact -
>will this affect the production instance default instance)
>3. Does it use a different service account to start?
>4. Can it use a second bound IP address?
>Any other tips I should be aware of? Thanks for your time.
>
>
|||Hi,
Just a followup on this post. I am wondering if you have any questions or
concerns about this issue. If you need further assistance, please do not
hesitate to let me know.
Hope this helps!
Sincerely,
Dana Brash
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/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
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.
--
>From: "Bad Beagle" <maxwelli@.nospam.postalias>
>Subject: Adding 2nd instance of SQL
>Date: Fri, 13 Jan 2006 07:29:45 -0700
>Lines: 12
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <eScgO3EGGHA.3944@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: 199.213.91.1
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msft ngp13.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:417520
>X-Tomcat-NG: microsoft.public.sqlserver.server
>I am adding a 2nd instance of SQL on a SQL SP4 box. Can someone please
>clarify a few things for me?
>1. Can this be done during productions hours - does it requre a reboot?
>2. Does it actually install another full version of SQL - I only have a
CD
>that has SQL 2000 on it so I would have to apply the SP 4 after the fact -
>will this affect the production instance default instance)
>3. Does it use a different service account to start?
>4. Can it use a second bound IP address?
>Any other tips I should be aware of? Thanks for your time.
>
>

Adding 2nd instance of SQL

I am adding a 2nd instance of SQL on a SQL SP4 box. Can someone please
clarify a few things for me?
1. Can this be done during productions hours - does it requre a reboot?
2. Does it actually install another full version of SQL - I only have a CD
that has SQL 2000 on it so I would have to apply the SP 4 after the fact -
will this affect the production instance default instance)
3. Does it use a different service account to start?
4. Can it use a second bound IP address?
Any other tips I should be aware of? Thanks for your time.Hi
"Bad Beagle" wrote:
> I am adding a 2nd instance of SQL on a SQL SP4 box. Can someone please
> clarify a few things for me?
> 1. Can this be done during productions hours - does it requre a reboot?
It should not require a re-boot, but it would not be a good idea to do it on
a production server whilst it is being used.
> 2. Does it actually install another full version of SQL - I only have a CD
> that has SQL 2000 on it so I would have to apply the SP 4 after the fact -
> will this affect the production instance default instance)
Yes you would need to apply a service pack separately.
> 3. Does it use a different service account to start?
This is however you configure it, the can be the same if required.
> 4. Can it use a second bound IP address?
You could do this via a firewall and filter on tcp/ip ports or possibly by
using the TCP/IP filtering in the advanced options of the network connection.
> Any other tips I should be aware of? Thanks for your time.
>
>
Having a second instance will use resources on this machine, you should not
(really) have a second instance on a production server if you can avoid it,
and certainly test/development instances should be kept separate.
John|||Hello,
Thank you for posting. Also, thanks very much to John for his contribution.
It seems that John has essentially answered all the questions appropriately.
You can install the instance without re-booting the system. Application of
SP4 does require a reboot. I would also recommend that, regardless, you do
reboot the system simply to refresh services and what not. You can
install the instance followed by SP4 then reboot.
My understanding is that the root of this question is "Can you do the
installation on a production server while it is being used?" While it
should work without issue, there are a number of reasons not to attempt
this. First, if something does go wrong, you may unexpectedly bring down
your production server while people are using it, thus potentially losing
data and incurring user and executive wrath: something we all generally
want to avoid. Secondly, and particularly with SP updates, installations
have been known to fail silently and return a successful message without
properly updating services. It's very much recommended to do any such type
of major administration while the database is not being accessed.
You can configure the service account either during the installation of the
SQL Server, or you can update it later through SQL Enterprise manager. As
John mentioned, you can set it to a different account or the same account
depending on your requirements.
You can bind each instance that you install to a separate port, but you can
not specify an IP address for each instance. Instead, the port for the
TCP/IP network protocal is dynamically configured during installation. As
John suggests you can manage this using NAT at your Router/Firewall. To
check/change the port for the instance, you can open SQL Enterprise Manger,
Right click the instance you want to change, select Properties > Network
Configuration... > TCP/IP > Properties...
John also brings up a very good point about the performance implications of
running an extra instance on a production server. Basically, when you add
an instance, all the following are replicated, taking up additional
resources:
> SQL Server database engine
> System and user databases.
> The SQL Server and SQL Server Agent services.
> The registry keys associated with the database engine and the SQL Server
and SQL Server Agent services.
> Network connection addresses
(*see:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_
ar_cs_9i5u.asp)
Please also review the BOL section on 'multiple instance of SQL Server' in
the index for BOL. See particularly the sections "best practices', 'memory
usage', and 'when to use' to better determine if installing separate
instances is actually in your best interest.
Hope this helps!
Sincerely,
Dana Brash [MSFT]
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.
>From: "Bad Beagle" <maxwelli@.nospam.postalias>
>Subject: Adding 2nd instance of SQL
>Date: Fri, 13 Jan 2006 07:29:45 -0700
>Lines: 12
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <eScgO3EGGHA.3944@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: 199.213.91.1
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:417520
>X-Tomcat-NG: microsoft.public.sqlserver.server
>I am adding a 2nd instance of SQL on a SQL SP4 box. Can someone please
>clarify a few things for me?
>1. Can this be done during productions hours - does it requre a reboot?
>2. Does it actually install another full version of SQL - I only have a
CD
>that has SQL 2000 on it so I would have to apply the SP 4 after the fact -
>will this affect the production instance default instance)
>3. Does it use a different service account to start?
>4. Can it use a second bound IP address?
>Any other tips I should be aware of? Thanks for your time.
>
>|||Hi,
Just want to say Hi, and I was wondering how everything is going. If
anything is unclear, please let me know. It is my pleasure to be of
assistance.
Hope this helps!
Sincerely,
Dana Brash
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/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
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.
>From: "Bad Beagle" <maxwelli@.nospam.postalias>
>Subject: Adding 2nd instance of SQL
>Date: Fri, 13 Jan 2006 07:29:45 -0700
>Lines: 12
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <eScgO3EGGHA.3944@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: 199.213.91.1
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:417520
>X-Tomcat-NG: microsoft.public.sqlserver.server
>I am adding a 2nd instance of SQL on a SQL SP4 box. Can someone please
>clarify a few things for me?
>1. Can this be done during productions hours - does it requre a reboot?
>2. Does it actually install another full version of SQL - I only have a
CD
>that has SQL 2000 on it so I would have to apply the SP 4 after the fact -
>will this affect the production instance default instance)
>3. Does it use a different service account to start?
>4. Can it use a second bound IP address?
>Any other tips I should be aware of? Thanks for your time.
>
>|||Hi,
Just a followup on this post. I am wondering if you have any questions or
concerns about this issue. If you need further assistance, please do not
hesitate to let me know.
Hope this helps!
Sincerely,
Dana Brash
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/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
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.
>From: "Bad Beagle" <maxwelli@.nospam.postalias>
>Subject: Adding 2nd instance of SQL
>Date: Fri, 13 Jan 2006 07:29:45 -0700
>Lines: 12
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <eScgO3EGGHA.3944@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: 199.213.91.1
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:417520
>X-Tomcat-NG: microsoft.public.sqlserver.server
>I am adding a 2nd instance of SQL on a SQL SP4 box. Can someone please
>clarify a few things for me?
>1. Can this be done during productions hours - does it requre a reboot?
>2. Does it actually install another full version of SQL - I only have a
CD
>that has SQL 2000 on it so I would have to apply the SP 4 after the fact -
>will this affect the production instance default instance)
>3. Does it use a different service account to start?
>4. Can it use a second bound IP address?
>Any other tips I should be aware of? Thanks for your time.
>
>

Adding 2nd instance of SQL

I am adding a 2nd instance of SQL on a SQL SP4 box. Can someone please
clarify a few things for me?
1. Can this be done during productions hours - does it requre a reboot?
2. Does it actually install another full version of SQL - I only have a CD
that has SQL 2000 on it so I would have to apply the SP 4 after the fact -
will this affect the production instance default instance)
3. Does it use a different service account to start?
4. Can it use a second bound IP address?
Any other tips I should be aware of? Thanks for your time.Hi
"Bad Beagle" wrote:

> I am adding a 2nd instance of SQL on a SQL SP4 box. Can someone please
> clarify a few things for me?
> 1. Can this be done during productions hours - does it requre a reboot?
It should not require a re-boot, but it would not be a good idea to do it on
a production server whilst it is being used.

> 2. Does it actually install another full version of SQL - I only have a C
D
> that has SQL 2000 on it so I would have to apply the SP 4 after the fact -
> will this affect the production instance default instance)
Yes you would need to apply a service pack separately.

> 3. Does it use a different service account to start?
This is however you configure it, the can be the same if required.

> 4. Can it use a second bound IP address?
You could do this via a firewall and filter on tcp/ip ports or possibly by
using the TCP/IP filtering in the advanced options of the network connection.">
> Any other tips I should be aware of? Thanks for your time.
>
>
Having a second instance will use resources on this machine, you should not
(really) have a second instance on a production server if you can avoid it,
and certainly test/development instances should be kept separate.
John|||Hello,
Thank you for posting. Also, thanks very much to John for his contribution.
It seems that John has essentially answered all the questions appropriately.
You can install the instance without re-booting the system. Application of
SP4 does require a reboot. I would also recommend that, regardless, you do
reboot the system simply to refresh services and what not. You can
install the instance followed by SP4 then reboot.
My understanding is that the root of this question is "Can you do the
installation on a production server while it is being used?" While it
should work without issue, there are a number of reasons not to attempt
this. First, if something does go wrong, you may unexpectedly bring down
your production server while people are using it, thus potentially losing
data and incurring user and executive wrath: something we all generally
want to avoid. Secondly, and particularly with SP updates, installations
have been known to fail silently and return a successful message without
properly updating services. It's very much recommended to do any such type
of major administration while the database is not being accessed.
You can configure the service account either during the installation of the
SQL Server, or you can update it later through SQL Enterprise manager. As
John mentioned, you can set it to a different account or the same account
depending on your requirements.
You can bind each instance that you install to a separate port, but you can
not specify an IP address for each instance. Instead, the port for the
TCP/IP network protocal is dynamically configured during installation. As
John suggests you can manage this using NAT at your Router/Firewall. To
check/change the port for the instance, you can open SQL Enterprise Manger,
Right click the instance you want to change, select Properties > Network
Configuration... > TCP/IP > Properties...
John also brings up a very good point about the performance implications of
running an extra instance on a production server. Basically, when you add
an instance, all the following are replicated, taking up additional
resources:

> SQL Server database engine
> System and user databases.
> The SQL Server and SQL Server Agent services.
> The registry keys associated with the database engine and the SQL Server
and SQL Server Agent services.
> Network connection addresses
(*see:
http://msdn.microsoft.com/library/d...-us/architec/8_
ar_cs_9i5u.asp)
Please also review the BOL section on 'multiple instance of SQL Server' in
the index for BOL. See particularly the sections "best practices', 'memory
usage', and 'when to use' to better determine if installing separate
instances is actually in your best interest.
Hope this helps!
Sincerely,
Dana Brash [MSFT]
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.
--
>From: "Bad Beagle" <maxwelli@.nospam.postalias>
>Subject: Adding 2nd instance of SQL
>Date: Fri, 13 Jan 2006 07:29:45 -0700
>Lines: 12
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <eScgO3EGGHA.3944@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: 199.213.91.1
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:417520
>X-Tomcat-NG: microsoft.public.sqlserver.server
>I am adding a 2nd instance of SQL on a SQL SP4 box. Can someone please
>clarify a few things for me?
>1. Can this be done during productions hours - does it requre a reboot?
>2. Does it actually install another full version of SQL - I only have a
CD
>that has SQL 2000 on it so I would have to apply the SP 4 after the fact -
>will this affect the production instance default instance)
>3. Does it use a different service account to start?
>4. Can it use a second bound IP address?
>Any other tips I should be aware of? Thanks for your time.
>
>|||Hi,
Just want to say Hi, and I was wondering how everything is going. If
anything is unclear, please let me know. It is my pleasure to be of
assistance.
Hope this helps!
Sincerely,
Dana Brash
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.
--
>From: "Bad Beagle" <maxwelli@.nospam.postalias>
>Subject: Adding 2nd instance of SQL
>Date: Fri, 13 Jan 2006 07:29:45 -0700
>Lines: 12
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <eScgO3EGGHA.3944@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: 199.213.91.1
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:417520
>X-Tomcat-NG: microsoft.public.sqlserver.server
>I am adding a 2nd instance of SQL on a SQL SP4 box. Can someone please
>clarify a few things for me?
>1. Can this be done during productions hours - does it requre a reboot?
>2. Does it actually install another full version of SQL - I only have a
CD
>that has SQL 2000 on it so I would have to apply the SP 4 after the fact -
>will this affect the production instance default instance)
>3. Does it use a different service account to start?
>4. Can it use a second bound IP address?
>Any other tips I should be aware of? Thanks for your time.
>
>|||Hi,
Just a followup on this post. I am wondering if you have any questions or
concerns about this issue. If you need further assistance, please do not
hesitate to let me know.
Hope this helps!
Sincerely,
Dana Brash
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.
--
>From: "Bad Beagle" <maxwelli@.nospam.postalias>
>Subject: Adding 2nd instance of SQL
>Date: Fri, 13 Jan 2006 07:29:45 -0700
>Lines: 12
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <eScgO3EGGHA.3944@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: 199.213.91.1
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:417520
>X-Tomcat-NG: microsoft.public.sqlserver.server
>I am adding a 2nd instance of SQL on a SQL SP4 box. Can someone please
>clarify a few things for me?
>1. Can this be done during productions hours - does it requre a reboot?
>2. Does it actually install another full version of SQL - I only have a
CD
>that has SQL 2000 on it so I would have to apply the SP 4 after the fact -
>will this affect the production instance default instance)
>3. Does it use a different service account to start?
>4. Can it use a second bound IP address?
>Any other tips I should be aware of? Thanks for your time.
>
>sql

Added CPUs

Hi,
If I add more CPUs to an existing computer running SQL Server 2000
Enterprise Edition + SP4, do I need to do anything special to get SQL Server
to recognize the extra CPUs, or does it automatically detect them? What
about Windows Server 2003 Enterprise Edition + SP2? I'd be going from 2
CPUs to 4 CPUs.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
It will detect and use automatically. If you want to check read about sp_configure and "affinity
mask". This setting specifies which CPUs SQL Server can use. By default all.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Daniel Jameson" <no_djameson_spam@.childrensoncologygroup.org> wrote in message
news:uEvg1JElIHA.4076@.TK2MSFTNGP05.phx.gbl...
> Hi,
> If I add more CPUs to an existing computer running SQL Server 2000 Enterprise Edition + SP4, do I
> need to do anything special to get SQL Server to recognize the extra CPUs, or does it
> automatically detect them? What about Windows Server 2003 Enterprise Edition + SP2? I'd be going
> from 2 CPUs to 4 CPUs.
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
|||Cool. Thanks for the quick answer.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23$sjjgElIHA.1744@.TK2MSFTNGP05.phx.gbl...
> It will detect and use automatically. If you want to check read about
> sp_configure and "affinity mask". This setting specifies which CPUs SQL
> Server can use. By default all.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Daniel Jameson" <no_djameson_spam@.childrensoncologygroup.org> wrote in
> message news:uEvg1JElIHA.4076@.TK2MSFTNGP05.phx.gbl...
>
sql

Added CPUs

Hi,
If I add more CPUs to an existing computer running SQL Server 2000
Enterprise Edition + SP4, do I need to do anything special to get SQL Server
to recognize the extra CPUs, or does it automatically detect them? What
about Windows Server 2003 Enterprise Edition + SP2? I'd be going from 2
CPUs to 4 CPUs.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.orgIt will detect and use automatically. If you want to check read about sp_configure and "affinity
mask". This setting specifies which CPUs SQL Server can use. By default all.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Daniel Jameson" <no_djameson_spam@.childrensoncologygroup.org> wrote in message
news:uEvg1JElIHA.4076@.TK2MSFTNGP05.phx.gbl...
> Hi,
> If I add more CPUs to an existing computer running SQL Server 2000 Enterprise Edition + SP4, do I
> need to do anything special to get SQL Server to recognize the extra CPUs, or does it
> automatically detect them? What about Windows Server 2003 Enterprise Edition + SP2? I'd be going
> from 2 CPUs to 4 CPUs.
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>|||Cool. Thanks for the quick answer.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23$sjjgElIHA.1744@.TK2MSFTNGP05.phx.gbl...
> It will detect and use automatically. If you want to check read about
> sp_configure and "affinity mask". This setting specifies which CPUs SQL
> Server can use. By default all.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Daniel Jameson" <no_djameson_spam@.childrensoncologygroup.org> wrote in
> message news:uEvg1JElIHA.4076@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> If I add more CPUs to an existing computer running SQL Server 2000
>> Enterprise Edition + SP4, do I need to do anything special to get SQL
>> Server to recognize the extra CPUs, or does it automatically detect them?
>> What about Windows Server 2003 Enterprise Edition + SP2? I'd be going
>> from 2 CPUs to 4 CPUs.
>> --
>> Thank you,
>> Daniel Jameson
>> SQL Server DBA
>> Children's Oncology Group
>> www.childrensoncologygroup.org
>>
>

Add user to R\O Database

I have a SQL 2000 Enterprise Edition sp4 production database that is log
shipping to 2 servers (one for DR purposes and the other for reporting).
On the reporting server, the database is in Read-Only mode. I need to
add another user to the database so they can run reports. How can I do
this with the database in read-only mode? I can't take it out of
read-only because I'll have to set up log shipping all over again, won't
I? This is a 40 GB database so that would take quite some time to do.
Do you have any suggestions?
Thank you.
Toni
*** Sent via Developersdex http://www.codecomments.com ***"Toni" <teibner@.SQLallina.com> wrote in message
news:evNCYtFSGHA.5736@.TK2MSFTNGP10.phx.gbl...
>I have a SQL 2000 Enterprise Edition sp4 production database that is log
> shipping to 2 servers (one for DR purposes and the other for reporting).
> On the reporting server, the database is in Read-Only mode. I need to
> add another user to the database so they can run reports. How can I do
> this with the database in read-only mode? I can't take it out of
> read-only because I'll have to set up log shipping all over again, won't
> I? This is a 40 GB database so that would take quite some time to do.
> Do you have any suggestions?
The only way is to add that user to a role that has access... you could
create a server role that has SELECT permissions for all objects, but I'm
not sure that a new role's access would be grand-fathered into an r/o db.
Unfortunately, the only built-in role that leaves you with is sysadmin...
but it's worth a try.
-Mark

> Thank you.
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***

Add User to Log-Shipped Database

I have a SQL 2000 Enterprise Edition sp4 production database that is log
shipping to 2 servers (one for DR purposes and the other for reporting).
On the reporting server, the database is in Read-Only mode. I need to
add another user to the database so they can run reports. How can I do
this with the database in read-only mode? I can't take it out of
read-only because I'll have to set up log shipping all over again, won't
I?
This is a 40 GB database so that would take quite some time to do.
Do you have any suggestions?
Thank you.
Toni
*** Sent via Developersdex http://www.codecomments.com ***
You need to add a login for that user on the originating server, add a user for that login on the
originating server and then add the same login on the reporting machine (making sure that the SID
matches).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Toni" <teibner@.SQLallina.com> wrote in message news:uv9UQ5OSGHA.256@.TK2MSFTNGP14.phx.gbl...
>I have a SQL 2000 Enterprise Edition sp4 production database that is log
> shipping to 2 servers (one for DR purposes and the other for reporting).
> On the reporting server, the database is in Read-Only mode. I need to
> add another user to the database so they can run reports. How can I do
> this with the database in read-only mode? I can't take it out of
> read-only because I'll have to set up log shipping all over again, won't
> I?
> This is a 40 GB database so that would take quite some time to do.
> Do you have any suggestions?
> Thank you.
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***
|||The sid! Of course! I made the sid match and everything is perfect!
I'll make sure to put this in my documentation so I don't forget.
Thank you so much!
Toni
*** Sent via Developersdex http://www.codecomments.com ***

Add User to Log-Shipped Database

I have a SQL 2000 Enterprise Edition sp4 production database that is log
shipping to 2 servers (one for DR purposes and the other for reporting).
On the reporting server, the database is in Read-Only mode. I need to
add another user to the database so they can run reports. How can I do
this with the database in read-only mode? I can't take it out of
read-only because I'll have to set up log shipping all over again, won't
I?
This is a 40 GB database so that would take quite some time to do.
Do you have any suggestions?
Thank you.
Toni
*** Sent via Developersdex http://www.codecomments.com ***You need to add a login for that user on the originating server, add a user
for that login on the
originating server and then add the same login on the reporting machine (mak
ing sure that the SID
matches).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Toni" <teibner@.SQLallina.com> wrote in message news:uv9UQ5OSGHA.256@.TK2MSFTNGP14.phx.gbl...

>I have a SQL 2000 Enterprise Edition sp4 production database that is log
> shipping to 2 servers (one for DR purposes and the other for reporting).
> On the reporting server, the database is in Read-Only mode. I need to
> add another user to the database so they can run reports. How can I do
> this with the database in read-only mode? I can't take it out of
> read-only because I'll have to set up log shipping all over again, won't
> I?
> This is a 40 GB database so that would take quite some time to do.
> Do you have any suggestions?
> Thank you.
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***|||The sid! Of course! I made the sid match and everything is perfect!
I'll make sure to put this in my documentation so I don't forget.
Thank you so much!
Toni
*** Sent via Developersdex http://www.codecomments.com ***

Add User to Log-Shipped Database

I have a SQL 2000 Enterprise Edition sp4 production database that is log
shipping to 2 servers (one for DR purposes and the other for reporting).
On the reporting server, the database is in Read-Only mode. I need to
add another user to the database so they can run reports. How can I do
this with the database in read-only mode? I can't take it out of
read-only because I'll have to set up log shipping all over again, won't
I?
This is a 40 GB database so that would take quite some time to do.
Do you have any suggestions?
Thank you.
Toni
*** Sent via Developersdex http://www.developersdex.com ***You need to add a login for that user on the originating server, add a user for that login on the
originating server and then add the same login on the reporting machine (making sure that the SID
matches).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Toni" <teibner@.SQLallina.com> wrote in message news:uv9UQ5OSGHA.256@.TK2MSFTNGP14.phx.gbl...
>I have a SQL 2000 Enterprise Edition sp4 production database that is log
> shipping to 2 servers (one for DR purposes and the other for reporting).
> On the reporting server, the database is in Read-Only mode. I need to
> add another user to the database so they can run reports. How can I do
> this with the database in read-only mode? I can't take it out of
> read-only because I'll have to set up log shipping all over again, won't
> I?
> This is a 40 GB database so that would take quite some time to do.
> Do you have any suggestions?
> Thank you.
> Toni
> *** Sent via Developersdex http://www.developersdex.com ***

Add new tables to replication

Using SQL Server 2000 with sp4, I have setup a merge replication with a snapshot, currently if we do a schema change we have to:
-break replication,
-stop the subscription,
-make our modifications to the publisher,
-then create a backup of the database,
-push the backup to all of the subscribers,
-restore the database to the subscriber,
-reinitialize the subscription and push the subscription to the subscribers without pushing the contents of the initial snapshot

the reason why we do it this way is that the various servers are located hundreds of miles apart from the publisher and even with broadband connections, re-initializing the subscription always fails.

this is incredibly cumbersome and time consuming and i know there must be a better way to do this.

i need to add two tables to an existing database that is currently being replicated via merge replication, is there an easier way to make the schema changes and have them replicate to all the subscribers?

Rather than making a schema change to an existing table article in the merge publication, it seems like you are trying to add an incremental article to an existing table article. Also from what I see in the scenario, it seems like the subscriptions are set up to be NO SYNC (SP_addmergesubscription @.sync_type='none'). The NO SYNC means you are populating the subscriber server with the initial data manually via backup/restore.

In order for you to add a new table or two to the existing merge publication you may not have to break replication. Here are some steps to try:

1) Create the new tables at the publisher. These new tables must include a rowguid column (i.e. [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL) because merge will use this for change tracking. This scenario will not work without rowguid columns on the new tables.

2) Populate the table

3) Add the new table as a table article in the existing merge publication. You will be warned appropriately that the full snapshot needs to be rerun.

4) Run the full snapshot agent job.

5) BCP out the data from the new table article to a file and send it to the remote subscriber server

6) Create the new table schema at the subscriber

7) BCP in the data on the subscriber server

8) Execute the merge agent. This is required to have the merge triggers built on the subscriber for change tracking. *** Do not do any DML changes on the subscriber server until the merge agent is executed at least one time.

9) Now you should be able to execute DML on the new table at both the publisher and subscriber and sync the changes.

Try this out on a test system to verify it works. I tried on my system and it accomplished what you wanted.

Tom

This posting is provided "AS IS" with no warranties, and confers no rights.

Add additional Subscribers

Hi all,
Have Transactional Replication on SQL 2000 EE SP4 for some time now as
follows:
Publisher/Distributor is an 8 way Server
8GB RAM, RAID 1 (ldf), RAID 10 (mdf), RAID 0 (snapshot folder)
Dist Agents run at Distributor as PUSH to existing Subscribers as follows
Subscriber1 - ServerA
Subscriber2 - ServerB
Subscriber3 - ServerC
This works great, and I now want to ADD:
Subscriber4 - ServerD
Subscriber5 - ServerE
What I want to do is add Server D and E as Subscribers (these are all
separate physical servers) without having to re-initialize any data on
Servers A, B, or C which have been running just fine.
I can prevent access to the PUBLISHER database (~20GB) and quiesce the
Publisher and Subscribers 1/2/3 on Servers A, B, C for several hours if need
be.
What is the best way overall to approach this ?
When the Replication was first setup, I set the Snapshot Agent to use 8 BCP
Threads and it took about 30 mins to generate the Initial Snapshot, and
about 1.5 hours to apply to each Subscriber A, B, C across the network
(these servers are all well connected with dedicated nics on a private LAN
solely for Server to Server Replication traffic).
If I go through the Publication Properties dialog and ADD my two new
Subscribers 4/5 (Servers D/E) using the Wizards...my questions are as
follows:
1 - Will the addition of the two NEW Subscription 4/5 for Servers D/E cause
the Snapshot Agent to actually create a fresh snapshot ? I assume it will
"invalidate", but there is no existing snapshot currently available, and the
SS Agent has only been run manually on occasion since the intitial setup to
publish new articles to the subscribers which has worked fine. Otherwise the
SS Agent remains disabled.
2 - Will the Snapshot attempt to be applied to, or mess up anything for the
existing (already in-synch) Subscriptions on Servers A, B, C or should it
*only* apply itself to the NEW Subscribers 4/5 on Servers D/E ?
I hope the above makes sense and ariticulates what I'm trying to do. Thanks
in advance for any advice, or hints/tips you can provide.
Thanks,
Frank
Just as a follow up question, if I have the Publisher DB and Subscriber DB's
all in synch, and not accessible to users can I:
1 - Take a FULL Backup of one of the Subscription DB on any of the existing
Subscriber Servers (A/B or C), then restore it to Servers D/E
2 - Use the Publication Wizard to PUSH a new Subscription to Servers D/E
using the No Synch option ?
Does that do all the necessary behind the scenes processing correctly ? I
assume because I've restored a backup of an existing Subscriber DB to the
NEW Subscriber DB that it will have all the INS, UPD, DEL StoredProcs in
place as well ?
I read you can do a backup/restore of the Publication DB on a Subscriber for
a no-synch initialization, but the Subscription DB's have auto created
ClusteredIndexes for the PriKeys that exist on the Publication so would that
be an issue ?
In addition, there are some different non-clustered indexes on the
Subscribers as they are used for read-only queries, and mostly reporting
purposes that are offloaded from running on the Publisher DB.
Thanks,
Frank
"Frank Conte" <Frank.Conte@.discussions.ms.com> wrote in message
news:%23GPqOqpwHHA.4640@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> Have Transactional Replication on SQL 2000 EE SP4 for some time now as
> follows:
> Publisher/Distributor is an 8 way Server
> 8GB RAM, RAID 1 (ldf), RAID 10 (mdf), RAID 0 (snapshot folder)
> Dist Agents run at Distributor as PUSH to existing Subscribers as follows
> Subscriber1 - ServerA
> Subscriber2 - ServerB
> Subscriber3 - ServerC
> This works great, and I now want to ADD:
> Subscriber4 - ServerD
> Subscriber5 - ServerE
> What I want to do is add Server D and E as Subscribers (these are all
> separate physical servers) without having to re-initialize any data on
> Servers A, B, or C which have been running just fine.
> I can prevent access to the PUBLISHER database (~20GB) and quiesce the
> Publisher and Subscribers 1/2/3 on Servers A, B, C for several hours if
> need be.
> What is the best way overall to approach this ?
> When the Replication was first setup, I set the Snapshot Agent to use 8
> BCP Threads and it took about 30 mins to generate the Initial Snapshot,
> and about 1.5 hours to apply to each Subscriber A, B, C across the network
> (these servers are all well connected with dedicated nics on a private LAN
> solely for Server to Server Replication traffic).
> If I go through the Publication Properties dialog and ADD my two new
> Subscribers 4/5 (Servers D/E) using the Wizards...my questions are as
> follows:
> 1 - Will the addition of the two NEW Subscription 4/5 for Servers D/E
> cause the Snapshot Agent to actually create a fresh snapshot ? I assume it
> will "invalidate", but there is no existing snapshot currently available,
> and the SS Agent has only been run manually on occasion since the intitial
> setup to publish new articles to the subscribers which has worked fine.
> Otherwise the SS Agent remains disabled.
> 2 - Will the Snapshot attempt to be applied to, or mess up anything for
> the existing (already in-synch) Subscriptions on Servers A, B, C or should
> it *only* apply itself to the NEW Subscribers 4/5 on Servers D/E ?
> I hope the above makes sense and ariticulates what I'm trying to do.
> Thanks in advance for any advice, or hints/tips you can provide.
> Thanks,
> Frank
>
|||(1) for an initialisation you'll need to create a new snapshot.
(2) initialization of the 2 new subscriptions won't affect existing
subscriptions.
HTH,
Paul Ibison
|||You can use a backup of the subscriber - it is much less work than taking the
publisher backup. You don't have to be concerned about identity columns,
deleteing data from filtred articles, removing redundant tables and so on.
There are more details which might be relevant here:
http://www.replicationanswers.com/NoSyncInitializations.asp
HTH,
Paul Ibison
|||Hi Paul,
Thanks for the quick reply, and helpful info.
So, according to section 2.1 "Adding Additional Articles" in the link you
provided; if I do this by way of backup an existing Subscriber, restore it
to a new Subscriber(s) than do a no-sych Subscription through the wizard I
LOSE the ability to run the snapshot agent thereafter where it would
ordinarily:
1 - Generate schema, index and data files for a NEWLY added article on the
publisher. For example, I add a new table on the Publisher, then go to the
Publication Properties and check off the new table to be added in the
Article tab. Normally I would run the Snapshot Agent manually and it
generated the files for just that newly added article to the Publication.
2 - Have the Distribution Agent pickup the new articles and push them to my
new Subscribers, like it would for my existing ones that were originally
initialized via Snapshot (sync_type=automatic).
If that is the case, I think I should take the other approach as per my
other POST ?
That is...add the two new Subscribers, generate a fresh snapshot, and let
it apply it to the two new Subscribers all while the Publication DB, and
existing Subscription DB's are quiesced and offline (as in no changes
allowed) ?
Hassan's workaround you noted doesn't apply if adding articles via the
Publication Properties, correct ? It would always require using
sp_addsubscription going forward, and as you mentioned make for a less
maintainable setup than I would like.
Thanks,
Frank
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:5C31C7A4-96EE-4DC5-963E-0D8751CA04B3@.microsoft.com...
> You can use a backup of the subscriber - it is much less work than taking
> the
> publisher backup. You don't have to be concerned about identity columns,
> deleteing data from filtred articles, removing redundant tables and so on.
> There are more details which might be relevant here:
> http://www.replicationanswers.com/NoSyncInitializations.asp
> HTH,
> Paul Ibison
>
|||Hi Paul,
I think this is the better approach for me (see my other reply to yours) in
this thread.
So provided I do not delete and re-add my existing Subscribers in the
Publication Properties they will remain completely uneffected by the new
snapshot files that I do want applied to my new Subscribers.
That sounds like the way to go to keep the maintainable setup going forward.
Is there any other known workaround similiar to Hassan's that doesn't
require using scripting after using the backup/restore method instead of the
initialize with snapshot files ?
Many thanks for you insight, and on going assistance you provide in these
NG's and via your web site.
Regards,
Frank
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:971720D2-2205-43B4-96B9-91FC56D37051@.microsoft.com...
> (1) for an initialisation you'll need to create a new snapshot.
> (2) initialization of the 2 new subscriptions won't affect existing
> subscriptions.
> HTH,
> Paul Ibison
|||Hi Frank - this is correct. For addition of new 'automatic' articles, you'd
need to do this in scripts. Therefore adding new subscriptions normally will
be more suitable. Actually I do the nosync method primarily when the
bandwidth is small and the snapshot is huge - in which case I zip it up
before FTPing over - quite different to your case as far as I can determine.
Cheers,
Paul Ibison
|||What I've seen some people do is to add the new articles to a new publication
in order for the process to be automatic (after having already done a nosync
initialization). This is fine for a one-off provided the articles aren't
related.
HTH,
Paul Ibison