Database Compatibility Mode - when to change??

I have been migrating Databases from a SQL Server 7.0 Instance to a 2000 Instance. Basically the method I use is as follows:

a)Create a new Database on the Destination Server (same name as the Source) When I create the new Database on my destination server, the compatibility mode is '80' and the source is always '70'

b) do a 'revlogin' on the Source Server and use the output from that query to recreate the logins on the destination server

c) make sure that the default DB for the newly creates logins are correct and change them if necessary

d) Backup the DB on the Source Server and Restore it to the Destination Server.

e) Check login permissions and fix any orphaned users - usually I don't find any that need to be fixed, but I always check.

I've read BOL on Changing the compatibility mode on the Database... but I'm still unsure when I would have to do this and why?????? SHOULD I be changing the compatiblity mode from 80 to 70 when migrating Databases from 7.0 to 2000?? Any advice on moving DBs in this manner would be appreciated.sounds like you're doing it correctly. I always changed the compatability mode as the very last step...I know I've had an issue when I didn't change it - at one point, but it happened a very long time ago, and I can't remember the specifics - I think it had something to do with Quoted Identifiers...if you have procs that use double quotes instead of single quotes to identify text fields...it was pretty bizarre.|||Thanks much for the reply!

Database being set to SIMPLE Recovery (no MSDB)

My production seems to, either upon admin rebooting it for updates, or
for someother reason set the Recovermy Mode FROM Full TO Simple?
Weird. How can I track down what is doing this? its SQL 2000 SP4.
Thanks
Erik> My production seems to, either upon admin rebooting it for updates, or
> for someother reason set the Recovermy Mode FROM Full TO Simple?
> Weird. How can I track down what is doing this? its SQL 2000 SP4.
Use the Profiler - track ALTER DATABASE yourdb SET RECOVERY command.
--
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message

Database being set to SIMPLE Recovery (no MSDB)

My production seems to, either upon admin rebooting it for updates, or
for someother reason set the Recovermy Mode FROM Full TO Simple?
Weird. How can I track down what is doing this? its SQL 2000 SP4.
Thanks
Erik
> My production seems to, either upon admin rebooting it for updates, or
> for someother reason set the Recovermy Mode FROM Full TO Simple?
> Weird. How can I track down what is doing this? its SQL 2000 SP4.
Use the Profiler - track ALTER DATABASE yourdb SET RECOVERY command.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message

Database being set to SIMPLE Recovery (no MSDB)

My production seems to, either upon admin rebooting it for updates, or
for someother reason set the Recovermy Mode FROM Full TO Simple?
Weird. How can I track down what is doing this? its SQL 2000 SP4.
Thanks
Erik> My production seems to, either upon admin rebooting it for updates, or
> for someother reason set the Recovermy Mode FROM Full TO Simple?
> Weird. How can I track down what is doing this? its SQL 2000 SP4.
Use the Profiler - track ALTER DATABASE yourdb SET RECOVERY command.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message

Database automatically resetting to Single User Mode frequently

The problem was with the SQL server that authenticates
users' access to the Intranet. For some reason, the SQL
server put itself in single-use mode, which prevents users
from accessing the database- hence denying logon. This
happens once every few months- for reasons we cannot
explain. This time, it also looked like the SQL server had
depleted resources. I rebooted the server and everything
came back immediately.
Frequency - Once in 10 days or a week.
Any Idea why this is happening?
Thanks.Perhaps the main plan? Remove the option to "fix minor problems", this is the cause. And if this is
your problem, make sure you are current on service pack.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Scott London" <anonymous@.discussions.microsoft.com> wrote in message
news:0afc01c3996f$7d736540$a401280a@.phx.gbl...
> The problem was with the SQL server that authenticates
> users' access to the Intranet. For some reason, the SQL
> server put itself in single-use mode, which prevents users
> from accessing the database- hence denying logon. This
> happens once every few months- for reasons we cannot
> explain. This time, it also looked like the SQL server had
> depleted resources. I rebooted the server and everything
> came back immediately.
> Frequency - Once in 10 days or a week.
>
> Any Idea why this is happening?
> Thanks.|||Scott,
I would bet you have a job or process that sets the db to
single user mode temporarily in order to perform some
function. If that job fails then the db may be left in
single user mode until the server is restarted. Check
the sql error logs and the windows application log around
the times this happens and see if you can find any clues.
Sincerely,
Invotion Engineering Team
Advanced Microsoft Hosting Solutions
http://www.Invotion.com
>--Original Message--
>The problem was with the SQL server that authenticates
>users' access to the Intranet. For some reason, the SQL
>server put itself in single-use mode, which prevents
users
>from accessing the database- hence denying logon. This
>happens once every few months- for reasons we cannot
>explain. This time, it also looked like the SQL server
had
>depleted resources. I rebooted the server and everything
>came back immediately.
>Frequency - Once in 10 days or a week.
>
>Any Idea why this is happening?
>Thanks.
>.
>|||Tibor,
a recent SP eliminates that problem?
Quentin
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eg6riEXmDHA.360@.TK2MSFTNGP12.phx.gbl...
> Perhaps the main plan? Remove the option to "fix minor problems", this is
the cause. And if this is
> your problem, make sure you are current on service pack.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Scott London" <anonymous@.discussions.microsoft.com> wrote in message
> news:0afc01c3996f$7d736540$a401280a@.phx.gbl...
> > The problem was with the SQL server that authenticates
> > users' access to the Intranet. For some reason, the SQL
> > server put itself in single-use mode, which prevents users
> > from accessing the database- hence denying logon. This
> > happens once every few months- for reasons we cannot
> > explain. This time, it also looked like the SQL server had
> > depleted resources. I rebooted the server and everything
> > came back immediately.
> >
> > Frequency - Once in 10 days or a week.
> >
> >
> > Any Idea why this is happening?
> >
> > Thanks.
>|||Yep. I think the problem occurred with SQL7, and fixed in sp3. Not 100% sure though. Note that
removing the fix option (which is a bad option in the first place) eliminates this problem.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Quentin Ran" <ab@.who.com> wrote in message news:uBVlkbXmDHA.1408@.TK2MSFTNGP11.phx.gbl...
> Tibor,
> a recent SP eliminates that problem?
> Quentin
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:eg6riEXmDHA.360@.TK2MSFTNGP12.phx.gbl...
> > Perhaps the main plan? Remove the option to "fix minor problems", this is
> the cause. And if this is
> > your problem, make sure you are current on service pack.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Scott London" <anonymous@.discussions.microsoft.com> wrote in message
> > news:0afc01c3996f$7d736540$a401280a@.phx.gbl...
> > > The problem was with the SQL server that authenticates
> > > users' access to the Intranet. For some reason, the SQL
> > > server put itself in single-use mode, which prevents users
> > > from accessing the database- hence denying logon. This
> > > happens once every few months- for reasons we cannot
> > > explain. This time, it also looked like the SQL server had
> > > depleted resources. I rebooted the server and everything
> > > came back immediately.
> > >
> > > Frequency - Once in 10 days or a week.
> > >
> > >
> > > Any Idea why this is happening?
> > >
> > > Thanks.
> >
> >
>

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