DataBase Development Server

Hi,
I am trying to create a development database server (make use of an old machine), with which to learn about sql 2005 and oracle etc. I'm using VS 2005 Beta 2 on my development/workstation machine.
My workstation and the prospective server are connected via a router and can 'see' each other.
I have installed win 2003 server on two seperate partitions (multiple boot) and installed sql server 2005 on one partition and will install oracle 10g on the other. (I understand these two databases can run on the same machine/OS, but I just wanted to keep things tidy and I won't be using them at the same time, so ...).
My question is how do I/should I configure win 2003 server / sql server 2005 on my server machine, in order to be able to connect from my workstation via vs 2005 beta 2 ?
Any suggestions or resources on configuration appreciated.

I have installed both but not in the same machine, in the same box I would use at least 1gig of ram. Oracle 10g will give you the most problems because Oracle installer is not very good. If you run into any problem with Oralce 10g try the link below for more info, it helped me better than Oracle site. SQL Server just remember to install the management tools also . Hope this helps.

http://www.rittman.net/

|||

Thanks for the reply. Since my original post, my sql server 2005 installation has stopped working, after a second installation. I had a dual boot set-up both running windows 2003 server. Perhaps this is too much for the machine.

I'll have to have a re-think.
Thanks again.

Database Design Theory

I have been tasked with creating a Data Warehouse.

Problem is that old storage vs reporting debate.

I have determined that the data that I will recieve and store will be like follows (simplified form) for expandability

KEY FldKEy FldData DateTime AuditTrail

Daily I will use this data based on use input process this data into the following format and say
if fldkey/ flddata open a cycle.
populate row with null close date
if fldkey/ flddata closes cycle
update row with date

If fldkey/ flddata changes a cutable value
update row

if fldkey/ flddata changes a cutable value (type 2 table)
insert a row into detail update value and obsolete previous row.

KEY DateStart DateEnd FLDDATA1 FLDDATE2 Op_Cl_IND HEADER Record

KEY EFFdate OBSDATE FLDdata3 FLDData4 Detail Records
KEY EFFdate OBSDATE FLDdata3 FLDData4
KEY EFFdate OBSDATE FLDdata3 FLDData4

Problem: FLDKey is a finite count however the max is undefined.

IS there any way to solve the problem of not being able to nail down users to tell you what they want to cut by. What I have been instructed by mgr (old IDMS) is that they wish to see all on the FldData and have the ability to cut by all of it. However the Flddata could be anything (cannot be indexed).

400,000,000 rows at least.

Do I need to nail the users down or am I am missing something.

Sorry if so cryptic

:(Too cryptic, and too much jargon. (Cut?, Cycle?)

But this sure doesn't look like a data warehouse project to me.|||Thought so.

User requirement store a undetermined amount of data about an item

Cannot create a structure with
Key Make Model Year Cost Sold Junked
1 Ford Taurus 97 10000 1/1/98 2/28/06
2 Ford Ranger 97 20000 1/12/99 null

Need to create
Key Fieldname data
1 Make Ford
1 Model Taurus
1 Year 97
1 Cost 10000
1 Sold 1/1/98
1 Junked 2/28/06

As the Number of fields captured is not fixed.

Now I need to sum the that in the data field by a cut of that data.
Total Cost of All 1997 Ford Cars and grouped by the age in months of the auto.

Work of a small table to do this however 400 m rows of data over 18 months Tring to find the data 1997 and Ford in the data field then determining the total dollars of those

Well.

Question is there some thing that I am missing or do I need to get the user to define a more static structure. And transform this into a star schema.

The table structure to store the data (Audit trail)

perhaps datamart is a more appropriate term.|||So you're trying to do the logical vertical table thingee...

Why?

There are sooo many bad reasons for doing this...

The concept is that you want to be able to add new "columns" on the fly

I'll tell you this, if you do go this route, you better make sure to use a partitioned view where the tables are in their own file groups and distributed across many physical disks...

I wonder what you would partition on though|||That is my thought that this a bad idea.

I need to convince my MGR and Users to give me more Defined specs.

Was just asking if there is something that I am missing.

The Goal to the Audit Trail is to capture every poteitial detail of a thing. And to get that info If you have that things ID you can do this. However any and look at every detail about this. the other reason for the data structure of the audit trail is scalability of data storage.

If the users can define the key of a thing and details about that I can use the daily audit file to transpose this information.

I just wanted to make sure that my thoughts were not clouded by preconcieved notions or by lack of knowledge about new techniques.

Thank you|||What you are talking about is known as an EAV (Entity/Attribute/Value) model, and while it is occasionally appropriate, it is very difficult to write code around and I would NEVER recommend it for a "data warehouse".

If you need an unstructured data schema, consider developing in SQL Server 2005 and using the new XML datatype instead of an EAV design.|||Thank You BlindMan for putting a name to my problem. (EAV)
Thank You Brett

I am only using this struture to store an audit trail of a dynamic data structure.

I will persuade the users better to define there business needs so that I may make a true Data Warehouse.

I will use the Audit trail data and evaluate the rows of data that I recieve nighlty to populate the Warehouse. From predifined specs.

I cannot force a change in the data entering the Database.

But once here it is mine all mine.. AH HA HA HA :D|||I think you need to rethink your audit trail. If you are trying to use a single table to store all data changes from any other table, you are going to find the resulting dataset quite unwieldy. Either just store a description of the change that occured, or create separate archive tables for each production to table to store a history of modifications.|||Thank You BlindMan for putting a name to my problem. (EAV)have a look at tony andrews' article OTLT and EAV: the two big design mistakes all beginners make (http://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html)

tony's another dbforums stallwart|||To tell the truth...for me, doing audits...simpler is better

Just have an exact copy of the table.

Add 3 columns, HIST_ADD_BY, HIST_ADD_DT, HIST_ADD_TYPE

Create a trigger that inserts into this table for all UPDATE and DELETE Operations and move the entire row

Put no indexes on this table

Boom, audit done

If you want performance on analysis, I'd reccomend that you bcp the data out and load it to a table with indexes (applied after the load)

I would not want to interfere with the triggers at all

MOO|||Thanks all for the reference to the column.

The EAV I am forced to use is a decision of My Management and cannot push them off of that concept. Oh the fate of a peon. I have explained the pros and cons of this decision but this is what has been decided.

The other option that they suggested was to dynamically evaluate the key fields and if a new one appears generate the DDL to add a field to a table of 800+ fields and transpose this data.

They wish to make the data flexible for any forseeable senario without further IT involvment.

If I had total control ....|||They wish to make the data flexible for any forseeable senario without further IT involvment.lol
I've been enjoying the evolution of this thread. I love it when threads like this end with the management concluding that they can conceive of a model that will require no "further IT involvment".

I've never had the misfortune to run into a full blown EAV model (appropriately implemented or not) in the flesh but it's pretty clear with only a little reading and even a substandard imagination like mine that this is precisly where they do not lead you.

Pro - can stick any data in the db
Con - can stick any data in the db
Con - Try getting it out again

"for any forseeable senario" lol.

Ah well - you tried. Best of luck :D|||Put it in writing/e-mail right now that you think XML is the way to go with this. Then save a copy of the document in your CYA file.
Some very complex coding is ahead of you.|||committing to writing that XML is the way to go may backfire on ya...

not that i've got any XML experience, but i've heard horror stories about performance, and since XML has such cachet with management, they may take you up on it and then you could be cooked|||Yes, the performance sucks compared with a standard normalized database, but is probably no worse than an EAV design and with a helluva lot less programming. I would not place data in an XML column unnecessarily, but would reserve it only for data that could not be predefined in the business model.

database design - keys

Hello All
I am designing a database, or rather redesigning a very old database and
have a question regarding setting up key fields. The old database has a
table called Equipment with two fields:
Equipment Code - text 8
Equipment Description - text 50
In the new design I will have a table called Manufacturers that will have an
Equipment Code field, which will link to the Equipment table to get the
description. My question is, should I make a new Integer key field called
say EquipID which is what would get stored in the Manufacturers table or
should I simply use the Equipment Code field as the key? What are the
advantages/disadvantages to each method?
I have several other tables with a similar situation where the 'Code' is
unique but there are more fields in these tables.
Thanks,
GerryThis is a subject that has spawned many heated debates. Here's my take on
it:
In your case, just use the codes that exist.
In general, if your data has a natural key, use it. If the natural key is a
composite key of sufficient length/complexity (a totally subjective
determination) there might be a good reason to use a surrogate. However,
you MUST also enforce the uniqueness of the natural key! I am not against
the use of surrogate keys at all, but they should be used only after much
careful thought and consideration. Surrogate keys tend, in the hands of the
inexperienced, to lend a false sense of security ("Of course I don't have
any duplicates, my surrogate key assures that!")
Since you have a simple natural key, there is really no reason not to use
it. Adding a surrogate key just creates more data...
"News" <gerrydyck@.shaw.ca> wrote in message
news:Q7cSb.328534$X%5.134270@.pd7tw2no...

quote:

---

> Hello All
> I am designing a database, or rather redesigning a very old database and
> have a question regarding setting up key fields. The old database has a
> table called Equipment with two fields:
> Equipment Code - text 8
> Equipment Description - text 50
> In the new design I will have a table called Manufacturers that will have

---

an

quote:

---

> Equipment Code field, which will link to the Equipment table to get the
> description. My question is, should I make a new Integer key field called
> say EquipID which is what would get stored in the Manufacturers table or
> should I simply use the Equipment Code field as the key? What are the
> advantages/disadvantages to each method?
> I have several other tables with a similar situation where the 'Code' is
> unique but there are more fields in these tables.
> Thanks,
> Gerry
>

---

|||There are no general rules or norms which recommend a specific datatype for
a key.
The considerations to select a good key are often misunderstood. They
include stability (column values rarely change), simplicity (so that
relational operations can be effective), familiarity (meaningful or commonly
understood by the user) and irreducibility (no proper subset of key column
be another key). A good design can tradeoff certain characteristics in favor
of others to tackle specific issues with regard to key selection.
In a precisely modeled system, a key is chosen only based on the rules
defined at the business model & key selection involves only logical
considerations.
However, the implementation of databases using popular SQL DBMSs, generally
favors the usage of narrow keys for query efficiency, due to their smaller
size at the physical level. This may often fall under the criteria of
simplicity (mentioned above), but shuffling keys just for performance sake
is not always a good idea.
Anith|||Thanks Don. This is my first SQL database and sometimes with new programs I
tend to overthink a solution. For this one, I will be sticking with a
natural key.
Gerry
"Don Peterson" <no1@.nunya.com> wrote in message
news:uVsFYAq5DHA.2556@.TK2MSFTNGP09.phx.gbl...

quote:

---

> This is a subject that has spawned many heated debates. Here's my take on
> it:
> In your case, just use the codes that exist.
> In general, if your data has a natural key, use it. If the natural key is

---

a

quote:

---

> composite key of sufficient length/complexity (a totally subjective
> determination) there might be a good reason to use a surrogate. However,
> you MUST also enforce the uniqueness of the natural key! I am not against
> the use of surrogate keys at all, but they should be used only after much
> careful thought and consideration. Surrogate keys tend, in the hands of

---

the

quote:

---

> inexperienced, to lend a false sense of security ("Of course I don't have
> any duplicates, my surrogate key assures that!")
> Since you have a simple natural key, there is really no reason not to use
> it. Adding a surrogate key just creates more data...
> "News" <gerrydyck@.shaw.ca> wrote in message
> news:Q7cSb.328534$X%5.134270@.pd7tw2no...
have[QUOTE]
> an
called[QUOTE]
>

---

|||Thanks for your input Anith.
Gerry
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ex9OK3q5DHA.1596@.TK2MSFTNGP10.phx.gbl...

quote:

---

> There are no general rules or norms which recommend a specific datatype

---

for

quote:

---

> a key.
> The considerations to select a good key are often misunderstood. They
> include stability (column values rarely change), simplicity (so that
> relational operations can be effective), familiarity (meaningful or

---

commonly

quote:

---

> understood by the user) and irreducibility (no proper subset of key column
> be another key). A good design can tradeoff certain characteristics in

---

favor

quote:

---

> of others to tackle specific issues with regard to key selection.
> In a precisely modeled system, a key is chosen only based on the rules
> defined at the business model & key selection involves only logical
> considerations.
> However, the implementation of databases using popular SQL DBMSs,

---

generally

quote:

---

> favors the usage of narrow keys for query efficiency, due to their smaller
> size at the physical level. This may often fall under the criteria of
> simplicity (mentioned above), but shuffling keys just for performance sake
> is not always a good idea.
> --
> Anith
>

---

database design - keys

Hello All
I am designing a database, or rather redesigning a very old database and
have a question regarding setting up key fields. The old database has a
table called Equipment with two fields:
Equipment Code - text 8
Equipment Description - text 50
In the new design I will have a table called Manufacturers that will have an
Equipment Code field, which will link to the Equipment table to get the
description. My question is, should I make a new Integer key field called
say EquipID which is what would get stored in the Manufacturers table or
should I simply use the Equipment Code field as the key? What are the
advantages/disadvantages to each method?
I have several other tables with a similar situation where the 'Code' is
unique but there are more fields in these tables.
Thanks,
GerryThis is a subject that has spawned many heated debates. Here's my take on
it:
In your case, just use the codes that exist.
In general, if your data has a natural key, use it. If the natural key is a
composite key of sufficient length/complexity (a totally subjective
determination) there might be a good reason to use a surrogate. However,
you MUST also enforce the uniqueness of the natural key! I am not against
the use of surrogate keys at all, but they should be used only after much
careful thought and consideration. Surrogate keys tend, in the hands of the
inexperienced, to lend a false sense of security ("Of course I don't have
any duplicates, my surrogate key assures that!")
Since you have a simple natural key, there is really no reason not to use
it. Adding a surrogate key just creates more data...
"News" <gerrydyck@.shaw.ca> wrote in message
news:Q7cSb.328534$X%5.134270@.pd7tw2no...
> Hello All
> I am designing a database, or rather redesigning a very old database and
> have a question regarding setting up key fields. The old database has a
> table called Equipment with two fields:
> Equipment Code - text 8
> Equipment Description - text 50
> In the new design I will have a table called Manufacturers that will have
an
> Equipment Code field, which will link to the Equipment table to get the
> description. My question is, should I make a new Integer key field called
> say EquipID which is what would get stored in the Manufacturers table or
> should I simply use the Equipment Code field as the key? What are the
> advantages/disadvantages to each method?
> I have several other tables with a similar situation where the 'Code' is
> unique but there are more fields in these tables.
> Thanks,
> Gerry
>|||There are no general rules or norms which recommend a specific datatype for
a key.
The considerations to select a good key are often misunderstood. They
include stability (column values rarely change), simplicity (so that
relational operations can be effective), familiarity (meaningful or commonly
understood by the user) and irreducibility (no proper subset of key column
be another key). A good design can tradeoff certain characteristics in favor
of others to tackle specific issues with regard to key selection.
In a precisely modeled system, a key is chosen only based on the rules
defined at the business model & key selection involves only logical
considerations.
However, the implementation of databases using popular SQL DBMSs, generally
favors the usage of narrow keys for query efficiency, due to their smaller
size at the physical level. This may often fall under the criteria of
simplicity (mentioned above), but shuffling keys just for performance sake
is not always a good idea.
--
Anith|||You may also want to consider this:
Do you have any manufacturers that manufacture more than
one piece of equipment found in the Equipment table?
This is usually the case. You may want to use some
unique identifier per Manufacturer, and add the
maufacturer's ID field to the Equipment table.
This could get even trickier since you may have mulitple
manufacturer's supplying multiple types of equipment, in
which case you may want to create a third table which
would have fields for the Manufacturer ID and the
Equipment ID to implement the many to many relationship.
Just a couple of thoughts.
Matthew Bando
matthew.bando@.CSCTGI(remove this).com
>--Original Message--
>Hello All
>I am designing a database, or rather redesigning a very
old database and
>have a question regarding setting up key fields. The
old database has a
>table called Equipment with two fields:
>Equipment Code - text 8
>Equipment Description - text 50
>In the new design I will have a table called
Manufacturers that will have an
>Equipment Code field, which will link to the Equipment
table to get the
>description. My question is, should I make a new
Integer key field called
>say EquipID which is what would get stored in the
Manufacturers table or
>should I simply use the Equipment Code field as the
key? What are the
>advantages/disadvantages to each method?
>I have several other tables with a similar situation
where the 'Code' is
>unique but there are more fields in these tables.
>Thanks,
>Gerry
>
>.
>|||You may also want to consider this:
Do you have any manufacturers that manufacture more than
one piece of equipment found in the Equipment table?
This is usually the case. You may want to use some
unique identifier per Manufacturer, and add the
maufacturer's ID field to the Equipment table.
This could get even trickier since you may have mulitple
manufacturer's supplying multiple types of equipment, in
which case you may want to create a third table which
would have fields for the Manufacturer ID and the
Equipment ID to implement the many to many relationship.
Just a couple of thoughts.
Matthew Bando
matthew.bando@.CSCTGI(remove this).com
>--Original Message--
>Hello All
>I am designing a database, or rather redesigning a very
old database and
>have a question regarding setting up key fields. The
old database has a
>table called Equipment with two fields:
>Equipment Code - text 8
>Equipment Description - text 50
>In the new design I will have a table called
Manufacturers that will have an
>Equipment Code field, which will link to the Equipment
table to get the
>description. My question is, should I make a new
Integer key field called
>say EquipID which is what would get stored in the
Manufacturers table or
>should I simply use the Equipment Code field as the
key? What are the
>advantages/disadvantages to each method?
>I have several other tables with a similar situation
where the 'Code' is
>unique but there are more fields in these tables.
>Thanks,
>Gerry
>
>.
>|||Thanks Don. This is my first SQL database and sometimes with new programs I
tend to overthink a solution. For this one, I will be sticking with a
natural key.
Gerry
"Don Peterson" <no1@.nunya.com> wrote in message
news:uVsFYAq5DHA.2556@.TK2MSFTNGP09.phx.gbl...
> This is a subject that has spawned many heated debates. Here's my take on
> it:
> In your case, just use the codes that exist.
> In general, if your data has a natural key, use it. If the natural key is
a
> composite key of sufficient length/complexity (a totally subjective
> determination) there might be a good reason to use a surrogate. However,
> you MUST also enforce the uniqueness of the natural key! I am not against
> the use of surrogate keys at all, but they should be used only after much
> careful thought and consideration. Surrogate keys tend, in the hands of
the
> inexperienced, to lend a false sense of security ("Of course I don't have
> any duplicates, my surrogate key assures that!")
> Since you have a simple natural key, there is really no reason not to use
> it. Adding a surrogate key just creates more data...
> "News" <gerrydyck@.shaw.ca> wrote in message
> news:Q7cSb.328534$X%5.134270@.pd7tw2no...
> > Hello All
> >
> > I am designing a database, or rather redesigning a very old database and
> > have a question regarding setting up key fields. The old database has a
> > table called Equipment with two fields:
> >
> > Equipment Code - text 8
> > Equipment Description - text 50
> >
> > In the new design I will have a table called Manufacturers that will
have
> an
> > Equipment Code field, which will link to the Equipment table to get the
> > description. My question is, should I make a new Integer key field
called
> > say EquipID which is what would get stored in the Manufacturers table or
> > should I simply use the Equipment Code field as the key? What are the
> > advantages/disadvantages to each method?
> >
> > I have several other tables with a similar situation where the 'Code' is
> > unique but there are more fields in these tables.
> >
> > Thanks,
> > Gerry
> >
> >
>|||Thanks for your input Anith.
Gerry
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ex9OK3q5DHA.1596@.TK2MSFTNGP10.phx.gbl...
> There are no general rules or norms which recommend a specific datatype
for
> a key.
> The considerations to select a good key are often misunderstood. They
> include stability (column values rarely change), simplicity (so that
> relational operations can be effective), familiarity (meaningful or
commonly
> understood by the user) and irreducibility (no proper subset of key column
> be another key). A good design can tradeoff certain characteristics in
favor
> of others to tackle specific issues with regard to key selection.
> In a precisely modeled system, a key is chosen only based on the rules
> defined at the business model & key selection involves only logical
> considerations.
> However, the implementation of databases using popular SQL DBMSs,
generally
> favors the usage of narrow keys for query efficiency, due to their smaller
> size at the physical level. This may often fall under the criteria of
> simplicity (mentioned above), but shuffling keys just for performance sake
> is not always a good idea.
> --
> Anith
>|||Thanks Matthew.
At this point, there will only be one equipment code for one manufacturer.
Although I am having meetings next week which will confirm this. The old
database had one-to-one but anytime you update a program or database then
that is the time to think about such things. Perhaps they don't know the
possibilities after having used a paradox database for 12 years!
Gerry
"Matthew Bando" <anonymous@.discussions.microsoft.com> wrote in message
news:740c01c3e738$180f8140$a401280a@.phx.gbl...
> You may also want to consider this:
> Do you have any manufacturers that manufacture more than
> one piece of equipment found in the Equipment table?
> This is usually the case. You may want to use some
> unique identifier per Manufacturer, and add the
> maufacturer's ID field to the Equipment table.
> This could get even trickier since you may have mulitple
> manufacturer's supplying multiple types of equipment, in
> which case you may want to create a third table which
> would have fields for the Manufacturer ID and the
> Equipment ID to implement the many to many relationship.
> Just a couple of thoughts.
> Matthew Bando
> matthew.bando@.CSCTGI(remove this).com
>
> >--Original Message--
> >Hello All
> >
> >I am designing a database, or rather redesigning a very
> old database and
> >have a question regarding setting up key fields. The
> old database has a
> >table called Equipment with two fields:
> >
> >Equipment Code - text 8
> >Equipment Description - text 50
> >
> >In the new design I will have a table called
> Manufacturers that will have an
> >Equipment Code field, which will link to the Equipment
> table to get the
> >description. My question is, should I make a new
> Integer key field called
> >say EquipID which is what would get stored in the
> Manufacturers table or
> >should I simply use the Equipment Code field as the
> key? What are the
> >advantages/disadvantages to each method?
> >
> >I have several other tables with a similar situation
> where the 'Code' is
> >unique but there are more fields in these tables.
> >
> >Thanks,
> >Gerry
> >
> >
> >.
> >

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
> > >
> > >
> >
> >
>