rebuild the replication

Easiest way is to script out replication on the publisher
using enterprise manager, modify the server names (change
Server X to Server Y and vice-versa) and keep this script
for failover. Once server X goes down, you apply the
script on Server Y.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Is there a way that I can rebuild the database in such a way that it becomes
original publisher and the subscriber can then push all changes been made so
far to it to publisher?
Here is what I want to do. Tell me if I 'm thinking wrong.
Server X is publisher and server Y is subscriber with merge replication set.
Now when X is up and Y is standby then all transactions are done at X and
been replicated to Y.
Now let's say X goes down by any reason (Fault or Disaster).
So I make Y as the main server for all user connections and transactions. I
still don't change the replication and keep it as subscriber.
Now how should I rebuild X so that I can make it publisher the way it was
and when I establish the connection, it merges all changes been made at
subscriber and take over again as publisher?
Is this something doable ?
Should we do this or not?
Please advice.
Sam
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1faf01c51bed$77819fb0$a601280a@.phx.gbl...
> Easiest way is to script out replication on the publisher
> using enterprise manager, modify the server names (change
> Server X to Server Y and vice-versa) and keep this script
> for failover. Once server X goes down, you apply the
> script on Server Y.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Rebuild replicated indexes

I have a number of databases that are being transactionally replicated from SQL 2000 Enterprise edition publisher to SQL 2005 Enterprise edition subscriber. I have included indexes in the replication. The subscriber database is then accessed and the data de-normalised and aggregated for reporting purposes.

My question is this: I want to periodically re-build the indexes on the publisher and subscriber via an automated task. If I rebuild the indexes on the publisher, will that automatically replicate to the subscriber? Will there be a problem with the "snapshot being out of date", and therefore replication stopping? I run a new snapshot once a day in the small hours of the morning. If there is likely to be a problem with the rebuild throwing the replication out, would it be wise to have the rebuild job running just before the new snapshot is taken?

Any help/advice appreciated.

No, the index rebuild isn't replicated. You would have to rebuild the indexes on the publisher and subscriber separately.|||

Or there is another option is to use @.pre/post_snapshot_script. You can use subscriber index rebuilding actions into a script and set the script as @.pre/post_snapshot_script in publication properties page -->Snapshot or through sql (sp_changepublication/sp_changemergepublication).

For example, as for post snapshot script, Distribution agent/merge agent will run the pre-snapshot script before any of the replicated object scripts when applying the snapshot at a Subscriber. The script is executed in the security context used by the Merge Agent when connecting to the subscription database. Pre-snapshot scripts are not run on SQL Server Mobile Subscribers.

Refer to: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/28a629a1-7374-4614-9b04-279d290a942a.htm

Therefore, once you rebuild index in publisher side, reinitialize sub, sub side index will be rebult through pre/post snapshot script as well.

Hope the above information will help.

-Yunjing

Re-attaching databases after upgrading to SQL Server Enterprise Edition

Since Upgrading from SQL Standard Edition to SQL Server EE we have been unable to re-attach the database files that we detached before the upgrade, to SQL Server Management studio. We get an error message that reads:

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

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

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

CREATE FILE encountered operating system error 5(error not found) while attempting to open or create the physical file

Any help with this matter would be truly appreciated. Thank you.

You need to reset the NTFS permisions on the physical files. SQL 2005 locks out the physical files NTFS permissions when the databases are detached.

re-attach database to another server

I have detached a database, say AB, from one SQL server 2000. Then attach it
in another server 2000 by using enterprise manager. But it is reattached as
read-only. So I can not make any change. There is no such a peoblem for sql
server 2005.
How to solve this problem?
Thank you for any help
David
Solved it by reading the previous posts.
Thanks
This is a good web site.
David
"david" wrote:

> I have detached a database, say AB, from one SQL server 2000. Then attach it
> in another server 2000 by using enterprise manager. But it is reattached as
> read-only. So I can not make any change. There is no such a peoblem for sql
> server 2005.
> How to solve this problem?
> Thank you for any help
> David

re-attach database to another server

