Wednesday, March 7, 2012

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
>

No comments:

Post a Comment