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
>