Database Documentation

If someone can provide a good guideline for finding
Database centric documentation, which would include naming
convention, Design & Pattern, Process & Procedure and such.
This guide would be something like MSF, but with more
details in planning, implementation, development,
deployment, maintenance, and troubleshooting.
Thanks in advance.from the Operational side , there is some good stuff in the SQL Ops guide as
per below
http://support.microsoft.com/defaul...kb;en-us;829024
also below, which is based around MSF anyway
http://www.microsoft.com/resources/...se/default.mspx
cheers,
Andy.
"TO" <anonymous@.discussions.microsoft.com> wrote in message
news:143001c48c3a$6214ab10$a601280a@.phx.gbl...
> If someone can provide a good guideline for finding
> Database centric documentation, which would include naming
> convention, Design & Pattern, Process & Procedure and such.
> This guide would be something like MSF, but with more
> details in planning, implementation, development,
> deployment, maintenance, and troubleshooting.
> Thanks in advance.

Database Documentation

If someone can provide a good guideline for finding
Database centric documentation, which would include naming
convention, Design & Pattern, Process & Procedure and such.
This guide would be something like MSF, but with more
details in planning, implementation, development,
deployment, maintenance, and troubleshooting.
Thanks in advance.from the Operational side , there is some good stuff in the SQL Ops guide as
per below
http://support.microsoft.com/default.aspx?scid=kb;en-us;829024
also below, which is based around MSF anyway
http://www.microsoft.com/resources/practices/database/default.mspx
cheers,
Andy.
"TO" <anonymous@.discussions.microsoft.com> wrote in message
news:143001c48c3a$6214ab10$a601280a@.phx.gbl...
> If someone can provide a good guideline for finding
> Database centric documentation, which would include naming
> convention, Design & Pattern, Process & Procedure and such.
> This guide would be something like MSF, but with more
> details in planning, implementation, development,
> deployment, maintenance, and troubleshooting.
> Thanks in advance.

Database Documentation

If someone can provide a good guideline for finding
Database centric documentation, which would include naming
convention, Design & Pattern, Process & Procedure and such.
This guide would be something like MSF, but with more
details in planning, implementation, development,
deployment, maintenance, and troubleshooting.
Thanks in advance.
from the Operational side , there is some good stuff in the SQL Ops guide as
per below
http://support.microsoft.com/default...b;en-us;829024
also below, which is based around MSF anyway
http://www.microsoft.com/resources/p...e/default.mspx
cheers,
Andy.
"TO" <anonymous@.discussions.microsoft.com> wrote in message
news:143001c48c3a$6214ab10$a601280a@.phx.gbl...
> If someone can provide a good guideline for finding
> Database centric documentation, which would include naming
> convention, Design & Pattern, Process & Procedure and such.
> This guide would be something like MSF, but with more
> details in planning, implementation, development,
> deployment, maintenance, and troubleshooting.
> Thanks in advance.

Database Design Standards

Well i've been given a big job of copying all the databases from an old
server to a new server. In order to provide better security, availabilty,
performance.
My servers are in a DMZ, so i have to use remote desktop/terminal services
to connect to it.
1. I have two logical partitions in the server. Is it a good practice to
store the OS and SQL server software itself on C:\ and all the data on
d:\??(Will it help me in anyways to achieve better performance? Can i
make separate directories for each databaseon d:\. and further on extending
it to sub directories for data and log files?
2. Should i copy all objects such as logins, DB plans, jobs etc. from the
old server or is it a better a practice to start all the plans over (create
new plans) to achieve better results and only copy the databases?
3. What is a good strategy for backup plans? For Log Files? For Primary
Files?
4. How to come up with a good Disaster Recovery Plan? What are all the
things you need to have in order to create a good DR plan? what is a good
way to test it?
5. What is the best way to secure SQL server? Who should have what
access? Which people should have access to the server itself? And how can
i give people read only access to the databases if they have access to the
server? Do they even need access to the server? How can they only have
read access to the SQL server databases? What tools do i need? Since i
have to use remote desktop to conncet to the servers, how can i give my
clients that just want read access to the all the data files including log
files? What do they need installed / or use in order to achieve this?
6. Is there any way you can come up with Roles scheme for certain users?
Lets say a particular group of users should have a certain permissions? Can
we create a something like that? that need to be done on the OS level
rather than SQL level.?
I know this is asking for a lot, but its really important to me, your
valuable knowledge on all this issues would be much much appreciated?
Thank you guys very much
NO HELP So far
"Shash Goyal" <Shash703@.gmail.com> wrote in message
news:ejQCy58tEHA.348@.tk2msftngp13.phx.gbl...
> Well i've been given a big job of copying all the databases from an old
> server to a new server. In order to provide better security, availabilty,
> performance.
> My servers are in a DMZ, so i have to use remote desktop/terminal services
> to connect to it.
> 1. I have two logical partitions in the server. Is it a good practice to
> store the OS and SQL server software itself on C:\ and all the data on
> d:\??(Will it help me in anyways to achieve better performance? Can i
> make separate directories for each databaseon d:\. and further on
extending
> it to sub directories for data and log files?
> 2. Should i copy all objects such as logins, DB plans, jobs etc. from
the
> old server or is it a better a practice to start all the plans over
(create
> new plans) to achieve better results and only copy the databases?
> 3. What is a good strategy for backup plans? For Log Files? For Primary
> Files?
> 4. How to come up with a good Disaster Recovery Plan? What are all the
> things you need to have in order to create a good DR plan? what is a
good
> way to test it?
> 5. What is the best way to secure SQL server? Who should have what
> access? Which people should have access to the server itself? And how
can
> i give people read only access to the databases if they have access to the
> server? Do they even need access to the server? How can they only
have
> read access to the SQL server databases? What tools do i need? Since i
> have to use remote desktop to conncet to the servers, how can i give my
> clients that just want read access to the all the data files including log
> files? What do they need installed / or use in order to achieve this?
> 6. Is there any way you can come up with Roles scheme for certain users?
> Lets say a particular group of users should have a certain permissions?
Can
> we create a something like that? that need to be done on the OS level
> rather than SQL level.?
> I know this is asking for a lot, but its really important to me, your
> valuable knowledge on all this issues would be much much appreciated?
> Thank you guys very much
>
|||On Sun, 24 Oct 2004 23:45:32 -0500, Shash Goyal wrote:

>NO HELP So far
Hi Shash,
There are everal replies in some of the other groups wherre you posted
this same question. I recommend you follow up on those replies instead of
complaining about lack of replies in this grooup.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||hi,
"Shash Goyal" <Shash703@.gmail.com> ha scritto nel messaggio
news:ejQCy58tEHA.348@.tk2msftngp13.phx.gbl
big deal, isn't it? =;-D

> Well i've been given a big job of copying all the databases from an
> old server to a new server. In order to provide better security,
> availabilty, performance.
> My servers are in a DMZ, so i have to use remote desktop/terminal
> services to connect to it.
> 1. I have two logical partitions in the server. Is it a good
> practice to store the OS and SQL server software itself on C:\ and
> all the data on d:\??(Will it help me in anyways to achieve better
> performance? Can i make separate directories for each databaseon
> d:\. and further on extending it to sub directories for data and log
> files?
I usually go this way on desktops... just for a schema order where data is
not directly bound and mixed with exes...
you do not gain better performance as drive and controller are the same..
perhaps you could get worser performance as the disk headers will have more
work... but for sure you do not gain benefits this way..
you are not granted against disk failures as one of them wil probably trash
exes and data..
again.. I do it myself the same way.. but only for a schema mapping order,
not for physical matters..
to achieve better througoutput performance, you should go for separate
controllers and drives... RAID solutions as well for improving security...
further basic info about RAID levels at http://www.acnc.com/04_00.html
as regard te second part of the question, yes, you can... no problem with
that..

> 2. Should i copy all objects such as logins, DB plans, jobs etc.
> from the old server or is it a better a practice to start all the
> plans over (create new plans) to achieve better results and only copy
> the databases?
I do not think you'll get problems copying all original objects to the new
server... I see non penalties in that... yo could just get orphaned objects
if your original server is not clean, but I do not see problems at all..

> 3. What is a good strategy for backup plans? For Log Files? For
> Primary Files?
hey, this is trickie... you have to think about your own needs... there' no
one size fits all strategy... depending on your needs you could be happy
with a dayly full backup for a little database, even a weekly full backup
for a quite read-only database..
if you are subscriber at SQL Server Magazine, you can have a look at good
articles like
http://www.windowsitpro.com/Article/...647/39647.html
http://www.windowsitpro.com/Article/...657/39657.html
this is free to read,
http://www.winnetmag.com/Article/Art...915/25915.html , for Very Large
Databases secenarios, with full, differential an log backup features
explanations..
again... it all depends on your data, your needs, your shadow time limits...
http://www.winnetmag.com/Article/Art...340/24340.html is free too...
by "Notre Dame SQL Server", Kalen Delaney

> 4. How to come up with a good Disaster Recovery Plan? What are all
> the things you need to have in order to create a good DR plan? what
> is a good way to test it?
same as above... plus add OS, cluster settings, applications CD to the
recovery schema...
a disaster can strike you down to your knees, so you'll have all (really
all) to start a new database server, application server, web server, farm
and all..
so, to your standard backups tapes, you should add all the CDs to install
and ugrade to your current service pack levels your OS and applications..
and please do not store them on the main server top =;-D
you should consider how important is your data and how you want to protect
all your IT scenario.. you can perhaps maintain a local copy and dayly ship
a backup copy to your bank or to specialised storing company..
it's a matter of costs, requirements and so on...

> 5. What is the best way to secure SQL server? Who should have what
> access? Which people should have access to the server itself? And
> how can i give people read only access to the databases if they have
> access to the server? Do they even need access to the server?
> How can they only have read access to the SQL server databases?
> What tools do i need? Since i have to use remote desktop to conncet
> to the servers, how can i give my clients that just want read access
> to the all the data files including log files? What do they need
> installed / or use in order to achieve this?
only users needing access to the data should be given right to access the
database server.. and only to the databases they need to access, with the
minimal privileges they need for theyr required activities..
the tool you need to implement security depends on your skill... Query
Analyzer could be enought to execute GRANT/DENY DCL statements to database
objects, where sp_addlogin/sp_grantlogin are required for server accces..
and again, give them only access (via sp_adduser) to the database they need
to access..
there's no readonly privilege to the database.. you are legitimated to
access it or not... you can give readonly privileges to tables and views
objects granting SELECT only privileges to some or all of them, but my
preferred access schema is via GRANT EXECUTE to
retrival/insert/update/delete stored procedures only and DENY all privileges
on table objects.. you can give them access to view object if needed..
a good starting point about secuirty and some best practice can be found at
http://www.sql-server-performance.co...l_security.asp , as long as
http://www.microsoft.com/technet/pro.../sp3sec03.mspx
...
you users do not need access to the physical files of your databases, nor do
they need access to the log files at all.. your database are only logical
matters for them, and this should remain that way..
they only need the application(s) they use to interact with data, both
windowsform or web interfaces, and, usually MDAC, in order to provide the
connectivity tools and SQL Server drivers.. not more, not less.. standar
users do not need acces to Enterprise Manager and/or Query Analyzer.. give
them only what they really need.. not more.. better less =;-D

> 6. Is there any way you can come up with Roles scheme for certain
> users? Lets say a particular group of users should have a certain
> permissions? Can we create a something like that? that need to be
> done on the OS level rather than SQL level.?
this is a best practice and standard idea.. instead of maintaining
thousounds of users privileges, create at database level different roles..
make each user part of the corresponding role and manage privileges at role
level... you will not become insane and you can achieve the granularity you
usually need..
roles have to be managed at database level, and are known as user defined
database roles... not at the OS level... roles at OS level can be usefull,
but you have to deal with database and related security...
have a look at
http://msdn.microsoft.com/library/de..._addp_33s5.asp

> I know this is asking for a lot, but its really important to me, your
> valuable knowledge on all this issues would be much much appreciated?
do you mean you don't know if our meanings will be appreciated? ROTFL
you are right... no one but you knows better your own needs ... I can only
give you some hints based on my own experience and based on my (poor) skill,
but you have the key of your success in this area..
good luck
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thank you very much Andrea!!!
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2u6on1F26o6hvU1@.uni-berlin.de...
> hi,
> "Shash Goyal" <Shash703@.gmail.com> ha scritto nel messaggio
> news:ejQCy58tEHA.348@.tk2msftngp13.phx.gbl
> big deal, isn't it? =;-D
>
> I usually go this way on desktops... just for a schema order where data is
> not directly bound and mixed with exes...
> you do not gain better performance as drive and controller are the same..
> perhaps you could get worser performance as the disk headers will have
more
> work... but for sure you do not gain benefits this way..
> you are not granted against disk failures as one of them wil probably
trash
> exes and data..
> again.. I do it myself the same way.. but only for a schema mapping order,
> not for physical matters..
> to achieve better througoutput performance, you should go for separate
> controllers and drives... RAID solutions as well for improving security...
> further basic info about RAID levels at http://www.acnc.com/04_00.html
> as regard te second part of the question, yes, you can... no problem with
> that..
>
> I do not think you'll get problems copying all original objects to the new
> server... I see non penalties in that... yo could just get orphaned
objects
> if your original server is not clean, but I do not see problems at all..
>
> hey, this is trickie... you have to think about your own needs... there'
no
> one size fits all strategy... depending on your needs you could be happy
> with a dayly full backup for a little database, even a weekly full backup
> for a quite read-only database..
> if you are subscriber at SQL Server Magazine, you can have a look at good
> articles like
> http://www.windowsitpro.com/Article/...647/39647.html
> http://www.windowsitpro.com/Article/...657/39657.html
> this is free to read,
> http://www.winnetmag.com/Article/Art...915/25915.html , for Very
Large
> Databases secenarios, with full, differential an log backup features
> explanations..
> again... it all depends on your data, your needs, your shadow time
limits...
> http://www.winnetmag.com/Article/Art...340/24340.html is free too...
> by "Notre Dame SQL Server", Kalen Delaney
>
> same as above... plus add OS, cluster settings, applications CD to the
> recovery schema...
> a disaster can strike you down to your knees, so you'll have all (really
> all) to start a new database server, application server, web server, farm
> and all..
> so, to your standard backups tapes, you should add all the CDs to install
> and ugrade to your current service pack levels your OS and applications..
> and please do not store them on the main server top =;-D
> you should consider how important is your data and how you want to protect
> all your IT scenario.. you can perhaps maintain a local copy and dayly
ship
> a backup copy to your bank or to specialised storing company..
> it's a matter of costs, requirements and so on...
>
> only users needing access to the data should be given right to access the
> database server.. and only to the databases they need to access, with the
> minimal privileges they need for theyr required activities..
> the tool you need to implement security depends on your skill... Query
> Analyzer could be enought to execute GRANT/DENY DCL statements to database
> objects, where sp_addlogin/sp_grantlogin are required for server accces..
> and again, give them only access (via sp_adduser) to the database they
need
> to access..
> there's no readonly privilege to the database.. you are legitimated to
> access it or not... you can give readonly privileges to tables and views
> objects granting SELECT only privileges to some or all of them, but my
> preferred access schema is via GRANT EXECUTE to
> retrival/insert/update/delete stored procedures only and DENY all
privileges
> on table objects.. you can give them access to view object if needed..
> a good starting point about secuirty and some best practice can be found
at
> http://www.sql-server-performance.co...l_security.asp , as long as
>
http://www.microsoft.com/technet/pro.../sp3sec03.mspx
> ..
> you users do not need access to the physical files of your databases, nor
do
> they need access to the log files at all.. your database are only logical
> matters for them, and this should remain that way..
> they only need the application(s) they use to interact with data, both
> windowsform or web interfaces, and, usually MDAC, in order to provide the
> connectivity tools and SQL Server drivers.. not more, not less.. standar
> users do not need acces to Enterprise Manager and/or Query Analyzer.. give
> them only what they really need.. not more.. better less =;-D
>
> this is a best practice and standard idea.. instead of maintaining
> thousounds of users privileges, create at database level different roles..
> make each user part of the corresponding role and manage privileges at
role
> level... you will not become insane and you can achieve the granularity
you
> usually need..
> roles have to be managed at database level, and are known as user defined
> database roles... not at the OS level... roles at OS level can be usefull,
> but you have to deal with database and related security...
> have a look at
>
http://msdn.microsoft.com/library/de..._addp_33s5.asp
>
> do you mean you don't know if our meanings will be appreciated? ROTFL
> you are right... no one but you knows better your own needs ... I can only
> give you some hints based on my own experience and based on my (poor)
skill,
> but you have the key of your success in this area..
> good luck
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

database design question

hi, I'm stuck with database design, and come to know of this forum, hope the experts here can provide me an advice.

I'm trying to design a database, to store the following type of information.

- Person (name, identity no...)
- Phone (phone type (mobile, residential, fax), number
- Address (street, country...)
- Person and phone relationship. A person can have 0 or more Phone, and the relationship can be user or subscriber.
- Person and address relationship. A person can live in 0 or more address, and the relationship can be occupants, owner...
- Phone and address relationship. A phone can be have 0 or more address, and the relationshp is installation address, billing address

My design forms a circle, person - person_phone- phone - phoneAddress - Address - personAddress (-back to person). Is this a bad design, if it is, any solution to overcome?I see nothing wrong with the circular design, since the intermediary table in your many-to-many relationships will prevent relational integrity issues cascading around your design.

A few questions, though...

Can a phone have more than one User?
Can a phone have more than one Subscriber?
Can a phone have more than one Installation Address?
Can a phone have more than one Billing Address?
Are these the only two types of Phone addresses, or are there potentially an unlimited number of address types for phones?

blindman|||thanks blindman for the assurance... coz I'm rather confuse, as I'm not sure will there be any hidden problems with this type of design.

1. Can a phone have more than one User? yes
2. Can a phone have more than one Subscriber? at any one time, no... but the number can be re-use (eg, the first subscriber unsubscribe, and another one subscribe to it)
3. Can a phone have more than one Installation Address? similar to 2
4. Can a phone have more than one Billing Address? similar to 2
5. Are these the only two types of Phone addresses, or are there potentially an unlimited number of address types for phones? might have more, but not identified as of now.

will this question affect my design?|||There are some alternative designs for this type of modeling, but it looks to me like you are going to require all the flexibility in the circular schema you have. That just means you will need to store more data-integrity logic in the procedures you write for inserting, updating, and deleting records.

See if anybody else has comments on this over the weekend. Otherwise, go with it.

blindman

Database design for College project

Hi,

I am very new to this form and I need urgent help and I hope someone can provide forme some light to my problem.

The college has a soccer team. And the application I am going to develope needs to use MS SQL Server 2000 Developer or Student edition.

I only mange to create these below table and I am not sure if this is the correct way to doing it.

The tables name:

tbl_TEAM: ID, StudentNumber, Name, Surname, Active
tbl_LEAGUE: ID, LeagueName, Active
tbl_WEEKS: ID, WeeksName, Active
tbl_FIXTURE: GameID, HomeTeam, VisitorTeam, Active
tbl_REFFERE: ReferreID, ReffereName, ReffereSurname, Active
tbl_OBSERVER: ObserverID, ObserverName, ObserverSurname, Active

The apliccation will create a fixture via user and then must assign reffere to each game. After the game the observer sends in the game report and user will use the report and assign the points for reffere.

Then Reffere sends the their reports and the application will assign the Goal, yellow card and Red card points to team member.

If any team member recevice 4 yellow card in total and not in one game then application will producea report to say this member team reach the point and have to sit one week and makes his yellow card points to zero. But in the end I have to know how much each team member had yellow card or red car in tottal.

If team member has goal he get 1 point and must show where is the goal. Because sometime they can make mistake and they have goal to their own teams net.

I am not sure if I can explaind correctly so I need help to develope the application and I am very new to soccer game as well as I don't know how to design the database.

Can anyone help me out to over come my SQL problem so I can finish my project before this christmas.

Thank you in advance.

Kind Regards,
NiyaziYou have not explained what your SQL problem is.

Your table design looks ok. But what is a "Fixture"?

If you want some help with SQL, post the code you have come up with so far and explain the errors or odd behavior you are getting. Do NOT expect us to do your homework for you.|||When creating tables, think about (and list out) all the objects you are trying to model (referees, games, players, etc.). These will likely end up as your tables.

Database Design

Well i've been given a big job of copying all the databases from an old
server to a new server. In order to provide better security, availabilty,
performance.
My servers are in a DMZ, so i have to use remote desktop/terminal services
to connect to it.
1. I have two logical partitions in the server. Is it a good practice to
store the OS and SQL server software itself on C:\ and all the data on
d:\??(Will it help me in anyways to achieve better performance? Can i
make separate directories for each databaseon d:\. and further on extending
it to sub directories for data and log files?
2. Should i copy all objects such as logins, DB plans, jobs etc. from the
old server or is it a better a practice to start all the plans over (create
new plans) to achieve better results and only copy the databases?
3. What is a good strategy for backup plans? For Log Files? For Primary
Files?
4. How to come up with a good Disaster Recovery Plan? What are all the
things you need to have in order to create a good DR plan? what is a good
way to test it?
5. What is the best way to secure SQL server? Who should have what
access? Which people should have access to the server itself? And how can
i give people read only access to the databases if they have access to the
server? Do they even need access to the server? How can they only have
read access to the SQL server databases? What tools do i need? Since i
have to use remote desktop to conncet to the servers, how can i give my
clients that just want read access to the all the data files including log
files? What do they need installed / or use in order to achieve this?
6. Is there any way you can come up with Roles scheme for certain users?
Lets say a particular group of users should have a certain permissions? Can
we create a something like that? that need to be done on the OS level
rather than SQL level.?
I know this is asking for a lot, but its really important to me, your
valuable knowledge on all this issues would be much much appreciated?
Thank you guys very much
I suggest you to start with SQL 2000 Operations Guide -
http://www.microsoft.com/technet/pro.../sqlops0.mspx.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Shash Goyal" <Shash703@.gmail.com> wrote in message
news:OdySK88tEHA.2128@.TK2MSFTNGP11.phx.gbl...
> Well i've been given a big job of copying all the databases from an old
> server to a new server. In order to provide better security, availabilty,
> performance.
> My servers are in a DMZ, so i have to use remote desktop/terminal services
> to connect to it.
> 1. I have two logical partitions in the server. Is it a good practice to
> store the OS and SQL server software itself on C:\ and all the data on
> d:\??(Will it help me in anyways to achieve better performance? Can i
> make separate directories for each databaseon d:\. and further on
extending
> it to sub directories for data and log files?
> 2. Should i copy all objects such as logins, DB plans, jobs etc. from
the
> old server or is it a better a practice to start all the plans over
(create
> new plans) to achieve better results and only copy the databases?
> 3. What is a good strategy for backup plans? For Log Files? For Primary
> Files?
> 4. How to come up with a good Disaster Recovery Plan? What are all the
> things you need to have in order to create a good DR plan? what is a
good
> way to test it?
> 5. What is the best way to secure SQL server? Who should have what
> access? Which people should have access to the server itself? And how
can
> i give people read only access to the databases if they have access to the
> server? Do they even need access to the server? How can they only
have
> read access to the SQL server databases? What tools do i need? Since i
> have to use remote desktop to conncet to the servers, how can i give my
> clients that just want read access to the all the data files including log
> files? What do they need installed / or use in order to achieve this?
> 6. Is there any way you can come up with Roles scheme for certain users?
> Lets say a particular group of users should have a certain permissions?
Can
> we create a something like that? that need to be done on the OS level
> rather than SQL level.?
> I know this is asking for a lot, but its really important to me, your
> valuable knowledge on all this issues would be much much appreciated?
> Thank you guys very much
>

Database Design

Well i've been given a big job of copying all the databases from an old
server to a new server. In order to provide better security, availabilty,
performance.
My servers are in a DMZ, so i have to use remote desktop/terminal services
to connect to it.
1. I have two logical partitions in the server. Is it a good practice to
store the OS and SQL server software itself on C:\ and all the data on
d:\??(Will it help me in anyways to achieve better performance? Can i
make separate directories for each databaseon d:\. and further on extending
it to sub directories for data and log files?
2. Should i copy all objects such as logins, DB plans, jobs etc. from the
old server or is it a better a practice to start all the plans over (create
new plans) to achieve better results and only copy the databases?
3. What is a good strategy for backup plans? For Log Files? For Primary
Files?
4. How to come up with a good Disaster Recovery Plan? What are all the
things you need to have in order to create a good DR plan? what is a good
way to test it?
5. What is the best way to secure SQL server? Who should have what
access? Which people should have access to the server itself? And how can
i give people read only access to the databases if they have access to the
server? Do they even need access to the server? How can they only have
read access to the SQL server databases? What tools do i need? Since i
have to use remote desktop to conncet to the servers, how can i give my
clients that just want read access to the all the data files including log
files? What do they need installed / or use in order to achieve this?
6. Is there any way you can come up with Roles scheme for certain users?
Lets say a particular group of users should have a certain permissions? Can
we create a something like that? that need to be done on the OS level
rather than SQL level.?
I know this is asking for a lot, but its really important to me, your
valuable knowledge on all this issues would be much much appreciated?
Thank you guys very much
"Shash Goyal" <Shash703@.gmail.com> wrote in message
news:%23J1QB78tEHA.3916@.TK2MSFTNGP10.phx.gbl...
> Well i've been given a big job of copying all the databases from an old
> server to a new server. In order to provide better security, availabilty,
> performance.
> My servers are in a DMZ, so i have to use remote desktop/terminal services
> to connect to it.
> 1. I have two logical partitions in the server. Is it a good practice to
> store the OS and SQL server software itself on C:\ and all the data on
> d:\??(Will it help me in anyways to achieve better performance? Can i
> make separate directories for each databaseon d:\. and further on
extending
> it to sub directories for data and log files?
It really doesn't make a difference performance wise if they are the same
physical volume.
However, an argument can be made for maintenance to at least put the
databases on the D: drive.
I would not do a separate directory for each DB though.

> 2. Should i copy all objects such as logins, DB plans, jobs etc. from
the
> old server or is it a better a practice to start all the plans over
(create
> new plans) to achieve better results and only copy the databases?
>
"It depends". It really does.
In my recent move, I moved all the logins, etc. There's a KB article on
this.

> 3. What is a good strategy for backup plans? For Log Files? For Primary
> Files?
>
I prefer to back them up to a NAS via a UNC. From there to tape is also
recommended. Do as often as business requirements dictate.

> 4. How to come up with a good Disaster Recovery Plan? What are all the
> things you need to have in order to create a good DR plan? what is a
good
> way to test it?
>
First, determine your needs. Are you a 24/7 company expecting 100% uptime.
How much recovery time is allowed. (i.e. if you have to be up and running in
5 minutes you may go with clustering or log-shipping and a lot of additional
cost. If you can wait 5 hours, just restoring from a backup may be ok.)
Again, what are the business needs?

> 5. What is the best way to secure SQL server?
MS has some white papers on this. Ideally give as little permissions as
possible.

>Who should have what
> access? Which people should have access to the server itself? And how
can
> i give people read only access to the databases if they have access to the
> server? Do they even need access to the server?
Generalyl not.

> How can they only have
> read access to the SQL server databases?
Read up on DB Roles.
DBdatareader may work for what you want.

> What tools do i need? Since i
> have to use remote desktop to conncet to the servers, how can i give my
> clients that just want read access to the all the data files including log
> files? What do they need installed / or use in order to achieve this?
> 6. Is there any way you can come up with Roles scheme for certain users?
> Lets say a particular group of users should have a certain permissions?
Can
> we create a something like that? that need to be done on the OS level
> rather than SQL level.?
>
Well, I can't answer all your questions, but hopefully this gives you a
start.

> I know this is asking for a lot, but its really important to me, your
> valuable knowledge on all this issues would be much much appreciated?
> Thank you guys very much
>
|||For your answer of question #1 whats the reason that you should not create a
separate directory for each DB?
As far as how critical the Db' are-- the server holds all the data for
different websites, so i guess they are pretty critical. so whats the most
cost effective DR plan we can establish?
thanks for all your help
And thanks for all your help so far
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:vQ_dd.313544$bp1.178867@.twister.nyroc.rr.com. ..[vbcol=seagreen]
> "Shash Goyal" <Shash703@.gmail.com> wrote in message
> news:%23J1QB78tEHA.3916@.TK2MSFTNGP10.phx.gbl...
availabilty,[vbcol=seagreen]
services[vbcol=seagreen]
to[vbcol=seagreen]
i[vbcol=seagreen]
> extending
> It really doesn't make a difference performance wise if they are the same
> physical volume.
> However, an argument can be made for maintenance to at least put the
> databases on the D: drive.
> I would not do a separate directory for each DB though.
>
> the
> (create
> "It depends". It really does.
> In my recent move, I moved all the logins, etc. There's a KB article on
> this.
Primary[vbcol=seagreen]
> I prefer to back them up to a NAS via a UNC. From there to tape is also
> recommended. Do as often as business requirements dictate.
the
> good
> First, determine your needs. Are you a 24/7 company expecting 100%
uptime.
> How much recovery time is allowed. (i.e. if you have to be up and running
in
> 5 minutes you may go with clustering or log-shipping and a lot of
additional[vbcol=seagreen]
> cost. If you can wait 5 hours, just restoring from a backup may be ok.)
> Again, what are the business needs?
>
> MS has some white papers on this. Ideally give as little permissions as
> possible.
> can
the[vbcol=seagreen]
> Generalyl not.
>
> Read up on DB Roles.
> DBdatareader may work for what you want.
log[vbcol=seagreen]
users?
> Can
> Well, I can't answer all your questions, but hopefully this gives you a
> start.
>
>
|||"Shash Goyal" <Shash703@.gmail.com> wrote in message
news:e$fJvu%23tEHA.3476@.TK2MSFTNGP14.phx.gbl...
> For your answer of question #1 whats the reason that you should not create
a
> separate directory for each DB?
No need in my book. Just extra path info to type, etc.

> As far as how critical the Db' are-- the server holds all the data for
> different websites, so i guess they are pretty critical. so whats the
most
> cost effective DR plan we can establish?
>
Again, what's the cost if the sites go down?
I deal with sites that downtime is measured in thousands of dollars per
minute. Even then it was hard to justify a clustered server configuration.
(Which can run $50K and up. Since list price for SQL Server 2000 Enterprise
Edition is ~$20K/CPU license, it gets expensive very quickly.)
Before that, I had log-shipping. Still required two servers, but I didn't
need a SAN or SQL 2000 EE licenses.
At one point it was simply, "make sure the hardware is really really
robust."
So, again, how much can you pay?
As a consultant I could design plans that cost next to nothing to cost
$250K. It would all depend on what a client needs and is willing to pay.
The usual test is ask your business team "what's service level agreement do
I need to provide for." Then go away, figure out how much it will cost and
then go back to them. I find generally folks very quickly get much more
realistic in their needs.
(i.e. they may say, "we want 99.999% uptime, guaranteed." You come back
with a $250K price tag and then all of a sudden 99% uptime (which you can do
for say $25K) is MUCH more palatable to them. :-)

> thanks for all your help
>
> And thanks for all your help so far
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message[vbcol=seagreen]
> news:vQ_dd.313544$bp1.178867@.twister.nyroc.rr.com. ..
old[vbcol=seagreen]
> availabilty,
> services
practice[vbcol=seagreen]
> to
Can[vbcol=seagreen]
> i
same[vbcol=seagreen]
from[vbcol=seagreen]
> Primary
> the
> uptime.
running[vbcol=seagreen]
> in
> additional
how[vbcol=seagreen]
> the
my[vbcol=seagreen]
> log
this?[vbcol=seagreen]
> users?
permissions?
>

Database Design

Well i've been given a big job of copying all the databases from an old
server to a new server. In order to provide better security, availabilty,
performance.
My servers are in a DMZ, so i have to use remote desktop/terminal services
to connect to it.
1. I have two logical partitions in the server. Is it a good practice to
store the OS and SQL server software itself on C:\ and all the data on
d:\'?(Will it help me in anyways to achieve better performance? Can i
make separate directories for each databaseon d:\. and further on extending
it to sub directories for data and log files'
2. Should i copy all objects such as logins, DB plans, jobs etc. from the
old server or is it a better a practice to start all the plans over (create
new plans) to achieve better results and only copy the databases?
3. What is a good strategy for backup plans? For Log Files? For Primary
Files?
4. How to come up with a good Disaster Recovery Plan' What are all the
things you need to have in order to create a good DR plan' what is a good
way to test it?
5. What is the best way to secure SQL server' Who should have what
access? Which people should have access to the server itself? And how can
i give people read only access to the databases if they have access to the
server? Do they even need access to the server' How can they only have
read access to the SQL server databases' What tools do i need? Since i
have to use remote desktop to conncet to the servers, how can i give my
clients that just want read access to the all the data files including log
files? What do they need installed / or use in order to achieve this'
6. Is there any way you can come up with Roles scheme for certain users?
Lets say a particular group of users should have a certain permissions? Can
we create a something like that' that need to be done on the OS level
rather than SQL level.'
I know this is asking for a lot, but its really important to me, your
valuable knowledge on all this issues would be much much appreciated?
Thank you guys very muchI suggest you to start with SQL 2000 Operations Guide -
http://www.microsoft.com/technet/pr...n/sqlops0.mspx.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Shash Goyal" <Shash703@.gmail.com> wrote in message
news:OdySK88tEHA.2128@.TK2MSFTNGP11.phx.gbl...
> Well i've been given a big job of copying all the databases from an old
> server to a new server. In order to provide better security, availabilty,
> performance.
> My servers are in a DMZ, so i have to use remote desktop/terminal services
> to connect to it.
> 1. I have two logical partitions in the server. Is it a good practice to
> store the OS and SQL server software itself on C:\ and all the data on
> d:\'?(Will it help me in anyways to achieve better performance? Can i
> make separate directories for each databaseon d:\. and further on
extending
> it to sub directories for data and log files'
> 2. Should i copy all objects such as logins, DB plans, jobs etc. from
the
> old server or is it a better a practice to start all the plans over
(create
> new plans) to achieve better results and only copy the databases?
> 3. What is a good strategy for backup plans? For Log Files? For Primary
> Files?
> 4. How to come up with a good Disaster Recovery Plan' What are all the
> things you need to have in order to create a good DR plan' what is a
good
> way to test it?
> 5. What is the best way to secure SQL server' Who should have what
> access? Which people should have access to the server itself? And how
can
> i give people read only access to the databases if they have access to the
> server? Do they even need access to the server' How can they only
have
> read access to the SQL server databases' What tools do i need? Since i
> have to use remote desktop to conncet to the servers, how can i give my
> clients that just want read access to the all the data files including log
> files? What do they need installed / or use in order to achieve this'
> 6. Is there any way you can come up with Roles scheme for certain users?
> Lets say a particular group of users should have a certain permissions?
Can
> we create a something like that' that need to be done on the OS level
> rather than SQL level.'
> I know this is asking for a lot, but its really important to me, your
> valuable knowledge on all this issues would be much much appreciated?
> Thank you guys very much
>

Database Design

Well i've been given a big job of copying all the databases from an old
server to a new server. In order to provide better security, availabilty,
performance.
My servers are in a DMZ, so i have to use remote desktop/terminal services
to connect to it.
1. I have two logical partitions in the server. Is it a good practice to
store the OS and SQL server software itself on C:\ and all the data on
d:\'?(Will it help me in anyways to achieve better performance? Can i
make separate directories for each databaseon d:\. and further on extending
it to sub directories for data and log files'
2. Should i copy all objects such as logins, DB plans, jobs etc. from the
old server or is it a better a practice to start all the plans over (create
new plans) to achieve better results and only copy the databases?
3. What is a good strategy for backup plans? For Log Files? For Primary
Files?
4. How to come up with a good Disaster Recovery Plan' What are all the
things you need to have in order to create a good DR plan' what is a good
way to test it?
5. What is the best way to secure SQL server' Who should have what
access? Which people should have access to the server itself? And how can
i give people read only access to the databases if they have access to the
server? Do they even need access to the server' How can they only have
read access to the SQL server databases' What tools do i need? Since i
have to use remote desktop to conncet to the servers, how can i give my
clients that just want read access to the all the data files including log
files? What do they need installed / or use in order to achieve this'
6. Is there any way you can come up with Roles scheme for certain users?
Lets say a particular group of users should have a certain permissions? Can
we create a something like that' that need to be done on the OS level
rather than SQL level.'
I know this is asking for a lot, but its really important to me, your
valuable knowledge on all this issues would be much much appreciated?
Thank you guys very much"Shash Goyal" <Shash703@.gmail.com> wrote in message
news:%23J1QB78tEHA.3916@.TK2MSFTNGP10.phx.gbl...
> Well i've been given a big job of copying all the databases from an old
> server to a new server. In order to provide better security, availabilty,
> performance.
> My servers are in a DMZ, so i have to use remote desktop/terminal services
> to connect to it.
> 1. I have two logical partitions in the server. Is it a good practice to
> store the OS and SQL server software itself on C:\ and all the data on
> d:\'?(Will it help me in anyways to achieve better performance? Can i
> make separate directories for each databaseon d:\. and further on
extending
> it to sub directories for data and log files'
It really doesn't make a difference performance wise if they are the same
physical volume.
However, an argument can be made for maintenance to at least put the
databases on the D: drive.
I would not do a separate directory for each DB though.
> 2. Should i copy all objects such as logins, DB plans, jobs etc. from
the
> old server or is it a better a practice to start all the plans over
(create
> new plans) to achieve better results and only copy the databases?
>
"It depends". It really does.
In my recent move, I moved all the logins, etc. There's a KB article on
this.
> 3. What is a good strategy for backup plans? For Log Files? For Primary
> Files?
>
I prefer to back them up to a NAS via a UNC. From there to tape is also
recommended. Do as often as business requirements dictate.
> 4. How to come up with a good Disaster Recovery Plan' What are all the
> things you need to have in order to create a good DR plan' what is a
good
> way to test it?
>
First, determine your needs. Are you a 24/7 company expecting 100% uptime.
How much recovery time is allowed. (i.e. if you have to be up and running in
5 minutes you may go with clustering or log-shipping and a lot of additional
cost. If you can wait 5 hours, just restoring from a backup may be ok.)
Again, what are the business needs?
> 5. What is the best way to secure SQL server'
MS has some white papers on this. Ideally give as little permissions as
possible.
>Who should have what
> access? Which people should have access to the server itself? And how
can
> i give people read only access to the databases if they have access to the
> server? Do they even need access to the server'
Generalyl not.
> How can they only have
> read access to the SQL server databases'
Read up on DB Roles.
DBdatareader may work for what you want.
> What tools do i need? Since i
> have to use remote desktop to conncet to the servers, how can i give my
> clients that just want read access to the all the data files including log
> files? What do they need installed / or use in order to achieve this'
> 6. Is there any way you can come up with Roles scheme for certain users?
> Lets say a particular group of users should have a certain permissions?
Can
> we create a something like that' that need to be done on the OS level
> rather than SQL level.'
>
Well, I can't answer all your questions, but hopefully this gives you a
start.
> I know this is asking for a lot, but its really important to me, your
> valuable knowledge on all this issues would be much much appreciated?
> Thank you guys very much
>|||For your answer of question #1 whats the reason that you should not create a
separate directory for each DB'
As far as how critical the Db' are-- the server holds all the data for
different websites, so i guess they are pretty critical. so whats the most
cost effective DR plan we can establish?
thanks for all your help
And thanks for all your help so far
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:vQ_dd.313544$bp1.178867@.twister.nyroc.rr.com...
> "Shash Goyal" <Shash703@.gmail.com> wrote in message
> news:%23J1QB78tEHA.3916@.TK2MSFTNGP10.phx.gbl...
> > Well i've been given a big job of copying all the databases from an old
> > server to a new server. In order to provide better security,
availabilty,
> > performance.
> > My servers are in a DMZ, so i have to use remote desktop/terminal
services
> > to connect to it.
> >
> > 1. I have two logical partitions in the server. Is it a good practice
to
> > store the OS and SQL server software itself on C:\ and all the data on
> > d:\'?(Will it help me in anyways to achieve better performance? Can
i
> > make separate directories for each databaseon d:\. and further on
> extending
> > it to sub directories for data and log files'
> It really doesn't make a difference performance wise if they are the same
> physical volume.
> However, an argument can be made for maintenance to at least put the
> databases on the D: drive.
> I would not do a separate directory for each DB though.
>
> >
> > 2. Should i copy all objects such as logins, DB plans, jobs etc. from
> the
> > old server or is it a better a practice to start all the plans over
> (create
> > new plans) to achieve better results and only copy the databases?
> >
> "It depends". It really does.
> In my recent move, I moved all the logins, etc. There's a KB article on
> this.
> > 3. What is a good strategy for backup plans? For Log Files? For
Primary
> > Files?
> >
> I prefer to back them up to a NAS via a UNC. From there to tape is also
> recommended. Do as often as business requirements dictate.
> > 4. How to come up with a good Disaster Recovery Plan' What are all
the
> > things you need to have in order to create a good DR plan' what is a
> good
> > way to test it?
> >
> First, determine your needs. Are you a 24/7 company expecting 100%
uptime.
> How much recovery time is allowed. (i.e. if you have to be up and running
in
> 5 minutes you may go with clustering or log-shipping and a lot of
additional
> cost. If you can wait 5 hours, just restoring from a backup may be ok.)
> Again, what are the business needs?
> > 5. What is the best way to secure SQL server'
> MS has some white papers on this. Ideally give as little permissions as
> possible.
> >Who should have what
> > access? Which people should have access to the server itself? And how
> can
> > i give people read only access to the databases if they have access to
the
> > server? Do they even need access to the server'
> Generalyl not.
> > How can they only have
> > read access to the SQL server databases'
> Read up on DB Roles.
> DBdatareader may work for what you want.
> > What tools do i need? Since i
> > have to use remote desktop to conncet to the servers, how can i give my
> > clients that just want read access to the all the data files including
log
> > files? What do they need installed / or use in order to achieve this'
> >
> > 6. Is there any way you can come up with Roles scheme for certain
users?
> > Lets say a particular group of users should have a certain permissions?
> Can
> > we create a something like that' that need to be done on the OS level
> > rather than SQL level.'
> >
> Well, I can't answer all your questions, but hopefully this gives you a
> start.
>
> > I know this is asking for a lot, but its really important to me, your
> > valuable knowledge on all this issues would be much much appreciated?
> >
> > Thank you guys very much
> >
> >
>|||"Shash Goyal" <Shash703@.gmail.com> wrote in message
news:e$fJvu%23tEHA.3476@.TK2MSFTNGP14.phx.gbl...
> For your answer of question #1 whats the reason that you should not create
a
> separate directory for each DB'
No need in my book. Just extra path info to type, etc.
> As far as how critical the Db' are-- the server holds all the data for
> different websites, so i guess they are pretty critical. so whats the
most
> cost effective DR plan we can establish?
>
Again, what's the cost if the sites go down?
I deal with sites that downtime is measured in thousands of dollars per
minute. Even then it was hard to justify a clustered server configuration.
(Which can run $50K and up. Since list price for SQL Server 2000 Enterprise
Edition is ~$20K/CPU license, it gets expensive very quickly.)
Before that, I had log-shipping. Still required two servers, but I didn't
need a SAN or SQL 2000 EE licenses.
At one point it was simply, "make sure the hardware is really really
robust."
So, again, how much can you pay?
As a consultant I could design plans that cost next to nothing to cost
$250K. It would all depend on what a client needs and is willing to pay.
The usual test is ask your business team "what's service level agreement do
I need to provide for." Then go away, figure out how much it will cost and
then go back to them. I find generally folks very quickly get much more
realistic in their needs.
(i.e. they may say, "we want 99.999% uptime, guaranteed." You come back
with a $250K price tag and then all of a sudden 99% uptime (which you can do
for say $25K) is MUCH more palatable to them. :-)
> thanks for all your help
>
> And thanks for all your help so far
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message
> news:vQ_dd.313544$bp1.178867@.twister.nyroc.rr.com...
> >
> > "Shash Goyal" <Shash703@.gmail.com> wrote in message
> > news:%23J1QB78tEHA.3916@.TK2MSFTNGP10.phx.gbl...
> > > Well i've been given a big job of copying all the databases from an
old
> > > server to a new server. In order to provide better security,
> availabilty,
> > > performance.
> > > My servers are in a DMZ, so i have to use remote desktop/terminal
> services
> > > to connect to it.
> > >
> > > 1. I have two logical partitions in the server. Is it a good
practice
> to
> > > store the OS and SQL server software itself on C:\ and all the data on
> > > d:\'?(Will it help me in anyways to achieve better performance?
Can
> i
> > > make separate directories for each databaseon d:\. and further on
> > extending
> > > it to sub directories for data and log files'
> >
> > It really doesn't make a difference performance wise if they are the
same
> > physical volume.
> >
> > However, an argument can be made for maintenance to at least put the
> > databases on the D: drive.
> >
> > I would not do a separate directory for each DB though.
> >
> >
> > >
> > > 2. Should i copy all objects such as logins, DB plans, jobs etc.
from
> > the
> > > old server or is it a better a practice to start all the plans over
> > (create
> > > new plans) to achieve better results and only copy the databases?
> > >
> >
> > "It depends". It really does.
> >
> > In my recent move, I moved all the logins, etc. There's a KB article on
> > this.
> >
> > > 3. What is a good strategy for backup plans? For Log Files? For
> Primary
> > > Files?
> > >
> >
> > I prefer to back them up to a NAS via a UNC. From there to tape is also
> > recommended. Do as often as business requirements dictate.
> >
> > > 4. How to come up with a good Disaster Recovery Plan' What are all
> the
> > > things you need to have in order to create a good DR plan' what is a
> > good
> > > way to test it?
> > >
> >
> > First, determine your needs. Are you a 24/7 company expecting 100%
> uptime.
> > How much recovery time is allowed. (i.e. if you have to be up and
running
> in
> > 5 minutes you may go with clustering or log-shipping and a lot of
> additional
> > cost. If you can wait 5 hours, just restoring from a backup may be ok.)
> >
> > Again, what are the business needs?
> >
> > > 5. What is the best way to secure SQL server'
> >
> > MS has some white papers on this. Ideally give as little permissions as
> > possible.
> >
> > >Who should have what
> > > access? Which people should have access to the server itself? And
how
> > can
> > > i give people read only access to the databases if they have access to
> the
> > > server? Do they even need access to the server'
> >
> > Generalyl not.
> >
> > > How can they only have
> > > read access to the SQL server databases'
> >
> > Read up on DB Roles.
> >
> > DBdatareader may work for what you want.
> >
> > > What tools do i need? Since i
> > > have to use remote desktop to conncet to the servers, how can i give
my
> > > clients that just want read access to the all the data files including
> log
> > > files? What do they need installed / or use in order to achieve
this'
> > >
> > > 6. Is there any way you can come up with Roles scheme for certain
> users?
> > > Lets say a particular group of users should have a certain
permissions?
> > Can
> > > we create a something like that' that need to be done on the OS level
> > > rather than SQL level.'
> > >
> >
> > Well, I can't answer all your questions, but hopefully this gives you a
> > start.
> >
> >
> > > I know this is asking for a lot, but its really important to me, your
> > > valuable knowledge on all this issues would be much much appreciated?
> > >
> > > Thank you guys very much
> > >
> > >
> >
> >
>

Database Design

Well i've been given a big job of copying all the databases from an old
server to a new server. In order to provide better security, availabilty,
performance.
My servers are in a DMZ, so i have to use remote desktop/terminal services
to connect to it.
1. I have two logical partitions in the server. Is it a good practice to
store the OS and SQL server software itself on C:\ and all the data on
d:\'?(Will it help me in anyways to achieve better performance? Can i
make separate directories for each databaseon d:\. and further on extending
it to sub directories for data and log files'
2. Should i copy all objects such as logins, DB plans, jobs etc. from the
old server or is it a better a practice to start all the plans over (create
new plans) to achieve better results and only copy the databases?
3. What is a good strategy for backup plans? For Log Files? For Primary
Files?
4. How to come up with a good Disaster Recovery Plan' What are all the
things you need to have in order to create a good DR plan' what is a good
way to test it?
5. What is the best way to secure SQL server' Who should have what
access? Which people should have access to the server itself? And how can
i give people read only access to the databases if they have access to the
server? Do they even need access to the server' How can they only have
read access to the SQL server databases' What tools do i need? Since i
have to use remote desktop to conncet to the servers, how can i give my
clients that just want read access to the all the data files including log
files? What do they need installed / or use in order to achieve this'
6. Is there any way you can come up with Roles scheme for certain users?
Lets say a particular group of users should have a certain permissions? Can
we create a something like that' that need to be done on the OS level
rather than SQL level.'
I know this is asking for a lot, but its really important to me, your
valuable knowledge on all this issues would be much much appreciated?
Thank you guys very muchAfter reading through this, it's pretty clear you're not that familiar with
sql server. You'll definately want to do some reading in books online on al
l
these topics. It might not hurt, either, to bring someone in on a consultin
g
basis to help you get started (no, I'm not a consultant, but I've used them
to approve/disapprove of stuff I've done with Oracle). While I don't
recommend you doing this without more research, here's some 10,000 ft.
answers to your questions...
1. Typically, you want to keep the OS isolated from other stuff. You
mentioned 2 logical partitions, but the physical layer underneath that is
going to be more interesting. If both these partitions are sitting on the
same physical drives (raid?), then it's going to be a wash. From an
organizational standpoint, if I only had one partition above and beyond my O
S
partition, I'd install the program and data to the same partition, separate
from the OS. You can make separate directories for each database, but this
won't have much impact on performance unless the directories for your data
and log files sit on separate physical partitions.
2. If you're comfortable migrating your master and msdb database files,
then it's a quick and easy. If you're not, then start from scratch. At
worst, you can script out your jobs and users. Look up sp_hexadecimal and
sp_help_revlogin. Once sp_hexadecimal has been created, you can use
sp_help_Revlogin to create a script you can run on the new server to recreat
e
all your users. DTS packages can be copied by opening them up and changing
the server name when performing a "save as...". Jobs can be scripted and
recreated in Query Analyzer on the new instance.
3. How much data can you afford to lose? If none, run a daily full with
hourly or bihourly transaction backups, at the least. If you can afford to
loose most or all of a day's transactions, run a daily full. You'll want to
consult with your business decision makers on this one. Their needs will
dictate the answer to this.
4. To have a good DR plan, you need a secondary site. You can use log
shipping to copy and restore transaction log backups to the remote site. If
it's not remote, then it's just a standby (which is still good to have), but
it's not DR. A good way to test would be to find a time when you can take
your system offline and see how long it takes for your team to get the
application back up using the secondary site. There are multiple vended
solutions to help with this as well, depending on your architecture
(clusters?, all stand-alones?, SAN?)
5. I've heard different answers from different quarters. With password
cracking what it is, I've found more and more recommendations for using mixe
d
security mode and relying on windows strong passwords instead of sql users.
All users should be locked down to the minimum amount of access required to
do their jobs. Only DBAs and select operations staff members should ever
have access to the server itself. Anyone other than the DBA should be
granted access at the discretion of the DBA. Some network admins will baulk
at this, but I haven't found one yet who'll open the exchange server to me.
Access to the server does NOT imply access to the database. You can lock
users down as read only using the db_Datareader database role in each
database. Whether or not users require access to the OS is going to depend
on your architecture. In a 3-tier environment, they should not require
access to the OS. The best tools are query analyzer and enterprise manager,
both included in the client tools portion of the sql server install. Users
will never have use for the log files. They're not legible without other
tools anyway. For that matter, no user is going to care about your data
files, either (not directly, anyway). The users' only mode of access is by
logging into the database. See note above about db_datareader. If the
application is a fat install, there's a good chance you're going to need to
put the Client Network Utility on each workstation. This is available off
the sql install - select Client Connectivity.
6. They're called Roles. They work just like Windows groups. If a user
has a right that conflicts with rights for a role the user belongs to, the
most restrictive wins.
"Shash Goyal" wrote:

> Well i've been given a big job of copying all the databases from an old
> server to a new server. In order to provide better security, availabilty,
> performance.
> My servers are in a DMZ, so i have to use remote desktop/terminal services
> to connect to it.
> 1. I have two logical partitions in the server. Is it a good practice to
> store the OS and SQL server software itself on C:\ and all the data on
> d:\'?(Will it help me in anyways to achieve better performance? Can i
> make separate directories for each databaseon d:\. and further on extendi
ng
> it to sub directories for data and log files'
> 2. Should i copy all objects such as logins, DB plans, jobs etc. from th
e
> old server or is it a better a practice to start all the plans over (creat
e
> new plans) to achieve better results and only copy the databases?
> 3. What is a good strategy for backup plans? For Log Files? For Primary
> Files?
> 4. How to come up with a good Disaster Recovery Plan' What are all the
> things you need to have in order to create a good DR plan' what is a goo
d
> way to test it?
> 5. What is the best way to secure SQL server' Who should have what
> access? Which people should have access to the server itself? And how c
an
> i give people read only access to the databases if they have access to the
> server? Do they even need access to the server' How can they only hav
e
> read access to the SQL server databases' What tools do i need? Since i
> have to use remote desktop to conncet to the servers, how can i give my
> clients that just want read access to the all the data files including log
> files? What do they need installed / or use in order to achieve this'
> 6. Is there any way you can come up with Roles scheme for certain users?
> Lets say a particular group of users should have a certain permissions? Ca
n
> we create a something like that' that need to be done on the OS level
> rather than SQL level.'
> I know this is asking for a lot, but its really important to me, your
> valuable knowledge on all this issues would be much much appreciated?
> Thank you guys very much
>
>|||Shash
I'd recommend you to vist the below sites
http://vyaskn.tripod.com/ sql_serve...r />
.htm#Step1
--administaiting
best practices
http://vyaskn.tripod.com/sql_server...t_practices.htm --secu
rity
best practices
"Shash Goyal" <Shash703@.gmail.com> wrote in message
news:OKU7978tEHA.2300@.TK2MSFTNGP09.phx.gbl...
> Well i've been given a big job of copying all the databases from an old
> server to a new server. In order to provide better security, availabilty,
> performance.
> My servers are in a DMZ, so i have to use remote desktop/terminal services
> to connect to it.
> 1. I have two logical partitions in the server. Is it a good practice to
> store the OS and SQL server software itself on C:\ and all the data on
> d:\'?(Will it help me in anyways to achieve better performance? Can i
> make separate directories for each databaseon d:\. and further on
extending
> it to sub directories for data and log files'
> 2. Should i copy all objects such as logins, DB plans, jobs etc. from
the
> old server or is it a better a practice to start all the plans over
(create
> new plans) to achieve better results and only copy the databases?
> 3. What is a good strategy for backup plans? For Log Files? For Primary
> Files?
> 4. How to come up with a good Disaster Recovery Plan' What are all the
> things you need to have in order to create a good DR plan' what is a
good
> way to test it?
> 5. What is the best way to secure SQL server' Who should have what
> access? Which people should have access to the server itself? And how
can
> i give people read only access to the databases if they have access to the
> server? Do they even need access to the server' How can they only
have
> read access to the SQL server databases' What tools do i need? Since i
> have to use remote desktop to conncet to the servers, how can i give my
> clients that just want read access to the all the data files including log
> files? What do they need installed / or use in order to achieve this'
> 6. Is there any way you can come up with Roles scheme for certain users?
> Lets say a particular group of users should have a certain permissions?
Can
> we create a something like that' that need to be done on the OS level
> rather than SQL level.'
> I know this is asking for a lot, but its really important to me, your
> valuable knowledge on all this issues would be much much appreciated?
> Thank you guys very much
>|||Thanks Uri!!
"Shash Goyal" <Shash703@.gmail.com> wrote in message
news:OKU7978tEHA.2300@.TK2MSFTNGP09.phx.gbl...
> Well i've been given a big job of copying all the databases from an old
> server to a new server. In order to provide better security, availabilty,
> performance.
> My servers are in a DMZ, so i have to use remote desktop/terminal services
> to connect to it.
> 1. I have two logical partitions in the server. Is it a good practice to
> store the OS and SQL server software itself on C:\ and all the data on
> d:\'?(Will it help me in anyways to achieve better performance? Can i
> make separate directories for each databaseon d:\. and further on
extending
> it to sub directories for data and log files'
> 2. Should i copy all objects such as logins, DB plans, jobs etc. from
the
> old server or is it a better a practice to start all the plans over
(create
> new plans) to achieve better results and only copy the databases?
> 3. What is a good strategy for backup plans? For Log Files? For Primary
> Files?
> 4. How to come up with a good Disaster Recovery Plan' What are all the
> things you need to have in order to create a good DR plan' what is a
good
> way to test it?
> 5. What is the best way to secure SQL server' Who should have what
> access? Which people should have access to the server itself? And how
can
> i give people read only access to the databases if they have access to the
> server? Do they even need access to the server' How can they only
have
> read access to the SQL server databases' What tools do i need? Since i
> have to use remote desktop to conncet to the servers, how can i give my
> clients that just want read access to the all the data files including log
> files? What do they need installed / or use in order to achieve this'
> 6. Is there any way you can come up with Roles scheme for certain users?
> Lets say a particular group of users should have a certain permissions?
Can
> we create a something like that' that need to be done on the OS level
> rather than SQL level.'
> I know this is asking for a lot, but its really important to me, your
> valuable knowledge on all this issues would be much much appreciated?
> Thank you guys very much
>