I have detached a database, say AB, from one SQL server 2000. Then attach it
in another server 2000 by using enterprise manager. But it is reattached as
read-only. So I can not make any change. There is no such a peoblem for sql
server 2005.
How to solve this problem?
Thank you for any help
DavidSolved it by reading the previous posts.
Thanks
This is a good web site.
David
"david" wrote:

> I have detached a database, say AB, from one SQL server 2000. Then attach
it
> in another server 2000 by using enterprise manager. But it is reattached a
s
> read-only. So I can not make any change. There is no such a peoblem for sq
l
> server 2005.
> How to solve this problem?
> Thank you for any help
> David

re-attach database to another server

I have detached a database, say AB, from one SQL server 2000. Then attach it
in another server 2000 by using enterprise manager. But it is reattached as
read-only. So I can not make any change. There is no such a peoblem for sql
server 2005.
How to solve this problem?
Thank you for any help
DavidSolved it by reading the previous posts.
Thanks
This is a good web site.
David
"david" wrote:
> I have detached a database, say AB, from one SQL server 2000. Then attach it
> in another server 2000 by using enterprise manager. But it is reattached as
> read-only. So I can not make any change. There is no such a peoblem for sql
> server 2005.
> How to solve this problem?
> Thank you for any help
> David

Reasons for an Enterprise to upgrade from SQL 2000 to SQL 2005

Hi
I'm trying to put a case together to encourage my employer to upgrade from
its SQL 2000 Enterprise farm to SQL Server 2005.
We're interested in Enterprise functionality, such as:
- Improvement in backups
- Monitoring improvements
- Resilience improvements (mirroring, replication, failover etc).
- Cost / payback case.
Can anyone point me in the right direction to be able to put together such a
case.
Thanks"Joe Spears" <joespears@.hotmail.co.uk> wrote in message
news:u4mag.156498$tc.56861@.fe2.news.blueyonder.co.uk...
> Hi
> I'm trying to put a case together to encourage my employer to upgrade from
> its SQL 2000 Enterprise farm to SQL Server 2005.
> We're interested in Enterprise functionality, such as:
> - Improvement in backups
> - Monitoring improvements
> - Resilience improvements (mirroring, replication, failover etc).
> - Cost / payback case.
> Can anyone point me in the right direction to be able to put together such
> a case.
>
What's New in SQL Server 2005
[url]http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx[/ur
l]
There's lots of cool stuff:
Mirroring.
Monitoring performance with Server reports and DMV's.
Transactional Replication.
The new database maintence plan desiner is great.
Database Mail.
SSIS.
But what does your organization struggle with? Stories that start "Remember
when we had that problem ...", help motivate the upgrade. Heve a concrete
vision for how SQL Server 2005 fits in your environment. That's the
business case.
David|||And do your COTS or applications support SQL 2005 yet?
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23n5sSKQeGHA.3948@.TK2MSFTNGP03.phx.gbl...
> "Joe Spears" <joespears@.hotmail.co.uk> wrote in message
> news:u4mag.156498$tc.56861@.fe2.news.blueyonder.co.uk...
>
> What's New in SQL Server 2005
> [url]http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx[/
url]
> There's lots of cool stuff:
> Mirroring.
> Monitoring performance with Server reports and DMV's.
> Transactional Replication.
> The new database maintence plan desiner is great.
> Database Mail.
> SSIS.
> But what does your organization struggle with? Stories that start
> "Remember when we had that problem ...", help motivate the upgrade. Heve
> a concrete vision for how SQL Server 2005 fits in your environment.
> That's the business case.
> David
>|||If you ask 10 people this question, you may get 10 different answers because
SQL2005 offers a wide array of new features and improvement. Personally, my
current reasons for upgrade are the following VLDB-related features (no
considerations given to rank these or other features systematically):
-- More robust DBCC
-- More granular backup/recovery
-- Table partitioning
-- x64 support
Linchi
"Joe Spears" wrote:

