Database Comatability level after Replication set up

Hello

We moved SQL Server 2000 Databases to SQL Server 2005 Instance. If we change the Database compatibility level from 80 to 90, shall Replication will Fail? or any consequencies will arise? please advice. Thanks,

refer link for the features ,

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-48ebca266659.htm

have you already configured your db in sql 2000 for replication and then moved to 2k5? if you are going to newly set it in sql 2005 it shudnt be a problem.........if uyou change the compatability to 90 many features provided in link will be supported also few 2k features will be depreciated i believe....further performance dashboard is supported only in 90.......

Database backup

I am currently building a new production server. For that I need to move
the databases and set up replication between the SQL Server databases.
I have backed-up the databases on the present server.
How can I now restore these databases on the other(new) server? I dont
have those databases already on that server and SQL Server asks which
database I wanna restore, if try to start the restoration, so basically
it assumes the database is already on the server!!
Do I have to create the databases on the other(new) Server before a
restore? If yes, how? I m running SQL 2k on Win 2k on both servers.
A point to a good article or explanation will be highly appreciated.
Best Regards!
--
Posted via http://dbforums.comAssuming that both servers can be online at the same time, go to the new
server and run the T-SQL RESTORE DATABASE statement. In the FROM line of
the RESTORE statement, use DISK=<UNC path pointing to backup file on old
server> In effect, you will be pulling the database backup from the old
server to the new one.
Should be something like:
RESTORE DATABASE mydatabase
FROM DISK = '\\oldserver\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Mydbbackup.bak'
Books online points out that the account under which you are running SQL
Server on the new server must have READ access to the remote "old" server's
backup file.
See "RESTORE" in books online for more info.
HTH,
Rob
"Ricky_Singh" <member32195@.dbforums.com> wrote in message
news:3146187.1059020556@.dbforums.com...
> I am currently building a new production server. For that I need to move
> the databases and set up replication between the SQL Server databases.
> I have backed-up the databases on the present server.
> How can I now restore these databases on the other(new) server? I dont
> have those databases already on that server and SQL Server asks which
> database I wanna restore, if try to start the restoration, so basically
> it assumes the database is already on the server!!
> Do I have to create the databases on the other(new) Server before a
> restore? If yes, how? I m running SQL 2k on Win 2k on both servers.
> A point to a good article or explanation will be highly appreciated.
> Best Regards!
> --
> Posted via http://dbforums.com|||It is asking which database you wish to create. It doesn't
have to exist. If it does exists you have to check the
option to overwrite.
Also look at the next tab which holds the location of the
files to create - you may have to change them if the paths
are different on this m/c.
>--Original Message--
>I am currently building a new production server. For that
I need to move
>the databases and set up replication between the SQL
Server databases.
>I have backed-up the databases on the present server.
>How can I now restore these databases on the other(new)
server? I dont
>have those databases already on that server and SQL
Server asks which
>database I wanna restore, if try to start the
restoration, so basically
>it assumes the database is already on the server!!
>Do I have to create the databases on the other(new)
Server before a
>restore? If yes, how? I m running SQL 2k on Win 2k on
both servers.
>A point to a good article or explanation will be highly
appreciated.
>Best Regards!
>--
>Posted via http://dbforums.com
>.
>

Data Versioning Transactional Replication

