Database level lock: order

Showing posts with label order. Show all posts

Thursday, March 29, 2012

Database images not showing

How should I store an image into my SQL database in order to be able to read
it using Reporting Services Image control? I have a table with an image
field (datatype of the field is 'Image').
I have tried several ways and the report is always rendered with the image
showing a red cross inside it (image broken). However if I read the table
using MS Access ADP and double click on the image field, MS Paint opens and
shows up the image (this is a bmp image). I have also set the MIMEType of
the rs image field to 'image/bmp' (filling this field this is compulsory
with database images).
It seems that, even though the database field contents ('Image' field) are
properly stored, Reporting Services does not know how to handle it. May I
need to do a CONVERT(Binary, MyImageField) or the database field be of
another type? How should I store the image inside the database so that RS
could read it?
Regards.
PS: This is RS SP2.This sounds like the images are stored as OLE images in the database (e.g.
Access would convert images into OLE images). You can try the following
expression to get rid of the OLE chunk:
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
105))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
news:e5gQDOJVFHA.584@.TK2MSFTNGP15.phx.gbl...
> How should I store an image into my SQL database in order to be able to
> read it using Reporting Services Image control? I have a table with an
> image field (datatype of the field is 'Image').
> I have tried several ways and the report is always rendered with the image
> showing a red cross inside it (image broken). However if I read the table
> using MS Access ADP and double click on the image field, MS Paint opens
> and shows up the image (this is a bmp image). I have also set the MIMEType
> of the rs image field to 'image/bmp' (filling this field this is
> compulsory with database images).
> It seems that, even though the database field contents ('Image' field) are
> properly stored, Reporting Services does not know how to handle it. May I
> need to do a CONVERT(Binary, MyImageField) or the database field be of
> another type? How should I store the image inside the database so that RS
> could read it?
> Regards.
> PS: This is RS SP2.
>|||Thanks for your reply but it seems that there should be another reason. Your
explanation is on the good road, but
I have tried your expression, with that 105 varying from 100 to 110 with the
same results. The red cross is still there.
Any other suggestion? Regards.
PS: Just for further debugging, I have an output of the first 950 bytes
generated by the expression:
=System.Convert.ToBase64String(Fields!Picture.Value)
Here they follow:
FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAYEgAAEJNYEgAAAAAAAB2AAAAKAAAAM8AAACxAAAAAQAEAAAAAAAAAAAAEBcAABAXAAAAAAAAAAAAAAAAAAD///8A+vr6APPz8wDn5+cA29vbAMzMzAC5ubkAo6OjAIuLiwBzc3MAWVlZAEJCQgAvLy8AHx8fAA8PDwAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBEREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> escribió en el
mensaje news:eL5pK2LVFHA.612@.TK2MSFTNGP12.phx.gbl...
> This sounds like the images are stored as OLE images in the database (e.g.
> Access would convert images into OLE images). You can try the following
> expression to get rid of the OLE chunk:
> =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
> 105))
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
> news:e5gQDOJVFHA.584@.TK2MSFTNGP15.phx.gbl...
>> How should I store an image into my SQL database in order to be able to
>> read it using Reporting Services Image control? I have a table with an
>> image field (datatype of the field is 'Image').
>> I have tried several ways and the report is always rendered with the
>> image showing a red cross inside it (image broken). However if I read the
>> table using MS Access ADP and double click on the image field, MS Paint
>> opens and shows up the image (this is a bmp image). I have also set the
>> MIMEType of the rs image field to 'image/bmp' (filling this field this is
>> compulsory with database images).
>> It seems that, even though the database field contents ('Image' field)
>> are properly stored, Reporting Services does not know how to handle it.
>> May I need to do a CONVERT(Binary, MyImageField) or the database field be
>> of another type? How should I store the image inside the database so that
>> RS could read it?
>> Regards.
>> PS: This is RS SP2.
>|||More information on the subject:
I have been doing more tests with this sample bitmap. I have sent it to
myself via email just to read the source code of the message and extract the
base64 of the bitmap. The header of attachment and some hundreds the
begining bytes are here:
--_=_NextPart_001_01C55532.6E34D77F
Content-Type: image/bmp;
name="PHOTO.BMP"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="PHOTO.BMP"
Qk1gSAAAAAAAAHYAAAAoAAAAzwAAALEAAAABAAQAAAAAAAAAAAAQFwAAEBcAAAAAAAAAAAAAAAAA
AP///wD6+voA8/PzAOfn5wDb29sAzMzMALm5uQCjo6MAi4uLAHNzcwBZWVkAQkJCAC8vLwAfHx8A
Dw8PABERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
EREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERER
ERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
ERERERERERERERERERERERERERERERERERERERERERERERERERERERARERERERERERERERERERER
ERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
EREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERER
ERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
etc...
If I copy this base64 chunk of data and set the Reporting Services image
field to:
=System.Convert.FromBase64String("the chunk")
The image is shown perfecly on the report.
However, if I compare some bytes of the begining of this chunk (which is
shown to be correct) and try to find it somewhere on the string returned by:
=System.Convert.ToBase64String(Fields!Picture.Value)
which is:
FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAYEgAAEJNYEgAAAAAAAB2AAAAKAAAAM8AAACxAAAAAQAEAAAAAAAAAAAAEBcAABAXAAAAAAAAAAAAAAAAAAD///8A+vr6APPz8wDn5+cA29vbAMzMzAC5ubkAo6OjAIuLiwBzc3MAWVlZAEJCQgAvLy8AHx8fAA8PDwAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBEREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
I can't find it anywhere. There is no 'Qk1gS' substring in it. Now my
question are: Are there multiple ways of converting a file into a base64
string? How can I remove the OLE header of the database if I cannot find the
matching of the image REAL data using this 'comparing' approach?
Regards.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> escribió en el mensaje
news:%23AtcAySVFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Thanks for your reply but it seems that there should be another reason.
> Your explanation is on the good road, but
> I have tried your expression, with that 105 varying from 100 to 110 with
> the same results. The red cross is still there.
> Any other suggestion? Regards.
> PS: Just for further debugging, I have an output of the first 950 bytes
> generated by the expression:
> =System.Convert.ToBase64String(Fields!Picture.Value)
> Here they follow:
> FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAYEgAAEJNYEgAAAAAAAB2AAAAKAAAAM8AAACxAAAAAQAEAAAAAAAAAAAAEBcAABAXAAAAAAAAAAAAAAAAAAD///8A+vr6APPz8wDn5+cA29vbAMzMzAC5ubkAo6OjAIuLiwBzc3MAWVlZAEJCQgAvLy8AHx8fAA8PDwAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBEREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> escribió en el
> mensaje news:eL5pK2LVFHA.612@.TK2MSFTNGP12.phx.gbl...
>> This sounds like the images are stored as OLE images in the database
>> (e.g. Access would convert images into OLE images). You can try the
>> following expression to get rid of the OLE chunk:
>> =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
>> 105))
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
>> news:e5gQDOJVFHA.584@.TK2MSFTNGP15.phx.gbl...
>> How should I store an image into my SQL database in order to be able to
>> read it using Reporting Services Image control? I have a table with an
>> image field (datatype of the field is 'Image').
>> I have tried several ways and the report is always rendered with the
>> image showing a red cross inside it (image broken). However if I read
>> the table using MS Access ADP and double click on the image field, MS
>> Paint opens and shows up the image (this is a bmp image). I have also
>> set the MIMEType of the rs image field to 'image/bmp' (filling this
>> field this is compulsory with database images).
>> It seems that, even though the database field contents ('Image' field)
>> are properly stored, Reporting Services does not know how to handle it.
>> May I need to do a CONVERT(Binary, MyImageField) or the database field
>> be of another type? How should I store the image inside the database so
>> that RS could read it?
>> Regards.
>> PS: This is RS SP2.
>>
>|||Instead of doing tests with the final image, I have created a 5x5 pix bitmap
to work/test with.
The Base64 encoding of it (grabbed from an email sourcecode is):
Qk2GAAAAAAAAADYAAAAoAAAABQAAAAUAAAABABgAAAAAAFAAAAAAAAAAAAAAAAAAAAAAAAAA////////////////////AP///wAA/////////wAA/wD///////////////////8A////////AAD/////AAD/AAAA/////////////////wA=
It works perfectly if I set the value property of the rs image to:
=System.Convert.FromBase64String("the former base64 string")
Now, the database version of the same file: I set a texbox in the RS report
and set its value to =System.Convert.ToBase64String(Fields!Picture.Value) so
that I could retrieve the complete base64 of the image stored in the
database. Here it is:
FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAoAAAAEJNhgAAAAAAAAA2AAAAKAAAAAUAAAAFAAAAAQAYAAAAAABQAAAAAAAAAAAAAAAAAAAAAAAAAP///////////////////wD///8AAP////////8AAP8A////////////////////AP///////wAA/////wAA/wAAAP////////////////8AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABBQAAAAAAAIqtBf4=
By other means, I have been able to revert this base64 string back to binary
and here are the results:
9 + ÿÿÿÿImagen de mapa de bits Paint.Picture PBrush
BM? 6 ( P ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ ÿÿÿ
ÿÿÿÿÿÿÿ ÿ ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿ ÿÿÿÿ ÿ ÿÿÿÿÿÿÿÿÿÿÿÿÿ
S þ
Of course, I have some rubbish here. But not everything is lost: Now I can
see that a localized language string is included in the OLE chunk before the
'BM' indicating the begining of the bitmap file. And now the final question:
Should I use other string than
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
105))
when the locale of the systems/servers/software is not english? In my case
I'm using spanish and since the string 'Imagen de mapa de bits' is not as
long as 'Bitmap image file', I think that 105 is not valid/correct when
other languages are used.
After some calculations I think that the OLE chunk size for spanish (which
is my case) is 121 instead of 105. However the red cross is still there!!!!!
:(((
Another aproach, instead of letting RS cropping the image ole data, is to
retrieve the image from the database without it. Is it feasible to retrieve
just a 'substring' of the image?
Please help. I really need it. Thaks.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> escribió en el mensaje
news:%23AtcAySVFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Thanks for your reply but it seems that there should be another reason.
> Your explanation is on the good road, but
> I have tried your expression, with that 105 varying from 100 to 110 with
> the same results. The red cross is still there.
> Any other suggestion? Regards.
> PS: Just for further debugging, I have an output of the first 950 bytes
> generated by the expression:
> =System.Convert.ToBase64String(Fields!Picture.Value)
> Here they follow:
> FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAYEgAAEJNYEgAAAAAAAB2AAAAKAAAAM8AAACxAAAAAQAEAAAAAAAAAAAAEBcAABAXAAAAAAAAAAAAAAAAAAD///8A+vr6APPz8wDn5+cA29vbAMzMzAC5ubkAo6OjAIuLiwBzc3MAWVlZAEJCQgAvLy8AHx8fAA8PDwAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBEREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> escribió en el
> mensaje news:eL5pK2LVFHA.612@.TK2MSFTNGP12.phx.gbl...
>> This sounds like the images are stored as OLE images in the database
>> (e.g. Access would convert images into OLE images). You can try the
>> following expression to get rid of the OLE chunk:
>> =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
>> 105))
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
>> news:e5gQDOJVFHA.584@.TK2MSFTNGP15.phx.gbl...
>> How should I store an image into my SQL database in order to be able to
>> read it using Reporting Services Image control? I have a table with an
>> image field (datatype of the field is 'Image').
>> I have tried several ways and the report is always rendered with the
>> image showing a red cross inside it (image broken). However if I read
>> the table using MS Access ADP and double click on the image field, MS
>> Paint opens and shows up the image (this is a bmp image). I have also
>> set the MIMEType of the rs image field to 'image/bmp' (filling this
>> field this is compulsory with database images).
>> It seems that, even though the database field contents ('Image' field)
>> are properly stored, Reporting Services does not know how to handle it.
>> May I need to do a CONVERT(Binary, MyImageField) or the database field
>> be of another type? How should I store the image inside the database so
>> that RS could read it?
>> Regards.
>> PS: This is RS SP2.
>>
>

Wednesday, March 7, 2012

Database Design Standards

Well i've been given a big job of copying all the databases from an old
server to a new server. In order to provide better security, availabilty,
performance.
My servers are in a DMZ, so i have to use remote desktop/terminal services
to connect to it.
1. I have two logical partitions in the server. Is it a good practice to
store the OS and SQL server software itself on C:\ and all the data on
d:\??(Will it help me in anyways to achieve better performance? Can i
make separate directories for each databaseon d:\. and further on extending
it to sub directories for data and log files?
2. Should i copy all objects such as logins, DB plans, jobs etc. from the
old server or is it a better a practice to start all the plans over (create
new plans) to achieve better results and only copy the databases?
3. What is a good strategy for backup plans? For Log Files? For Primary
Files?
4. How to come up with a good Disaster Recovery Plan? What are all the
things you need to have in order to create a good DR plan? what is a good
way to test it?
5. What is the best way to secure SQL server? Who should have what
access? Which people should have access to the server itself? And how can
i give people read only access to the databases if they have access to the
server? Do they even need access to the server? How can they only have
read access to the SQL server databases? What tools do i need? Since i
have to use remote desktop to conncet to the servers, how can i give my
clients that just want read access to the all the data files including log
files? What do they need installed / or use in order to achieve this?
6. Is there any way you can come up with Roles scheme for certain users?
Lets say a particular group of users should have a certain permissions? Can
we create a something like that? that need to be done on the OS level
rather than SQL level.?
I know this is asking for a lot, but its really important to me, your
valuable knowledge on all this issues would be much much appreciated?
Thank you guys very much
NO HELP So far
"Shash Goyal" <Shash703@.gmail.com> wrote in message
news:ejQCy58tEHA.348@.tk2msftngp13.phx.gbl...
> Well i've been given a big job of copying all the databases from an old
> server to a new server. In order to provide better security, availabilty,
> performance.
> My servers are in a DMZ, so i have to use remote desktop/terminal services
> to connect to it.
> 1. I have two logical partitions in the server. Is it a good practice to
> store the OS and SQL server software itself on C:\ and all the data on
> d:\??(Will it help me in anyways to achieve better performance? Can i
> make separate directories for each databaseon d:\. and further on
extending
> it to sub directories for data and log files?
> 2. Should i copy all objects such as logins, DB plans, jobs etc. from
the
> old server or is it a better a practice to start all the plans over
(create
> new plans) to achieve better results and only copy the databases?
> 3. What is a good strategy for backup plans? For Log Files? For Primary
> Files?
> 4. How to come up with a good Disaster Recovery Plan? What are all the
> things you need to have in order to create a good DR plan? what is a
good
> way to test it?
> 5. What is the best way to secure SQL server? Who should have what
> access? Which people should have access to the server itself? And how
can
> i give people read only access to the databases if they have access to the
> server? Do they even need access to the server? How can they only
have
> read access to the SQL server databases? What tools do i need? Since i
> have to use remote desktop to conncet to the servers, how can i give my
> clients that just want read access to the all the data files including log
> files? What do they need installed / or use in order to achieve this?
> 6. Is there any way you can come up with Roles scheme for certain users?
> Lets say a particular group of users should have a certain permissions?
Can
> we create a something like that? that need to be done on the OS level
> rather than SQL level.?
> I know this is asking for a lot, but its really important to me, your
> valuable knowledge on all this issues would be much much appreciated?
> Thank you guys very much
>
|||On Sun, 24 Oct 2004 23:45:32 -0500, Shash Goyal wrote:

>NO HELP So far
Hi Shash,
There are everal replies in some of the other groups wherre you posted
this same question. I recommend you follow up on those replies instead of
complaining about lack of replies in this grooup.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||hi,
"Shash Goyal" <Shash703@.gmail.com> ha scritto nel messaggio
news:ejQCy58tEHA.348@.tk2msftngp13.phx.gbl
big deal, isn't it? =;-D

> Well i've been given a big job of copying all the databases from an
> old server to a new server. In order to provide better security,
> availabilty, performance.
> My servers are in a DMZ, so i have to use remote desktop/terminal
> services to connect to it.
> 1. I have two logical partitions in the server. Is it a good
> practice to store the OS and SQL server software itself on C:\ and
> all the data on d:\??(Will it help me in anyways to achieve better
> performance? Can i make separate directories for each databaseon
> d:\. and further on extending it to sub directories for data and log
> files?
I usually go this way on desktops... just for a schema order where data is
not directly bound and mixed with exes...
you do not gain better performance as drive and controller are the same..
perhaps you could get worser performance as the disk headers will have more
work... but for sure you do not gain benefits this way..
you are not granted against disk failures as one of them wil probably trash
exes and data..
again.. I do it myself the same way.. but only for a schema mapping order,
not for physical matters..
to achieve better througoutput performance, you should go for separate
controllers and drives... RAID solutions as well for improving security...
further basic info about RAID levels at http://www.acnc.com/04_00.html
as regard te second part of the question, yes, you can... no problem with
that..

> 2. Should i copy all objects such as logins, DB plans, jobs etc.
> from the old server or is it a better a practice to start all the
> plans over (create new plans) to achieve better results and only copy
> the databases?
I do not think you'll get problems copying all original objects to the new
server... I see non penalties in that... yo could just get orphaned objects
if your original server is not clean, but I do not see problems at all..

> 3. What is a good strategy for backup plans? For Log Files? For
> Primary Files?
hey, this is trickie... you have to think about your own needs... there' no
one size fits all strategy... depending on your needs you could be happy
with a dayly full backup for a little database, even a weekly full backup
for a quite read-only database..
if you are subscriber at SQL Server Magazine, you can have a look at good
articles like
http://www.windowsitpro.com/Article/...647/39647.html
http://www.windowsitpro.com/Article/...657/39657.html
this is free to read,
http://www.winnetmag.com/Article/Art...915/25915.html , for Very Large
Databases secenarios, with full, differential an log backup features
explanations..
again... it all depends on your data, your needs, your shadow time limits...
http://www.winnetmag.com/Article/Art...340/24340.html is free too...
by "Notre Dame SQL Server", Kalen Delaney

> 4. How to come up with a good Disaster Recovery Plan? What are all
> the things you need to have in order to create a good DR plan? what
> is a good way to test it?
same as above... plus add OS, cluster settings, applications CD to the
recovery schema...
a disaster can strike you down to your knees, so you'll have all (really
all) to start a new database server, application server, web server, farm
and all..
so, to your standard backups tapes, you should add all the CDs to install
and ugrade to your current service pack levels your OS and applications..
and please do not store them on the main server top =;-D
you should consider how important is your data and how you want to protect
all your IT scenario.. you can perhaps maintain a local copy and dayly ship
a backup copy to your bank or to specialised storing company..
it's a matter of costs, requirements and so on...

> 5. What is the best way to secure SQL server? Who should have what
> access? Which people should have access to the server itself? And
> how can i give people read only access to the databases if they have
> access to the server? Do they even need access to the server?
> How can they only have read access to the SQL server databases?
> What tools do i need? Since i have to use remote desktop to conncet
> to the servers, how can i give my clients that just want read access
> to the all the data files including log files? What do they need
> installed / or use in order to achieve this?
only users needing access to the data should be given right to access the
database server.. and only to the databases they need to access, with the
minimal privileges they need for theyr required activities..
the tool you need to implement security depends on your skill... Query
Analyzer could be enought to execute GRANT/DENY DCL statements to database
objects, where sp_addlogin/sp_grantlogin are required for server accces..
and again, give them only access (via sp_adduser) to the database they need
to access..
there's no readonly privilege to the database.. you are legitimated to
access it or not... you can give readonly privileges to tables and views
objects granting SELECT only privileges to some or all of them, but my
preferred access schema is via GRANT EXECUTE to
retrival/insert/update/delete stored procedures only and DENY all privileges
on table objects.. you can give them access to view object if needed..
a good starting point about secuirty and some best practice can be found at
http://www.sql-server-performance.co...l_security.asp , as long as
http://www.microsoft.com/technet/pro.../sp3sec03.mspx
...
you users do not need access to the physical files of your databases, nor do
they need access to the log files at all.. your database are only logical
matters for them, and this should remain that way..
they only need the application(s) they use to interact with data, both
windowsform or web interfaces, and, usually MDAC, in order to provide the
connectivity tools and SQL Server drivers.. not more, not less.. standar
users do not need acces to Enterprise Manager and/or Query Analyzer.. give
them only what they really need.. not more.. better less =;-D

> 6. Is there any way you can come up with Roles scheme for certain
> users? Lets say a particular group of users should have a certain
> permissions? Can we create a something like that? that need to be
> done on the OS level rather than SQL level.?
this is a best practice and standard idea.. instead of maintaining
thousounds of users privileges, create at database level different roles..
make each user part of the corresponding role and manage privileges at role
level... you will not become insane and you can achieve the granularity you
usually need..
roles have to be managed at database level, and are known as user defined
database roles... not at the OS level... roles at OS level can be usefull,
but you have to deal with database and related security...
have a look at
http://msdn.microsoft.com/library/de..._addp_33s5.asp

> I know this is asking for a lot, but its really important to me, your
> valuable knowledge on all this issues would be much much appreciated?
do you mean you don't know if our meanings will be appreciated? ROTFL
you are right... no one but you knows better your own needs ... I can only
give you some hints based on my own experience and based on my (poor) skill,
but you have the key of your success in this area..
good luck
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thank you very much Andrea!!!
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2u6on1F26o6hvU1@.uni-berlin.de...
> hi,
> "Shash Goyal" <Shash703@.gmail.com> ha scritto nel messaggio
> news:ejQCy58tEHA.348@.tk2msftngp13.phx.gbl
> big deal, isn't it? =;-D
>
> I usually go this way on desktops... just for a schema order where data is
> not directly bound and mixed with exes...
> you do not gain better performance as drive and controller are the same..
> perhaps you could get worser performance as the disk headers will have
more
> work... but for sure you do not gain benefits this way..
> you are not granted against disk failures as one of them wil probably
trash
> exes and data..
> again.. I do it myself the same way.. but only for a schema mapping order,
> not for physical matters..
> to achieve better througoutput performance, you should go for separate
> controllers and drives... RAID solutions as well for improving security...
> further basic info about RAID levels at http://www.acnc.com/04_00.html
> as regard te second part of the question, yes, you can... no problem with
> that..
>
> I do not think you'll get problems copying all original objects to the new
> server... I see non penalties in that... yo could just get orphaned
objects
> if your original server is not clean, but I do not see problems at all..
>
> hey, this is trickie... you have to think about your own needs... there'
no
> one size fits all strategy... depending on your needs you could be happy
> with a dayly full backup for a little database, even a weekly full backup
> for a quite read-only database..
> if you are subscriber at SQL Server Magazine, you can have a look at good
> articles like
> http://www.windowsitpro.com/Article/...647/39647.html
> http://www.windowsitpro.com/Article/...657/39657.html
> this is free to read,
> http://www.winnetmag.com/Article/Art...915/25915.html , for Very
Large
> Databases secenarios, with full, differential an log backup features
> explanations..
> again... it all depends on your data, your needs, your shadow time
limits...
> http://www.winnetmag.com/Article/Art...340/24340.html is free too...
> by "Notre Dame SQL Server", Kalen Delaney
>
> same as above... plus add OS, cluster settings, applications CD to the
> recovery schema...
> a disaster can strike you down to your knees, so you'll have all (really
> all) to start a new database server, application server, web server, farm
> and all..
> so, to your standard backups tapes, you should add all the CDs to install
> and ugrade to your current service pack levels your OS and applications..
> and please do not store them on the main server top =;-D
> you should consider how important is your data and how you want to protect
> all your IT scenario.. you can perhaps maintain a local copy and dayly
ship
> a backup copy to your bank or to specialised storing company..
> it's a matter of costs, requirements and so on...
>
> only users needing access to the data should be given right to access the
> database server.. and only to the databases they need to access, with the
> minimal privileges they need for theyr required activities..
> the tool you need to implement security depends on your skill... Query
> Analyzer could be enought to execute GRANT/DENY DCL statements to database
> objects, where sp_addlogin/sp_grantlogin are required for server accces..
> and again, give them only access (via sp_adduser) to the database they
need
> to access..
> there's no readonly privilege to the database.. you are legitimated to
> access it or not... you can give readonly privileges to tables and views
> objects granting SELECT only privileges to some or all of them, but my
> preferred access schema is via GRANT EXECUTE to
> retrival/insert/update/delete stored procedures only and DENY all
privileges
> on table objects.. you can give them access to view object if needed..
> a good starting point about secuirty and some best practice can be found
at
> http://www.sql-server-performance.co...l_security.asp , as long as
>
http://www.microsoft.com/technet/pro.../sp3sec03.mspx
> ..
> you users do not need access to the physical files of your databases, nor
do
> they need access to the log files at all.. your database are only logical
> matters for them, and this should remain that way..
> they only need the application(s) they use to interact with data, both
> windowsform or web interfaces, and, usually MDAC, in order to provide the
> connectivity tools and SQL Server drivers.. not more, not less.. standar
> users do not need acces to Enterprise Manager and/or Query Analyzer.. give
> them only what they really need.. not more.. better less =;-D
>
> this is a best practice and standard idea.. instead of maintaining
> thousounds of users privileges, create at database level different roles..
> make each user part of the corresponding role and manage privileges at
role
> level... you will not become insane and you can achieve the granularity
you
> usually need..
> roles have to be managed at database level, and are known as user defined
> database roles... not at the OS level... roles at OS level can be usefull,
> but you have to deal with database and related security...
> have a look at
>
http://msdn.microsoft.com/library/de..._addp_33s5.asp
>
> do you mean you don't know if our meanings will be appreciated? ROTFL
> you are right... no one but you knows better your own needs ... I can only
> give you some hints based on my own experience and based on my (poor)
skill,
> but you have the key of your success in this area..
> good luck
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

Saturday, February 25, 2012

Database design question

Hi there

I'm in the process of creating an order database that contains tables for products and an order table that contains information for orders. Should the order table have a xref to the products table to show what products are associated or should there be a separate table to show what products are associated for the order (ie a orderProduct table) that would also contain pricing. The reason for my question is for tracking order history. If the price of a product changes and the order table is associated with the product id then old orders will have the new pricing which is incorrect. Would it be better to when an order is place copy the values of price and the product info to an other table?

Thanks

Good thinking. Separate table. OrderProducts. OrderID FK, ProductID FK, OrderPrice. OrderPrice is the price at the time of the order.

Incidentally, can anyone delete a product if your customer discontinues it? If so, you might want to think about a YesNo/bit column to flag a discontinued product for suppression rather than wiping it from the database.

|||

I would recommend that you do that, for exactly the reason you state. I have worked with MS Commerce Server 2000 and it had a similar database design. Here is a simplified example from my vague memory:

Order
OrderID (primary key)
OrderNumber (human readable order number)
CustomerID (foreign key to customer)
Date
SubTotal
Tax
Shipping
GrandTotal

Product
ProductID (primary key)
Active
SKU
Name
Description
Price

OrderItem
SKU
Name
Price
Quantity
Total

The idea is that any field that could change throughout the lifetime of a product should be stored with the order in order to record exactly what they ordered. You may even think about moving the Price for Products out into a ProductPrice table like the following.

Product
ProductID
SKU
Name
Description

ProductPrice
ProductID (foreign key to product)
EffectiveDate (date this price becomes effective)
Price

This design allows you to change prices ahead of time and specify when they become effective. To get the current price you would select the ProductPrice with the highest EffectiveDate that is greater than or equal to today's date. This may be totally unnecessary though, it's just an idea.

Mark

|||thanks for the replies guys that does help clarify things. I'm currently having the same type of issues with a room booking database design. I want to have tenants book certain rooms for specified time intervals. The tenants can book rooms (rooms are stored in a table) and select things like room setup (configurations are stored in a table). All the tables have an active flag on then. I also have a bookings table but unsure as to how to store the rooms and configurations that are selected. Should i be using ids in the booking table? What happens if the rooms become inactive down the road and the client was to view past bookings? Do i leave out the check on the active flags and show the rooms anyway? I believe that i need the room ids in order to validate new bookings against what is already booked. If the admins want to edit bookings and say a room or configuration that was previously active is now inactive would i force the admin to select a new configuration? tables such as room and room configuration do not have an interface to change values or inactivate them, i'm just wondering for the future if they say they want to add a room and remove a room then past bookings will get affected.

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

Database Design

Well i've been given a big job of copying all the databases from an old
server to a new server. In order to provide better security, availabilty,
performance.
My servers are in a DMZ, so i have to use remote desktop/terminal services
to connect to it.
1. I have two logical partitions in the server. Is it a good practice to
store the OS and SQL server software itself on C:\ and all the data on
d:\'?(Will it help me in anyways to achieve better performance? Can i
make separate directories for each databaseon d:\. and further on extending
it to sub directories for data and log files'
2. Should i copy all objects such as logins, DB plans, jobs etc. from the
old server or is it a better a practice to start all the plans over (create
new plans) to achieve better results and only copy the databases?
3. What is a good strategy for backup plans? For Log Files? For Primary
Files?
4. How to come up with a good Disaster Recovery Plan' What are all the
things you need to have in order to create a good DR plan' what is a good
way to test it?
5. What is the best way to secure SQL server' Who should have what
access? Which people should have access to the server itself? And how can
i give people read only access to the databases if they have access to the
server? Do they even need access to the server' How can they only have
read access to the SQL server databases' What tools do i need? Since i
have to use remote desktop to conncet to the servers, how can i give my
clients that just want read access to the all the data files including log
files? What do they need installed / or use in order to achieve this'
6. Is there any way you can come up with Roles scheme for certain users?
Lets say a particular group of users should have a certain permissions? Can
we create a something like that' that need to be done on the OS level
rather than SQL level.'
I know this is asking for a lot, but its really important to me, your
valuable knowledge on all this issues would be much much appreciated?
Thank you guys very muchAfter reading through this, it's pretty clear you're not that familiar with
sql server. You'll definately want to do some reading in books online on al
l
these topics. It might not hurt, either, to bring someone in on a consultin
g
basis to help you get started (no, I'm not a consultant, but I've used them
to approve/disapprove of stuff I've done with Oracle). While I don't
recommend you doing this without more research, here's some 10,000 ft.
answers to your questions...
1. Typically, you want to keep the OS isolated from other stuff. You
mentioned 2 logical partitions, but the physical layer underneath that is
going to be more interesting. If both these partitions are sitting on the
same physical drives (raid?), then it's going to be a wash. From an
organizational standpoint, if I only had one partition above and beyond my O
S
partition, I'd install the program and data to the same partition, separate
from the OS. You can make separate directories for each database, but this
won't have much impact on performance unless the directories for your data
and log files sit on separate physical partitions.
2. If you're comfortable migrating your master and msdb database files,
then it's a quick and easy. If you're not, then start from scratch. At
worst, you can script out your jobs and users. Look up sp_hexadecimal and
sp_help_revlogin. Once sp_hexadecimal has been created, you can use
sp_help_Revlogin to create a script you can run on the new server to recreat
e
all your users. DTS packages can be copied by opening them up and changing
the server name when performing a "save as...". Jobs can be scripted and
recreated in Query Analyzer on the new instance.
3. How much data can you afford to lose? If none, run a daily full with
hourly or bihourly transaction backups, at the least. If you can afford to
loose most or all of a day's transactions, run a daily full. You'll want to
consult with your business decision makers on this one. Their needs will
dictate the answer to this.
4. To have a good DR plan, you need a secondary site. You can use log
shipping to copy and restore transaction log backups to the remote site. If
it's not remote, then it's just a standby (which is still good to have), but
it's not DR. A good way to test would be to find a time when you can take
your system offline and see how long it takes for your team to get the
application back up using the secondary site. There are multiple vended
solutions to help with this as well, depending on your architecture
(clusters?, all stand-alones?, SAN?)
5. I've heard different answers from different quarters. With password
cracking what it is, I've found more and more recommendations for using mixe
d
security mode and relying on windows strong passwords instead of sql users.
All users should be locked down to the minimum amount of access required to
do their jobs. Only DBAs and select operations staff members should ever
have access to the server itself. Anyone other than the DBA should be
granted access at the discretion of the DBA. Some network admins will baulk
at this, but I haven't found one yet who'll open the exchange server to me.
Access to the server does NOT imply access to the database. You can lock
users down as read only using the db_Datareader database role in each
database. Whether or not users require access to the OS is going to depend
on your architecture. In a 3-tier environment, they should not require
access to the OS. The best tools are query analyzer and enterprise manager,
both included in the client tools portion of the sql server install. Users
will never have use for the log files. They're not legible without other
tools anyway. For that matter, no user is going to care about your data
files, either (not directly, anyway). The users' only mode of access is by
logging into the database. See note above about db_datareader. If the
application is a fat install, there's a good chance you're going to need to
put the Client Network Utility on each workstation. This is available off
the sql install - select Client Connectivity.
6. They're called Roles. They work just like Windows groups. If a user
has a right that conflicts with rights for a role the user belongs to, the
most restrictive wins.
"Shash Goyal" wrote:

> Well i've been given a big job of copying all the databases from an old
> server to a new server. In order to provide better security, availabilty,
> performance.
> My servers are in a DMZ, so i have to use remote desktop/terminal services
> to connect to it.
> 1. I have two logical partitions in the server. Is it a good practice to
> store the OS and SQL server software itself on C:\ and all the data on
> d:\'?(Will it help me in anyways to achieve better performance? Can i
> make separate directories for each databaseon d:\. and further on extendi
ng
> it to sub directories for data and log files'
> 2. Should i copy all objects such as logins, DB plans, jobs etc. from th
e
> old server or is it a better a practice to start all the plans over (creat
e
> new plans) to achieve better results and only copy the databases?
> 3. What is a good strategy for backup plans? For Log Files? For Primary
> Files?
> 4. How to come up with a good Disaster Recovery Plan' What are all the
> things you need to have in order to create a good DR plan' what is a goo
d
> way to test it?
> 5. What is the best way to secure SQL server' Who should have what
> access? Which people should have access to the server itself? And how c
an
> i give people read only access to the databases if they have access to the
> server? Do they even need access to the server' How can they only hav
e
> read access to the SQL server databases' What tools do i need? Since i
> have to use remote desktop to conncet to the servers, how can i give my
> clients that just want read access to the all the data files including log
> files? What do they need installed / or use in order to achieve this'
> 6. Is there any way you can come up with Roles scheme for certain users?
> Lets say a particular group of users should have a certain permissions? Ca
n
> we create a something like that' that need to be done on the OS level
> rather than SQL level.'
> I know this is asking for a lot, but its really important to me, your
> valuable knowledge on all this issues would be much much appreciated?
> Thank you guys very much
>
>|||Shash
I'd recommend you to vist the below sites
http://vyaskn.tripod.com/ sql_serve...r />
.htm#Step1
--administaiting
best practices
http://vyaskn.tripod.com/sql_server...t_practices.htm --secu
rity
best practices
"Shash Goyal" <Shash703@.gmail.com> wrote in message
news:OKU7978tEHA.2300@.TK2MSFTNGP09.phx.gbl...
> Well i've been given a big job of copying all the databases from an old
> server to a new server. In order to provide better security, availabilty,
> performance.
> My servers are in a DMZ, so i have to use remote desktop/terminal services
> to connect to it.
> 1. I have two logical partitions in the server. Is it a good practice to
> store the OS and SQL server software itself on C:\ and all the data on
> d:\'?(Will it help me in anyways to achieve better performance? Can i
> make separate directories for each databaseon d:\. and further on
extending
> it to sub directories for data and log files'
> 2. Should i copy all objects such as logins, DB plans, jobs etc. from
the
> old server or is it a better a practice to start all the plans over
(create
> new plans) to achieve better results and only copy the databases?
> 3. What is a good strategy for backup plans? For Log Files? For Primary
> Files?
> 4. How to come up with a good Disaster Recovery Plan' What are all the
> things you need to have in order to create a good DR plan' what is a
good
> way to test it?
> 5. What is the best way to secure SQL server' Who should have what
> access? Which people should have access to the server itself? And how
can
> i give people read only access to the databases if they have access to the
> server? Do they even need access to the server' How can they only
have
> read access to the SQL server databases' What tools do i need? Since i
> have to use remote desktop to conncet to the servers, how can i give my
> clients that just want read access to the all the data files including log
> files? What do they need installed / or use in order to achieve this'
> 6. Is there any way you can come up with Roles scheme for certain users?
> Lets say a particular group of users should have a certain permissions?
Can
> we create a something like that' that need to be done on the OS level
> rather than SQL level.'
> I know this is asking for a lot, but its really important to me, your
> valuable knowledge on all this issues would be much much appreciated?
> Thank you guys very much
>|||Thanks Uri!!
"Shash Goyal" <Shash703@.gmail.com> wrote in message
news:OKU7978tEHA.2300@.TK2MSFTNGP09.phx.gbl...
> Well i've been given a big job of copying all the databases from an old
> server to a new server. In order to provide better security, availabilty,
> performance.
> My servers are in a DMZ, so i have to use remote desktop/terminal services
> to connect to it.
> 1. I have two logical partitions in the server. Is it a good practice to
> store the OS and SQL server software itself on C:\ and all the data on
> d:\'?(Will it help me in anyways to achieve better performance? Can i
> make separate directories for each databaseon d:\. and further on
extending
> it to sub directories for data and log files'
> 2. Should i copy all objects such as logins, DB plans, jobs etc. from
the
> old server or is it a better a practice to start all the plans over
(create
> new plans) to achieve better results and only copy the databases?
> 3. What is a good strategy for backup plans? For Log Files? For Primary
> Files?
> 4. How to come up with a good Disaster Recovery Plan' What are all the
> things you need to have in order to create a good DR plan' what is a
good
> way to test it?
> 5. What is the best way to secure SQL server' Who should have what
> access? Which people should have access to the server itself? And how
can
> i give people read only access to the databases if they have access to the
> server? Do they even need access to the server' How can they only
have
> read access to the SQL server databases' What tools do i need? Since i
> have to use remote desktop to conncet to the servers, how can i give my
> clients that just want read access to the all the data files including log
> files? What do they need installed / or use in order to achieve this'
> 6. Is there any way you can come up with Roles scheme for certain users?
> Lets say a particular group of users should have a certain permissions?
Can
> we create a something like that' that need to be done on the OS level
> rather than SQL level.'
> I know this is asking for a lot, but its really important to me, your
> valuable knowledge on all this issues would be much much appreciated?
> Thank you guys very much
>

Thursday, March 29, 2012

Database images not showing

Wednesday, March 7, 2012

Database Design Standards

Saturday, February 25, 2012

Database design question

Sunday, February 19, 2012

Database Design

Database Design

Database Design

Database Design

Database Design

Database level lock

Blog Archive

About Me