> Hi
> I'm trying to put a case together to encourage my employer to upgrade from
> its SQL 2000 Enterprise farm to SQL Server 2005.
> We're interested in Enterprise functionality, such as:
> - Improvement in backups
> - Monitoring improvements
> - Resilience improvements (mirroring, replication, failover etc).
> - Cost / payback case.
> Can anyone point me in the right direction to be able to put together such
a
> case.
> Thanks
>
>
>|||Actually you'd probably get 40 different answers.
Start the process with all of the things that you struggle with currently.
1. Does reindexing lock up your tables and make applications unavailble? -
Online reindexing in 2005 Enterprise Edition
2. Do you have to do large scale data loads or archives that cause massive
contention making the data unavailable? - Table partitioning
3. Have you ever had a production issue where you simply scratched your head
because the engine is just a great big black box that you can't get detailed
diagnostics from? - Dynamic management views and functions
4. Have you ever needed to create an index in order to improve performance
at a critical point, but couldn't because it would lock up the entire
table? - Online index creation in the Enterprise Edition
5. Have you ever had an index corrupt causing everything to be
unavailable? - online index creation in Enterprise Edition
6. Have you ever had a table corrupted that required restoring the entire
database? - page level restores
7. Have you ever needed to restore just a single filegroup into a database
without taking everything else offline? - online restores in Enterprise
Edition
8. Have you ever needed to encrypt data within a table? - encryption
features
The list goes on and on and on. You'll note that I didn't pick on a single
feature that is classified as "cool". Management doesn't care about "cool".
They care about the databases being online, always. Each one of the items
above hit very definite problems that many people have run across in
production that caused databases to be unavailable and management to be
unhappy. 2005 eliminates all of the issues above. (You can keep filling in
the blanks with additional issues from your enviroment.)
If you need to make a case to management, talking about "cool" features is
going to get the request denied. Talking about things that happened in your
environment (and will likely happen again) which will no longer occur by
deploying SQL Server 2005 is going to get management to listen.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:E309B075-01DC-4E40-B4E0-7FBA6F4272F3@.microsoft.com...[vbcol=seagreen]
> If you ask 10 people this question, you may get 10 different answers
> because
> SQL2005 offers a wide array of new features and improvement. Personally,
> my
> current reasons for upgrade are the following VLDB-related features (no
> considerations given to rank these or other features systematically):
> -- More robust DBCC
> -- More granular backup/recovery
> -- Table partitioning
> -- x64 support
> Linchi
> "Joe Spears" wrote:
>

Reasons for an Enterprise to upgrade from SQL 2000 to SQL 2005