Hi,
I've got a requirement to know when some data has been propagated to
all my (read-only) subscribers. This is because the data will be
cleaned, and the subscribers shouldn't "use" the new version for
certain queries until I know that the new data is there.
I've created a model where I have a "version" table with an identity,
that gets updated by the log reader every time it reads. I check the
version at the time the data changes are finished. Then, when i save
the version with the query that i want to run at the subscriber, and
wait until the version number is greater than or equal to my version
number.
This model seems to work, it requires me to add some steps to the
distribution job, but I'm wondering if there is a better way? Ie,
some form of replication that doesn't require me to custom code the
versioning scheme of the data.
Thx,
Doug
If the data is updated on the publisher in discrete batches you can always
wait for the distribution agent to complete. Other than that the scheme you
have implemented sounds like the best way to go. SQL 2005 will have tracer
tokens that might help with your requirement.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Douglas Tarr" <douglas.tarr@.gmail.com> wrote in message
news:c33eac8b.0409070825.1654dcb3@.posting.google.c om...
> Hi,
> I've got a requirement to know when some data has been propagated to
> all my (read-only) subscribers. This is because the data will be
> cleaned, and the subscribers shouldn't "use" the new version for
> certain queries until I know that the new data is there.
> I've created a model where I have a "version" table with an identity,
> that gets updated by the log reader every time it reads. I check the
> version at the time the data changes are finished. Then, when i save
> the version with the query that i want to run at the subscriber, and
> wait until the version number is greater than or equal to my version
> number.
> This model seems to work, it requires me to add some steps to the
> distribution job, but I'm wondering if there is a better way? Ie,
> some form of replication that doesn't require me to custom code the
> versioning scheme of the data.
> Thx,
> Doug

Data Transformation in Transactional Replication

I already put this set of questions to another group and oly one person
replied. I would appreciate if other people can share their opinion as well.
Thanks
=====================
I have a transactional replication set up from one system to another.
Subscriber has only read mode on data (Uni-directional Replication). I need
to do lots of transformation in the data while replication from Publisher to
Subcriber Like adding the same record in the other table as well or do some
denormalization etc.
Which route should I choose?
1. Modify Replicaiton procedures to have this business rule in-built.
2. Should I create indexed views and other tables on reporting server.
3. Should I create trigger on the reporting server and have the replication
procedure only do the insert and trigger do rest of the business rules
implementaion.
4. Can reporting services be used for such a transformation.
5. Should I have DTS as a part of replication.
-Nitin
Perhaps you should call PSS and get their input.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nitin" <Nitin@.discussions.microsoft.com> wrote in message
news:A2EE86E0-024D-4E1D-9051-6144C65B310F@.microsoft.com...
> I already put this set of questions to another group and oly one person
> replied. I would appreciate if other people can share their opinion as
well.
> Thanks
> =====================
> I have a transactional replication set up from one system to another.
> Subscriber has only read mode on data (Uni-directional Replication). I
need
> to do lots of transformation in the data while replication from Publisher
to
> Subcriber Like adding the same record in the other table as well or do
some
> denormalization etc.
> Which route should I choose?
> 1. Modify Replicaiton procedures to have this business rule in-built.
> 2. Should I create indexed views and other tables on reporting server.
> 3. Should I create trigger on the reporting server and have the
replication
> procedure only do the insert and trigger do rest of the business rules
> implementaion.
> 4. Can reporting services be used for such a transformation.
> 5. Should I have DTS as a part of replication.
> -Nitin

Data Transformation in Replication

I have a transactional replication set up from one system to another.
Subscriber has only read mode on data (Uni-directional Replication). I need
to do lots of transformation in the data while replication from Publisher to
Subcriber Like adding the same record in the other table as well or do some
denormalization etc.
Which route should I choose?
1. Modify Replicaiton procedures to have this business rule in-built.
2. Should I create indexed views and other tables on reporting server.
3. Should I create trigger on the reporting server and have the replication
procedure only do the insert and trigger do rest of the business rules
implementaion.
4. Can reporting services be used for such a transformation.
5. Should I have DTS as a part of replication.
-Nitin
1. Yes, these procs will be whacked the next time you do a reinitialization.
Make sure you uncheck the Create the stored procedures during the initial
synchronization of subscriptions option. This is possibly the preferred way
to go.
2. This is another option. However Indexed Views may not be able to do
everything you need. Indexed Views are less complex to set up than custom
stored procedures. Indexed Views are best used on EE, as opposed to standard
as their indexes won't be included in the query plans query optimizer
generates on standard.
3.No, No, No, No, No. You will find that triggers will slow down the
performance of the replication stored procedures. Your latency will increase
many fold.
4. I believe so, however, replication processes may be better tuned for this
this. I think this will be the case only if you replicate the execution of
stored procedures.
5. No, for performance reasons. Use DTS when you have to modify the data en
route to heterogeneous subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nitin" <Nitin@.discussions.microsoft.com> wrote in message
news:1C92DDBA-5FC5-4910-B022-A87A7C75466D@.microsoft.com...
> I have a transactional replication set up from one system to another.
> Subscriber has only read mode on data (Uni-directional Replication). I
need
> to do lots of transformation in the data while replication from Publisher
to
> Subcriber Like adding the same record in the other table as well or do
some
> denormalization etc.
> Which route should I choose?
> 1. Modify Replicaiton procedures to have this business rule in-built.
> 2. Should I create indexed views and other tables on reporting server.
> 3. Should I create trigger on the reporting server and have the
replication
> procedure only do the insert and trigger do rest of the business rules
> implementaion.
> 4. Can reporting services be used for such a transformation.
> 5. Should I have DTS as a part of replication.
> -Nitin

