Database Snapshot on SAN attached disks

Hi All

I am in process of moving a SQL 2005 solution from a development box that used local storage to UAT environment with SAN attached storage. The solution uses database snapshots

The database files are on the SAN storage but during testing I was unable to create a Database snapshot on the SAN disk. Creating snapshots on the local disk worked fine.

Is their some restriction/problem in using the database snapshot technology with SAN storage?

What edition of Sql server 2005 you have ? Database snapshot is possible only with Enterprise edition I believe
|||

SQL Server Enterprise 2005 SP2 is being used on all systems.

The snapshots are working against the local disks on the server but not against the SAN attached storage.

Regards

Nadreck

|||

Are the SAN attached drives formatted using the NTFS file system? Database Snapshots use NTFS Sparse Files for the underlying technology, and will not work under any other file system.

I have database snapshots on production clustered servers, using SAN storage, and haven't had any problems.

|||Your use of the term "SAN attached" has me wondering. There are Network Attached Storage (NAS) devices that often have their own proprietary file system and then there are Storage Area Networks (SAN) which are usually NTFS in the Windows world. Which type of storage are you referring to? Database snapshots require NTFS.
|||There is no difference between a "SAN" and a local drive to SQL, they both look like local drives. This must be a security issue on the directory.

If you are talking about a "NAS" drive, as in you access it using "\\servername\share", SQL does not support putting data/transaction files on share drives.

|||

The storage is on a true SAN with the disks attached by Fibre Channel and being presented as Local disk.

I have spoken with the SAN administrators and they have confirmed that the disks are formatted as NTFS

The error received when trying to create the sbnapshot was

Msg 1823, Level 16, State 2, Line 1

A database snapshot cannot be created because it failed to start.

Msg 5123, Level 16, State 1, Line 1

CREATE FILE encountered operating system error 5(error not found) while attempting to open or create the physical file 'filename'.

|||I think the login with which you have logged into Sql server(windows authentication) might not be having modify privilege to the folder where the Sparse file is being created ! also check if the sql service account has necessary privileges to that folder ! ! !

Thanxx
Deepak


|||

The logon accounts and service accounts both have local admin rights on the box so write permissions do not appear to be the issue. The mdf and ldf files for the database are both on SAN storage so SQL appears to have sufficient rights to write to these disks. Creating snapshots on local disk (with the same accounts) has no issue.

Obviously it may still be some permissions issue with the SAN but I cannot see any difference.

regards

Nadreck

Database Service wont start after installing SP2

I am hoping someone can help me. I am having issues changing the service account MSSQLSERVER. I changed it to Local System only due to issues installing Service Packs up SP2 Critical. I used the Configuration tool to do so. I have installed all patches up to and including Critical update ( KB 934458 ). I had a heck of a time getting them to install under custom service accounts. Now when I change the service accounts back to a custom account using the configuration tool the service wont start at all.

To be clear. The service runs fine under Local System Account, but I want to run it under a limited account. I used the config tool and made sure the permissions groups were added, but it still fails to start. I am not sure what SSL log entries below is referring to.

I am running:

SQL Server Standard Edition x86

Windows 2003 Stnd SP2

Below is the log file:

2007-09-01 23:50:16.15 spid9s Starting up database 'model'.
2007-09-01 23:50:16.20 Server Error: 17190, Severity: 16, State: 1.
2007-09-01 23:50:16.20 Server FallBack certificate initialization failed with error code: 1.
2007-09-01 23:50:16.20 Server Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.
2007-09-01 23:50:16.21 Server Error: 17182, Severity: 16, State: 1.
2007-09-01 23:50:16.21 Server TDSSNIClient initialization failed with error 0x80092004, status code 0x80.
2007-09-01 23:50:16.21 Server Error: 17182, Severity: 16, State: 1.
2007-09-01 23:50:16.21 Server TDSSNIClient initialization failed with error 0x80092004, status code 0x1.
2007-09-01 23:50:16.21 Server Error: 17826, Severity: 18, State: 3.
2007-09-01 23:50:16.21 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2007-09-01 23:50:16.21 Server Error: 17120, Severity: 16, State: 1.
2007-09-01 23:50:16.21 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

I am not DBA or very heavy IT, but rather a developer wearing multiple hats, for the time being. So any help is greatly appreciated.

Thanks, DMC

Can anyone explain to me what SSL log error is referring to?

Database security on a Local Network

This is regarding general protection of a database hosted on a network. I am developing a database application for my college library using VB.NET, that will reside on a network.
For some reasons, I did not want to hardcode the Database location in the application. Instead, when a user logs in, he can choose the database location using a folder browser control, if the location has changed.

Now, I realize that for this, I have to put the database in a shared folder, which makes it quite vulnerable. Having pondered over the problem for sometime, a solution that comes to my mind is to place a Text file in the same shared folder that always contains the correct path of the database. When a user chooses that folder, I will read the actual path of the database from the text file, and move the database to a non-shared folder.
I haven't yet implemented this approach, but felt it better to consult someone before. So, would this approach work, and is it a good idea.
For information purposes, I consider it important to mention that the database is in MS Access. I know this is not a place for discussing it, but this is a general security concern. So, I thought
people would not mind answering it....