Hi
I'm trying to put a case together to encourage my employer to upgrade from
its SQL 2000 Enterprise farm to SQL Server 2005.
We're interested in Enterprise functionality, such as:
- Improvement in backups
- Monitoring improvements
- Resilience improvements (mirroring, replication, failover etc).
- Cost / payback case.
Can anyone point me in the right direction to be able to put together such a
case.
Thanks"Joe Spears" <joespears@.hotmail.co.uk> wrote in message
news:u4mag.156498$tc.56861@.fe2.news.blueyonder.co.uk...
> Hi
> I'm trying to put a case together to encourage my employer to upgrade from
> its SQL 2000 Enterprise farm to SQL Server 2005.
> We're interested in Enterprise functionality, such as:
> - Improvement in backups
> - Monitoring improvements
> - Resilience improvements (mirroring, replication, failover etc).
> - Cost / payback case.
> Can anyone point me in the right direction to be able to put together such
> a case.
>
What's New in SQL Server 2005
http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx
There's lots of cool stuff:
Mirroring.
Monitoring performance with Server reports and DMV's.
Transactional Replication.
The new database maintence plan desiner is great.
Database Mail.
SSIS.
But what does your organization struggle with? Stories that start "Remember
when we had that problem ...", help motivate the upgrade. Heve a concrete
vision for how SQL Server 2005 fits in your environment. That's the
business case.
David|||And do your COTS or applications support SQL 2005 yet?
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23n5sSKQeGHA.3948@.TK2MSFTNGP03.phx.gbl...
> "Joe Spears" <joespears@.hotmail.co.uk> wrote in message
> news:u4mag.156498$tc.56861@.fe2.news.blueyonder.co.uk...
>> Hi
>> I'm trying to put a case together to encourage my employer to upgrade
>> from its SQL 2000 Enterprise farm to SQL Server 2005.
>> We're interested in Enterprise functionality, such as:
>> - Improvement in backups
>> - Monitoring improvements
>> - Resilience improvements (mirroring, replication, failover etc).
>> - Cost / payback case.
>> Can anyone point me in the right direction to be able to put together
>> such a case.
>
> What's New in SQL Server 2005
> http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx
> There's lots of cool stuff:
> Mirroring.
> Monitoring performance with Server reports and DMV's.
> Transactional Replication.
> The new database maintence plan desiner is great.
> Database Mail.
> SSIS.
> But what does your organization struggle with? Stories that start
> "Remember when we had that problem ...", help motivate the upgrade. Heve
> a concrete vision for how SQL Server 2005 fits in your environment.
> That's the business case.
> David
>|||If you ask 10 people this question, you may get 10 different answers because
SQL2005 offers a wide array of new features and improvement. Personally, my
current reasons for upgrade are the following VLDB-related features (no
considerations given to rank these or other features systematically):
-- More robust DBCC
-- More granular backup/recovery
-- Table partitioning
-- x64 support
Linchi
"Joe Spears" wrote:
> Hi
> I'm trying to put a case together to encourage my employer to upgrade from
> its SQL 2000 Enterprise farm to SQL Server 2005.
> We're interested in Enterprise functionality, such as:
> - Improvement in backups
> - Monitoring improvements
> - Resilience improvements (mirroring, replication, failover etc).
> - Cost / payback case.
> Can anyone point me in the right direction to be able to put together such a
> case.
> Thanks
>
>
>|||Actually you'd probably get 40 different answers. :)
Start the process with all of the things that you struggle with currently.
1. Does reindexing lock up your tables and make applications unavailble? -
Online reindexing in 2005 Enterprise Edition
2. Do you have to do large scale data loads or archives that cause massive
contention making the data unavailable? - Table partitioning
3. Have you ever had a production issue where you simply scratched your head
because the engine is just a great big black box that you can't get detailed
diagnostics from? - Dynamic management views and functions
4. Have you ever needed to create an index in order to improve performance
at a critical point, but couldn't because it would lock up the entire
table? - Online index creation in the Enterprise Edition
5. Have you ever had an index corrupt causing everything to be
unavailable? - online index creation in Enterprise Edition
6. Have you ever had a table corrupted that required restoring the entire
database? - page level restores
7. Have you ever needed to restore just a single filegroup into a database
without taking everything else offline? - online restores in Enterprise
Edition
8. Have you ever needed to encrypt data within a table? - encryption
features
The list goes on and on and on. You'll note that I didn't pick on a single
feature that is classified as "cool". Management doesn't care about "cool".
They care about the databases being online, always. Each one of the items
above hit very definite problems that many people have run across in
production that caused databases to be unavailable and management to be
unhappy. 2005 eliminates all of the issues above. (You can keep filling in
the blanks with additional issues from your enviroment.)
If you need to make a case to management, talking about "cool" features is
going to get the request denied. Talking about things that happened in your
environment (and will likely happen again) which will no longer occur by
deploying SQL Server 2005 is going to get management to listen.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:E309B075-01DC-4E40-B4E0-7FBA6F4272F3@.microsoft.com...
> If you ask 10 people this question, you may get 10 different answers
> because
> SQL2005 offers a wide array of new features and improvement. Personally,
> my
> current reasons for upgrade are the following VLDB-related features (no
> considerations given to rank these or other features systematically):
> -- More robust DBCC
> -- More granular backup/recovery
> -- Table partitioning
> -- x64 support
> Linchi
> "Joe Spears" wrote:
>> Hi
>> I'm trying to put a case together to encourage my employer to upgrade
>> from
>> its SQL 2000 Enterprise farm to SQL Server 2005.
>> We're interested in Enterprise functionality, such as:
>> - Improvement in backups
>> - Monitoring improvements
>> - Resilience improvements (mirroring, replication, failover etc).
>> - Cost / payback case.
>> Can anyone point me in the right direction to be able to put together
>> such a
>> case.
>> Thanks
>>
>>