Data Transfer via Email

Hi,
I have a client that needs to transfer data changes from remote SQL
sites to their HQ central DB. I would prefer to use replication but
unfortunately this is not an option as they only want to use email.
We did a home rolled system about 10 years ago using Access with
custom flags for edits and additions in the DB and the changes were
written to ascii files. I don't want to go down this path again!!
Could anyone give suggestions as to the best direction
- Low bandwidth
- Simple to implement and maintain
- Robust
- Latest 'fashion'
The data changes are primarily in one direction - client->HQ and of
low volume say 5 edits per day. If the way to go is an XML file is
there a mechanism for field based selection, or does it have to be the
entire row?
Appreciate any input,
Thanks,
casey
Publish that table in a transactional replication publication and create a
local subscription and make sure you transaction retention is 1 day.
Then issue a call to sp_browsereplcmds and you will get a list of procedure
calls with parameters which you can email to the other SQL Server. If the
procs are on the remote server you will be able to run these procedures
there.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"casy" <mailkerry@.gmail.com> wrote in message
news:1176105883.833718.305870@.q75g2000hsh.googlegr oups.com...
> Hi,
> I have a client that needs to transfer data changes from remote SQL
> sites to their HQ central DB. I would prefer to use replication but
> unfortunately this is not an option as they only want to use email.
> We did a home rolled system about 10 years ago using Access with
> custom flags for edits and additions in the DB and the changes were
> written to ascii files. I don't want to go down this path again!!
> Could anyone give suggestions as to the best direction
> - Low bandwidth
> - Simple to implement and maintain
> - Robust
> - Latest 'fashion'
> The data changes are primarily in one direction - client->HQ and of
> low volume say 5 edits per day. If the way to go is an XML file is
> there a mechanism for field based selection, or does it have to be the
> entire row?
> Appreciate any input,
> Thanks,
> casey
>
|||Wow,
Thanks Hilary, just what I was looking for, will explore this option!
Casey
On Apr 9, 10:26 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Publish that table in a transactional replication publication and create a
> local subscription and make sure you transaction retention is 1 day.
> Then issue a call to sp_browsereplcmds and you will get a list of procedure
> calls with parameters which you can email to the other SQL Server. If the
> procs are on the remote server you will be able to run these procedures
> there.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "casy" <mailke...@.gmail.com> wrote in message
> news:1176105883.833718.305870@.q75g2000hsh.googlegr oups.com...
>
>
>
>
>
> - Show quoted text -
|||The mouse is still warm...
New to tranactional replication so it will take a little time for me
to get up to speed. Having created a dummy set up as you suggested and
looked at the stored procedures my question is (before I invest some
serious time) ..
The main data source is -from- the client and installing full SQL
server is probably not an option on these numerous remote sites.
Is it possible/feasable to use Xpress on the client and set it up as a
transactional subscriber - maybe connect initially- and use the method
you outlined previously?
Pardon my ignorance if I am talking nonsense!
Casey
On Apr 10, 7:19 am, "casy" <mailke...@.gmail.com> wrote:
> Wow,
> Thanks Hilary, just what I was looking for, will explore this option!
> Casey
> On Apr 9, 10:26 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>
>
>
>
>
>
>
>
>
> - Show quoted text -