Hi,

how aout securing the MDB file using the appropiate NTFS permissions and eventually additional Access password security or using an ldb file ? I don′t know if there can be concurrent users on the database, but coyping the database file to a shared folder will allow other users also to copy the file to another folder and working on it, for you having the trouble to bring the data together afterwards.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||More than the problem of bringing it together afterwards, I am worried about someone manipulating it mischievously. That's why I thought of putting the database in a non-shared folder on the server and a text file in a shared folder, which will always

contain the correct path of the DB on the server.

So, when a user logs in, he will select the path of the text file. As he

will do so with a folder chooser, he will not know that the folder contains a

text file & not the actual DB. Internally, I will read the database path from

that file in my application, & use that path to construct the connection

string...

I think this approach will shield the database from direct access on the network, using an explorer etc.

I already have Access password security, but still I dont want the database to be directly accessible on the network.
Can you elaborate a bit more on securing the Database on the server with NTFS permissions, in a way that my application can still access & manipulate it?|||

One thing, you cannot perform such move/copy with a SQL Server database as that will be in exclusive use of SQLengine.

Refer to KBA http://support.microsoft.com/kb/295234, http://support.microsoft.com/kb/307901 and link http://vb123.com/toolshed/links/map/opr.htm for more information.

|||There are two things I will mention again here...
1) My database is in Access
2) And, I am not moving the Database at run-time. The database will remain in its non-shared folder. And there will be a text file, that will act as a sort of pointer to the database location for my application, as I will read the DB path from the text file.|||

I suggest posting this question on a Microsoft Access or Microsoft Visual Basic forum instead of this one. This forum is used for posting questions related to Microsoft SQL Server security features, as you observed, and your question is Access specific.

Thanks
Laurentiu

|||

Hi,

You can do this well with NTFS permission with Read right for everyone in Group so that everyone can read the database files (this will not make user to able to copy files/folder too) , and give write/modify permission to specific users who need to insert/update/delete records in your access database. Refer www.windowsecurity.com/articles/Understanding-Windows-NTFS-Permissions.html to understand NTFS permission properly and with Advance you may restrict Take Ownership/traversing etc.

HTH

Hemantgiri S. Goswami

Database Restore Woes

I attempt to back up a database on one server and restore it on my local machine.

This is what the query text looks like:

restore database model from
Disk='c:\JQJ\mydump\model.bak'
with replace

I get this error:

The backup of the system database on device c:\JQJ\mydump\model.bak cannot be restored because it was created by a
different version of the server
(134217922) than this server (134218488).

The other server product version is: 8.00.194(RTM)
The server on my machine is 8.00.760(SP3)

1.How can I make this restore work? Seems to me I have the later version, so I should be able to handle it. Mine is a
10-user, and the other is enterprise.

2.Also I need to be able to force a different path and even database name: The source server where I create the backup file
may have a different location than I have on my machine, so what parameters can I give the command so that it will use the
location I specifiy? The backup file will have the location information relative to the source server, which I can not
always use. This means I will need to programatically extract that location information on my machine. (I can't hard code
it, since other people with different installation setups will use my program on their machines).

3.The database name on the source server may be ABC, but I may want to restore over XYZ. How can I force that? Perhaps it
would be the same parameter I need for 2.

Thanks,

JonJon Jacobs (JonJacobsAtcomcast.net) writes:
> I attempt to back up a database on one server and restore it on my local
> machine.
> This is what the query text looks like:
> restore database model from
> Disk='c:\JQJ\mydump\model.bak'
> with replace
> I get this error:
> The backup of the system database on device c:\JQJ\mydump\model.bak
> cannot be restored because it was created by a different version of the
> server (134217922) than this server (134218488).
> The other server product version is: 8.00.194(RTM)
> The server on my machine is 8.00.760(SP3)
> 1.How can I make this restore work? Seems to me I have the later
> version, so I should be able to handle it. Mine is a 10-user, and the
> other is enterprise.

I would expect that it is possible to restore a backup from SQL 2000
RTM on SQL 2000 SP3, but it is very clear that it is not possible in
this case. But then again, you are not restoring any database - you
are restoring model. That's a system database, so I assume it is special.

Question: are you restoring model, because you really need a copy of model
from the other box on your machine? Or did you just pick model as a test
case? In the former case, the easiest may be to script any user objects
you have in model, and run the script. I would not really expect that
you have any data in model. If you are just testing, try Northwind or
pubs instead.

> 2.Also I need to be able to force a different path and even database
> name: The source server where I create the backup file may have a
> different location than I have on my machine, so what parameters can I
> give the command so that it will use the location I specifiy?

You use MOVE:

RESTORE DATABASE mydatase FROM disk = 'C:\temp\mydump.bak'
WITH MOVE 'mydatabase_data' TO 'e:\mylocation\mydatabase.mdf',
MOVE 'mydatabase_log' TO 'f:\myloglocation\mydatabase.ldf',
REPLACE

It does not seem that you can use variable in place of the device
and paths, but you could build the BACKUP command dynamically and
execute with EXEC(@.sql).

> 3.The database name on the source server may be ABC, but I may want to
> restore over XYZ. How can I force that? Perhaps it would be the same
> parameter I need for 2.

RESTORE DATABASE XYZ ...

There is no law that says that the database you restore to must have
the same name as the source database.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>Question: are you restoring model, because you really need a copy of model
>from the other box on your machine? Or did you just pick model as a test
>case? In the former case, the easiest may be to script any user objects
>you have in model, and run the script. I would not really expect that
>you have any data in model. If you are just testing, try Northwind or
>pubs instead.

Yes, Model was just for testing. I will try another database.

>You use MOVE:
> RESTORE DATABASE mydatase FROM disk = 'C:\temp\mydump.bak'
> WITH MOVE 'mydatabase_data' TO 'e:\mylocation\mydatabase.mdf',
> MOVE 'mydatabase_log' TO 'f:\myloglocation\mydatabase.ldf',
> REPLACE

Excellent. I will put that to use.

>It does not seem that you can use variable in place of the device
>and paths, but you could build the BACKUP command dynamically and
>execute with EXEC(@.sql).

I will need to build the command dynamically as you say. But first I need to extract the destination path. How?

>RESTORE DATABASE XYZ ...
>There is no law that says that the database you restore to must have
>the same name as the source database.

I've been trying that to no avail. Well, maybe it will work after I get the other issues resolved, but so far, the error
messages complain about the original database name.

Thank you very, very much.

Jon|||Jon Jacobs (JonJacobsAtcomcast.net) writes:
> I will need to build the command dynamically as you say. But first I
> need to extract the destination path. How?

Well, I silently passed over that question, since I can't really tell
from a distance where you want to have your databases.

But assuming that you want the database in the default location of the
server.

This information is stored in the registry of the server, and you can
retrieve it with xp_regread. However, this is a undocumented and
unsupported function. (Note also that with SQL 2000 SP4, the function
will be restricted to access to SQL Server own values.)

A somewhat more roundabout way of doing it, is this:

CREATE DATABASE temp
SELECT @.path = filename FROM sysdatabases WHERE name = 'temp'
SELECT @.path = replace(@.path, '//', '/')
SELECT @.path = substring(@.path, 1, len(@.path) - len('temp.mdf'))
DROP DATABASE temp

>>RESTORE DATABASE XYZ ...
>>
>>There is no law that says that the database you restore to must have
>>the same name as the source database.
> I've been trying that to no avail. Well, maybe it will work after I get
> the other issues resolved, but so far, the error messages complain about
> the original database name.

You to use WITH REPLACE in this case. Since you already had this in
your example, I did not mention this.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

database restore error

Hello
when i try to restore the wss database in sql, i am getting following error.
any idea?
Warning: The join order has been enforced because a local join hint is used
Using STSADM
regards
Gopi
Hi
This looks like there are Hash//Merge join hints are being used as described
in
http://msdn.microsoft.com/library/de...fa-fz_4ox9.asp
The warning is for information.
John
"Gopi" <gopigopi@.hotmail.com> wrote in message
news:uNlysBnHGHA.2704@.TK2MSFTNGP15.phx.gbl...
> Hello
> when i try to restore the wss database in sql, i am getting following
> error. any idea?
>
> Warning: The join order has been enforced because a local join hint is
> used
> Using STSADM
>
> regards
> Gopi
>

database restore error

Hello
when i try to restore the wss database in sql, i am getting following error.
any idea?
Warning: The join order has been enforced because a local join hint is used
Using STSADM
regards
GopiHi
This looks like there are Hash//Merge join hints are being used as described
in
4ox9.asp" target="_blank">http://msdn.microsoft.com/library/d...br />
4ox9.asp
The warning is for information.
John
"Gopi" <gopigopi@.hotmail.com> wrote in message
news:uNlysBnHGHA.2704@.TK2MSFTNGP15.phx.gbl...
> Hello
> when i try to restore the wss database in sql, i am getting following
> error. any idea?
>
> Warning: The join order has been enforced because a local join hint is
> used
> Using STSADM
>
> regards
> Gopi
>

database replication

Hi, newbie here.
I have an sql express database on my local machine (xp sp2) with many tables populated with plenty of data.
How can i duplicate/replicate this database?
Seems like it should be a straight forward thing, but i guess not.

Thanks!
-GarySQL Express can only subscribe to a replication, it can't publish I'm afraid.

Database Problem

hello all

i am develope a travel website, in this site there is an 14 database tables

in my local machine all the work is done properly like (insert, update, delete, adminlogin , client login, etc...)

but in the internet or main server it not work properly

what is this prob.

plz help me

ashwani

what happens when you run it on the internet?

Does it give you an error Message or just not save changes to the database?

|||

it could be a connection string problem, the application cannot connect to the database, or ASP.NET account doesn't have the proper rights.