Database Snapshot Disk IO considerations and Best Practices

I understand the concept of database snapshots, however, I'm unsure how to tackle this from disk utilization point of view.

First off I'll explain how I want to use database snapshots, then the configurations at the disk level I was considering.

Currently in 2005 we can also find Snapshot Isolation whcih helps with concurrency but it will use the tempdb heavily. Since I have enough things hammering the tempdb I was considering using database snapshots to help with the reporting functionality.

Since a database snapshot creates sparse files there will not be a lot of space being consumed (I'll have a question on this one later). Since creating a database snapshot is pretty light weight I was considering creating a job that will snapshot the database every minute (don't freak out yet let me explain). Then I will have another job to drop snapshots that do not have an active transaction in them. I've created a function that will return the latest available snapshot and using some dynamic SQL I can use that in my reporting code.

I've created all the code for this to happen, but now comes the fun part... where do I put the database snapshots?

Currently my databases have seperated array for Log files, seperate array for the clustered indexes, and a seperate array for non-clustered indexes (as well as partitioned tables, BLOB, etc...). So all together my IO is split up across about 32 spindles. I have 3 spindles in a RAID 5 available. Do I created the database snapshot in the same location as the files (because creating a db snapshot is just like creating a real database). Or to I lump them up on the small array, data and logs and all?

Also, if my database is let say 75GB and I want to snapshot it to a 36GB partition I know that the sparse files will be small and it show actual space on disk to be small but logically it will show at 75GB will SQL or the OS have an issue with that?

Thanks

Rich

If there is a possibility to create another drive then use that to store the backup/snapshot files. I'm not sure if you are using EMC then it will be easy to store them inspite of any issue. Why you want to compress the snapshot in this case.|||I'm not looking to compress anything. I'm not sure if I'm being clear but I'm talking about SQL 2005 new ability to create database snapshots, which automatically creates sparse files. We are using an EMC CX3-20 SAN if that helps.

Database Size

1) How can we use T-SQL to find the database allocation simliar to view database size in taskpad.

2) Is there a way to set alert when database size is reach at 80%?

thanks

I am using SQL Server 2000.

Database Security

How i can Secure my database file from to be attached to
any other SERVER (sql server ) and view database design
Thanks"Said Fadel" <saidfadel@.hotmail.com> wrote in message
news:265001c4c104$ad703b20$a401280a@.phx.gbl...
> How i can Secure my database file from to be attached to
> any other SERVER (sql server ) and view database design
Are you asking, can your SQL Server 2000 database (and log) files be moved
to another remote server, keeping SQL Server on the original server? If so,
the database data and log files need to be keep on drives that appear to be
local to SQL Server, however these drives could be SAN attached.
Steve|||Typically this is done by securing access to the server itself - who
can access the directories where the data and log files are, who can
stop and start services, etc.
If you are referring to having a database that you give to a customer
and you don't want that customer to be able to view your design.
Likely the best was to manage this is through legal agreements.
Outside of that, you can look at some encryption mechanisms. You can
find links for these products in the encryption section of this FAQ:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
-Sue
On Tue, 2 Nov 2004 09:52:10 -0800, "Said Fadel"
<saidfadel@.hotmail.com> wrote:

>How i can Secure my database file from to be attached to
>any other SERVER (sql server ) and view database design
>Thanks|||I want to deploy a SQL server database on my client windows 2003 server
; I
will have admin access on the server through Terminal services
The client server will have SQL server 2000 installed ; and I will use a
backup copy of my db and install it using Restor Database command in the
Enterprise manager
How can prevent my client from viewing/modifing my Database while he
has
admin access to the server ?
The database will host information relative to an ASP.net website that
is
hosted on IIS on the same server ; a username/password for this DB
should be
available also on the web.config file of the ASP.net website .. what is
the
minimum security setting that should be given to this username/password
to
allow the website manipulate the database ?
Please advice about that ; the reason I want to do this is to prevent my
client from reverse engineering my system analysis and databse design;
but
at the same time being able to use my ASP.net web application .
.
How can prevent my client from viewing/modifing my Database while he has
admin access to the server ?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Database security

hi thr,

May I know how to create a secure database? I heard some of my seniors said about something that outsider unable to open and view our database table unless the user is the admin itself...instead of setting password to our database, is there any other way to avoid our database to be viewed from other people?

p/s: Any databases but I prefer SQL Server 2000 for this question...thx!!Nobody can access database unless you grant them rights.
If you are sysadmin revoke all rights and privileges like security administrator or system administrator from all users and create, for example, read_only group (role) in each database. You can grant some rights to this group like select from some tables of views, execute from some reporting stored procedures. Any new user should be a member of this group. And you will not have to worry granting rights separately to each user.
If you have user_id and password that everyone knows. You just change a password and nobody will be able to login without your knowledge or permission.

