HP StorageWorks 8000 Storing Microsoft SQL Server Databases on the NAS 8000 - Page 9

share level security In Share level security, the NAS 8000 administrator, in conjunction with the SQL Server DBA can decide whether or not to password protect the CIFS share(s) that house the database(s). It is recommended that the shares that house the SQL database(s) be password protected and reserved exclusively for the database(s). Security on the share (or mapped drive) is handled when the connection to the NAS 8000 is initially made, not on every access to the NAS 8000. user level security In User/Domain level security, the NAS 8000 joins a Domain and the PDC is responsible for performing all security. Only valid users of the Domain, or of trusted domains will be allowed to access the storage on the NAS 8000. The owner of the SQL Server service must be set to a valid Domain user or SQL Server will be denied access to the NAS 8000 because of a security violation. Please refer to the section above on setting the service owner for SQL Server for more details. creating a database with the SQL Server Enterprise Manager utility The SQL Server Enterprise Manager utility can be used to create a database independent of the NAS 8000 security level (Share or User) once the SQL Server has been properly prepared as described above. Execute the Enterprise Manager and drill down through the navigation tree until the desired SQL Server is selected. With the desired SQL Server selected, execute the database creation wizard (right click on the SQL Server, select 'New', and then select 'Database'). Once the database creation wizard is executing, there will be entry fields for the path and filename of all database data files and transaction logs. It is strongly recommended that UNC names be used to explicitly name the exact path for the database data/log files. For more information, please refer to the above section on addressing the NAS 8000 from the SQL Server. Be sure that the trace flag (1807) has been set to on, otherwise SQL Server will refuse to utilize "non-local" storage. If needed, open the Query analyzer and execute 'DBCC TRACEON (1807)' before attempting to create the database, A better method is to set the trace flag in the startup parameters as described above. creating a database through SQL Scripts SQL Scripts can also be used to create a database independent of NAS 8000 the security level (Share or User). Use the path (either mapped drive or UNC Name) to access the NAS 8000 in the SQL statements. SQL scripts can be executed from the Query Analyzer inside of Enterprise Manager, or from ISQL / ISQLW. If the database creation fails because SQL Server rejects the "non-local" storage, make sure that trace flag 1807 has been turned on. This can be done by setting the startup parameters as described above, or by including the following SQL commands before the create database command: DBCC TRACEON (1807) GO Additionally, if the NAS 8000 is in User/Domain level security, be sure to set the SQL Service owner on the Windows Server for the SQL Server service. Failure to set the service owner to a valid member of the Domain (or of a trusted Domain) will cause the database creation to fail due to a security violation (illegal access). moving data files from already existing databases After the NAS 8000 has been installed and SQL Server has been setup to utilize the storage of the NAS 8000, it may be necessary (or desirable) to move already existing database data/log files onto the NAS 8000. In order to move a database (or any of the data/log files) the database must be "detached" from SQL Server, that is taken offline, then the data/log files can be moved/renamed, and then the database is re-attached to SQL Server. The database can be detached through the use of the Query Analyzer, or through ISQL / ISQLW. The following is an example of detaching a database, moving its data and log files, and then reattaching the database, all through a "command window". The SQL Server system databases (master, tempdb, msdb and model) cannot be detached. Therefore it is not possible/supported to move these databases onto the NAS 8000. C:\> ISQL -Ssql_server -Uaccount -Ppassword -Q"sp_detach_db db_name" C:\> move C:\somepath\somefile.mdf \\NAS8000\somepath\somefile.mdf C:\> move C:\somepath\somefile.ldf \\NAS8000\somepath\somefile.ldf : C:\> ISQL -Ssql_server -Uaccount -Ppassword -Q"sp_attach_db @db_name = 'db_name' 9