Best techniques for databse creation ?

Dear Experts,
Any one knows any link regarding best practices for schema and database crea
tion in SQL server 2000 in detail .
Faheem Latif
NETWORK SOLUTIONFaheem,
What specifically are you looking for? There are many things on the web if
you search google. However I recommend these links as a start:
SQL Server 2000 Operations Guide
http://www.microsoft.com/technet/pr...in/sqlops0.mspx
Vyas's article on Best Practices
http://www.sql-server-performance.c...t_practices.asp
Microsoft SQL Server 2000 Best Practices Analyzer 1.0 Beta
E07339C1F22&displaylang=en" target="_blank">http://www.microsoft.com/downloads/...&displaylang=en
Tinyurl: http://tinyurl.com/upzi
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Faheem" <anonymous@.discussions.microsoft.com> wrote in message
news:189C9EC5-9492-4B34-A0D7-C9908D15BA0E@.microsoft.com...
> Dear Experts,
> Any one knows any link regarding best practices for schema and database
creation in SQL server 2000 in detail .
> Faheem Latif
> NETWORK SOLUTION
>

Best techniques for databse creation ?

Dear Experts
Any one knows any link regarding best practices for schema and database creation in SQL server 2000 in detail
Faheem Latif
NETWORK SOLUTIONFaheem,
What specifically are you looking for? There are many things on the web if
you search google. However I recommend these links as a start:
SQL Server 2000 Operations Guide
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops0.mspx
Vyas's article on Best Practices
http://www.sql-server-performance.com/vk_sql_best_practices.asp
Microsoft SQL Server 2000 Best Practices Analyzer 1.0 Beta
http://www.microsoft.com/downloads/details.aspx?FamilyId=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
Tinyurl: http://tinyurl.com/upzi
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Faheem" <anonymous@.discussions.microsoft.com> wrote in message
news:189C9EC5-9492-4B34-A0D7-C9908D15BA0E@.microsoft.com...
> Dear Experts,
> Any one knows any link regarding best practices for schema and database
creation in SQL server 2000 in detail .
> Faheem Latif
> NETWORK SOLUTION
>

Best techniques for databse creation ?

Dear Experts,
Any one knows any link regarding best practices for schema and database creation in SQL server 2000 in detail .
Faheem Latif
NETWORK SOLUTION
Faheem,
What specifically are you looking for? There are many things on the web if
you search google. However I recommend these links as a start:
SQL Server 2000 Operations Guide
http://www.microsoft.com/technet/pro...n/sqlops0.mspx
Vyas's article on Best Practices
http://www.sql-server-performance.co..._practices.asp
Microsoft SQL Server 2000 Best Practices Analyzer 1.0 Beta
http://www.microsoft.com/downloads/d...displaylang=en
Tinyurl: http://tinyurl.com/upzi
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Faheem" <anonymous@.discussions.microsoft.com> wrote in message
news:189C9EC5-9492-4B34-A0D7-C9908D15BA0E@.microsoft.com...
> Dear Experts,
> Any one knows any link regarding best practices for schema and database
creation in SQL server 2000 in detail .
> Faheem Latif
> NETWORK SOLUTION
>

Best technique for Replication

thx Christian and Paul fro previous answers!
I have a SQL2000 server that hosts our accounting database and I want to
replicate it to another SQL2000 server so I can write reports etc, and do not
want to use the production server for these tasks. I tried to setup a
"snapshot" and was somewhat successful. My question is would it be better to
do a snapshot or transactional replication? The snapshot seemed to take along
time to run. I also do log backups every 15 minutes.
ps Paul I ordered your book...hope it will help also.
Rick Rushing
System Administrator
SQL Newbie
D & J Construction
Rick,
snapshot is sometimes OK but it'll lock the tables and you can't really do
this regularly, and unless all your data is changing it is overkill.
Transactional would be more appropriate. Log shipping is sometimes used, but
you have to be out of the standby server when the log is restored, which in
most cases rules it out.
Rgds,
Paul Ibison, SQL Server MVP
BTW the only book I've written is my address book - Hilary's the man for
replication books and it's a very useful resource.
|||Paul,
There will be about 20 users on the SQL production server and only myself
using the other SQL server. My goal is to find a way to have a copy of the
database on the production server to the other server for use in writing
reports etc. So you think transaction would be a better approach?
ps you are right..it was Hilary's bok on replication.
Rick Rushing
System Administrator
D & J Construction
"Paul Ibison" wrote:

> Rick,
> snapshot is sometimes OK but it'll lock the tables and you can't really do
> this regularly, and unless all your data is changing it is overkill.
> Transactional would be more appropriate. Log shipping is sometimes used, but
> you have to be out of the standby server when the log is restored, which in
> most cases rules it out.
> Rgds,
> Paul Ibison, SQL Server MVP
> BTW the only book I've written is my address book - Hilary's the man for
> replication books and it's a very useful resource.
>
>
|||Rick - yes this is quite a common approach, and gives you the control to
decide exactly which tables are selected. BTW the Database snapshots in SQL
Server 2005 will offer an interesting alternative.
Rgds,
Paul Ibison
|||Thanks Paul
I will pursue using transactional when my book arrives. We just installed
these SQL servers back in October so it will be a while before we upgrade.
thanks again for the input.
Rick Rushing
System Administrator
D & J Construction
"Paul Ibison" wrote:

> Rick - yes this is quite a common approach, and gives you the control to
> decide exactly which tables are selected. BTW the Database snapshots in SQL
> Server 2005 will offer an interesting alternative.
> Rgds,
> Paul Ibison
>
>

Best strategy? How to filter Report results

Hi,

I’m using SQL Server 2000.

I need some help on what strategy to adopt in order to filter some existing reports that I have written. I’m basically a web developer (ASP.NET) with some ? T-SQL skills (therefore I need advice on which is the best strategy please).

The business system I’m working on has 2 levels of access: Administrators (there can be several) and Users (20,000+).

Users can login and do stuff (do a task).

Administrators can login and give tasks to the Users to do. Administrators can also run reports to see the status of the Users relating to a particular task. A report on task X will show a list of all Users who have been given that same task (example of a task: “attend to a conference”) and next to each User will be the status (e.g. “Completed” or “Not yet completed”).

That is the existing system (above). Now I need to add a new type of person who can login and view the same reports as the administrator. This type of person will be called a “Manager”. The difference is that the Manager will only be able to view information on a defined set of Users. There might be a few hundred “Managers”.

So my “Wizard web page” code that allows an Administrator to create a Manager account will allow the Administrator to select (from a list of Users) which Users this Manager is allowed to see.

This means I’m going to have a new TABLE to relate the Manager to the User. I’ll call this the tblReporterUser table.

This table will have a ReporterID and UserID (read strategy #1 for reason this is not called “ManagerID”).

Here are the strategies I can think of. Which is best or is there one even better?

Strategy #1

As well as filling the tblReporterUser with Manager to User relationships, also fill it will Administrator to User relationships.

This means that when I call the Stored Procedure to select the rows of my report I need to pass in the ReporterID (which will actually be a ManagerID if it’s a Manager running the report, or an AdministratorID if it is an Administrator).

In order to filter Users out of the report I would then use this ReporterID to JOIN against the tblReporterUser TABLE.

Disadvantages: Each time Users are added to the system I have to make sure all administrators get permission to view the new User (add a row to tblReporterUser TABLE for each administrator with the new UserID).

Also it might make reporting for administrators slower?

Strategy #2

Explained with some pseudo-code (I don’t like this because of the “repeated code” as its easy to get into maintenance problems):

If (RoleIsAdministrator) THEN

-- role is Administrator

Existing report code

ELSE

-- role is Manager

Existing report code

JOIN on the tblReporterUser using the ManagerID as the ReporterID

ENDIF

Strategy #3

Is there a way to do a “conditional” JOIN ON the tblReporterUser if the “role” is Manager (but don’t do the JOIN if the role is Administrator).

If there is a way then this sounds like the best solution.

I have no idea how to do a conditional join – could someone give me an example please?

Massive thanks in advance to anyone replying with any amount of help.

Cheers,

Just back from 5 days camping. It rained! :D But in Australia that is a good thing!!!!

I guess this is a *BUMP* for this thread. If anyone has any ideas about "conditional JOINs" please help.

Many thanks,

Chiz

sql

Best Strategy to backup the system db

Hi,
I am trying to approach method to copy my system database using enterprise
tool. I have researched on Database Mainteance tool and Backup database. I
would like to use one of the method to copy my database without any
transcational log since we don't have any transcational going on and not
growing my database too much.
how would i accomplish this?
any comments would be appreciate
Pooja,
You might want to clarify whether or not this is a system database i.e.,
Master, MSDB, etc... or a user-defined database. When you say "my" database
I'm going to assume it is a user-defined database. If you do not require
the transaction logs to be backed up as part of your database recovery
strategy, you can set the recovery mode for the database to SIMPLE (the
t-log will be automatically truncated). Then schedule your full database
backup as a job or use a Maintenance Plan.
HTH
Jerry
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:8F05EF72-186D-4B93-BA20-E81364824F29@.microsoft.com...
> Hi,
> I am trying to approach method to copy my system database using enterprise
> tool. I have researched on Database Mainteance tool and Backup database. I
> would like to use one of the method to copy my database without any
> transcational log since we don't have any transcational going on and not
> growing my database too much.
> how would i accomplish this?
> any comments would be appreciate

Best Strategy to backup the system db

Hi,
I am trying to approach method to copy my system database using enterprise
tool. I have researched on Database Mainteance tool and Backup database. I
would like to use one of the method to copy my database without any
transcational log since we don't have any transcational going on and not
growing my database too much.
how would i accomplish this?
any comments would be appreciatePooja,
You might want to clarify whether or not this is a system database i.e.,
Master, MSDB, etc... or a user-defined database. When you say "my" database
I'm going to assume it is a user-defined database. If you do not require
the transaction logs to be backed up as part of your database recovery
strategy, you can set the recovery mode for the database to SIMPLE (the
t-log will be automatically truncated). Then schedule your full database
backup as a job or use a Maintenance Plan.
HTH
Jerry
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:8F05EF72-186D-4B93-BA20-E81364824F29@.microsoft.com...
> Hi,
> I am trying to approach method to copy my system database using enterprise
> tool. I have researched on Database Mainteance tool and Backup database. I
> would like to use one of the method to copy my database without any
> transcational log since we don't have any transcational going on and not
> growing my database too much.
> how would i accomplish this?
> any comments would be appreciate