To test my words:
1.create some login_id make it a member of read_only group.
2.login with this new UID and see what youll see or be able to do.

Hope it helps.
There is much more to it. But people write books on Server Security and it is not a place for it.. :)

database role secureables not shows up

Hello!

After creating a new database role I have add some permissions. Now, when I want to view the role an the permissions the secureables page are empty.

This problem is only within the management studio. If I use Enterprise Manager I can see that the changes are taking effect.

Any hints?

Lutz

I see the same, I will open a bug on this.

If you would like to know whether the the permissions are granted to an object, right click the object and select ->Properties and view the permissions tab

Thanks,

Gops Dwarak

Database questions

We are negotiating with a vendor and we have a few questions:

1) From a licensing point of view, what is a database? Can we install
multiple copies of SQLServer on one box? In that case, would each
copy be a different database?

2) Within a single SQL Server installation, I can create multiple
databases, as listed in the Enterprise Manager. I can switch among
them using "use ..." commands. Would these be treated as different
databases?

3) We have copies of our transactional database in identical
development, QC, and production environments. Is this a single
database, or 3 databases?php newbie (newtophp2000@.yahoo.com) writes:
> We are negotiating with a vendor and we have a few questions:
> 1) From a licensing point of view, what is a database? Can we install
> multiple copies of SQLServer on one box? In that case, would each
> copy be a different database?
> 2) Within a single SQL Server installation, I can create multiple
> databases, as listed in the Enterprise Manager. I can switch among
> them using "use ..." commands. Would these be treated as different
> databases?
> 3) We have copies of our transactional database in identical
> development, QC, and production environments. Is this a single
> database, or 3 databases?

Technically, you can install several instances of SQL Server on one box, and
I cannot recall that the license terms for SQL Server puts any constraints
on this. Each instance can host any number of databases up to some high
number determined by technical constraints.

From this follows that the technical answers to question 2 and 3 is that
yes, they are each one database, and they are three databases.

But from a legal perspective it may be different, and it is up to the
license terms of this particular vendor. If you buy a product from a
vendor, it is not unreasonable that you should be permitted that beside
the production environment, to also have an test environment where you
can test new releases from the customer. On the other hand, if you
would have two different departments running the application with
two different databases, it would not be unreasonable if that required
a second license.

In any case, this nothing that can be answered in this group. If you are
negotiating license terms with a vendor before buying their stuff, it
is up to your negotiating skills. If you are discussing the implications
of the terms of a license you already have, then you need a lawyer.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"php newbie" <newtophp2000@.yahoo.com> wrote in message
news:124f428e.0411160231.4ccde1c2@.posting.google.c om...
> We are negotiating with a vendor and we have a few questions:

To add to Erland's response, I think perhaps the best way to think of this
is to think of databases, database servers/instances and physical servers.

> 1) From a licensing point of view, what is a database? Can we install
> multiple copies of SQLServer on one box? In that case, would each
> copy be a different database?

Each would be a separate database server usually known as a separate
instance. As Erland points out, check with MS. I believe Standard Edition
does requires a separate license for each instance, Enterprise Server
permits multiple instances.

> 2) Within a single SQL Server installation, I can create multiple
> databases, as listed in the Enterprise Manager. I can switch among
> them using "use ..." commands. Would these be treated as different
> databases?

Yes, separate databases, but not separate instances.

> 3) We have copies of our transactional database in identical
> development, QC, and production environments. Is this a single
> database, or 3 databases?

You have 3 copies of the same database on 3 separate physical servers.
Again, see MS for details on licensing. (For example, you may need CPU
licenses for Production, but could get away with developer licenses in Dev.|||Thanks for the helpful responses! Things are now easier to explain on our side.

Database properties

Hi.

I am trying to look for ways on how to get all the databse properties under each instance.

say I want to view the size, recovery model, physical location, etc.

The sys.database_files is only applicable per database. Is there a query, or a table in master database to get these or store these values?

thanks...

You can query the sys.databases and sys.master_files to get this information on a per instance basis. You will need to query each instance seperatly.|||Thanks for that!

Database Properties

is there anyway to retrieve the database properties and display in an asp.net web form as a quick over view rather than logging onto the server to check, items such as size, space available, last backup data etc?

Cheers

I have managed to get the database size using :

EXEC sp_MSforeachtable @.command1=EXEC sp_spaceused

|||

Hi,

Haven't tried it but you should be able to use theMicrosoft.SqlServer.Management.Smo to do so:

Here's an example :

http://www.aspfree.com/c/a/MS-SQL-Server/Retrieving-SQL-Server-2005-Database-Info-Using-SMO-Database-Info-Table-Info/1/

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--