May 28, 2013 at 11:16 am
I'm struggling to deploy MyDevDatabase to my local development environment on my workstation.
The script to create MyDevDatabase assumes SQL Server can write to a G drive. This works well enough in production because all of our production servers have a partition for the G drive.
I faked a G drive by the following steps:
* create the directory D:\MyDevDatabase
* Share the directory as \\localhost\MyDevDatabase
* Map the network location \\localhost\MyDevDatabase to drive G.
* Grant full control to the G drive to the SQL Server database engine following the MSDN instructions.
The first step of the script creates the directory structure for database files on the G drive. A typical statement looks like this:
EXECUTE master.sys.xp_create_subdir N'G:\MSSQL\SQLData\MyDevDatabase';
On my workstation this fails with an error message like this:
Msg 22048, Level 16, State 1, Line 0
xp_create_subdir() returned error 3, 'The system cannot find the path specified.'
If I change the command by replacing the mapped drive with the shared directory like this:
EXECUTE master.sys.xp_create_subdir N'D:\MyDevDatabase\MSSQL\SQLData\MyDevDatabase';
Then the command completes successfully, and the directory structure is created.
I'd like to make this work without modifying the deployment scripts.
Is there a better way to fake a G drive for SQL Server?
Do I actually have to create a partition?
May 28, 2013 at 2:03 pm
Use a named share and a UNC instead.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 29, 2013 at 4:02 am
The second step of faking a G drive was to create a named share. Its UNC path is \\localhost\MyDevDatabase.
When I pass the UNC path to xp_create_subdir like this:
EXECUTE master.sys.xp_create_subdir N'\\localhost\MyDevDatabase\MSSQL\SQLData\MyDevDatabase';
The command succeeds.
The problem with this approach is that it means I would have to change all the deployment scripts to use a UNC path instead of a G drive. I would rather make it work with the G drive if possible.
The problem with my solution is that I mapped the drive using the 'Map Network Drive...' option in Windows Explorer. This maps the drive only for my desktop user (CORP\iain) and not my SQL Server user (NT SERVICE\MSSQLSERVER).
You can list the drives at the command prompt by using this command:
wmic logicaldisk get name
In my user context it produces output like this:
Name
C:
D:
G:
P:
Y:
Z:
The G drive is listed among all the other drives I can access in my user context.
When I execute the same command in SQL Server's context using the xp_cmdshell procedure:
EXECUTE master.sys.xp_cmdshell N'wmic logicaldisk get name';
I get a result set like this:
output
--------
Name
C:
D:
NULL
This output shows that there is no G drive in SQL Server's context, which is why the original xp_create_subdir command failed.
I used the command-line equivalent of the 'Map Network Drive...' option to fix it for SQL Server.
You can map the UNC path \\localhost\MyDevDatabase to the G drive by using the 'net use' command like this:
net use G: \\localhost\MyDevDatabase /PERSISTENT:YES
When I execute the command in SQL Server using the xp_cmdshell procedure:
EXECUTE master.sys.xp_cmdshell N'net use G: \\localhost\MyDevDatrabase /PERSISTENT:YES';
It produces a result set like this:
output
-----------------------------------------
The command completed successfully.
NULL
NULL
When I execute the command to list the logical drives again in SQL Server's context, the output is now:
output
-----------
Name
C:
D:
G:
NULL
Now the original xp_create_subdir command completes successfully.
May 29, 2013 at 7:32 am
The final step now would be to bounce the server to make sure that SQL Server actually does see the persisted mapping after a reboot. I'm pretty sure it will but you don't actually know until you try it.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 29, 2013 at 8:22 am
This may persist, or it may not. As you've done it, I'd think it should, though as Jeff noted, you need to restart the SQL Server services and have the service account log in.
The reason this doesn't work is that the service account for the database service is a user like your account. That is the account that must have the mapping in its profile.
May 29, 2013 at 8:36 am
I'm glad you asked me to try that, Jeff. It turns out that we were too optimistic about the persistence.
I restarted my workstation, started the SQL Server service, and connected to the server.
I executed the wmic command using xp_cmdshell:
EXECUTE master.sys.xp_cmdshell N'wmic logicaldisk get name';
The output looks like this:
output
----------
Name
C:
D:
NULL
The problem has regressed. The only logical drives visible to SQL Server are C: and D:.
I executed the 'net use' command in SQL Server's context without parameters to list the drive mappings:
EXECUTE master.sys.xp_cmdshell N'net use';
The output looks like this:
output
-------------
New connections will be remembered.
NULL
NULL
Status Local Remote Network
NULL
-------------------------------------------------------------------------------
Unavailable G: \\localhost\MyDevDatabase
Microsoft Windows Network
The command completed successfully.
NULL
NULL
It looks like the operating system has persisted the mapping, but its status is 'Unavailable'.
The output of 'net use' in my user context looks like this:
New connections will be remembered.
Status Local Remote Network
-------------------------------------------------------------------------------
OK G: \\localhost\MyDevDatabase
Microsoft Windows Network
OK P: \\fileshare\project Microsoft Windows Network
OK Y: \\123.456.789.012\c Microsoft Windows Network
OK Z: \\usershare\iain$ Microsoft Windows Network
The command completed successfully.
The same G drive mapping exists and is 'OK'. All the other mappings I use are 'OK' as well.
When I execute again the mapping command in SQL Server's context:
EXECUTE master.sys.xp_cmdshell N'net use G: \\localhost\BrowseQuoteProcessor /PERSISTENT:YES';
The command reports successful completion as before.
The output of the 'net use' command changes:
output
--------
New connections will be remembered.
NULL
NULL
Status Local Remote Network
NULL
-------------------------------------------------------------------------------
OK G: \\localhost\BrowseQuoteProcessor
Microsoft Windows Network
The command completed successfully.
NULL
The status of the G drive is now 'OK'.
The output of the wmic command in SQL Server's context now looks like this:
output
----------
Name
C:
D:
G:
NULL
The G drive is listed in the output. Once more procedures such as xp_create_subdir can reference the G drive.
I don't have to restart the workstation to reproduce this behavior; restarting the SQL Server service (MSSQLSERVER) is enough. After restarting the SQL Server service, the output of 'net use' in SQL Server's context shows that the G drive is 'Unavailable'.
This could be a Windows issue as much as a SQL Server issue. Is it a quirk of 'net use', or some part of SQL Server itself? Any ideas?
May 29, 2013 at 8:53 am
Maybe put the NET USE in a startup proc? Execute it on service start so that the mapping is created?
May 31, 2013 at 3:47 am
I solved the problem thanks to Steve's suggestion. It's a hack, but it works well enough that I can run my deployment scripts unmodified.
First I created a stored procedure to encapsulate the action of mapping the G drive to a UNC share.
The batch to create the procedure looks like this:
USE master;
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.map_g_drive
AS
BEGIN
EXECUTE master.sys.xp_cmdshell N'net use G: \\localhost\MyDevDatabase /PERSISTENT:YES', 'no_output';
END;
The body of the procedure has one difference from the original mapping command. The call to xp_cmdshell an extra 'no_output' parameter to supress the result set. Later I'll explain why.
To test that the procedure works, first I restart the server.
After the server starts I execute the 'net use' command in SQL Server's context to return the status of mapped drives:
EXECUTE master.sys.xp_cmdshell 'net use';
The result set looks like this:
output
---------
New connections will be remembered.
NULL
NULL
Status Local Remote Network
NULL
-------------------------------------------------------------------------------
Unavailable G: \\localhost\MyDevDatabase
Microsoft Windows Network
The command completed successfully.
NULL
NULL
The result shows that the G drive status is 'Unavailable'.
Now I execute the new procedure map_g_drive:
EXECUTE master.dbo.map_g_drive;
The procedure produces no output. My client reports that the procedure executed successfully.
Now when I execute the 'net use' command, I see a result set like this:
output
-----------
New connections will be remembered.
NULL
NULL
Status Local Remote Network
NULL
-------------------------------------------------------------------------------
OK G: \\localhost\MyDevDatabase
Microsoft Windows Network
The command completed successfully.
NULL
NULL
The result shows that the G drive status is 'OK'.
You can mark a procedure for automatic execution when SQL Server starts.
According to MSDN, an 'autoproc' must
* exist in the master database
* have no parameters
* return no result sets
The map_g_drive procedure meets these constraints. The 'no_output' parameter of xp_cmdshell was useful to ensure the procedure returns no result sets.
To mark the procedure for automatic execution, I used the sp_procoption procedure like this:
EXECUTE master.sys.sp_procoption
@ProcName = N'dbo.map_g_drive',
@OptionName = 'startup',
@OptionValue = 'true';
The procedure produces no output. To check its effect, I queried the is_auto_executed column of the sys.procedures view:
SELECT name, is_auto_executed
FROM master.sys.procedures
WHERE name = N'map_g_drive';
The result set shows that the procedure is marked for auto-execution:
name is_auto_executed
-------------- ----------------
map_g_drive 1
To check that the procedure is executed at startup, I have to restart the server one more time and inspect the output of 'net use' before executing anything else.
After I restart the server, I execute the 'net use' command:
EXECUTE master.sys.xp_cmdshell 'net use';
The result set looks like this:
output
---------
New connections will be remembered.
NULL
NULL
Status Local Remote Network
NULL
-------------------------------------------------------------------------------
OK G: \\localhost\MyDevDatabase
Microsoft Windows Network
The command completed successfully.
NULL
NULL
The status of the G drive is 'OK' at startup.
Now the xp_create_subdir command executes successfully when it references the G drive.
Thanks, guys!
May 31, 2013 at 4:22 am
If you create a database and put its files on the G drive, the database will be in 'Recovery Pending' after a server restart.
It looks like SQL Server tries to recover the database before the autoprocs are executed.
You can create a database with the files on the G drive like this:
CREATE DATABASE MyDevDatabase
ON PRIMARY
(NAME = N'MyDevDatabase_Data', FILENAME = N'G:\MyDevDatabase_Data.mdf')
LOG ON
(NAME = N'MyDevDatabase_Log', FILENAME = N'G:\MyDevDatabase_Log.ldf');
The command produces no output.
To check the initial state of the new database, you can query the state_desc column of the sys.databases table like this:
SELECT name, state_desc
FROM sys.databases
WHERE name = N'MyDevDatabase';
The result set looks like this:
name state_desc
-------------- ----------
MyDevDatabase ONLINE
The database exists and is initially in an online state.
Normally when you restart a server, the database would be recovered to an online state.
After a restart, the state_desc column for MyDevDatabase has changed:
name state_desc
-------------- ----------
MyDevDatabase RECOVERY_PENDING
The database is in 'recovery pending' mode.
The server was unable to automatically recover the database on the G drive.
To bring the database back online, you can use the ALTER DATABASE statement like this:
ALTER DATABASE MyDevDatabase SET ONLINE;
The command produces no output.
The state_desc column for MyDevDatabase has changed again:
name state_desc
-------------- ----------
MyDevDatabase ONLINE
The database is online once more.
Would it be safe to add the ALTER DATABASE statement in the autoproc?
The creeping scope of this hack suggests that Jeff's original advice to use UNC paths instead of drive names might be a better way to reference a file system location.
June 3, 2013 at 8:47 am
I would use UNC paths. I know they're supported.
http://www.brentozar.com/archive/2012/01/sql-server-databases-on-network-shares-nas/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply