Re: Database Design Standards

From: Andrea Montanari (andrea.sqlDMO_at_virgilio.it)
Date: 10/26/04 

Date: Tue, 26 Oct 2004 12:54:24 +0200

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/ArticleID/39647/39647.html
http://www.windowsitpro.com/Article/ArticleID/39657/39657.html

this is free to read,
http://www.winnetmag.com/Article/ArticleID/25915/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/ArticleID/24340/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.com/vk_sql_security.asp , as long as
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/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/default.asp?url=/library/en-us/tsqlref/ts_sp_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.shtm        http://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

Relevant Pages
Loading