What would be the difference between these 2 job titles ?The difference really depends on the job descriptions rather than the
titles. These positions could very well have the same responsibilities is
some companies. That said, I would expect a person with the title DBA to be
focused on operational areas like availability, backup/recovery and
maintenance. A Database Engineer usually is more focused on database
development.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OdhwPHusGHA.1216@.TK2MSFTNGP03.phx.gbl...
> What would be the difference between these 2 job titles ?
>|||> some companies. That said, I would expect a person with the title DBA to be
> focused on operational areas like availability, backup/recovery and
> maintenance.
AND most importantly: securiy!
> A Database Engineer usually is more focused on database development.
Shouldn't it be about making a DBMS?
--
.~. Might, Courage, Vision, SINCERITY. http://www.linux-sxs.org
/ v \ Simplicity is Beauty! May the Force and Farce be with you!
/( _ )\ (Ubuntu 6.06) Linux 2.6.17.6
^ ^ 21:35:02 up 13 days 4:58 0 users load average: 1.00 1.00 1.00
news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk|||> AND most importantly: securiy!
Absolutely. Security is also a very important DBA concern. I only listed a
few examples.
> Shouldn't it be about making a DBMS?
Yes, the responsibilities of a Database Engineer can include logical and
physical data modeling as well as stored procedure development. In larger
organizations, data modeling can be a specialized position by itself with a
variety of titles, including Database Engineer, Data Architect, etc.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Man-wai Chang" <toylet.toylet@.gmail.com> wrote in message
news:e2DitPxsGHA.5044@.TK2MSFTNGP05.phx.gbl...
>> some companies. That said, I would expect a person with the title DBA to
>> be
>> focused on operational areas like availability, backup/recovery and
>> maintenance.
> AND most importantly: securiy!
>> A Database Engineer usually is more focused on database development.
> Shouldn't it be about making a DBMS?
> --
> .~. Might, Courage, Vision, SINCERITY. http://www.linux-sxs.org
> / v \ Simplicity is Beauty! May the Force and Farce be with you!
> /( _ )\ (Ubuntu 6.06) Linux 2.6.17.6
> ^ ^ 21:35:02 up 13 days 4:58 0 users load average: 1.00 1.00 1.00
> news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk|||On Sat, 29 Jul 2006 07:04:32 -0500, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:
>The difference really depends on the job descriptions rather than the
>titles. These positions could very well have the same responsibilities is
>some companies. That said, I would expect a person with the title DBA to be
>focused on operational areas like availability, backup/recovery and
>maintenance. A Database Engineer usually is more focused on database
>development.
Might want to throw the term "architect" in there somewhere, "database
architect" seems pretty common these days, meaning emphasis of
development, compared to "DBA".
Performance tuning seems to fall somewhere in the middle.
J.sql
Showing posts with label job. Show all posts
Showing posts with label job. Show all posts
Wednesday, March 21, 2012
Database Engineer vs DBA
What would be the difference between these 2 job titles ?The difference really depends on the job descriptions rather than the
titles. These positions could very well have the same responsibilities is
some companies. That said, I would expect a person with the title DBA to be
focused on operational areas like availability, backup/recovery and
maintenance. A Database Engineer usually is more focused on database
development.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OdhwPHusGHA.1216@.TK2MSFTNGP03.phx.gbl...
> What would be the difference between these 2 job titles ?
>|||> some companies. That said, I would expect a person with the title DBA to ben">
> focused on operational areas like availability, backup/recovery and
> maintenance.
AND most importantly: securiy!
> A Database Engineer usually is more focused on database development.
Shouldn't it be about making a DBMS?
.~. Might, Courage, Vision, SINCERITY. http://www.linux-sxs.org
/ v \ Simplicity is Beauty! May the Force and Farce be with you!
/( _ )\ (Ubuntu 6.06) Linux 2.6.17.6
^ ^ 21:35:02 up 13 days 4:58 0 users load average: 1.00 1.00 1.00
news://news.3home.netnews://news.hkpcug.orgnews://news.newsgroup.com.hk|||> AND most importantly: securiy!
Absolutely. Security is also a very important DBA concern. I only listed a
few examples.
> Shouldn't it be about making a DBMS?
Yes, the responsibilities of a Database Engineer can include logical and
physical data modeling as well as stored procedure development. In larger
organizations, data modeling can be a specialized position by itself with a
variety of titles, including Database Engineer, Data Architect, etc.
Hope this helps.
Dan Guzman
SQL Server MVP
"Man-wai Chang" <toylet.toylet@.gmail.com> wrote in message
news:e2DitPxsGHA.5044@.TK2MSFTNGP05.phx.gbl...
> AND most importantly: securiy!
>
> Shouldn't it be about making a DBMS?
> --
> .~. Might, Courage, Vision, SINCERITY. http://www.linux-sxs.org
> / v \ Simplicity is Beauty! May the Force and Farce be with you!
> /( _ )\ (Ubuntu 6.06) Linux 2.6.17.6
> ^ ^ 21:35:02 up 13 days 4:58 0 users load average: 1.00 1.00 1.00
> news://news.3home.netnews://news.hkpcug.orgnews://news.newsgroup.com.hk|||On Sat, 29 Jul 2006 07:04:32 -0500, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:
>The difference really depends on the job descriptions rather than the
>titles. These positions could very well have the same responsibilities is
>some companies. That said, I would expect a person with the title DBA to b
e
>focused on operational areas like availability, backup/recovery and
>maintenance. A Database Engineer usually is more focused on database
>development.
Might want to throw the term "architect" in there somewhere, "database
architect" seems pretty common these days, meaning emphasis of
development, compared to "DBA".
Performance tuning seems to fall somewhere in the middle.
J.
titles. These positions could very well have the same responsibilities is
some companies. That said, I would expect a person with the title DBA to be
focused on operational areas like availability, backup/recovery and
maintenance. A Database Engineer usually is more focused on database
development.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OdhwPHusGHA.1216@.TK2MSFTNGP03.phx.gbl...
> What would be the difference between these 2 job titles ?
>|||> some companies. That said, I would expect a person with the title DBA to ben">
> focused on operational areas like availability, backup/recovery and
> maintenance.
AND most importantly: securiy!
> A Database Engineer usually is more focused on database development.
Shouldn't it be about making a DBMS?
.~. Might, Courage, Vision, SINCERITY. http://www.linux-sxs.org
/ v \ Simplicity is Beauty! May the Force and Farce be with you!
/( _ )\ (Ubuntu 6.06) Linux 2.6.17.6
^ ^ 21:35:02 up 13 days 4:58 0 users load average: 1.00 1.00 1.00
news://news.3home.netnews://news.hkpcug.orgnews://news.newsgroup.com.hk|||> AND most importantly: securiy!
Absolutely. Security is also a very important DBA concern. I only listed a
few examples.
> Shouldn't it be about making a DBMS?
Yes, the responsibilities of a Database Engineer can include logical and
physical data modeling as well as stored procedure development. In larger
organizations, data modeling can be a specialized position by itself with a
variety of titles, including Database Engineer, Data Architect, etc.
Hope this helps.
Dan Guzman
SQL Server MVP
"Man-wai Chang" <toylet.toylet@.gmail.com> wrote in message
news:e2DitPxsGHA.5044@.TK2MSFTNGP05.phx.gbl...
> AND most importantly: securiy!
>
> Shouldn't it be about making a DBMS?
> --
> .~. Might, Courage, Vision, SINCERITY. http://www.linux-sxs.org
> / v \ Simplicity is Beauty! May the Force and Farce be with you!
> /( _ )\ (Ubuntu 6.06) Linux 2.6.17.6
> ^ ^ 21:35:02 up 13 days 4:58 0 users load average: 1.00 1.00 1.00
> news://news.3home.netnews://news.hkpcug.orgnews://news.newsgroup.com.hk|||On Sat, 29 Jul 2006 07:04:32 -0500, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:
>The difference really depends on the job descriptions rather than the
>titles. These positions could very well have the same responsibilities is
>some companies. That said, I would expect a person with the title DBA to b
e
>focused on operational areas like availability, backup/recovery and
>maintenance. A Database Engineer usually is more focused on database
>development.
Might want to throw the term "architect" in there somewhere, "database
architect" seems pretty common these days, meaning emphasis of
development, compared to "DBA".
Performance tuning seems to fall somewhere in the middle.
J.
Wednesday, March 7, 2012
Database Developer Job
Here is a job we are currently looking for. If you have or are working on your MCDBA please apply.
https://www2.mdanderson.org/sapp/webhire/index.cfm?pagename=details&JobID=04-0015432
Treyo-yea...if you have any question please ask
Trey
https://www2.mdanderson.org/sapp/webhire/index.cfm?pagename=details&JobID=04-0015432
Treyo-yea...if you have any question please ask
Trey
Database Design Tool
I've been desiging a database.
From a previous job, I've seen a report that runs off the database management tool in SQL Server 2005 that prints out comments entered at the table and column level. I can't find that report on my current system.
I wondered if this is a feature of the tool when the tool is installed as a client rather than the tool locally on the server, as part of the server install.
Any help is appreciated.
Andrew
Hi Andrew,Just a guess but maybe you are talking about extended properties?
From the context menu of a table or column you will find a "page" called Extended Properties for that table or column. Should work the same regardless of whether you are on a local SSMS or a remote SSMS. I believe you can also script only the extended properties of all or selected objects if that is interesting. Note that extended properties are optional and must be provided by developer. Otherwise they are blank.
Regards,
Bill
|||
I've found a comment property for both columns and tables. I don't recall going to an 'extended' property page. The comment property at the table level appears on the regular list of table properties at the right of the design tool. To put a comment on a column, I've used the column modify method. I right click on the column and pick 'modify'.
Is the script you refer to something I have to write, or is there an existing script in the tool.
I wasn't paying close enough attention, but I think the output from the report I saw was a Word Doc.
Andrew
|||There is not anything built in that will generate a word doc but I am sure the community has one, try seaerching some of the following sites;
www.codeplex.com
www.sqlteam.com
www.sqlservercentral.com
www.codeproject.com
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
>
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
>
Saturday, February 25, 2012
Database design question
I am brand new to both programming and databases and am trying to design a database for my job as a paramedic.
I am using Visual Studio 2005 , and MSSQL . I am trying to create a log for all of our ambulance calls.
My question comes in when I try to design a field that holds our case number , which is formatted as 07-0001. Basically, the year , then four digits for the remainder of the case number .
Any help on how to accomplish this would be SO appreciated !!!I would use a varchar for this.
presumably you want to preserve the minus sign and the leading zero - this rules out any numeric storage types such as int.|||Thanks ! So, does this mean I can't use that field as an ID for my database ? Also, how would I go about the formatting I would need ?
Sorry to be such a novice, but I do SO appreciate the help !|||the reply depends on many other questions. will the database generate the next call-number or it is a user input/front-end generated number? is the number editable? will the number recycle missing numbers specially the last deleted ones?
there are quite a few options. it could be saved in 2 separate fileds and formatted properly during display, it could be a formula field, it could be a plain varchar field with validations etc. etc...
I am using Visual Studio 2005 , and MSSQL . I am trying to create a log for all of our ambulance calls.
My question comes in when I try to design a field that holds our case number , which is formatted as 07-0001. Basically, the year , then four digits for the remainder of the case number .
Any help on how to accomplish this would be SO appreciated !!!I would use a varchar for this.
presumably you want to preserve the minus sign and the leading zero - this rules out any numeric storage types such as int.|||Thanks ! So, does this mean I can't use that field as an ID for my database ? Also, how would I go about the formatting I would need ?
Sorry to be such a novice, but I do SO appreciate the help !|||the reply depends on many other questions. will the database generate the next call-number or it is a user input/front-end generated number? is the number editable? will the number recycle missing numbers specially the last deleted ones?
there are quite a few options. it could be saved in 2 separate fileds and formatted properly during display, it could be a formula field, it could be a plain varchar field with validations etc. etc...
Database Design Question
I am writing a job tracking app with ASP/MSSQL and I have a question about setting up an archive system.
For my app, the database consists of several relational tables that track all of the information about a job. I want to set it up so that if a job is completed, it gets archived.
Over the next 2 or 3 years, this system will likely grow to 100,000+ records.
My question is, should I (1) just use a flag to mark the job as archived, (2) make a flat record of the job and move it into one archive table, or (3) set up archive tables that mirror my production tables and copy the record to the archive tables keeping the relationships intact?
Does anyone have a method that has worked well for them in the past? What will keep my app running the fastest? Any suggestions would be appreciated. Thanks!i always prefer a single table with the jobs flagged as not active (1 or 0)
it makes for simpler querying later with just
select * from table1
where active is 1
and
when you want to see completed tasks
select * from table1
where active is 0
(less tables, less joins etc,... )|||Cool ... thanks!
Do you think 100,000+ records somewhere down the line will be a problem when running reports or doing searches if I just use a flag? I'm trying to design this w/ optimized tuning in mind because I don't know how long it will be in place.
When I flag a job as complete (bit 0,1) and then query w/
WHERE open=(0 or 1)
It still has to check every record correct? How many records before there is a noticable difference in the speed of the app?|||two ways to think of this
you could make the column a bit datatype to save on space but it stores 1,0 or null small to the point but you cannot create an index on this column
OR
create the column as tinyint (1 byte , largest value 255) and then you could index it
but the indexing on a heavily duplicated columns is not recommended
you also could create statistics on the flagged column
i'm of the mind that you should be okay with this but just monitor performance during operations|||Thanks for your ideas!|||The optimizer will likely not use an index on a boolean column, regardless of whether it is bit, tiny int, or even char(1). There just isn't sufficient cardinality to make it worthwhile.|||true
this is an area where i have (gulp!) envied ORACLE
they have bitmap indexes that you can use on heavily duplicated columns like gender and yes\no
oh well maybe in yukon.|||Yeah, but that's not the point.
You could create an index on boolean values stored in a tinyint field, but the optimizer would likely ignore it because its not any more efficient than using a table scan. The cardinality is likely so low (high? I can never remember...) that the small performance boost gained by using the index is offset by the overhead of accessing the index.
I'm not an Oracle DBA, but I don't see how Oracle would gain much from indexing boolean values either.|||yes and using bitmap indexes on yes no or gender or small sets like rating can give you a boost
but you are right about the boolean but still i prefer the 1/0 flag to the splitting up of tables|||I agree. In most cases (not all) a flag is better than splitting the data.|||And the rambling goes on. Well, I don't know who would come up with an idea on creating an index just on a Gender or Yes/No field. And you guys keep on going on it. Kind of silly, would you really consider doing it? Its value WILL come to play if such a field is combined with something else.
And another thing, - YOU CAN CREATE AN INDEX ON A BIT FIELD, even by itself.|||Yes and no.
You can't create an index on a bit field through Enterprise Manager's table design form.
Also, Books Online states:
"Columns of type bit cannot have indexes on them. "
But you can create the index using SQL:
CREATE INDEX [IX_BitTest] ON [dbo].[YourTable]([BitValue]) ON [PRIMARY]
GO
...so there is a loophole or inconsistency in SQL server regarding bit values.
Lot's of people come up with the idea of indexing boolean fields because they don't have an in depth understanding of how SQL Server works. That's why they come to dbforums for advice, not ridicule Bob.|||Originally posted by blindman
...Lot's of people come up with the idea of indexing boolean fields because they don't have an in depth understanding of how SQL Server works. That's why they come to dbforums for advice, not ridicule Bob. Hey, you started first, I just try to survive here. Remember your comments about my inability to understand English? Help me out here, - weren't you ridiculing me?
Somet things I found funny, others I saw as an insult, but in neither of those posts were you even attempting to hide or retract your intent to ridicule. I guess you yourself can't take your own medicine, hey?! ;)
Originally posted by blindman
...You can't create an index on a bit field through Enterprise Manager's table design form.
Also, Books Online states:
"Columns of type bit cannot have indexes on them. "
Which Books Online are you reading, Lindman? Get on with the program, everybody else did!
SQL Server Books Online January 2004 Update (http://www.microsoft.com/downloads/details.aspx?FamilyId=A6F79CB1-A420-445F-8A4B-BD77A7DA194B&displaylang=en#filelist)|||YEAH BOB!!!!
rdjabarov does insulted....|||Always go with your strength.|||At the scale of 100,00 or so records, you'll certainly want to be able to index the field, so bit is out. Plus people writing code against boolean fields get confused about null evaluations and equalities, so it's better to use a more humane data type anyway.
tinyint will take the least amount of space and provide the fastest field evaluation available. Although, at such a small table size, neither is a major concern. Whether or not you are expanding a server tree in the Enterprise Manager during query execution will have a much greater effect on performance than how you optimize this field. I agree that standard indexing would have little effect, but I would use a clustered index with the status field as the first element and then your primary where condition field as the second element of the index. The optimizer is unable to overlook this index becuase it is reflected in the table's physicality.|||The optimizer does not overlook boolean indexes because it is lazy, or because it can't use them. It chooses to overlook them when it is more efficient NOT to use them. You could force it to use the index using a query hint, but forcing the use of the index does not necessarily make the query more efficient. The same goes for putting the boolean index at the front of the key.|||Originally posted by dbslave
tinyint will take the least amount of space
Isn't tinyint and char(1) the same size?|||Originally posted by Lindman
The optimizer does not overlook boolean indexes because it is lazy, or because it can't use them. It chooses to overlook them when it is more efficient NOT to use them... The optimizer DOES NOT overlook indexes when it is more efficient not to use them. That decision is based on STATISTICS associated with tables referenced by the query. Don't give too much credit to the Optimizer.
And again, according to Books Online (UPDATED!!!) and personal experience, you can have an index on a bit field. In fact, you can even have a clustered index on a bit field (wouldn't know what for though). The point is that you shouldn't have an index on a field which data is equally split 50/50. Such a field should be part of a composite index to make a difference.
Going back to the original question, whether to have a flag or a replica of the original table for archive purposes...It depends on how many records are going to be archived at a time, and how many records regardless of whether they are archived or not would be retrieved. And also, how wide is the table now.
The decision should depend on these questions because if you plan to archive thousands of rows at a time and the table is wide enough (judgment call, no particulat width is in mind), I'd go with a flag of bit datatype, since updating of 1 field will not kill my transaction log as opposed to inserting into a different table.|||Originally posted by rdjabarov
In fact, you can even have a clustered index on a bit field (wouldn't know what for though). The point is that you shouldn't have an index on a field which data is equally split 50/50. Such a field should be part of a composite index to make a difference.
Logically, yes. Physically, this can be useful for partitioning data.|||Originally posted by Brett Kaiser
Isn't tinyint and char(1) the same size?
Yes, but comparisons on a tinyint are way faster than on a char(1)|||Why would that be?
Got any links that discuss this?
and whats waaaaaaaaaay faster?|||Originally posted by dbslave
Logically, yes. Physically, this can be useful for partitioning data. You're planning to partition 2 records?|||Clustered does not necessarily mean unique.|||Originally posted by blindman
Clustered does not necessarily mean unique. Did you get your "real" Books Online? You have a lot to read...|||USE Northwind
GO
CREATE TABLE myTable99(Col1 int, Col2 int)
GO
CREATE CLUSTERED INDEX myTable99_IX1 ON myTable99(Col1)
GO
INSERT INTO myTable99 SELECT 1,1 UNION ALL SELECT 1,1
GO
SELECT * FROM myTable99
GO
DROP TABLE myTable99
GO
Now say you're sorry...
And I want you all to play nice or it's time out...
Oh, wait, that stuff doesn't start till I get home...
Sorry...|||Hey Brett, I wasn't arguing that clustered does not mean unique! I just don't like "him" butting into other people's conversation, just to make some pointless point (or however "he" put it).|||Oh good heavens, that's exactly why I participate. Having a dozen eyes available helps "keep me honest" because I know that there are many points of view, most of which are probably more "on track" at any given point in time than mine are.
While there are plenty of people that annoy me in one way or another, its still a good thing that they all participate (at least in my opinion). If one person annoys me too badly, I just filter them from my view for a while (until I cool off).
There's a lot of advice that is well meant, and well received that nearly sends me ballistic. I know how badly certain approaches can hammer things up with large (anything between 150 and 1200 Gb) databases, but those approaches often work fine in smaller, simpler environments. I have to keep reminding myself to give appropriate answers based on the questions!
-PatP|||Originally posted by Brett Kaiser
Isn't tinyint and char(1) the same size?
yes they are both 1 byte datatypes but
integer vs char|||I'm with Brett in never having read anything about tinyint being a faster comparison than char(1). White papers?|||boys, this is a non-smoking flight, put your guns down
tiny, small, why not bit? you'd have to have at least 9 of them before it reaches the size of the tinyint. what's the fuss about?|||If tinyint is indeed faster than char(1) then I'll use it in the future.|||oh that's just absurd! how can a datatype be faster than another datatype?|||Originally posted by ms_sql_dba
oh that's just absurd! how can a datatype be faster than another datatype? Way to go, she got you on that one :D :D :D :D :D|||Hey .. where was I when all this was going on ...
hmm ... have been busy lately ... but will be back soon ...|||she?|||Now that you point out .. i was also confused by "She" ...
Hmm .. i m getting confused a lot lately ...|||Originally posted by r937
she? The "ms" can be interpreted as many things,k among them: Microsoft, or as an honoric "Ms." that was quite popular among what were called the "rabidly liberated" in the 1970s. Ms. was used instead of Mrs. or Miss by a very small group of women that didn't want to reveal their marital status. I assume that rdjabarov read it in that context.
-PatP|||She got me?
I never claimed it was faster. The notion that tinyint was faster than char(1) was news to both Brett and I, and we simply asked if there was any documentation to support the assertion.
As usual, ms_sql_dba didn't bother to really read the previous posts. She keeps to her old habit of dropping in at the end of the thread and repeating something somebody else had already written 10 posts before, without adding a shred of value to the conversation.
Nyah, nyah, ya missed me! Ya missed me! PPPbbbbbtttt! :p
Grow up.|||It's got to be a full moon or something...
does ANYONE want to put forth WHY tinyint would be more effecient that char(1)?
I'm here for education (and social interaction, well I guess some types of it...the others type are a watse of time...life is too short)|||Originally posted by ms_sql_dba
boys, this is a non-smoking flight, put your guns down
tiny, small, why not bit? you'd have to have at least 9 of them before it reaches the size of the tinyint. what's the fuss about?
[With the guns blazing]
This is straight out of the Holy book
bit
If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte
tinyint
Integer data from 0 through 255. Storage size is 1 byte.
[/With the guns blazing]
first Get a copy of the Holy Book and then read it from end to end.|||OK, so that means...
1 bit = 1 byte
2 bits = 1 byte
3 bits = 1 byte
4 bits = 1 byte
5 bits = 1 byte
6 bits = 1 byte
7 bits = 1 byte
8 bits = 1 byte
But the values of bit is still 0 or 1 or null (which isn't a value at all)
And has very limited meaning, and I've never seen a good reason to index it..
And Char(1) and tinyint are still both 1 byte
And is it a misconception that 1 of the above is more effecient in an index than the other...|||Originally posted by Brett Kaiser
OK, so that means...
1 bit = 1 byte
2 bits = 1 byte
3 bits = 1 byte
4 bits = 1 byte
5 bits = 1 byte
6 bits = 1 byte
7 bits = 1 byte
8 bits = 1 byte
9 bit = 2 byte
10 bits = 2 byte
11 bits = 2 byte
12 bits = 2 byte
13 bits = 2 byte
14 bits = 2 byte
15 bits = 2 byte
16 bits = 2 byte
and so on ...
so 1 single bit storage space = tiny int storage space|||It was dbslave who originally stated that tinyint was faster, and since we haven't heard anything back from him I'm going to assume that was an error and recommend we drop the inquiry.|||i feel slightly responisble because it was me who brought it up.
but if you are gonna flame someone else then count me in
Mob Rules
string him up...|||Nah, I wasn't stringing him up. Everybody makes mistakes, or has made incorrect assumptions while wading through the masses of documentation for SQL server. I've learned a hell of a lot from this forum.|||Originally posted by blindman
I've learned a hell of a lot from this forum.
Ditto...
Just trying to realize either way...
My gut says no....|||My gut says no....
Thats wierd ... you still got guts|||Originally posted by blindman
She got me?
I never claimed it was faster. The notion that tinyint was faster than char(1) was news to both Brett and I, and we simply asked if there was any documentation to support the assertion.
As usual, ms_sql_dba didn't bother to really read the previous posts. She keeps to her old habit of dropping in at the end of the thread and repeating something somebody else had already written 10 posts before, without adding a shred of value to the conversation.
Nyah, nyah, ya missed me! Ya missed me! PPPbbbbbtttt! :p
Grow up. So, that makes 2 of us, me and ms_sql_dba. At least I am not alone!!!|||Not to jump in and strive for polarity, but there's been nothing I've seen that say that's true...
And are you going to the rainbow lounge?
Make sure you bring a lot of singles...
:D|||Originally posted by Enigma
Thats wierd ... you still got guts I'll bet that I've got more gut than anybody around here!
Somebody made a comment about a rather heated discussion here being an "international incident", and I pointed out that even I'm not that fat (yet).
-PatP|||...Sounds like a call for a pissing contest...|||Originally posted by rdjabarov
...Sounds like a call for a pissing contest... Well, I'd guess that I could "hold my own" (if you'll pardon the term) in that area too!
-PatP|||Try this for yourself. Run test few times to be sure table is in cache. You will be surprised.
create table test(i int identity primary key, a tinyint, b char)
set nocount on
declare @.c int
set @.c = 0
while @.c < 20000 begin
insert test(a,b)
values(0,'0')
insert test(a,b)
values(1,'1')
set @.c = @.c + 1 end
--test scan
declare @.t datetime
set @.t = getdate()
select a
from test
where a = 1
print 'tinyint: ' + cast(datediff(ms,@.t,getdate()) as varchar) + 'ms'
set @.t = getdate()
select b
from test
where b = '1'
print 'char: ' + cast(datediff(ms,@.t,getdate()) as varchar) + 'ms'
--test seek as run above script after adding these indexes:
create index test_a on test(a)
create index test_b on test(b)
Chars were slower before SQL Server version 7.|||I got about 700ms faster on tinyint after creating indexes.|||And about 1600 faster than using char if I replace tinyint with bit...|||Well, in the interests of scientific speculation:
-- CREATE TABLE test_datatype_speed_1(
-- ident INT IDENTITY PRIMARY KEY,
-- tinyint_noindex TINYINT,
-- char1_noindex CHAR(1),
-- bit_noindex BIT,
-- tinyint_index TINYINT,
-- char1_index CHAR(1),
-- bit_index BIT)
--
-- CREATE INDEX idx_test_datatype_speed_1_tinyint ON test_datatype_speed_1(tinyint_index)
-- CREATE INDEX idx_test_datatype_speed_1_char1 ON test_datatype_speed_1(char1_index)
-- CREATE INDEX idx_test_datatype_speed_1_bit ON test_datatype_speed_1(bit_index)
--
-- SET NOCOUNT ON
--
-- DECLARE @.c INT
--
-- SELECT @.c = 0
--
-- WHILE @.c < 20000
-- BEGIN
--
-- INSERT test_datatype_speed_1(tinyint_noindex, char1_noindex, bit_noindex, tinyint_index, char1_index, bit_index)
-- VALUES(0,'0',0,0,'0',0)
-- INSERT test_datatype_speed_1(tinyint_noindex, char1_noindex, bit_noindex, tinyint_index, char1_index, bit_index)
-- VALUES(1,'1',1,1,'1',1)
-- SELECT @.c = @.c + 1
--
-- END
GO
SET STATISTICS IO ON
--test scan
DECLARE @.t DATETIME
--Test tinyint_noindex
SELECT @.t = GETDATE()
SELECT tinyint_noindex
FROM test_datatype_speed_1
WHERE tinyint_noindex = 1
PRINT 'tinyint_noindex: ' + CAST(DATEDIFF(ms,@.t,GETDATE()) AS VARCHAR) + 'ms'
--Test char1_noindex
SELECT @.t = GETDATE()
SELECT char1_noindex
FROM test_datatype_speed_1
WHERE char1_noindex = '1'
PRINT 'char1_noindex: ' + CAST(DATEDIFF(ms,@.t,GETDATE()) AS VARCHAR) + 'ms'
--Test bit_noindex
SELECT @.t = GETDATE()
SELECT bit_noindex
FROM test_datatype_speed_1
WHERE bit_noindex = '1'
PRINT 'bit_noindex: ' + CAST(DATEDIFF(ms,@.t,GETDATE()) AS VARCHAR) + 'ms'
--Test tinyint_index
SELECT @.t = GETDATE()
SELECT tinyint_index
FROM test_datatype_speed_1
WHERE tinyint_index = 1
PRINT 'tinyint_index: ' + CAST(DATEDIFF(ms,@.t,GETDATE()) AS VARCHAR) + 'ms'
--Test char1_index
SELECT @.t = GETDATE()
SELECT char1_index
FROM test_datatype_speed_1
WHERE char1_index = '1'
PRINT 'char1_index: ' + CAST(DATEDIFF(ms,@.t,GETDATE()) AS VARCHAR) + 'ms'
--Test bit_index
SELECT @.t = GETDATE()
SELECT bit_index
FROM test_datatype_speed_1
WHERE bit_index = '1'
PRINT 'bit_index: ' + CAST(DATEDIFF(ms,@.t,GETDATE()) AS VARCHAR) + 'ms'
SET STATISTICS IO OFF
GO
This was the average result on my machine:
Table 'test_datatype_speed_1'. Scan count 1, logical reads 91, physical reads 0, read-ahead reads 0.
tinyint_noindex: 30ms
Table 'test_datatype_speed_1'. Scan count 1, logical reads 91, physical reads 0, read-ahead reads 0.
char1_noindex: 93ms
Table 'test_datatype_speed_1'. Scan count 1, logical reads 91, physical reads 0, read-ahead reads 0.
bit_noindex: 63ms
Table 'test_datatype_speed_1'. Scan count 1, logical reads 30, physical reads 0, read-ahead reads 0.
tinyint_index: 63ms
Table 'test_datatype_speed_1'. Scan count 1, logical reads 30, physical reads 0, read-ahead reads 0.
char1_index: 93ms
Table 'test_datatype_speed_1'. Scan count 1, logical reads 30, physical reads 0, read-ahead reads 0.
bit_index: 60ms
Kind of interesting.
I'm running Windows 2003 Standard Edition/SQL Server 2000 Enterprise Edition/2ghz Xeon/1gb RAM|||I'm running Windows 2003 Standard Edition/SQL Server 2000 Enterprise Edition/2ghz Xeon/1gb RAM
i'm more impressed by your server.
oh yeah nice code too|||OK, cool, I'm up for testing things out...
Anyone got anything from M$ though?
Also, how curious...an non indexed tinuint is 33 ms faster?
Doesn't make sense...
I bet the results are significantly different when we're talking volumes...|||I just got around to running HansVE's code, and sometimes tinyint was faster and sometimes char was faster. It was about 50/50, so it still looks like a wash to me, at least until I see some kind of documentation or at least some reasoning.
And indexes made no difference.|||How many records are you guys testing with? Also, testing against 1-field index with 1/0 or y/n is going to yield an index scan. And we all know that the number of index pages for all 3 datatypes (char, tinyint, bit) will be the same. In order to test it for practical real-life situations we at least need to agree that such a field needs to be tested with another field with higher selectivity than 50/50.|||Agreed. 50/50 may be a typical distribution of boolean data, but it is not always the case and performance may be different with different cardinality.
90/10?|||No, I was talking about combining this boolean field with another, let's say a 10-character field or a date, while creating an index, and then using this combination of fields in the WHERE clause or in a JOIN. Something like this:
create table t1 (f1 char(10) not null, f1 tinyint/char(1)/bit not null)
--here we need to insert a large number of records, about 100K, where f1 would be 10% duplicates and f2 would be 50% dups.
select * from t1 where f1 = <something> and f2 = 'y'/1/1|||If you read the whole post, the entire thing was centered around bit vs. tinyint. Then, the whole char(1) thing got thrown in. So, if we're talking true bit comparison, you should stick with 0 and 1. Otherwise, you're expanding the field to do a full-scale comparison of datatypes.
In this case, you would have to set baselines at all kinds of different selectivity levels, index level (fill-factors, clustered vs. non-clustered, single vs multi-column), scale, precision, datatype, etc.
After you set the baseline, you have just began the real work of analyzing the datatype performance. You would then have to compare across size of system, memory, load in tps, size of recordset per selectivity, etc.
It would be great fun, but it's probably a little out of the scope of the thread isn't it.
:)|||Oh no...A thread that has strayed....
What will happen next?
No more tequila?
AAAAAAAAAAAAAHHHHHHHHHHHHHHHHHHHHHHHHHHHH
:D|||Let the thread keep going as long as it is interesting and informative.
rdjabarov, can you adapt the prevous sample code to test or illustrate your conjecture?|||I was hoping Brett will do his "create mytable99..." thing and I'll tweak it ;)|||Spoken a like a true lazy DBA! (And I was hinting that YOU should do it because I didn't want to.)
In Enterprise Manager, right-click on the Procedures folder, select "New Stored Procedure", and choose the "Ask Brett to do it" button.|||use a cross join
:cool:|||Originally posted by blindman
Spoken a like a true lazy DBA! (And I was hinting that YOU should do it because I didn't want to.)
In Enterprise Manager, right-click on the Procedures folder, select "New Stored Procedure", and choose the "Ask Brett to do it" button.
That's coming out in Yukon
:D|||Let the thread keep going as long as it is interesting and informative.
We better just stick to the interesting part. :)|||Personally, I don't give a rats arse which is faster but I am surprised that the collation of the CHAR data type has not been mentioned.
A binary collation outperforms a non binary collation...
Thanks to Derrick's code and simply adding "COLLATE Latin1_General_BIN " against the CHAR columns reveals...
Table 'TEST_DATATYPE_SPEED_1'. Scan count 1, logical reads 91, physical reads 0, read-ahead reads 0.
tinyint_noindex: 73ms
Table 'TEST_DATATYPE_SPEED_1'. Scan count 1, logical reads 91, physical reads 0, read-ahead reads 0.
char1_noindex: 100ms
Table 'TEST_DATATYPE_SPEED_1'. Scan count 1, logical reads 91, physical reads 0, read-ahead reads 0.
bit_noindex: 90ms
Table 'TEST_DATATYPE_SPEED_1'. Scan count 1, logical reads 23, physical reads 0, read-ahead reads 0.
tinyint_index: 80ms
Table 'TEST_DATATYPE_SPEED_1'. Scan count 1, logical reads 23, physical reads 0, read-ahead reads 0.
char1_index: 80ms
Table 'TEST_DATATYPE_SPEED_1'. Scan count 1, logical reads 23, physical reads 0, read-ahead reads 0.
bit_index: 80ms|||(20000 row(s) affected)
Table 'test_datatype_speed_1'. Scan count 1, logical reads 92, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
tinyint_noindex: 230ms
(20000 row(s) affected)
Table 'test_datatype_speed_1'. Scan count 1, logical reads 92, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
char1_noindex: 360ms
(20000 row(s) affected)
Table 'test_datatype_speed_1'. Scan count 1, logical reads 92, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
bit_noindex: 290ms
(20000 row(s) affected)
Table 'test_datatype_speed_1'. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
tinyint_index: 183ms
(20000 row(s) affected)
Table 'test_datatype_speed_1'. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
char1_index: 230ms
(20000 row(s) affected)
Table 'test_datatype_speed_1'. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
bit_index: 250ms|||Tried out the following
In Enterprise Manager, right-click on the Procedures folder, select "New Stored Procedure", and choose the "Ask Brett to do it" button.
It simply closed my SQL workbench and shut down the system ...
Weird !!! :D|||That's becasue it engages the margarita request task...
It involves shut down, turn off lights, catch train, fire up blender....
and ok...if we say tinyint IS faster, that's great...
Now add the join back to the code table to obtain the meaningful value for the surrogate key...|||Originally posted by Brett Kaiser
That's becasue it engages the margarita request task...
It involves shut down, turn off lights, catch train, fire up blender....
Take a train ... you must be living quite far from your office ...|||3 stops...can read 1 article...
town shuttle picks me up 3 houses away to the train...monthly pass US$36.00 a month
Company shuttle takes me to work...
reverse on the way home...
transmission blew on the piece o junk chrysler...after they replaced it under warranty...
No payement, No Gas, No Insurance, No hassle
beautiful Newark NJ|||Wish we had commuter rail in Ohio...|||Originally posted by blindman
Wish we had commuter rail in Ohio...
http://www.cota.com/cota/cotaweb/main.xml
Actually it's just proposed
http://www.cotafasttrax.com/nclrt_transit_large.php|||Originally posted by Enigma
Take a train ... you must be living quite far from your office ... I guess that depends on your definition of far...
I can work from a number of places. They range from zero to three hundred Km from home (home itself moves twice a week too, but that's another story).
Working downtown can be ugly. If you do it the hard way, the last 100 Km took significantly longer than the first 200 Km! The commuter rail makes the whole process a lot easier, faster, and safer.
-PatP|||km?
I thought you were in southern Cal...|||I created a monster.
Thank you all for the long, very educational discussion regarding my problem. I think I am all set.|||What about another approach? Instead of using binary information such as an archive flag, use e.g. a datetime attribute for creation and another for the closing or also archiving date/time. Have an index for each. Set the value of creation and close to the same value and exclude in every selection, don't use the NOT argument but the CREATION=CLOSE for active. The index seed will be optimal and you can access only archived or open or both and in addition every range of time. This is how you would manage temporal valid records in one to many relationships, e.g. portfolio constructions or account records which you may correct but not delete for tracability reasons.
More far..if you would need uniqueness you could use a timestamp as well, but then you have to define a second attribute not as a timestamp but as a (guess) long. The timestamp field is filled automatically, a value cannot be assigned, but you can read it with an insert trigger, the inserted record will have the timestamp value, wich you can access while inserting the record and write it to the second attribute, which would not be of type timestamp.This would make the values unique.
If you have to access the archived values very often and you are not happy with the performance you have to store the latest timestamp used in a meta table. This will allow direct access of the last and compare with earlier closed ones.
Tried this with SQL Server 2000 and Sybase System 11, it is very fast.
Regards|||News flash! Dead posts are rising from the grave to walk the Earth once again! Authorities urge all citizens to remain calm! Stayed tuned for further instructions...|||mambo,
Thanks for the additional insight ... I am still in the design phase on this project (it got pushed back), so your idea may prove to be useful. I'll check it out. Thanks!|||Wow. Blast to the past. lol Let us know what you finally end up doing Wigz. It's always fun to see 1800 year old posts resurrect themselves.
For my app, the database consists of several relational tables that track all of the information about a job. I want to set it up so that if a job is completed, it gets archived.
Over the next 2 or 3 years, this system will likely grow to 100,000+ records.
My question is, should I (1) just use a flag to mark the job as archived, (2) make a flat record of the job and move it into one archive table, or (3) set up archive tables that mirror my production tables and copy the record to the archive tables keeping the relationships intact?
Does anyone have a method that has worked well for them in the past? What will keep my app running the fastest? Any suggestions would be appreciated. Thanks!i always prefer a single table with the jobs flagged as not active (1 or 0)
it makes for simpler querying later with just
select * from table1
where active is 1
and
when you want to see completed tasks
select * from table1
where active is 0
(less tables, less joins etc,... )|||Cool ... thanks!
Do you think 100,000+ records somewhere down the line will be a problem when running reports or doing searches if I just use a flag? I'm trying to design this w/ optimized tuning in mind because I don't know how long it will be in place.
When I flag a job as complete (bit 0,1) and then query w/
WHERE open=(0 or 1)
It still has to check every record correct? How many records before there is a noticable difference in the speed of the app?|||two ways to think of this
you could make the column a bit datatype to save on space but it stores 1,0 or null small to the point but you cannot create an index on this column
OR
create the column as tinyint (1 byte , largest value 255) and then you could index it
but the indexing on a heavily duplicated columns is not recommended
you also could create statistics on the flagged column
i'm of the mind that you should be okay with this but just monitor performance during operations|||Thanks for your ideas!|||The optimizer will likely not use an index on a boolean column, regardless of whether it is bit, tiny int, or even char(1). There just isn't sufficient cardinality to make it worthwhile.|||true
this is an area where i have (gulp!) envied ORACLE
they have bitmap indexes that you can use on heavily duplicated columns like gender and yes\no
oh well maybe in yukon.|||Yeah, but that's not the point.
You could create an index on boolean values stored in a tinyint field, but the optimizer would likely ignore it because its not any more efficient than using a table scan. The cardinality is likely so low (high? I can never remember...) that the small performance boost gained by using the index is offset by the overhead of accessing the index.
I'm not an Oracle DBA, but I don't see how Oracle would gain much from indexing boolean values either.|||yes and using bitmap indexes on yes no or gender or small sets like rating can give you a boost
but you are right about the boolean but still i prefer the 1/0 flag to the splitting up of tables|||I agree. In most cases (not all) a flag is better than splitting the data.|||And the rambling goes on. Well, I don't know who would come up with an idea on creating an index just on a Gender or Yes/No field. And you guys keep on going on it. Kind of silly, would you really consider doing it? Its value WILL come to play if such a field is combined with something else.
And another thing, - YOU CAN CREATE AN INDEX ON A BIT FIELD, even by itself.|||Yes and no.
You can't create an index on a bit field through Enterprise Manager's table design form.
Also, Books Online states:
"Columns of type bit cannot have indexes on them. "
But you can create the index using SQL:
CREATE INDEX [IX_BitTest] ON [dbo].[YourTable]([BitValue]) ON [PRIMARY]
GO
...so there is a loophole or inconsistency in SQL server regarding bit values.
Lot's of people come up with the idea of indexing boolean fields because they don't have an in depth understanding of how SQL Server works. That's why they come to dbforums for advice, not ridicule Bob.|||Originally posted by blindman
...Lot's of people come up with the idea of indexing boolean fields because they don't have an in depth understanding of how SQL Server works. That's why they come to dbforums for advice, not ridicule Bob. Hey, you started first, I just try to survive here. Remember your comments about my inability to understand English? Help me out here, - weren't you ridiculing me?
Somet things I found funny, others I saw as an insult, but in neither of those posts were you even attempting to hide or retract your intent to ridicule. I guess you yourself can't take your own medicine, hey?! ;)
Originally posted by blindman
...You can't create an index on a bit field through Enterprise Manager's table design form.
Also, Books Online states:
"Columns of type bit cannot have indexes on them. "
Which Books Online are you reading, Lindman? Get on with the program, everybody else did!
SQL Server Books Online January 2004 Update (http://www.microsoft.com/downloads/details.aspx?FamilyId=A6F79CB1-A420-445F-8A4B-BD77A7DA194B&displaylang=en#filelist)|||YEAH BOB!!!!
rdjabarov does insulted....|||Always go with your strength.|||At the scale of 100,00 or so records, you'll certainly want to be able to index the field, so bit is out. Plus people writing code against boolean fields get confused about null evaluations and equalities, so it's better to use a more humane data type anyway.
tinyint will take the least amount of space and provide the fastest field evaluation available. Although, at such a small table size, neither is a major concern. Whether or not you are expanding a server tree in the Enterprise Manager during query execution will have a much greater effect on performance than how you optimize this field. I agree that standard indexing would have little effect, but I would use a clustered index with the status field as the first element and then your primary where condition field as the second element of the index. The optimizer is unable to overlook this index becuase it is reflected in the table's physicality.|||The optimizer does not overlook boolean indexes because it is lazy, or because it can't use them. It chooses to overlook them when it is more efficient NOT to use them. You could force it to use the index using a query hint, but forcing the use of the index does not necessarily make the query more efficient. The same goes for putting the boolean index at the front of the key.|||Originally posted by dbslave
tinyint will take the least amount of space
Isn't tinyint and char(1) the same size?|||Originally posted by Lindman
The optimizer does not overlook boolean indexes because it is lazy, or because it can't use them. It chooses to overlook them when it is more efficient NOT to use them... The optimizer DOES NOT overlook indexes when it is more efficient not to use them. That decision is based on STATISTICS associated with tables referenced by the query. Don't give too much credit to the Optimizer.
And again, according to Books Online (UPDATED!!!) and personal experience, you can have an index on a bit field. In fact, you can even have a clustered index on a bit field (wouldn't know what for though). The point is that you shouldn't have an index on a field which data is equally split 50/50. Such a field should be part of a composite index to make a difference.
Going back to the original question, whether to have a flag or a replica of the original table for archive purposes...It depends on how many records are going to be archived at a time, and how many records regardless of whether they are archived or not would be retrieved. And also, how wide is the table now.
The decision should depend on these questions because if you plan to archive thousands of rows at a time and the table is wide enough (judgment call, no particulat width is in mind), I'd go with a flag of bit datatype, since updating of 1 field will not kill my transaction log as opposed to inserting into a different table.|||Originally posted by rdjabarov
In fact, you can even have a clustered index on a bit field (wouldn't know what for though). The point is that you shouldn't have an index on a field which data is equally split 50/50. Such a field should be part of a composite index to make a difference.
Logically, yes. Physically, this can be useful for partitioning data.|||Originally posted by Brett Kaiser
Isn't tinyint and char(1) the same size?
Yes, but comparisons on a tinyint are way faster than on a char(1)|||Why would that be?
Got any links that discuss this?
and whats waaaaaaaaaay faster?|||Originally posted by dbslave
Logically, yes. Physically, this can be useful for partitioning data. You're planning to partition 2 records?|||Clustered does not necessarily mean unique.|||Originally posted by blindman
Clustered does not necessarily mean unique. Did you get your "real" Books Online? You have a lot to read...|||USE Northwind
GO
CREATE TABLE myTable99(Col1 int, Col2 int)
GO
CREATE CLUSTERED INDEX myTable99_IX1 ON myTable99(Col1)
GO
INSERT INTO myTable99 SELECT 1,1 UNION ALL SELECT 1,1
GO
SELECT * FROM myTable99
GO
DROP TABLE myTable99
GO
Now say you're sorry...
And I want you all to play nice or it's time out...
Oh, wait, that stuff doesn't start till I get home...
Sorry...|||Hey Brett, I wasn't arguing that clustered does not mean unique! I just don't like "him" butting into other people's conversation, just to make some pointless point (or however "he" put it).|||Oh good heavens, that's exactly why I participate. Having a dozen eyes available helps "keep me honest" because I know that there are many points of view, most of which are probably more "on track" at any given point in time than mine are.
While there are plenty of people that annoy me in one way or another, its still a good thing that they all participate (at least in my opinion). If one person annoys me too badly, I just filter them from my view for a while (until I cool off).
There's a lot of advice that is well meant, and well received that nearly sends me ballistic. I know how badly certain approaches can hammer things up with large (anything between 150 and 1200 Gb) databases, but those approaches often work fine in smaller, simpler environments. I have to keep reminding myself to give appropriate answers based on the questions!
-PatP|||Originally posted by Brett Kaiser
Isn't tinyint and char(1) the same size?
yes they are both 1 byte datatypes but
integer vs char|||I'm with Brett in never having read anything about tinyint being a faster comparison than char(1). White papers?|||boys, this is a non-smoking flight, put your guns down
tiny, small, why not bit? you'd have to have at least 9 of them before it reaches the size of the tinyint. what's the fuss about?|||If tinyint is indeed faster than char(1) then I'll use it in the future.|||oh that's just absurd! how can a datatype be faster than another datatype?|||Originally posted by ms_sql_dba
oh that's just absurd! how can a datatype be faster than another datatype? Way to go, she got you on that one :D :D :D :D :D|||Hey .. where was I when all this was going on ...
hmm ... have been busy lately ... but will be back soon ...|||she?|||Now that you point out .. i was also confused by "She" ...
Hmm .. i m getting confused a lot lately ...|||Originally posted by r937
she? The "ms" can be interpreted as many things,k among them: Microsoft, or as an honoric "Ms." that was quite popular among what were called the "rabidly liberated" in the 1970s. Ms. was used instead of Mrs. or Miss by a very small group of women that didn't want to reveal their marital status. I assume that rdjabarov read it in that context.
-PatP|||She got me?
I never claimed it was faster. The notion that tinyint was faster than char(1) was news to both Brett and I, and we simply asked if there was any documentation to support the assertion.
As usual, ms_sql_dba didn't bother to really read the previous posts. She keeps to her old habit of dropping in at the end of the thread and repeating something somebody else had already written 10 posts before, without adding a shred of value to the conversation.
Nyah, nyah, ya missed me! Ya missed me! PPPbbbbbtttt! :p
Grow up.|||It's got to be a full moon or something...
does ANYONE want to put forth WHY tinyint would be more effecient that char(1)?
I'm here for education (and social interaction, well I guess some types of it...the others type are a watse of time...life is too short)|||Originally posted by ms_sql_dba
boys, this is a non-smoking flight, put your guns down
tiny, small, why not bit? you'd have to have at least 9 of them before it reaches the size of the tinyint. what's the fuss about?
[With the guns blazing]
This is straight out of the Holy book
bit
If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte
tinyint
Integer data from 0 through 255. Storage size is 1 byte.
[/With the guns blazing]
first Get a copy of the Holy Book and then read it from end to end.|||OK, so that means...
1 bit = 1 byte
2 bits = 1 byte
3 bits = 1 byte
4 bits = 1 byte
5 bits = 1 byte
6 bits = 1 byte
7 bits = 1 byte
8 bits = 1 byte
But the values of bit is still 0 or 1 or null (which isn't a value at all)
And has very limited meaning, and I've never seen a good reason to index it..
And Char(1) and tinyint are still both 1 byte
And is it a misconception that 1 of the above is more effecient in an index than the other...|||Originally posted by Brett Kaiser
OK, so that means...
1 bit = 1 byte
2 bits = 1 byte
3 bits = 1 byte
4 bits = 1 byte
5 bits = 1 byte
6 bits = 1 byte
7 bits = 1 byte
8 bits = 1 byte
9 bit = 2 byte
10 bits = 2 byte
11 bits = 2 byte
12 bits = 2 byte
13 bits = 2 byte
14 bits = 2 byte
15 bits = 2 byte
16 bits = 2 byte
and so on ...
so 1 single bit storage space = tiny int storage space|||It was dbslave who originally stated that tinyint was faster, and since we haven't heard anything back from him I'm going to assume that was an error and recommend we drop the inquiry.|||i feel slightly responisble because it was me who brought it up.
but if you are gonna flame someone else then count me in
Mob Rules
string him up...|||Nah, I wasn't stringing him up. Everybody makes mistakes, or has made incorrect assumptions while wading through the masses of documentation for SQL server. I've learned a hell of a lot from this forum.|||Originally posted by blindman
I've learned a hell of a lot from this forum.
Ditto...
Just trying to realize either way...
My gut says no....|||My gut says no....
Thats wierd ... you still got guts|||Originally posted by blindman
She got me?
I never claimed it was faster. The notion that tinyint was faster than char(1) was news to both Brett and I, and we simply asked if there was any documentation to support the assertion.
As usual, ms_sql_dba didn't bother to really read the previous posts. She keeps to her old habit of dropping in at the end of the thread and repeating something somebody else had already written 10 posts before, without adding a shred of value to the conversation.
Nyah, nyah, ya missed me! Ya missed me! PPPbbbbbtttt! :p
Grow up. So, that makes 2 of us, me and ms_sql_dba. At least I am not alone!!!|||Not to jump in and strive for polarity, but there's been nothing I've seen that say that's true...
And are you going to the rainbow lounge?
Make sure you bring a lot of singles...
:D|||Originally posted by Enigma
Thats wierd ... you still got guts I'll bet that I've got more gut than anybody around here!
Somebody made a comment about a rather heated discussion here being an "international incident", and I pointed out that even I'm not that fat (yet).
-PatP|||...Sounds like a call for a pissing contest...|||Originally posted by rdjabarov
...Sounds like a call for a pissing contest... Well, I'd guess that I could "hold my own" (if you'll pardon the term) in that area too!
-PatP|||Try this for yourself. Run test few times to be sure table is in cache. You will be surprised.
create table test(i int identity primary key, a tinyint, b char)
set nocount on
declare @.c int
set @.c = 0
while @.c < 20000 begin
insert test(a,b)
values(0,'0')
insert test(a,b)
values(1,'1')
set @.c = @.c + 1 end
--test scan
declare @.t datetime
set @.t = getdate()
select a
from test
where a = 1
print 'tinyint: ' + cast(datediff(ms,@.t,getdate()) as varchar) + 'ms'
set @.t = getdate()
select b
from test
where b = '1'
print 'char: ' + cast(datediff(ms,@.t,getdate()) as varchar) + 'ms'
--test seek as run above script after adding these indexes:
create index test_a on test(a)
create index test_b on test(b)
Chars were slower before SQL Server version 7.|||I got about 700ms faster on tinyint after creating indexes.|||And about 1600 faster than using char if I replace tinyint with bit...|||Well, in the interests of scientific speculation:
-- CREATE TABLE test_datatype_speed_1(
-- ident INT IDENTITY PRIMARY KEY,
-- tinyint_noindex TINYINT,
-- char1_noindex CHAR(1),
-- bit_noindex BIT,
-- tinyint_index TINYINT,
-- char1_index CHAR(1),
-- bit_index BIT)
--
-- CREATE INDEX idx_test_datatype_speed_1_tinyint ON test_datatype_speed_1(tinyint_index)
-- CREATE INDEX idx_test_datatype_speed_1_char1 ON test_datatype_speed_1(char1_index)
-- CREATE INDEX idx_test_datatype_speed_1_bit ON test_datatype_speed_1(bit_index)
--
-- SET NOCOUNT ON
--
-- DECLARE @.c INT
--
-- SELECT @.c = 0
--
-- WHILE @.c < 20000
-- BEGIN
--
-- INSERT test_datatype_speed_1(tinyint_noindex, char1_noindex, bit_noindex, tinyint_index, char1_index, bit_index)
-- VALUES(0,'0',0,0,'0',0)
-- INSERT test_datatype_speed_1(tinyint_noindex, char1_noindex, bit_noindex, tinyint_index, char1_index, bit_index)
-- VALUES(1,'1',1,1,'1',1)
-- SELECT @.c = @.c + 1
--
-- END
GO
SET STATISTICS IO ON
--test scan
DECLARE @.t DATETIME
--Test tinyint_noindex
SELECT @.t = GETDATE()
SELECT tinyint_noindex
FROM test_datatype_speed_1
WHERE tinyint_noindex = 1
PRINT 'tinyint_noindex: ' + CAST(DATEDIFF(ms,@.t,GETDATE()) AS VARCHAR) + 'ms'
--Test char1_noindex
SELECT @.t = GETDATE()
SELECT char1_noindex
FROM test_datatype_speed_1
WHERE char1_noindex = '1'
PRINT 'char1_noindex: ' + CAST(DATEDIFF(ms,@.t,GETDATE()) AS VARCHAR) + 'ms'
--Test bit_noindex
SELECT @.t = GETDATE()
SELECT bit_noindex
FROM test_datatype_speed_1
WHERE bit_noindex = '1'
PRINT 'bit_noindex: ' + CAST(DATEDIFF(ms,@.t,GETDATE()) AS VARCHAR) + 'ms'
--Test tinyint_index
SELECT @.t = GETDATE()
SELECT tinyint_index
FROM test_datatype_speed_1
WHERE tinyint_index = 1
PRINT 'tinyint_index: ' + CAST(DATEDIFF(ms,@.t,GETDATE()) AS VARCHAR) + 'ms'
--Test char1_index
SELECT @.t = GETDATE()
SELECT char1_index
FROM test_datatype_speed_1
WHERE char1_index = '1'
PRINT 'char1_index: ' + CAST(DATEDIFF(ms,@.t,GETDATE()) AS VARCHAR) + 'ms'
--Test bit_index
SELECT @.t = GETDATE()
SELECT bit_index
FROM test_datatype_speed_1
WHERE bit_index = '1'
PRINT 'bit_index: ' + CAST(DATEDIFF(ms,@.t,GETDATE()) AS VARCHAR) + 'ms'
SET STATISTICS IO OFF
GO
This was the average result on my machine:
Table 'test_datatype_speed_1'. Scan count 1, logical reads 91, physical reads 0, read-ahead reads 0.
tinyint_noindex: 30ms
Table 'test_datatype_speed_1'. Scan count 1, logical reads 91, physical reads 0, read-ahead reads 0.
char1_noindex: 93ms
Table 'test_datatype_speed_1'. Scan count 1, logical reads 91, physical reads 0, read-ahead reads 0.
bit_noindex: 63ms
Table 'test_datatype_speed_1'. Scan count 1, logical reads 30, physical reads 0, read-ahead reads 0.
tinyint_index: 63ms
Table 'test_datatype_speed_1'. Scan count 1, logical reads 30, physical reads 0, read-ahead reads 0.
char1_index: 93ms
Table 'test_datatype_speed_1'. Scan count 1, logical reads 30, physical reads 0, read-ahead reads 0.
bit_index: 60ms
Kind of interesting.
I'm running Windows 2003 Standard Edition/SQL Server 2000 Enterprise Edition/2ghz Xeon/1gb RAM|||I'm running Windows 2003 Standard Edition/SQL Server 2000 Enterprise Edition/2ghz Xeon/1gb RAM
i'm more impressed by your server.
oh yeah nice code too|||OK, cool, I'm up for testing things out...
Anyone got anything from M$ though?
Also, how curious...an non indexed tinuint is 33 ms faster?
Doesn't make sense...
I bet the results are significantly different when we're talking volumes...|||I just got around to running HansVE's code, and sometimes tinyint was faster and sometimes char was faster. It was about 50/50, so it still looks like a wash to me, at least until I see some kind of documentation or at least some reasoning.
And indexes made no difference.|||How many records are you guys testing with? Also, testing against 1-field index with 1/0 or y/n is going to yield an index scan. And we all know that the number of index pages for all 3 datatypes (char, tinyint, bit) will be the same. In order to test it for practical real-life situations we at least need to agree that such a field needs to be tested with another field with higher selectivity than 50/50.|||Agreed. 50/50 may be a typical distribution of boolean data, but it is not always the case and performance may be different with different cardinality.
90/10?|||No, I was talking about combining this boolean field with another, let's say a 10-character field or a date, while creating an index, and then using this combination of fields in the WHERE clause or in a JOIN. Something like this:
create table t1 (f1 char(10) not null, f1 tinyint/char(1)/bit not null)
--here we need to insert a large number of records, about 100K, where f1 would be 10% duplicates and f2 would be 50% dups.
select * from t1 where f1 = <something> and f2 = 'y'/1/1|||If you read the whole post, the entire thing was centered around bit vs. tinyint. Then, the whole char(1) thing got thrown in. So, if we're talking true bit comparison, you should stick with 0 and 1. Otherwise, you're expanding the field to do a full-scale comparison of datatypes.
In this case, you would have to set baselines at all kinds of different selectivity levels, index level (fill-factors, clustered vs. non-clustered, single vs multi-column), scale, precision, datatype, etc.
After you set the baseline, you have just began the real work of analyzing the datatype performance. You would then have to compare across size of system, memory, load in tps, size of recordset per selectivity, etc.
It would be great fun, but it's probably a little out of the scope of the thread isn't it.
:)|||Oh no...A thread that has strayed....
What will happen next?
No more tequila?
AAAAAAAAAAAAAHHHHHHHHHHHHHHHHHHHHHHHHHHHH
:D|||Let the thread keep going as long as it is interesting and informative.
rdjabarov, can you adapt the prevous sample code to test or illustrate your conjecture?|||I was hoping Brett will do his "create mytable99..." thing and I'll tweak it ;)|||Spoken a like a true lazy DBA! (And I was hinting that YOU should do it because I didn't want to.)
In Enterprise Manager, right-click on the Procedures folder, select "New Stored Procedure", and choose the "Ask Brett to do it" button.|||use a cross join
:cool:|||Originally posted by blindman
Spoken a like a true lazy DBA! (And I was hinting that YOU should do it because I didn't want to.)
In Enterprise Manager, right-click on the Procedures folder, select "New Stored Procedure", and choose the "Ask Brett to do it" button.
That's coming out in Yukon
:D|||Let the thread keep going as long as it is interesting and informative.
We better just stick to the interesting part. :)|||Personally, I don't give a rats arse which is faster but I am surprised that the collation of the CHAR data type has not been mentioned.
A binary collation outperforms a non binary collation...
Thanks to Derrick's code and simply adding "COLLATE Latin1_General_BIN " against the CHAR columns reveals...
Table 'TEST_DATATYPE_SPEED_1'. Scan count 1, logical reads 91, physical reads 0, read-ahead reads 0.
tinyint_noindex: 73ms
Table 'TEST_DATATYPE_SPEED_1'. Scan count 1, logical reads 91, physical reads 0, read-ahead reads 0.
char1_noindex: 100ms
Table 'TEST_DATATYPE_SPEED_1'. Scan count 1, logical reads 91, physical reads 0, read-ahead reads 0.
bit_noindex: 90ms
Table 'TEST_DATATYPE_SPEED_1'. Scan count 1, logical reads 23, physical reads 0, read-ahead reads 0.
tinyint_index: 80ms
Table 'TEST_DATATYPE_SPEED_1'. Scan count 1, logical reads 23, physical reads 0, read-ahead reads 0.
char1_index: 80ms
Table 'TEST_DATATYPE_SPEED_1'. Scan count 1, logical reads 23, physical reads 0, read-ahead reads 0.
bit_index: 80ms|||(20000 row(s) affected)
Table 'test_datatype_speed_1'. Scan count 1, logical reads 92, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
tinyint_noindex: 230ms
(20000 row(s) affected)
Table 'test_datatype_speed_1'. Scan count 1, logical reads 92, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
char1_noindex: 360ms
(20000 row(s) affected)
Table 'test_datatype_speed_1'. Scan count 1, logical reads 92, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
bit_noindex: 290ms
(20000 row(s) affected)
Table 'test_datatype_speed_1'. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
tinyint_index: 183ms
(20000 row(s) affected)
Table 'test_datatype_speed_1'. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
char1_index: 230ms
(20000 row(s) affected)
Table 'test_datatype_speed_1'. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
bit_index: 250ms|||Tried out the following
In Enterprise Manager, right-click on the Procedures folder, select "New Stored Procedure", and choose the "Ask Brett to do it" button.
It simply closed my SQL workbench and shut down the system ...
Weird !!! :D|||That's becasue it engages the margarita request task...
It involves shut down, turn off lights, catch train, fire up blender....
and ok...if we say tinyint IS faster, that's great...
Now add the join back to the code table to obtain the meaningful value for the surrogate key...|||Originally posted by Brett Kaiser
That's becasue it engages the margarita request task...
It involves shut down, turn off lights, catch train, fire up blender....
Take a train ... you must be living quite far from your office ...|||3 stops...can read 1 article...
town shuttle picks me up 3 houses away to the train...monthly pass US$36.00 a month
Company shuttle takes me to work...
reverse on the way home...
transmission blew on the piece o junk chrysler...after they replaced it under warranty...
No payement, No Gas, No Insurance, No hassle
beautiful Newark NJ|||Wish we had commuter rail in Ohio...|||Originally posted by blindman
Wish we had commuter rail in Ohio...
http://www.cota.com/cota/cotaweb/main.xml
Actually it's just proposed
http://www.cotafasttrax.com/nclrt_transit_large.php|||Originally posted by Enigma
Take a train ... you must be living quite far from your office ... I guess that depends on your definition of far...
I can work from a number of places. They range from zero to three hundred Km from home (home itself moves twice a week too, but that's another story).
Working downtown can be ugly. If you do it the hard way, the last 100 Km took significantly longer than the first 200 Km! The commuter rail makes the whole process a lot easier, faster, and safer.
-PatP|||km?
I thought you were in southern Cal...|||I created a monster.
Thank you all for the long, very educational discussion regarding my problem. I think I am all set.|||What about another approach? Instead of using binary information such as an archive flag, use e.g. a datetime attribute for creation and another for the closing or also archiving date/time. Have an index for each. Set the value of creation and close to the same value and exclude in every selection, don't use the NOT argument but the CREATION=CLOSE for active. The index seed will be optimal and you can access only archived or open or both and in addition every range of time. This is how you would manage temporal valid records in one to many relationships, e.g. portfolio constructions or account records which you may correct but not delete for tracability reasons.
More far..if you would need uniqueness you could use a timestamp as well, but then you have to define a second attribute not as a timestamp but as a (guess) long. The timestamp field is filled automatically, a value cannot be assigned, but you can read it with an insert trigger, the inserted record will have the timestamp value, wich you can access while inserting the record and write it to the second attribute, which would not be of type timestamp.This would make the values unique.
If you have to access the archived values very often and you are not happy with the performance you have to store the latest timestamp used in a meta table. This will allow direct access of the last and compare with earlier closed ones.
Tried this with SQL Server 2000 and Sybase System 11, it is very fast.
Regards|||News flash! Dead posts are rising from the grave to walk the Earth once again! Authorities urge all citizens to remain calm! Stayed tuned for further instructions...|||mambo,
Thanks for the additional insight ... I am still in the design phase on this project (it got pushed back), so your idea may prove to be useful. I'll check it out. Thanks!|||Wow. Blast to the past. lol Let us know what you finally end up doing Wigz. It's always fun to see 1800 year old posts resurrect themselves.
Sunday, February 19, 2012
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
>
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
Hello,
I am not so experienced in database design and in my job now I have to
design a database for publications. The problem is the design should be
worked out as soon as possible.
The publication database should contain the
following details:
Title
Subtitle
Authors
Place of publication
Year of publication
Number of sites
Keywords
There could be several authors and several keywords.
Could anybody tell me a good design for this attributes and what database
will be fit best?
thanks in advance
lg
max
Max,
Might look here for a comparable data model:
http://www.databaseanswers.org/data_models/index.htm
HTH
Jerry
"max" <max@.discussions.microsoft.com> wrote in message
news:2E11BD72-0411-490A-BC15-434A457C33BF@.microsoft.com...
> Hello,
> I am not so experienced in database design and in my job now I have to
> design a database for publications. The problem is the design should be
> worked out as soon as possible. The publication database should contain
> the
> following details:
> Title
> Subtitle
> Authors
> Place of publication
> Year of publication
> Number of sites
> Keywords
> There could be several authors and several keywords.
> Could anybody tell me a good design for this attributes and what database
> will be fit best?
> thanks in advance
> lg
> max
I am not so experienced in database design and in my job now I have to
design a database for publications. The problem is the design should be
worked out as soon as possible.
The publication database should contain thefollowing details:
Title
Subtitle
Authors
Place of publication
Year of publication
Number of sites
Keywords
There could be several authors and several keywords.
Could anybody tell me a good design for this attributes and what database
will be fit best?
thanks in advance
lg
max
Max,
Might look here for a comparable data model:
http://www.databaseanswers.org/data_models/index.htm
HTH
Jerry
"max" <max@.discussions.microsoft.com> wrote in message
news:2E11BD72-0411-490A-BC15-434A457C33BF@.microsoft.com...
> Hello,
> I am not so experienced in database design and in my job now I have to
> design a database for publications. The problem is the design should be
> worked out as soon as possible.
The publication database should contain> the
> following details:
> Title
> Subtitle
> Authors
> Place of publication
> Year of publication
> Number of sites
> Keywords
> There could be several authors and several keywords.
> Could anybody tell me a good design for this attributes and what database
> will be fit best?
> thanks in advance
> lg
> max
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?
>
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
Hello,
I am not so experienced in database design and in my job now I have to
design a database for publications. The problem is the design should be
worked out as soon as possible.:( The publication database should contain th
e
following details:
Title
Subtitle
Authors
Place of publication
Year of publication
Number of sites
Keywords
There could be several authors and several keywords.
Could anybody tell me a good design for this attributes and what database
will be fit best?
thanks in advance
lg
max/*
Here is a quick, down and dirty design (SQL Server or MSDE only, as it is
T-SQL). Copy and paste to Query Analyzer and take a look:
NOTE: Commented out so you can copy the entire post and run it in Query
Analyzer.
*/
CREATE TABLE [dbo].[Author] (
[AuthorID] [int] IDENTITY (1, 1) NOT NULL ,
[AuthorName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AuthorPublicationAssoc] (
[AuthorPublicationAssocID] [int] IDENTITY (1, 1) NOT NULL ,
[AuthorID] [int] NOT NULL ,
[PublicationID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Keyword] (
[KeywordID] [int] IDENTITY (1, 1) NOT NULL ,
[KeywordName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[KeywordPublicationAssoc] (
[KeywordPublicationAssocID] [int] IDENTITY (1, 1) NOT NULL ,
[KeywordID] [int] NOT NULL ,
[PublicationID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Place] (
[PlaceID] [int] IDENTITY (1, 1) NOT NULL ,
[PlaceName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Publication] (
[PublicationID] [int] IDENTITY (1, 1) NOT NULL ,
[PlaceID] [int] NULL ,
[PublicationName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[PublicationYear] [smallint] NOT NULL ,
[NumberOfSites] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Author] WITH NOCHECK ADD
CONSTRAINT [PK_Author] PRIMARY KEY CLUSTERED
(
[AuthorID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AuthorPublicationAssoc] WITH NOCHECK ADD
CONSTRAINT [PK_AuthorPublicationAssoc] PRIMARY KEY CLUSTERED
(
[AuthorPublicationAssocID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Keyword] WITH NOCHECK ADD
CONSTRAINT [PK_Keyword] PRIMARY KEY CLUSTERED
(
[KeywordID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[KeywordPublicationAssoc] WITH NOCHECK ADD
CONSTRAINT [PK_KeywordPublicationAssoc] PRIMARY KEY CLUSTERED
(
[KeywordPublicationAssocID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Place] WITH NOCHECK ADD
CONSTRAINT [PK_Place] PRIMARY KEY CLUSTERED
(
[PlaceID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Publication] WITH NOCHECK ADD
CONSTRAINT [PK_Publication] PRIMARY KEY CLUSTERED
(
[PublicationID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Publication] ADD
CONSTRAINT [DF_Publication_NumberOfSites] DEFAULT (0) FOR [NumberOfSites]
GO
ALTER TABLE [dbo].[AuthorPublicationAssoc] ADD
CONSTRAINT [FK_AuthorPublicationAssoc_Author] FOREIGN KEY
(
[AuthorID]
) REFERENCES [dbo].[Author] (
[AuthorID]
),
CONSTRAINT [FK_AuthorPublicationAssoc_Publication] FOREIGN KEY
(
[PublicationID]
) REFERENCES [dbo].[Publication] (
[PublicationID]
)
GO
ALTER TABLE [dbo].[KeywordPublicationAssoc] ADD
CONSTRAINT [FK_KeywordPublicationAssoc_Keyword] FOREIGN KEY
(
[KeywordID]
) REFERENCES [dbo].[Keyword] (
[KeywordID]
),
CONSTRAINT [FK_KeywordPublicationAssoc_Publication]
FOREIGN KEY
(
[PublicationID]
) REFERENCES [dbo].[Publication] (
[PublicationID]
)
GO
ALTER TABLE [dbo].[Publication] ADD
CONSTRAINT [FK_Publication_Place] FOREIGN KEY
(
[PlaceID]
) REFERENCES [dbo].[Place] (
[PlaceID]
)
GO
/*
This is just basic layout, so alter to your hearts content.
As for database, that really depends on the app and the amount of traffic. I
like SQL Server (and assume you do to since you are posting in a SQL Server
group), but it is not the only database out there.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"max" wrote:
> Hello,
> I am not so experienced in database design and in my job now I have to
> design a database for publications. The problem is the design should be
> worked out as soon as possible.:( The publication database should contain
the
> following details:
> Title
> Subtitle
> Authors
> Place of publication
> Year of publication
> Number of sites
> Keywords
> There could be several authors and several keywords.
> Could anybody tell me a good design for this attributes and what database
> will be fit best?
> thanks in advance
> lg
> max
*/
I am not so experienced in database design and in my job now I have to
design a database for publications. The problem is the design should be
worked out as soon as possible.:( The publication database should contain th
e
following details:
Title
Subtitle
Authors
Place of publication
Year of publication
Number of sites
Keywords
There could be several authors and several keywords.
Could anybody tell me a good design for this attributes and what database
will be fit best?
thanks in advance
lg
max/*
Here is a quick, down and dirty design (SQL Server or MSDE only, as it is
T-SQL). Copy and paste to Query Analyzer and take a look:
NOTE: Commented out so you can copy the entire post and run it in Query
Analyzer.
*/
CREATE TABLE [dbo].[Author] (
[AuthorID] [int] IDENTITY (1, 1) NOT NULL ,
[AuthorName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AuthorPublicationAssoc] (
[AuthorPublicationAssocID] [int] IDENTITY (1, 1) NOT NULL ,
[AuthorID] [int] NOT NULL ,
[PublicationID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Keyword] (
[KeywordID] [int] IDENTITY (1, 1) NOT NULL ,
[KeywordName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[KeywordPublicationAssoc] (
[KeywordPublicationAssocID] [int] IDENTITY (1, 1) NOT NULL ,
[KeywordID] [int] NOT NULL ,
[PublicationID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Place] (
[PlaceID] [int] IDENTITY (1, 1) NOT NULL ,
[PlaceName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Publication] (
[PublicationID] [int] IDENTITY (1, 1) NOT NULL ,
[PlaceID] [int] NULL ,
[PublicationName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[PublicationYear] [smallint] NOT NULL ,
[NumberOfSites] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Author] WITH NOCHECK ADD
CONSTRAINT [PK_Author] PRIMARY KEY CLUSTERED
(
[AuthorID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AuthorPublicationAssoc] WITH NOCHECK ADD
CONSTRAINT [PK_AuthorPublicationAssoc] PRIMARY KEY CLUSTERED
(
[AuthorPublicationAssocID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Keyword] WITH NOCHECK ADD
CONSTRAINT [PK_Keyword] PRIMARY KEY CLUSTERED
(
[KeywordID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[KeywordPublicationAssoc] WITH NOCHECK ADD
CONSTRAINT [PK_KeywordPublicationAssoc] PRIMARY KEY CLUSTERED
(
[KeywordPublicationAssocID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Place] WITH NOCHECK ADD
CONSTRAINT [PK_Place] PRIMARY KEY CLUSTERED
(
[PlaceID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Publication] WITH NOCHECK ADD
CONSTRAINT [PK_Publication] PRIMARY KEY CLUSTERED
(
[PublicationID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Publication] ADD
CONSTRAINT [DF_Publication_NumberOfSites] DEFAULT (0) FOR [NumberOfSites]
GO
ALTER TABLE [dbo].[AuthorPublicationAssoc] ADD
CONSTRAINT [FK_AuthorPublicationAssoc_Author] FOREIGN KEY
(
[AuthorID]
) REFERENCES [dbo].[Author] (
[AuthorID]
),
CONSTRAINT [FK_AuthorPublicationAssoc_Publication] FOREIGN KEY
(
[PublicationID]
) REFERENCES [dbo].[Publication] (
[PublicationID]
)
GO
ALTER TABLE [dbo].[KeywordPublicationAssoc] ADD
CONSTRAINT [FK_KeywordPublicationAssoc_Keyword] FOREIGN KEY
(
[KeywordID]
) REFERENCES [dbo].[Keyword] (
[KeywordID]
),
CONSTRAINT [FK_KeywordPublicationAssoc_Publication]
FOREIGN KEY
(
[PublicationID]
) REFERENCES [dbo].[Publication] (
[PublicationID]
)
GO
ALTER TABLE [dbo].[Publication] ADD
CONSTRAINT [FK_Publication_Place] FOREIGN KEY
(
[PlaceID]
) REFERENCES [dbo].[Place] (
[PlaceID]
)
GO
/*
This is just basic layout, so alter to your hearts content.
As for database, that really depends on the app and the amount of traffic. I
like SQL Server (and assume you do to since you are posting in a SQL Server
group), but it is not the only database out there.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"max" wrote:
> Hello,
> I am not so experienced in database design and in my job now I have to
> design a database for publications. The problem is the design should be
> worked out as soon as possible.:( The publication database should contain
the
> following details:
> Title
> Subtitle
> Authors
> Place of publication
> Year of publication
> Number of sites
> Keywords
> There could be several authors and several keywords.
> Could anybody tell me a good design for this attributes and what database
> will be fit best?
> thanks in advance
> lg
> max
*/
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
>
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
Hello,
I am not so experienced in database design and in my job now I have to
design a database for publications. The problem is the design should be
worked out as soon as possible. The publication database should contain th
e
following details:
Title
Subtitle
Authors
Place of publication
Year of publication
Number of sites
Keywords
There could be several authors and several keywords.
Could anybody tell me a good design for this attributes and what database
will be fit best?
thanks in advance
lg
maxMax,
Might look here for a comparable data model:
http://www.databaseanswers.org/data_models/index.htm
HTH
Jerry
"max" <max@.discussions.microsoft.com> wrote in message
news:2E11BD72-0411-490A-BC15-434A457C33BF@.microsoft.com...
> Hello,
> I am not so experienced in database design and in my job now I have to
> design a database for publications. The problem is the design should be
> worked out as soon as possible. The publication database should contain
> the
> following details:
> Title
> Subtitle
> Authors
> Place of publication
> Year of publication
> Number of sites
> Keywords
> There could be several authors and several keywords.
> Could anybody tell me a good design for this attributes and what database
> will be fit best?
> thanks in advance
> lg
> max
I am not so experienced in database design and in my job now I have to
design a database for publications. The problem is the design should be
worked out as soon as possible.
The publication database should contain the
following details:
Title
Subtitle
Authors
Place of publication
Year of publication
Number of sites
Keywords
There could be several authors and several keywords.
Could anybody tell me a good design for this attributes and what database
will be fit best?
thanks in advance
lg
maxMax,
Might look here for a comparable data model:
http://www.databaseanswers.org/data_models/index.htm
HTH
Jerry
"max" <max@.discussions.microsoft.com> wrote in message
news:2E11BD72-0411-490A-BC15-434A457C33BF@.microsoft.com...
> Hello,
> I am not so experienced in database design and in my job now I have to
> design a database for publications. The problem is the design should be
> worked out as soon as possible.
The publication database should contain> the
> following details:
> Title
> Subtitle
> Authors
> Place of publication
> Year of publication
> Number of sites
> Keywords
> There could be several authors and several keywords.
> Could anybody tell me a good design for this attributes and what database
> will be fit best?
> thanks in advance
> lg
> max
Database Design
Hello,
I am not so experienced in database design and in my job now I have to
design a database for publications. The problem is the design should be
worked out as soon as possible.:( The publication database should contain the
following details:
Title
Subtitle
Authors
Place of publication
Year of publication
Number of sites
Keywords
There could be several authors and several keywords.
Could anybody tell me a good design for this attributes and what database
will be fit best?
thanks in advance
lg
maxMax,
Might look here for a comparable data model:
http://www.databaseanswers.org/data_models/index.htm
HTH
Jerry
"max" <max@.discussions.microsoft.com> wrote in message
news:2E11BD72-0411-490A-BC15-434A457C33BF@.microsoft.com...
> Hello,
> I am not so experienced in database design and in my job now I have to
> design a database for publications. The problem is the design should be
> worked out as soon as possible.:( The publication database should contain
> the
> following details:
> Title
> Subtitle
> Authors
> Place of publication
> Year of publication
> Number of sites
> Keywords
> There could be several authors and several keywords.
> Could anybody tell me a good design for this attributes and what database
> will be fit best?
> thanks in advance
> lg
> max
I am not so experienced in database design and in my job now I have to
design a database for publications. The problem is the design should be
worked out as soon as possible.:( The publication database should contain the
following details:
Title
Subtitle
Authors
Place of publication
Year of publication
Number of sites
Keywords
There could be several authors and several keywords.
Could anybody tell me a good design for this attributes and what database
will be fit best?
thanks in advance
lg
maxMax,
Might look here for a comparable data model:
http://www.databaseanswers.org/data_models/index.htm
HTH
Jerry
"max" <max@.discussions.microsoft.com> wrote in message
news:2E11BD72-0411-490A-BC15-434A457C33BF@.microsoft.com...
> Hello,
> I am not so experienced in database design and in my job now I have to
> design a database for publications. The problem is the design should be
> worked out as soon as possible.:( The publication database should contain
> the
> following details:
> Title
> Subtitle
> Authors
> Place of publication
> Year of publication
> Number of sites
> Keywords
> There could be several authors and several keywords.
> Could anybody tell me a good design for this attributes and what database
> will be fit best?
> thanks in advance
> lg
> max
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
> > >
> > >
> >
> >
>
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
> > >
> > >
> >
> >
>
Subscribe to:
Posts (Atom)