Maintenance Plans with “Backup Database Task” fails when the “Create a sub-directory for each database” option is checked, with error similar to the below,
“Cannot open backup device 'D... The package execution fa... The step failed.,00:00:01,0,0,,,,0”
Detailed Error Message
==================
Executed as user: MachineName\SYSTEM. ...9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 7:15:03 AM Progress: 2008-07-24 07:15:04.11 Source: {134957B2-5C5F-4D4F-BDB7-ECAC9C3D8E20} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2008-07-24 07:15:04.69 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Program F".: 100% complete End Progress Error: 2008-07-24 07:15:04.71 Code: 0xC002F210 Source: Back Up Database Task Execute SQL Task Description: Executing the query "BACKUP DATABASE [Mstest ] TO DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Mstest \Mstest _backup_200807240715.bak' WITH NOFORMAT, NOINIT, NAME = N'MStest _backup_20080724071504', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "Cannot open backup device 'D... The package execution fa... The step failed.
Analysis
=======
When the “Create a sub-directory for each database” option is selected, the Maintenance task executes the xp_create_subdir procedure to create a directory with the Database Name. If you carefully look at the Detailed Error message above, you will see that there is a space character in the Database name.
“D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\MStest \Mstest _backup_200807240715.bak”
If you try to run the Backup command extracted from the above error in the Query windows it gives a more informative error ,
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Mstest \Mstest _backup_200807240715.bak'. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Cause:
======
The Operating System does not allow you to create a folder with trailing spaces. When xp_create_subdir creates the folder with the space, the OS creates the folder but without the trailing space.
For example you can try this command EXECUTE master.dbo.xp_create_subdir N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Mstest \'
Now when the backup command fires it fails to read the directory, since there is no directory with a space created.
Resolution
=========
Follow the below method to remove the trailing space from the Database Name
1.
Right click and rename the database to any name. (This is because Management Studio will not allow you to remove the trailing space, since it thinks there is no change made to the name and that a database already exist with the same name
2.
Now rename the database to the original name without the space.
3.
Reconfigure your Maintenance Plan
Ads
28 April 2011
27 April 2011
Failed to notify ''operator name'' via email?...
First Check the value for Database mail profile is NUll
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile'
If So
select 'exec master.dbo.xp_instance_regwrite N''HKEY_LOCAL_MACHINE'', N''SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'', N''DatabaseMailProfile'', N''REG_SZ'', N''' + name + ''''
from msdb.dbo.sysmail_profile
where profile_id in (Select profile_id from msdb.dbo.sysmail_principalprofile )
Copy the output and Execute, it will be as below
exec master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'DBA'
Now Restart the SQL Agent. mail will work now.
------------------------------ OR --------------------------------
Verify below Steps are configured properly or not
1. Enable database mail, create a new profile and mail account
2. Right click SQL Agent>Properties>Alerts System>Enable Mail Profile
3. Expand SQL Agent>Operators>Create New Operator
Configuring above steps will work make DB mail to work properly.
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile'
If So
select 'exec master.dbo.xp_instance_regwrite N''HKEY_LOCAL_MACHINE'', N''SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'', N''DatabaseMailProfile'', N''REG_SZ'', N''' + name + ''''
from msdb.dbo.sysmail_profile
where profile_id in (Select profile_id from msdb.dbo.sysmail_principalprofile )
Copy the output and Execute, it will be as below
exec master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'DBA'
Now Restart the SQL Agent. mail will work now.
------------------------------ OR --------------------------------
Verify below Steps are configured properly or not
1. Enable database mail, create a new profile and mail account
2. Right click SQL Agent>Properties>Alerts System>Enable Mail Profile
3. Expand SQL Agent>Operators>Create New Operator
Configuring above steps will work make DB mail to work properly.
26 April 2011
Connecting to a Remote Integration Services Server
To connect to Integration Services on a Remote Server
1.
Open SQL Server Management Studio.
2.
Select File, Connect Object Explorer to display the Connect to Server dialog box.
3.
Select Integration Services in the Server type list.
4.
Type the name of a SQL Server Integration Services server in the Server name text box.
Note
The Integration Services service is not instance-specific. You connect to the service by using the name of the computer on which the Integration Services service is running.
5.
Click Connect.
Eliminating the "Access Is Denied" Error
--------------------------------------------------------------------------------
When a user without sufficient rights attempts to connect to an instance of Integration Services on a remote server, the server responds with an "Access is denied" error message. You can avoid this error message by ensuring that users have the required DCOM permissions.
To configure rights for remote users on Windows Server 2003 or Windows XP
1.
If the user is not a member of the local Administrators group, add the user to the Distributed COM Users group. You can do this in the Computer Management MMC snap-in accessed from the Administrative Tools menu.
2.
Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in.
3.
Expand the Component Services node in the left pane of the console. Expand the Computers node, expand My Computer, and then click the DCOM Config node.
4.
Select the DCOM Config node, and then select MsDtsServer in the list of applications that can be configured.
5.
Right-click on MsDtsServer and select Properties.
6.
In the MsDtsServer Properties dialog box, select the Security tab.
7.
Under Launch and Activation Permissions, select Customize, then click Edit to open the Launch Permission dialog box.
8.
In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service.
9.
Click OK to close the dialog box.
10.
Under Access Permissions, repeat steps 7 and 8 to assign the appropriate permissions to the appropriate users and groups.
11.
Close the MMC snap-in.
12.
Restart the Integration Services service.
To configure rights for remote users on Windows 2000 with the latest service packs
1.
Run dcomcnfg.exe at the command prompt.
2.
On the Applications page of the Distributed COM Configuration Properties dialog box, select MSDTSServer and then click Properties.
3.
Select the Security page.
4.
Use the two separate dialog boxes to configure Access Permissions and Launch Permissions. You cannot distinguish between remote and local access - Access permissions include local and remote access, and Launch permissions include local and remote launch.
5.
Close the dialog boxes and dcomcnfg.exe.
6.
Restart the Integration Services service.
Connecting by using a Local Account
--------------------------------------------------------------------------------
If you are working in a local Windows account on a client computer, you can connect to the Integration Services service on a remote computer only if a local account that has the same name and password and the appropriate rights exists on the remote computer.
Delegation Is Not Supported
--------------------------------------------------------------------------------
SQL Server Integration Services does not support the delegation of credentials, sometimes referred to as a double hop. In this scenario, you are working on a client computer, Integration Services is installed on a second computer, and SQL Server is installed on a third computer. Although SQL Server Management Studio successfully passes your credentials from the client computer to the second computer on which Integration Services is running, Integration Services cannot delegate your credentials from the second computer to the third computer on which SQL Server is running.
Thanks to :- http://msdn.microsoft.com/en-us/library/aa337083.aspx
1.
Open SQL Server Management Studio.
2.
Select File, Connect Object Explorer to display the Connect to Server dialog box.
3.
Select Integration Services in the Server type list.
4.
Type the name of a SQL Server Integration Services server in the Server name text box.
Note
The Integration Services service is not instance-specific. You connect to the service by using the name of the computer on which the Integration Services service is running.
5.
Click Connect.
Eliminating the "Access Is Denied" Error
--------------------------------------------------------------------------------
When a user without sufficient rights attempts to connect to an instance of Integration Services on a remote server, the server responds with an "Access is denied" error message. You can avoid this error message by ensuring that users have the required DCOM permissions.
To configure rights for remote users on Windows Server 2003 or Windows XP
1.
If the user is not a member of the local Administrators group, add the user to the Distributed COM Users group. You can do this in the Computer Management MMC snap-in accessed from the Administrative Tools menu.
2.
Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in.
3.
Expand the Component Services node in the left pane of the console. Expand the Computers node, expand My Computer, and then click the DCOM Config node.
4.
Select the DCOM Config node, and then select MsDtsServer in the list of applications that can be configured.
5.
Right-click on MsDtsServer and select Properties.
6.
In the MsDtsServer Properties dialog box, select the Security tab.
7.
Under Launch and Activation Permissions, select Customize, then click Edit to open the Launch Permission dialog box.
8.
In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service.
9.
Click OK to close the dialog box.
10.
Under Access Permissions, repeat steps 7 and 8 to assign the appropriate permissions to the appropriate users and groups.
11.
Close the MMC snap-in.
12.
Restart the Integration Services service.
To configure rights for remote users on Windows 2000 with the latest service packs
1.
Run dcomcnfg.exe at the command prompt.
2.
On the Applications page of the Distributed COM Configuration Properties dialog box, select MSDTSServer and then click Properties.
3.
Select the Security page.
4.
Use the two separate dialog boxes to configure Access Permissions and Launch Permissions. You cannot distinguish between remote and local access - Access permissions include local and remote access, and Launch permissions include local and remote launch.
5.
Close the dialog boxes and dcomcnfg.exe.
6.
Restart the Integration Services service.
Connecting by using a Local Account
--------------------------------------------------------------------------------
If you are working in a local Windows account on a client computer, you can connect to the Integration Services service on a remote computer only if a local account that has the same name and password and the appropriate rights exists on the remote computer.
Delegation Is Not Supported
--------------------------------------------------------------------------------
SQL Server Integration Services does not support the delegation of credentials, sometimes referred to as a double hop. In this scenario, you are working on a client computer, Integration Services is installed on a second computer, and SQL Server is installed on a third computer. Although SQL Server Management Studio successfully passes your credentials from the client computer to the second computer on which Integration Services is running, Integration Services cannot delegate your credentials from the second computer to the third computer on which SQL Server is running.
Thanks to :- http://msdn.microsoft.com/en-us/library/aa337083.aspx
20 April 2011
Backup Log Database with truncate _only
It became deprecated Command and removed in 2008. You can achieve the same result by sending the file to the NUL blackhole
BACKUP LOG [DBNAME] TO DISK='NUL'
GO
DBCC SHRINKFILE("LOGFILE",0)
OR
alter database DBNAME set recovery simple
go
alter database DBNAME set recovery full
go
sp_helpdb 'DBNAME '
GO
use DBNAME
go
dbcc shrinkfile(LOGFILE,0)
BACKUP LOG [DBNAME] TO DISK='NUL'
GO
DBCC SHRINKFILE("LOGFILE",0)
OR
alter database DBNAME set recovery simple
go
alter database DBNAME set recovery full
go
sp_helpdb 'DBNAME '
GO
use DBNAME
go
dbcc shrinkfile(LOGFILE,0)
Cluster node 'SQLSVRHOST00' was removed from the active failover cluster membership. The Cluster service on this node may have stopped.
= Event ID 1135 — Cluster Service Startup =
===========================================
http://technet.microsoft.com/en-us/library/dd353973(WS.10).aspx
Event Details
Product: Windows Operating System
ID: 1135
Source: Microsoft-Windows-FailoverClustering
Version: 6.1
Symbolic Name: EVENT_NODE_DOWN
Message: Cluster node '%1' was removed from the active failover cluster membership. The Cluster service on this node may have stopped. This could also be due to the node having lost communication with other active nodes in the failover cluster. Run the Validate a Configuration wizard to check your network configuration. If the condition persists, check for hardware or software errors related to the network adapters on this node. Also check for failures in any other network components to which the node is connected such as hubs, switches, or bridges.
Resolve
Check network hardware and configuration
If you do not currently have Event Viewer open, see "Opening Event Viewer and viewing events related to failover clustering." After reviewing event messages, choose actions that apply to your situation:
Run the Validate a Configuration Wizard, selecting only the network and inventory tests. For more information, see "Using the Validate a Configuration Wizard to review the network configuration."
Check the system event log for hardware or software errors related to the network adapters on this node.
Check the network adapter, cables, and network configuration for the networks that connect the nodes.
Check hubs, switches, or bridges in the networks that connect the nodes.
To perform the following procedures, you must be a member of the local Administrators group on each clustered server, and the account you use must be a domain account, or you must have been delegated the equivalent authority.
Using the Validate a Configuration Wizard to review the network configuration
To use the Validate a Configuration Wizard to review the network configuration:
1.To open the failover cluster snap-in, click Start, click Administrative Tools, and then click Failover Cluster Management. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.
2.In the Failover Cluster Management snap-in, in the console tree, make sure Failover Cluster Management is selected. Then under Management, click Validate a Configuration.
3.Follow the instructions in the wizard to specify the cluster you want to test.
4.On the Testing Options page, select Run only tests I select.
5.On the Test Selection page, clear all check boxes except those for the Network tests.
6.Follow the instructions in the wizard to run the tests.
7.On the Summary page, click View Report.
Opening Event Viewer and viewing events related to failover clustering
To open Event Viewer and view events related to failover clustering:
1.If Server Manager is not already open, click Start, click Administrative Tools, and then click Server Manager. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.
2.In the console tree, expand Diagnostics, expand Event Viewer, expand Windows Logs, and then click System.
3.To filter the events so that only events with a Source of FailoverClustering are shown, in the Actions pane, click Filter Current Log. On the Filter tab, in the Event sources box, select FailoverClustering. Select other options as appropriate, and then click OK.
4.To sort the displayed events by date and time, in the center pane, click the Date and Time column heading.
Verify
To perform this procedure, you must be a member of the local Administrators group on each clustered server, and the account you use must be a domain account, or you must have been delegated the equivalent authority.
Verifying that the Cluster service is started on all the nodes in a failover cluster
To verify that the Cluster service is started on all the nodes in a failover cluster:
1.To open the failover cluster snap-in, click Start, click Administrative Tools, and then click Failover Cluster Management. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.
2.In the Failover Cluster Management snap-in, if the cluster you want to manage is not displayed, in the console tree, right-click Failover Cluster Management, click Manage a Cluster, and then select or specify the cluster that you want.
3.If the console tree is collapsed, expand the tree under the cluster you want to manage, and then click Nodes.
4.View the status for each node. If a node is Up, the Cluster service is started on that node.
Another way to check whether the Cluster service is started is to run a command on a node in the cluster.
Using a command to check whether the Cluster service is started on a node
To use a command to check whether the Cluster service is started on a node:
1.On the node that you are checking, click Start, point to All Programs, click Accessories, right-click Command Prompt, and then click Run as administrator.
2.Type:
CLUSTER NODE /STATUS
If the node status is Up, the Cluster service is started on that node.
===========================================
http://technet.microsoft.com/en-us/library/dd353973(WS.10).aspx
Event Details
Product: Windows Operating System
ID: 1135
Source: Microsoft-Windows-FailoverClustering
Version: 6.1
Symbolic Name: EVENT_NODE_DOWN
Message: Cluster node '%1' was removed from the active failover cluster membership. The Cluster service on this node may have stopped. This could also be due to the node having lost communication with other active nodes in the failover cluster. Run the Validate a Configuration wizard to check your network configuration. If the condition persists, check for hardware or software errors related to the network adapters on this node. Also check for failures in any other network components to which the node is connected such as hubs, switches, or bridges.
Resolve
Check network hardware and configuration
If you do not currently have Event Viewer open, see "Opening Event Viewer and viewing events related to failover clustering." After reviewing event messages, choose actions that apply to your situation:
Run the Validate a Configuration Wizard, selecting only the network and inventory tests. For more information, see "Using the Validate a Configuration Wizard to review the network configuration."
Check the system event log for hardware or software errors related to the network adapters on this node.
Check the network adapter, cables, and network configuration for the networks that connect the nodes.
Check hubs, switches, or bridges in the networks that connect the nodes.
To perform the following procedures, you must be a member of the local Administrators group on each clustered server, and the account you use must be a domain account, or you must have been delegated the equivalent authority.
Using the Validate a Configuration Wizard to review the network configuration
To use the Validate a Configuration Wizard to review the network configuration:
1.To open the failover cluster snap-in, click Start, click Administrative Tools, and then click Failover Cluster Management. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.
2.In the Failover Cluster Management snap-in, in the console tree, make sure Failover Cluster Management is selected. Then under Management, click Validate a Configuration.
3.Follow the instructions in the wizard to specify the cluster you want to test.
4.On the Testing Options page, select Run only tests I select.
5.On the Test Selection page, clear all check boxes except those for the Network tests.
6.Follow the instructions in the wizard to run the tests.
7.On the Summary page, click View Report.
Opening Event Viewer and viewing events related to failover clustering
To open Event Viewer and view events related to failover clustering:
1.If Server Manager is not already open, click Start, click Administrative Tools, and then click Server Manager. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.
2.In the console tree, expand Diagnostics, expand Event Viewer, expand Windows Logs, and then click System.
3.To filter the events so that only events with a Source of FailoverClustering are shown, in the Actions pane, click Filter Current Log. On the Filter tab, in the Event sources box, select FailoverClustering. Select other options as appropriate, and then click OK.
4.To sort the displayed events by date and time, in the center pane, click the Date and Time column heading.
Verify
To perform this procedure, you must be a member of the local Administrators group on each clustered server, and the account you use must be a domain account, or you must have been delegated the equivalent authority.
Verifying that the Cluster service is started on all the nodes in a failover cluster
To verify that the Cluster service is started on all the nodes in a failover cluster:
1.To open the failover cluster snap-in, click Start, click Administrative Tools, and then click Failover Cluster Management. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.
2.In the Failover Cluster Management snap-in, if the cluster you want to manage is not displayed, in the console tree, right-click Failover Cluster Management, click Manage a Cluster, and then select or specify the cluster that you want.
3.If the console tree is collapsed, expand the tree under the cluster you want to manage, and then click Nodes.
4.View the status for each node. If a node is Up, the Cluster service is started on that node.
Another way to check whether the Cluster service is started is to run a command on a node in the cluster.
Using a command to check whether the Cluster service is started on a node
To use a command to check whether the Cluster service is started on a node:
1.On the node that you are checking, click Start, point to All Programs, click Accessories, right-click Command Prompt, and then click Run as administrator.
2.Type:
CLUSTER NODE /STATUS
If the node status is Up, the Cluster service is started on that node.
19 April 2011
Tail-log Backup without the Primary Data File
While I was reading a post, written by Vinod Kumar (blog | twitter) about Files and FileGroups with SQL Server, I found an interesting quiz question: “Do we need the primary data file available to backup your transaction log after a crash?“
The answer to the above question is No, if the transaction-log file is not damaged, then we can initiate a log backup to backup the data till the time of the crash, since last log backup. To support my answer, I have created the following test script. In the first part of the script, I have created a test database named “TestDB” and change its recovery model to “Full”. The recovery model of the model database on my SQL 2008 R2 Express instance is set as Simple, so I manually change the recovery model of TestDB to Full.
use [master]
go
if exists (select 1 from sys.databases where name = 'TestDB')
drop database TestDB;
go
-- Create database TestDB
use [master]
go
create database [TestDB] ON Primary
(name = 'TestDB_Data', filename = 'C:\projects\demodb\TestDB_Data.MDF',
size = 10MB, filegrowth = 10MB)
LOG ON
(name = 'TestDB_Log', filename = 'C:\projects\demodb\TestDB_Log.MDF',
size = 10MB, filegrowth = 10MB);
go
-- Changing the Recovery Model to full
use [master]
alter database [TestDB] set recovery full;
go
Since I have created the database, its time to initiate a full backup of the database. Next I will add a demo tables with two columns and inserted some data into the table. After verifying the data in the table, initiate the first transaction-log backup.
-- Initiate a Full backup of the database
use [master]
Backup database [TestDB] to disk = 'C:\projects\TestDB.bak'
go
-- Create a table in the database
use [TestDB]
create table dbo.demotable1 (
demoid int identity(1,1),
demodate datetime default getdate()
);
go
-- Insert base data
use [TestDB]
insert into dbo.demotable1 default values;
go 10
-- verify base data
select * from TestDB.dbo.demotable1;
go
-- Initiate the 1st T-log backup
use [master]
backup log [TestDB] to disk = 'c:\projects\testDB1.trn';
go
Since the first log backup is completed, lets add some additional data into the table in the TestDB database. After inserting the additional row into the table, I will verify the same.
-- Insert some additional data
use [TestDB]
insert into dbo.demotable1 default values;
go 15
-- verify all the data in the database
select COUNT(*) from TestDB.dbo.demotable1;
go
/*(total = 25, till log backup it was 10)*/
Now I am going to crash the TestDB database. I am going to shutdown my SQL Server Express instance and manually delete the data file of the TestDB database. Once the file is deleted, restart the SQL Server instance, and try to access data from the database. The following error will occur: “Database ‘TestDB’ cannot be opened due to inaccessible files or insufficient memory or disk space“
-- try to access the table, will get error as data file is missing
use [TestDB]
select COUNT(*) from TestDB.dbo.demotable1;
go
As I am unable to access the database, I need to backup the transaction log to retrieve the data, which was inserted into the database after the last transaction-log backup, to minimize the data loss. The syntax to initiate the tail-log backup is as follows:
-- Initiate the tail log backup
use [master]
BACKUP log [testDB] to disk = 'C:\Projects\TestDB_Tail_Log.trn'
with No_Truncate, Norecovery;
go
/* This complete the tail log backup without the primary data file */
If the transaction-log file of the database is not damaged, then the above command will create the log backup (also known as tail-log backup). This completes the answer to the given question; however, a backup is never successful, if it can’t be restored. So I am going to test, whether I can restore the database to the same point, when the crash occurred, using the available backups. The next part of the script will restore the database and verify the data in the database after the restoration is completed.
use [master]
Restore database [TestDB]
from disk = 'C:\projects\TestDB.bak'
with replace, norecovery;
go
Restore Log [TestDB]
from disk = 'c:\projects\testDB1.trn'
with replace, norecovery;
go
Restore Log [TestDB]
from disk = 'c:\projects\TestDB_Tail_Log.trn'
with recovery;
go
-- Verify the data
Select count(*) from TestDB.dbo.demotable1;
go
The database is recovered successfully using the available backups and the complete data is also recovered. If we place the data file and the t-log files in different drives, in case, if the drive containing the data file is damaged, then we can retrive the data if we are able to backup the t-log. However, if the drive containing the t-log files is crashed, then we are going to face some data loss.
The complete script to reproduce the above scenario is mentioned below:
/* Create Tail-log backup after a crash, if the primary data file is missing
------------------------------------------------------------------------------
@skganguly
*/
use [master]
go
if exists (select 1 from sys.databases where name = 'TestDB')
drop database TestDB;
go
-- Create database TestDB
use [master]
go
create database [TestDB] ON Primary
(name = 'TestDB_Data', filename = 'C:\projects\demodb\TestDB_Data.MDF',
size = 10MB, filegrowth = 10MB)
LOG ON
(name = 'TestDB_Log', filename = 'C:\projects\demodb\TestDB_Log.MDF',
size = 10MB, filegrowth = 10MB);
go
-- Changing the Recovery Model to full
use [master]
alter database [TestDB] set recovery full;
go
-- Initiate a Full backup of the database
use [master]
Backup database [TestDB] to disk = 'C:\projects\TestDB.bak'
go
-- Create a table in the database
use [TestDB]
create table dbo.demotable1 (
demoid int identity(1,1),
demodate datetime default getdate()
);
go
-- Insert base data
use [TestDB]
insert into dbo.demotable1 default values;
go 10
-- verify base data
select * from TestDB.dbo.demotable1;
go
-- Initiate the 1st T-log backup
use [master]
backup log [TestDB] to disk = 'c:\projects\testDB1.trn';
go
-- Insert some additional data
use [TestDB]
insert into dbo.demotable1 default values;
go 15
-- verify all the data in the database
select COUNT(*) from TestDB.dbo.demotable1;
go
/*(total = 25, till log backup it was 10)*/
-- initiate the crash, for this demo, I've shutdown the server and
-- delete the data file manually, to verify whether the tail-log backup is
-- possible or not if the data file is not available
-- try to access the table
use [TestDB]
select COUNT(*) from TestDB.dbo.demotable1;
go
-- Database 'TestDB' cannot be opened due to inaccessible files
-- or insufficient memory or disk space
/* Initiate the tail log backup*/
use [master]
BACKUP log [testDB] to disk = 'C:\Projects\TestDB_Tail_Log.trn'
with No_Truncate, Norecovery;
go
/* This complete the tail log backup without the primary data file */
/* The script will not be successful, if we can't restore the tail log.
Thus, we are going to restore the database and verify the data before cleanup */
use [master]
Restore database [TestDB]
from disk = 'C:\projects\TestDB.bak'
with replace, norecovery;
go
Restore Log [TestDB]
from disk = 'c:\projects\testDB1.trn'
with replace, norecovery;
go
Restore Log [TestDB]
from disk = 'c:\projects\TestDB_Tail_Log.trn'
with recovery;
go
-- Verify the data
Select count(*) from TestDB.dbo.demotable1;
go
-- clean up the database
use [master]
drop database [TestDB]
go
=======================================
Thanks To :- http://sudeeptaganguly.wordpress.com/2011/04/15/taillogbackupwithoutdatafile/
The answer to the above question is No, if the transaction-log file is not damaged, then we can initiate a log backup to backup the data till the time of the crash, since last log backup. To support my answer, I have created the following test script. In the first part of the script, I have created a test database named “TestDB” and change its recovery model to “Full”. The recovery model of the model database on my SQL 2008 R2 Express instance is set as Simple, so I manually change the recovery model of TestDB to Full.
use [master]
go
if exists (select 1 from sys.databases where name = 'TestDB')
drop database TestDB;
go
-- Create database TestDB
use [master]
go
create database [TestDB] ON Primary
(name = 'TestDB_Data', filename = 'C:\projects\demodb\TestDB_Data.MDF',
size = 10MB, filegrowth = 10MB)
LOG ON
(name = 'TestDB_Log', filename = 'C:\projects\demodb\TestDB_Log.MDF',
size = 10MB, filegrowth = 10MB);
go
-- Changing the Recovery Model to full
use [master]
alter database [TestDB] set recovery full;
go
Since I have created the database, its time to initiate a full backup of the database. Next I will add a demo tables with two columns and inserted some data into the table. After verifying the data in the table, initiate the first transaction-log backup.
-- Initiate a Full backup of the database
use [master]
Backup database [TestDB] to disk = 'C:\projects\TestDB.bak'
go
-- Create a table in the database
use [TestDB]
create table dbo.demotable1 (
demoid int identity(1,1),
demodate datetime default getdate()
);
go
-- Insert base data
use [TestDB]
insert into dbo.demotable1 default values;
go 10
-- verify base data
select * from TestDB.dbo.demotable1;
go
-- Initiate the 1st T-log backup
use [master]
backup log [TestDB] to disk = 'c:\projects\testDB1.trn';
go
Since the first log backup is completed, lets add some additional data into the table in the TestDB database. After inserting the additional row into the table, I will verify the same.
-- Insert some additional data
use [TestDB]
insert into dbo.demotable1 default values;
go 15
-- verify all the data in the database
select COUNT(*) from TestDB.dbo.demotable1;
go
/*(total = 25, till log backup it was 10)*/
Now I am going to crash the TestDB database. I am going to shutdown my SQL Server Express instance and manually delete the data file of the TestDB database. Once the file is deleted, restart the SQL Server instance, and try to access data from the database. The following error will occur: “Database ‘TestDB’ cannot be opened due to inaccessible files or insufficient memory or disk space“
-- try to access the table, will get error as data file is missing
use [TestDB]
select COUNT(*) from TestDB.dbo.demotable1;
go
As I am unable to access the database, I need to backup the transaction log to retrieve the data, which was inserted into the database after the last transaction-log backup, to minimize the data loss. The syntax to initiate the tail-log backup is as follows:
-- Initiate the tail log backup
use [master]
BACKUP log [testDB] to disk = 'C:\Projects\TestDB_Tail_Log.trn'
with No_Truncate, Norecovery;
go
/* This complete the tail log backup without the primary data file */
If the transaction-log file of the database is not damaged, then the above command will create the log backup (also known as tail-log backup). This completes the answer to the given question; however, a backup is never successful, if it can’t be restored. So I am going to test, whether I can restore the database to the same point, when the crash occurred, using the available backups. The next part of the script will restore the database and verify the data in the database after the restoration is completed.
use [master]
Restore database [TestDB]
from disk = 'C:\projects\TestDB.bak'
with replace, norecovery;
go
Restore Log [TestDB]
from disk = 'c:\projects\testDB1.trn'
with replace, norecovery;
go
Restore Log [TestDB]
from disk = 'c:\projects\TestDB_Tail_Log.trn'
with recovery;
go
-- Verify the data
Select count(*) from TestDB.dbo.demotable1;
go
The database is recovered successfully using the available backups and the complete data is also recovered. If we place the data file and the t-log files in different drives, in case, if the drive containing the data file is damaged, then we can retrive the data if we are able to backup the t-log. However, if the drive containing the t-log files is crashed, then we are going to face some data loss.
The complete script to reproduce the above scenario is mentioned below:
/* Create Tail-log backup after a crash, if the primary data file is missing
------------------------------------------------------------------------------
@skganguly
*/
use [master]
go
if exists (select 1 from sys.databases where name = 'TestDB')
drop database TestDB;
go
-- Create database TestDB
use [master]
go
create database [TestDB] ON Primary
(name = 'TestDB_Data', filename = 'C:\projects\demodb\TestDB_Data.MDF',
size = 10MB, filegrowth = 10MB)
LOG ON
(name = 'TestDB_Log', filename = 'C:\projects\demodb\TestDB_Log.MDF',
size = 10MB, filegrowth = 10MB);
go
-- Changing the Recovery Model to full
use [master]
alter database [TestDB] set recovery full;
go
-- Initiate a Full backup of the database
use [master]
Backup database [TestDB] to disk = 'C:\projects\TestDB.bak'
go
-- Create a table in the database
use [TestDB]
create table dbo.demotable1 (
demoid int identity(1,1),
demodate datetime default getdate()
);
go
-- Insert base data
use [TestDB]
insert into dbo.demotable1 default values;
go 10
-- verify base data
select * from TestDB.dbo.demotable1;
go
-- Initiate the 1st T-log backup
use [master]
backup log [TestDB] to disk = 'c:\projects\testDB1.trn';
go
-- Insert some additional data
use [TestDB]
insert into dbo.demotable1 default values;
go 15
-- verify all the data in the database
select COUNT(*) from TestDB.dbo.demotable1;
go
/*(total = 25, till log backup it was 10)*/
-- initiate the crash, for this demo, I've shutdown the server and
-- delete the data file manually, to verify whether the tail-log backup is
-- possible or not if the data file is not available
-- try to access the table
use [TestDB]
select COUNT(*) from TestDB.dbo.demotable1;
go
-- Database 'TestDB' cannot be opened due to inaccessible files
-- or insufficient memory or disk space
/* Initiate the tail log backup*/
use [master]
BACKUP log [testDB] to disk = 'C:\Projects\TestDB_Tail_Log.trn'
with No_Truncate, Norecovery;
go
/* This complete the tail log backup without the primary data file */
/* The script will not be successful, if we can't restore the tail log.
Thus, we are going to restore the database and verify the data before cleanup */
use [master]
Restore database [TestDB]
from disk = 'C:\projects\TestDB.bak'
with replace, norecovery;
go
Restore Log [TestDB]
from disk = 'c:\projects\testDB1.trn'
with replace, norecovery;
go
Restore Log [TestDB]
from disk = 'c:\projects\TestDB_Tail_Log.trn'
with recovery;
go
-- Verify the data
Select count(*) from TestDB.dbo.demotable1;
go
-- clean up the database
use [master]
drop database [TestDB]
go
=======================================
Thanks To :- http://sudeeptaganguly.wordpress.com/2011/04/15/taillogbackupwithoutdatafile/
14 April 2011
Msg 3201, Level 16 Cannot open backup device . Operating system error 5(Access is denied.)
Thanks to :- http://blog.sqlauthority.com/2011/04/13/sql-server-fix-error-msg-3201-level-16-cannot-open-backup-device-operating-system-error-5access-is-denied/
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘C:\AdventureWorks.bak’. Operating system error 5(Access is denied.).
Fix/Resolution/Workaround:
I checked which user account my SQL Server services are running.
I gave full permission to that account to get read of the error.
NOTE: I have checked with my personal machine and not on production server. Please check with your network administrator for permission on production server.
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘C:\AdventureWorks.bak’. Operating system error 5(Access is denied.).
Fix/Resolution/Workaround:
I checked which user account my SQL Server services are running.
I gave full permission to that account to get read of the error.
NOTE: I have checked with my personal machine and not on production server. Please check with your network administrator for permission on production server.
12 April 2011
Hosting a Report Server Database in a SQL Server Failover Cluster
SQL Server provides failover clustering support so that you can use multiple disks for one or more SQL Server instances. Failover clustering is supported only for the report server database; you cannot run the Report Server service as part of a failover cluster.
To host a report server database on a SQL Server failover cluster, the cluster must already be installed and configured. You can then select the failover cluster as the server name when you create the report server database in the Database Setup page of the Reporting Services Configuration tool.
Although the Report Server service cannot participate in a failover cluster, you can install Reporting Services on a computer that has a SQL Server failover cluster installed. The report server runs independently of the failover cluster. If you install a report server on a computer that is part of a SQL Server failover instance, you are not required to use the failover cluster for the report server database; you can use a different SQL Server instance to host the database.
=======================================================================
So on a Active passive node follow below steps,
1) On active node we already selected reporting services and installed
2) On passive node there is no Reporting services installed as its not a clustered feature
3) Install Reporting services as a named instance
4) configure RS with clustered name of SQL Server, by using existing URL and Report Server DB
5) Take a backup of encryption key and restore on passive node.
6) Now we can access the reports on Fail over.
To host a report server database on a SQL Server failover cluster, the cluster must already be installed and configured. You can then select the failover cluster as the server name when you create the report server database in the Database Setup page of the Reporting Services Configuration tool.
Although the Report Server service cannot participate in a failover cluster, you can install Reporting Services on a computer that has a SQL Server failover cluster installed. The report server runs independently of the failover cluster. If you install a report server on a computer that is part of a SQL Server failover instance, you are not required to use the failover cluster for the report server database; you can use a different SQL Server instance to host the database.
=======================================================================
So on a Active passive node follow below steps,
1) On active node we already selected reporting services and installed
2) On passive node there is no Reporting services installed as its not a clustered feature
3) Install Reporting services as a named instance
4) configure RS with clustered name of SQL Server, by using existing URL and Report Server DB
5) Take a backup of encryption key and restore on passive node.
6) Now we can access the reports on Fail over.
11 April 2011
Reporting Services under Cluster environment
1. Reporting Services under Cluster environment
http://www.microsoft.com/dynamics/crm/using/deploy/clusteringssrs.mspx
2. Deployment Topology.
http://msdn.microsoft.com/en-us/library/ms157293.aspx
3. Edition and Features supported from SQL Server.
http://www.microsoft.com/sqlserver/en/us/product-info/compare.aspx
http://www.microsoft.com/dynamics/crm/using/deploy/clusteringssrs.mspx
2. Deployment Topology.
http://msdn.microsoft.com/en-us/library/ms157293.aspx
3. Edition and Features supported from SQL Server.
http://www.microsoft.com/sqlserver/en/us/product-info/compare.aspx
09 April 2011
How to create SQL Server 2005 Clustering with Virtual Server 2005 (Part 5)
This is the last part of the series. Once you have configured windows cluster and MSDTC, installing SQL is piece of cake. I already mentioned in part 1 that you have to download ISO (SQLEVAL.ISO) which we would need to attached as CD drive which would have setup files. I have copied the image on E:\Virtual Server\SQLEVAL.ISO
We also need to create domain account and domain groups in domain controller. So, logon to domain controller. Go to Start > Run > DSA.msc and follow below screen-shots.
AD Users and Computers
Give the details of account (i.e. SQLSVC)
SQLSVC Account
Set the password after clicking next button. Make sure that "Password never expires" is checked and "User cannot change password" is checked.
SQLSVC Account Password
One account is created, lets add a Group and add SQLSVC into the group.
SQLSVC New Group
Give Group Name "SQL Admin Groups"
SQLSVC Group Name
Right click on "SQL Admin Groups" properties and add "members"
SQLSVC New Group Members
Now you are done with Domain controller.
Now, let's mount ISO image to MyNode1 and MyNode2. To do this, go to Virtual server console.
CD_DVD
Click on CD/DVD and fill details as below
CD_DVD_Properties
Above has to be done on both the nodes. Once you do that, you can see SQL Installation CD mounted on MyNode1 and MyNode2.
Lets Start SQL setup now.
1. Log off from MyNode2. This is required to avoid running into known bug.
2. Log on to MyNode1.
3. Move all the resource groups to MyNode1 using Cluster AdminMyNode1 Active Groups
4. Go to DVD drive and double click on setup.exe from Servers folder
5. Accept EULA. Next.
6. Click 'Install' and wait for SQL Server to configure components and setup the installation. Once the required components (.NET Framework 2.0, MS SQL Native Client and the SQL Server 2005 setup support files) have been installed click 'Next'
7. Click 'next' when prompted with the screen 'Welcome to the Microsoft SQL Server Installation Wizard. The installation will then proceed through the 'System Configuration Check'- click 'next' to proceed.
8. Enter Registration Information, click 'next'
9. Components to Install- Select all components (SQL Server Database Services, Workstation components) , then click 'next' components to install
10. Since this is the first installation of a new database instance for SQL server, select 'Default Instance'.
11. Provide Virtual Server Name (VirtualSQL) VirtualSQL
12. Provide Virtual SQL Server IP Address (192.1.1.6) in Next screen and click on Add. Then Next
13. VirtualSQLIP
14. Provide SQL Group in Next screen as shown below Cluster Group Selection
15. Click Next on Below Screen
16. Select Nodes
17. Provide Administrator Password in next screen and click Next
18. Provide SQL Service Account created earlier on domain controller(sqlsvc) SQLSVC Provide
19. Provide Domain Group which we created earlier (SQL Admin Groups) SQLSVC Group Provide
20. Authentication Mode. Choose Mixed Mode (Windows Authentication and SQL Server Authentication), provide sa password and click 'next'
21. Keep the default settings (SQL Collations- Dictionary Order, case-insensitive, 1252 character set) and click next
22. Error and Usage Report Settings. Leave defaults (none checked). Click 'next'
23. Check installation Settings, click 'install'.
24. During the install process you can always see what is happening on other node.
25. Once the installation process is completed, click 'next'
26. And finally... Finish.
Now we are done with the setup and we should see the resources in cluster admin under SQL Group.
We also need to create domain account and domain groups in domain controller. So, logon to domain controller. Go to Start > Run > DSA.msc and follow below screen-shots.
AD Users and Computers
Give the details of account (i.e. SQLSVC)
SQLSVC Account
Set the password after clicking next button. Make sure that "Password never expires" is checked and "User cannot change password" is checked.
SQLSVC Account Password
One account is created, lets add a Group and add SQLSVC into the group.
SQLSVC New Group
Give Group Name "SQL Admin Groups"
SQLSVC Group Name
Right click on "SQL Admin Groups" properties and add "members"
SQLSVC New Group Members
Now you are done with Domain controller.
Now, let's mount ISO image to MyNode1 and MyNode2. To do this, go to Virtual server console.
CD_DVD
Click on CD/DVD and fill details as below
CD_DVD_Properties
Above has to be done on both the nodes. Once you do that, you can see SQL Installation CD mounted on MyNode1 and MyNode2.
Lets Start SQL setup now.
1. Log off from MyNode2. This is required to avoid running into known bug.
2. Log on to MyNode1.
3. Move all the resource groups to MyNode1 using Cluster AdminMyNode1 Active Groups
4. Go to DVD drive and double click on setup.exe from Servers folder
5. Accept EULA. Next.
6. Click 'Install' and wait for SQL Server to configure components and setup the installation. Once the required components (.NET Framework 2.0, MS SQL Native Client and the SQL Server 2005 setup support files) have been installed click 'Next'
7. Click 'next' when prompted with the screen 'Welcome to the Microsoft SQL Server Installation Wizard. The installation will then proceed through the 'System Configuration Check'- click 'next' to proceed.
8. Enter Registration Information, click 'next'
9. Components to Install- Select all components (SQL Server Database Services, Workstation components) , then click 'next' components to install
10. Since this is the first installation of a new database instance for SQL server, select 'Default Instance'.
11. Provide Virtual Server Name (VirtualSQL) VirtualSQL
12. Provide Virtual SQL Server IP Address (192.1.1.6) in Next screen and click on Add. Then Next
13. VirtualSQLIP
14. Provide SQL Group in Next screen as shown below Cluster Group Selection
15. Click Next on Below Screen
16. Select Nodes
17. Provide Administrator Password in next screen and click Next
18. Provide SQL Service Account created earlier on domain controller(sqlsvc) SQLSVC Provide
19. Provide Domain Group which we created earlier (SQL Admin Groups) SQLSVC Group Provide
20. Authentication Mode. Choose Mixed Mode (Windows Authentication and SQL Server Authentication), provide sa password and click 'next'
21. Keep the default settings (SQL Collations- Dictionary Order, case-insensitive, 1252 character set) and click next
22. Error and Usage Report Settings. Leave defaults (none checked). Click 'next'
23. Check installation Settings, click 'install'.
24. During the install process you can always see what is happening on other node.
25. Once the installation process is completed, click 'next'
26. And finally... Finish.
Now we are done with the setup and we should see the resources in cluster admin under SQL Group.
How to create SQL Server 2005 Clustering with Virtual Server 2005 (Part 4)
In previous parts(Part 1, Part 2 and Part 3), we have completed windows clustering. Now, before installing SQL Cluster we have to configure MSDTC. We also need to make some shared drive which we would use to keep SQL Server databases and MSDTC. We are going to use Disk M for MSDTC and Disk X for SQL Server. We have already done the steps for Disk Q in part 2 (Create Quorum Disk section). I will reproduce the steps for Disk M and X here.
Configure Shared Disks
1. Turn off MyNode1 and MyNode2
2. Choose Virtual Disks > Create > Fixed Size Virtual Hard Disks
Create Virtual Disk(M Drive)
3. Provide Details for M Drive (250 MB) and hit "Create" button
Create Virtual Disk(M Drive) Details
4. Same way we would create X drive (2 GB) and Hit "Create" button.
Create Virtual Disk(X Drive) Details
Create Virtual Disk(Y Drive) Details
5. Below is what you would see in Windows Explorer
All Disks
6. Lets attach the disks to MyNode1 and MyNode2 (They are off at this moment) I am showing for MyNode1. Please follow same for MyNode2 also.
MyNode1_Edit Configuration
MyNode1 Edit hard disk
Click on Hard Disks
MyNode1 HardDisks Properties
Hit OK and it would attach the disks to MyNode1. Follow the same for MyNode2
7. Turn on ONLY MyNode1.
8. Login to MyNode1 and Start > Run > DiskMgmt.msc. Cancel on the screen if you get any.
9. You should see two new Disks which are Not Initialized. Right Click and choose Initialize Disk. Then you create partition by Right Click New Partition. Below is final screen shot which you should see.
MyNode1 Disk management
10. Now Turn Off MyNode1 and Turn On MyNode2
11. Go to DiskMgmt.msc on MyNode2 and assign drive letters (M for 250 MB drive and X for 2 GB Drive)
12. Open Cluster Administrator (Start > Run > CluAdmin)
13. Create New Group called MSDTC (File > New > Group) and provide details
New Group MSDTC
14. Choose Both Nodes as preferred owner. You might get an error at last screen but that's OK because MyNode1 is offline.
15. Create a new "Disk" resource under MSDTC Group (File > New > Resource) Choose details as below screen shot.
New Resource M Drive
17. Choose M Drive in "Disk Parameters" screen. and finish.
Disk Parameters
18. Now you should see a Disk resource under MSDTC Group. Right click and choose "Bring Online"
19. Follow 13 to 18 again to Create new groups called "SQL Group" and add "Disk X"
SQL Group Online
20. Up to here we have completed shared disk configuration for MyNode1 and MyNode2. Let's start MyNode1 and test fail over for new groups.
21. Before we install SQL Server, we have to configure MSDTC
Configure MSDTC as Resource
1. On MyNode1 and MyNode2 follow below steps
* Open Control Panel on the cluster node
* Double-click Add/Remove Programs
* On the left side of the screen, double click Add/Remove Windows Components
* Under Components, highlight Application Server and click Details
* Select Enable network DTC access, and then click OK
* Click Next
* Click Finish
2. Add "MSDTC IP Address resource" in MSDTC Group
* Right click MSDTC Group and select New > Resource
* Type a some name such as MSDTC IP Address
* In Resource Type, choose IP Address
* Click Next
* Click Next on Possible Owners screen
* Click Next on Dependencies Screen
* Give IP Address as 192.1.1.5
* Click Finish
* Right click the MSDTC IP Address resource and select Bring Online.
3. Add "MSDTC Network Name" resource in MSDTC Group
* Right click MSDTC Group and select New > Resource
* Type a some name such as MSDTC NN
* In Resource Type, choose Network Name
* Click Next
* Click Next on Possible Owners screen
* On Dependencies Screen, move MSDTC IP Address and click on Add to bring on Right window. Now hit Next.
* Give Name as MSDTC
* Click Finish
* Right click the MSDTC NN resource and select Bring Online.
4. Add "MSDTC" resource in MSDTC Group
* Right click MSDTC Group and select New > Resource
* Type a some name such as MSDTC
* In Resource Type, choose Distributed Transaction Coordinator
* Click Next
* Click Next on Possible Owners screen
* On Dependencies Screen, choose "MSDTC NN" and "M Drive" and click on Add to bring on Right window.
MSDTC Dependencies
* Click Finish
* Right click the MSDTC resource and select Bring Online.
MSDTC Group Online
Above is the screen shot of "MSDTC group". Make sure you have matching "Resource Type" as shown in the screen.
Test fail over for MSDTC group. This finishes all Prerequisites to install SQL Server on Cluster. Get ready to install SQL Server in last part of the series.
You can follow http://support.microsoft.com/kb/899191 to make changes to MSDTC Services.
Lets move on to last part of the series
Configure Shared Disks
1. Turn off MyNode1 and MyNode2
2. Choose Virtual Disks > Create > Fixed Size Virtual Hard Disks
Create Virtual Disk(M Drive)
3. Provide Details for M Drive (250 MB) and hit "Create" button
Create Virtual Disk(M Drive) Details
4. Same way we would create X drive (2 GB) and Hit "Create" button.
Create Virtual Disk(X Drive) Details
Create Virtual Disk(Y Drive) Details
5. Below is what you would see in Windows Explorer
All Disks
6. Lets attach the disks to MyNode1 and MyNode2 (They are off at this moment) I am showing for MyNode1. Please follow same for MyNode2 also.
MyNode1_Edit Configuration
MyNode1 Edit hard disk
Click on Hard Disks
MyNode1 HardDisks Properties
Hit OK and it would attach the disks to MyNode1. Follow the same for MyNode2
7. Turn on ONLY MyNode1.
8. Login to MyNode1 and Start > Run > DiskMgmt.msc. Cancel on the screen if you get any.
9. You should see two new Disks which are Not Initialized. Right Click and choose Initialize Disk. Then you create partition by Right Click New Partition. Below is final screen shot which you should see.
MyNode1 Disk management
10. Now Turn Off MyNode1 and Turn On MyNode2
11. Go to DiskMgmt.msc on MyNode2 and assign drive letters (M for 250 MB drive and X for 2 GB Drive)
12. Open Cluster Administrator (Start > Run > CluAdmin)
13. Create New Group called MSDTC (File > New > Group) and provide details
New Group MSDTC
14. Choose Both Nodes as preferred owner. You might get an error at last screen but that's OK because MyNode1 is offline.
15. Create a new "Disk" resource under MSDTC Group (File > New > Resource) Choose details as below screen shot.
New Resource M Drive
17. Choose M Drive in "Disk Parameters" screen. and finish.
Disk Parameters
18. Now you should see a Disk resource under MSDTC Group. Right click and choose "Bring Online"
19. Follow 13 to 18 again to Create new groups called "SQL Group" and add "Disk X"
SQL Group Online
20. Up to here we have completed shared disk configuration for MyNode1 and MyNode2. Let's start MyNode1 and test fail over for new groups.
21. Before we install SQL Server, we have to configure MSDTC
Configure MSDTC as Resource
1. On MyNode1 and MyNode2 follow below steps
* Open Control Panel on the cluster node
* Double-click Add/Remove Programs
* On the left side of the screen, double click Add/Remove Windows Components
* Under Components, highlight Application Server and click Details
* Select Enable network DTC access, and then click OK
* Click Next
* Click Finish
2. Add "MSDTC IP Address resource" in MSDTC Group
* Right click MSDTC Group and select New > Resource
* Type a some name such as MSDTC IP Address
* In Resource Type, choose IP Address
* Click Next
* Click Next on Possible Owners screen
* Click Next on Dependencies Screen
* Give IP Address as 192.1.1.5
* Click Finish
* Right click the MSDTC IP Address resource and select Bring Online.
3. Add "MSDTC Network Name" resource in MSDTC Group
* Right click MSDTC Group and select New > Resource
* Type a some name such as MSDTC NN
* In Resource Type, choose Network Name
* Click Next
* Click Next on Possible Owners screen
* On Dependencies Screen, move MSDTC IP Address and click on Add to bring on Right window. Now hit Next.
* Give Name as MSDTC
* Click Finish
* Right click the MSDTC NN resource and select Bring Online.
4. Add "MSDTC" resource in MSDTC Group
* Right click MSDTC Group and select New > Resource
* Type a some name such as MSDTC
* In Resource Type, choose Distributed Transaction Coordinator
* Click Next
* Click Next on Possible Owners screen
* On Dependencies Screen, choose "MSDTC NN" and "M Drive" and click on Add to bring on Right window.
MSDTC Dependencies
* Click Finish
* Right click the MSDTC resource and select Bring Online.
MSDTC Group Online
Above is the screen shot of "MSDTC group". Make sure you have matching "Resource Type" as shown in the screen.
Test fail over for MSDTC group. This finishes all Prerequisites to install SQL Server on Cluster. Get ready to install SQL Server in last part of the series.
You can follow http://support.microsoft.com/kb/899191 to make changes to MSDTC Services.
Lets move on to last part of the series
How to create SQL Server 2005 Clustering with Virtual Server 2005 (Part 3)
In Part 1 and Part 2, we configured till setting up Quorum drive on MyNode1.
First we need to change the IP address of heartbeat network which we added to MyNode1 and MyNode2.
Below is the screen shot of heartbeat IP on MyNode1. We need to give IP as 10.10.10.1 (once you hit tab, subnet would come automatically, 255.0.0.0)
This would be displayed as "Local Area Network 2", you can rename it to private or heartbeat for easy identification.
MyNode1 Heartbeat IP
Follow the same thing on MyNode2. Provide heartbeat IP address as 10.10.10.2
Once IP Setup is done, "Turn Off" MyNode2 and Login to MyNode1. First we are going to install One node cluster and later add MyNode2 in this cluster (we will call this as SQLCluster)
1, Go to Start>Run>Cluadmin
Start.run.Cluadmin
2. Select "Create New Cluster" from the options.
Create New Cluster
3. Hit OK
4. on "Cluster Name and Domain" screen, provide the domain name as MyCluster.com and Cluster Name as "SQLCluster" and Hit "Next"
5. In Next screen, Provide Name as MyNode1 and Also click on Advanced button to select "Advanced (minimum) configuration"
Select Computer
6. Next screen will analyze the settings, this may take a while to finish, You may get one warning since we selected "minimum configuration", just ignore that and hit next.
Analyzing Cluster
7. In Next screen, you will be prompted for IP Address. Give it as 192.1.1.4
Cluster IP Address
8. Next screen is for Cluster Service Account. For easiness of this lab, I am using Administrator account. In real world, you should create a separate account in domain.
Cluster Service Account
9. Hitting Next will show proposed cluster configuration.
Proposed Cluster Config
Make sure that you hit on Quorum and select Q: as shown in above screen shot.
10. Once above step is complete, we have successfully installed, one node cluster. Screen Below...
One Node Cluster
11. Now, remember that MyNode2 was Turned off by this time.At this point, Turn On MyNode2 and let it boot up completely. Don't Login to the node.
12. Come back to MyNode1 and Select File > New > Node
Add MyNode2
13. In below screen, provide MyNode2
Add MyNode2 Wizard 1
14. Keep Moving forward and you will get below screen.
Add MyNode2 Wizard 2
15. Keep going....
Add MyNode2 Wizard 3
16. Finally.... you are done with Windows clustering
Two Nodes Windows Cluster
Click for Part 4
First we need to change the IP address of heartbeat network which we added to MyNode1 and MyNode2.
Below is the screen shot of heartbeat IP on MyNode1. We need to give IP as 10.10.10.1 (once you hit tab, subnet would come automatically, 255.0.0.0)
This would be displayed as "Local Area Network 2", you can rename it to private or heartbeat for easy identification.
MyNode1 Heartbeat IP
Follow the same thing on MyNode2. Provide heartbeat IP address as 10.10.10.2
Once IP Setup is done, "Turn Off" MyNode2 and Login to MyNode1. First we are going to install One node cluster and later add MyNode2 in this cluster (we will call this as SQLCluster)
1, Go to Start>Run>Cluadmin
Start.run.Cluadmin
2. Select "Create New Cluster" from the options.
Create New Cluster
3. Hit OK
4. on "Cluster Name and Domain" screen, provide the domain name as MyCluster.com and Cluster Name as "SQLCluster" and Hit "Next"
5. In Next screen, Provide Name as MyNode1 and Also click on Advanced button to select "Advanced (minimum) configuration"
Select Computer
6. Next screen will analyze the settings, this may take a while to finish, You may get one warning since we selected "minimum configuration", just ignore that and hit next.
Analyzing Cluster
7. In Next screen, you will be prompted for IP Address. Give it as 192.1.1.4
Cluster IP Address
8. Next screen is for Cluster Service Account. For easiness of this lab, I am using Administrator account. In real world, you should create a separate account in domain.
Cluster Service Account
9. Hitting Next will show proposed cluster configuration.
Proposed Cluster Config
Make sure that you hit on Quorum and select Q: as shown in above screen shot.
10. Once above step is complete, we have successfully installed, one node cluster. Screen Below...
One Node Cluster
11. Now, remember that MyNode2 was Turned off by this time.At this point, Turn On MyNode2 and let it boot up completely. Don't Login to the node.
12. Come back to MyNode1 and Select File > New > Node
Add MyNode2
13. In below screen, provide MyNode2
Add MyNode2 Wizard 1
14. Keep Moving forward and you will get below screen.
Add MyNode2 Wizard 2
15. Keep going....
Add MyNode2 Wizard 3
16. Finally.... you are done with Windows clustering
Two Nodes Windows Cluster
Click for Part 4
How to create SQL Server 2005 Clustering with Virtual Server 2005 (Part 1)
After delivering SQL Training, I realized that there is a need to write a blog which talks about Installing SQL Server 2005 cluster on Virtual Environment. Many DBA wants to see how Cluster looks like and they don't have capability to buy/see actual cluster.
There is an easy solution to the problem by using Virtual Server 2005.
I will try to be as descriptive as possible. In case something is not clear, please provide feedback.
Prerequisites
You also have to download Microsoft Virtual Server (http://technet.microsoft.com/hi-in/bb738033(en-us).aspx)
You also need to download ISO image for SQL Server 2005 evaluation edition (http://www.microsoft.com/downloads/details.aspx?FamilyID=6931fa7f-c094-49a2-a050-2d07993566ec&DisplayLang=en)
You should also have virtual hard disk of operating system. (Download available http://www.microsoft.com/downloads/details.aspx?FamilyID=77f24c9d-b4b8-4f73-99e3-c66f80e415b6&DisplayLang=en)
Above link has three files to download. You have to download all files. Once you download content from above link, you need to extract to a folder in local machine using WIN2K3R2EESP2.part1.exe. I extracted in (E:\Virtual Server)
Extracted Files
Please refer the readme file in above folder after you download.
NOTE: THIS SETUP WILL WORK ONLY FOR 30 days(depends on Read Me)
One you extracted the files, make three copies of virtual hard disk. One for Domain Controller, second for node1 and third for node2. I have created files as below:
E:\Virtual Server\MyDC\MyDC.vhd
E:\Virtual Server\MyNode1\MyNode1.vhd
E:\Virtual Server\MyNode2\MyNode2.vhd
Folders
Once you have downloaded and installed Virtual Server you should see new menu item in Programs.
Virtual Server Startup
Now, we will configure the Virtual Servers. First we need to Create Virtual Machine so that we have three separate virtual servers available. Make sure you set the Search Path at below screen.
On Left Side Menu choose Virtual Server > Server Properties > Search Path
Search Path
Create Domain Controller
below is the screen shot of "MyDC" setting. Information which I filled in is
Virtual Machine Name MyDC
Memory 256
Use Existing hard disk Use the location from drop down (E:\Virtual Server\MyDC\MyDC.vhd)
Connected To Internal Network
My DC Create Virtual Server
Click on Create.
Now, Go to Master Status and try to "Turn ON" the domain controller (I know its not yet a DC but in next step we are going to make it)
One you log in to MyDC, rename the machine to MyDC (by right click on My Computer > Properties) You need to restart the computer after renaming.
Next step is to run "dcpromo" command to make this machine as domain controller. Start>Run>dcpromo
dcpromo
Accept all settings as default, below are few which you need to modify.
Install DNS on this computer
MyCluster
During setup if it asks for windows installation CD, please point it to C:\WindowsInstallationFiles\I386 folder. Also, when prompted, please provide IP address as 192.1.1.1. You will be prompted to restart the machine.
When you can logon screen after reboot, "Log on to" should have MyCluster selected.
LogOn After Making DC
Now we have one machine ready which will act as domain controller for our cluster setup.
Create Member Servers
You are now having one Domain Controller in your Virtual Network. Now, lets add two member servers in the domain. As we did earlier, we have to add rest two vhd to server. Below screen shot is for Node2.
Node 2 Create Virtual Server
After configuration on Virtual Servers, follow below steps
1. Rename the nodes to MyNode1 and MyNode2 (still in Workgroup).
My Computer > Right Click > Properties > Computer Name
Rename Nodes
Then Restart MyNode1 and MyNode2
2. Disable the Firewall on all three machines.
Control Panel > Windows Firewall
Firewall
Select "Off" from the UI.
3. Change the IP address of machines.
MyNode1 - 192.1.1.2 (DNS Server 192.1.1.1)
MyNode2 - 192.1.1.3 (DNS Server 192.1.1.1)
4. Join the machine to domain (MyCluster.com) Below screen shot.
Join MyCluster
This finishes setup of one domain controller and two member servers.
Now we have to make MyNode1 and MyNode2 as single cluster.
lets move on to Part 2
How to install Clustered SQL Server 2008 on Windows 2008 Cluster using Hyper-V Part – 2
http://blogs.msdn.com/b/sqlserverfaq/archive/2010/04/28/how-to-install-clustered-sql-server-2008-on-windows-2008-cluster-using-hyper-v-guest-clustering-part-2.aspx
OR
Thanks for overwhelming response of part 1 of the series. As a part of this series, we have created a Domain Controller (Win2k8MyDC). Let’s go ahead and follow the steps below to add two more machines into my domain (blakhani.com). For this you need two more VHDs extracted. Here is the folder structure I have followed:
F Drive
I have kept two more VHDs Win2k8MyNode1.vhd and Win2k8MyNode2.vhd in F:\Win2k8MyNode1 and F:\Win2k8MyNode2 folders respectively. We are going to build two machines on Hyper-V now. I would show you steps for Win2k8MyNode1 and same should be followed for the second node. Let’s Open Hyper-V manager, Right Click on Server Name and Choose New > Virtual Machine
New Virtual Mahine 01
Below are few self-explanatory screen shots so I am not going to spend time in explaining what they are.
New Virtual Mahine 02
Put Name of the machine.
New Virtual Mahine 03
We have configured Physical Memory for this machine to be 1 GB.
New Virtual Mahine 04
Choose Private VM Network
New Virtual Mahine 05
Here, we have to give the file which we have extracted earlier.
New Virtual Mahine 06
Finish the Wizard and Machine 2 is ready to use.
New Virtual Mahine 07
Once machine is started, it may ask you to restart. Once you are logged in, Below is the first screen you would get
First LogOn_00
As you can see, machine is has a randomly assigned name, it is in a workgroup with no IP Address and is not activated etc.. I am going to make some changes below. I would not spend much time in demonstrating “how to change computer name”, “how to change IP”, “How to join a machine to domain” etc. You can bing them and find steps easily. Finally below is the screenshot for Win2k8MyNode2
First LogOn_01
Okay. So all three machines are ready. Quick Summary
Machine Name Role IP Address
Win2k8MyDC Domain Controller 192.1.1.1
Win2k8MyNode1 Member Server 192.1.1.2
Win2k8MyNode1 Member Server 192.1.1.3
Just to be on safer side, make sure that you are able to Ping them from each other.
Now, Next step is to add new drive to the Domain controller and use iSCSI to connect them from Node1 and Node2.
But wait… before you do that, you may need to transfer some data from the “host” to the “guest”. I would spend some time in explaining how to do that.
Do you remember that we have created two network in Hyper V (Refer Part 1, Step 1). Lets go ahead and add second network to DC, Node1 and Node2. To avoid confusion, rename the current network adaptors on “guests” to “Domain Network” as shown below
Domain Network
Lets stop All three machines using Hyper V manager and add “Internal” network for all machines. Open Hyper V manager, Right Click on machine name, choose settings. On “Add Hardware” tab, choose “Network Adapter”
Add Hardware_Network Adapter
Click on Add and choose “Internal” as shown below.
Network Adapter_Internal
Click on Apply on the previous screen. Follow the same steps for the rest of the machines as well. Go ahead and start the machines. Once you login, you would see the new network coming up. Rename that to Internal and give IP addresses as 192.168.10.11 (DC), 192.168.10.12(Win2k8MyNode1), 192.168.10.13(Win2k8MyNode2). Now one host machine would have two networks. Please provide the IP address as 192.168.10.10. Here is the quick screenshot of all the Summary screens (notice IP Addresses)
Win2k8MyNode1_SummaryWin2k8MyNode2_SummaryWin2k8MyDC_Summary
Now, you should be able to ping Guest machine(s) from the host machine and we are all set to copy files from host to guest. You may wonder that its so simple. But believe me, with my limited knowledge of networking, it took me a day to figure this out.
To Configure shared drive you have to use iSCSI initiator and iSCSI Target. Initiator would be available on your VMs but you need to get the Target (its not free). There are few free iSCSI target software such as Starwind that can also be used for this setup. I am using internal version of Microsoft iSCSI Target.
Since I am going to add a disk to Win2k8MyDC which would be exposed to the rest of the nodes, I have created a disk using below steps
VirtualHardDisk_00
Click Next on Welcome Screen
VirtualHardDisk_01
Choose Fixed Size
VirtualHardDisk_02
I have created folder “F:\Shared disks” and would place file there.
VirtualHardDisk_03
Size.. umm… 80 GB
VirtualHardDisk_04
Complete the wizard
VirtualHardDisk_05
This completes creation of a big disk (80 GB) which would be used to make smaller drive later. Attach the disk to storage server. I'm using my Windows Server 2008 R2 Domain Controller for my storage server as well, this saves me one VM.
* Turn off Win2k8MyDC.
* Right Click on Server and choose Setting and follow below
Add_NewHDD_01
* Choose Add Hardware, Select SCSI Controller and “Add”
Add_NewHDD_00
* Click on Add again
Add_NewHDD_02
* Choose the hard drive created earlier.
Add_NewHDD_03
* Click on Apply / OK
Let’s go ahead and start the VM (Win2k8MyDC). Once you login to server, go to start > Run and type DiskMgmt.msc and hit OK. You would see 80 GB Hard Disk and all you need is “Right Click” and “online”
Add_NewHDD_05
if there is no drive letter, the go ahead and assign a drive letter, say S: in this case. Now you should be able to see the drive in My Computer (Of course, Your Computer) :)
Add_NewHDD_06
I guess there are too many images on this blog, so I am going to write next post to demonstrate the configuration of shared disk inside a “guest” using iSCSI target.
Stay Tuned and Happy Clustering !!!
OR
Thanks for overwhelming response of part 1 of the series. As a part of this series, we have created a Domain Controller (Win2k8MyDC). Let’s go ahead and follow the steps below to add two more machines into my domain (blakhani.com). For this you need two more VHDs extracted. Here is the folder structure I have followed:
F Drive
I have kept two more VHDs Win2k8MyNode1.vhd and Win2k8MyNode2.vhd in F:\Win2k8MyNode1 and F:\Win2k8MyNode2 folders respectively. We are going to build two machines on Hyper-V now. I would show you steps for Win2k8MyNode1 and same should be followed for the second node. Let’s Open Hyper-V manager, Right Click on Server Name and Choose New > Virtual Machine
New Virtual Mahine 01
Below are few self-explanatory screen shots so I am not going to spend time in explaining what they are.
New Virtual Mahine 02
Put Name of the machine.
New Virtual Mahine 03
We have configured Physical Memory for this machine to be 1 GB.
New Virtual Mahine 04
Choose Private VM Network
New Virtual Mahine 05
Here, we have to give the file which we have extracted earlier.
New Virtual Mahine 06
Finish the Wizard and Machine 2 is ready to use.
New Virtual Mahine 07
Once machine is started, it may ask you to restart. Once you are logged in, Below is the first screen you would get
First LogOn_00
As you can see, machine is has a randomly assigned name, it is in a workgroup with no IP Address and is not activated etc.. I am going to make some changes below. I would not spend much time in demonstrating “how to change computer name”, “how to change IP”, “How to join a machine to domain” etc. You can bing them and find steps easily. Finally below is the screenshot for Win2k8MyNode2
First LogOn_01
Okay. So all three machines are ready. Quick Summary
Machine Name Role IP Address
Win2k8MyDC Domain Controller 192.1.1.1
Win2k8MyNode1 Member Server 192.1.1.2
Win2k8MyNode1 Member Server 192.1.1.3
Just to be on safer side, make sure that you are able to Ping them from each other.
Now, Next step is to add new drive to the Domain controller and use iSCSI to connect them from Node1 and Node2.
But wait… before you do that, you may need to transfer some data from the “host” to the “guest”. I would spend some time in explaining how to do that.
Do you remember that we have created two network in Hyper V (Refer Part 1, Step 1). Lets go ahead and add second network to DC, Node1 and Node2. To avoid confusion, rename the current network adaptors on “guests” to “Domain Network” as shown below
Domain Network
Lets stop All three machines using Hyper V manager and add “Internal” network for all machines. Open Hyper V manager, Right Click on machine name, choose settings. On “Add Hardware” tab, choose “Network Adapter”
Add Hardware_Network Adapter
Click on Add and choose “Internal” as shown below.
Network Adapter_Internal
Click on Apply on the previous screen. Follow the same steps for the rest of the machines as well. Go ahead and start the machines. Once you login, you would see the new network coming up. Rename that to Internal and give IP addresses as 192.168.10.11 (DC), 192.168.10.12(Win2k8MyNode1), 192.168.10.13(Win2k8MyNode2). Now one host machine would have two networks. Please provide the IP address as 192.168.10.10. Here is the quick screenshot of all the Summary screens (notice IP Addresses)
Win2k8MyNode1_SummaryWin2k8MyNode2_SummaryWin2k8MyDC_Summary
Now, you should be able to ping Guest machine(s) from the host machine and we are all set to copy files from host to guest. You may wonder that its so simple. But believe me, with my limited knowledge of networking
To Configure shared drive you have to use iSCSI initiator and iSCSI Target. Initiator would be available on your VMs but you need to get the Target (its not free). There are few free iSCSI target software such as Starwind that can also be used for this setup. I am using internal version of Microsoft iSCSI Target.
Since I am going to add a disk to Win2k8MyDC which would be exposed to the rest of the nodes, I have created a disk using below steps
VirtualHardDisk_00
Click Next on Welcome Screen
VirtualHardDisk_01
Choose Fixed Size
VirtualHardDisk_02
I have created folder “F:\Shared disks” and would place file there.
VirtualHardDisk_03
Size.. umm… 80 GB
VirtualHardDisk_04
Complete the wizard
VirtualHardDisk_05
This completes creation of a big disk (80 GB) which would be used to make smaller drive later. Attach the disk to storage server. I'm using my Windows Server 2008 R2 Domain Controller for my storage server as well, this saves me one VM.
* Turn off Win2k8MyDC.
* Right Click on Server and choose Setting and follow below
Add_NewHDD_01
* Choose Add Hardware, Select SCSI Controller and “Add”
Add_NewHDD_00
* Click on Add again
Add_NewHDD_02
* Choose the hard drive created earlier.
Add_NewHDD_03
* Click on Apply / OK
Let’s go ahead and start the VM (Win2k8MyDC). Once you login to server, go to start > Run and type DiskMgmt.msc and hit OK. You would see 80 GB Hard Disk and all you need is “Right Click” and “online”
Add_NewHDD_05
if there is no drive letter, the go ahead and assign a drive letter, say S: in this case. Now you should be able to see the drive in My Computer (Of course, Your Computer) :)
Add_NewHDD_06
I guess there are too many images on this blog, so I am going to write next post to demonstrate the configuration of shared disk inside a “guest” using iSCSI target.
Stay Tuned and Happy Clustering !!!
Rule "SQL Server Database Services feature state" failed
Change registry key on active node:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.\ConfigurationState.
Change all values from 2 to 1 for all Four items in the right pane.
Re-Run the rule on the installation. Now it will be solved and through.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.
Change all values from 2 to 1 for all Four items in the right pane.
Re-Run the rule on the installation. Now it will be solved and through.
The current SKU is invalid. When adding second node to SQL 2008 failover Cluster
Resolution
===========
There will be an ini file in setup. Z\SQLsetup\x86 folder >> DefaultSetup.ini
which contains following entry like
;SQLSERVER2008 Configuration File
[SQLSERVER2008]
PID=”*****-*****-*****-*****-*****”
Just comment out the PID key by semi collen (;) and then copy the key and put that key during the installation. It should be like this
;SQLSERVER2008 Configuration File
[SQLSERVER2008]
;PID=”*****-*****-*****-*****-*****”
copy the PID key *****-*****-*****-*****-***** and paste during the installation.
===========
There will be an ini file in setup. Z\SQLsetup\x86 folder >> DefaultSetup.ini
which contains following entry like
;SQLSERVER2008 Configuration File
[SQLSERVER2008]
PID=”*****-*****-*****-*****-*****”
Just comment out the PID key by semi collen (;) and then copy the key and put that key during the installation. It should be like this
;SQLSERVER2008 Configuration File
[SQLSERVER2008]
;PID=”*****-*****-*****-*****-*****”
copy the PID key *****-*****-*****-*****-***** and paste during the installation.
Not able to add SQL 2008 secone node , SQL server agent account is grayed out
Resolution 1 :-
============
Add the SQl Server Agent to the existing resources. From Right pane of Server manager >> Add resource>>Server>> Select Sql Server Agent
Bring it online
This will work out
Resolution 2:-
==============
If its failing with credential issues, try to install from command line. This is for Default installation, named give name in instance name.
Choose CMD run as Administrator
setup.exe /ACTION=AddNode /INSTANCENAME="MSSQLSERVER" /SQLSVCACCOUNT="DOMAIN\ACCOUNT" /SQLSVCPASSWORD="*******" /AGTSVCACCOUNT="DOMAIN\ACCOUNT" /AGTSVCPASSWORD="*******
This will resolve .
============
Add the SQl Server Agent to the existing resources. From Right pane of Server manager >> Add resource>>Server>> Select Sql Server Agent
Bring it online
This will work out
Resolution 2:-
==============
If its failing with credential issues, try to install from command line. This is for Default installation, named give name in instance name.
Choose CMD run as Administrator
setup.exe /ACTION=AddNode /INSTANCENAME="MSSQLSERVER" /SQLSVCACCOUNT="DOMAIN\ACCOUNT" /SQLSVCPASSWORD="*******" /AGTSVCACCOUNT="DOMAIN\ACCOUNT" /AGTSVCPASSWORD="*******
This will resolve .
Unable to start SQLServer agent resource on cluster after installation
Resolution
========
Modify the the following Key.
=======================
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\SQLServerAgent
ServerHost :- This will be blank, Give the VIRTUAL SERVER NAME, i.e Same name when we choose during installation of SQL SERVER
========
Modify the the following Key.
=======================
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\SQLServerAgent
ServerHost :- This will be blank, Give the VIRTUAL SERVER NAME, i.e Same name when we choose during installation of SQL SERVER
08 April 2011
Updating a XML Column
DECLARE @newValue varchar(50)
SELECT @newValue = 'NewValueXX'
UPDATE [temp1]
SET request_data.modify('replace value of (/request_data/segment/text())[1] with sql:variable("@newValue")')
Where request_data.value(' (/request_data/segment)[1]', 'varchar(50)' ) like 'ta%'
SELECT @newValue = 'NewValueXX'
UPDATE [temp1]
SET request_data.modify('replace value of (/request_data/segment/text())[1] with sql:variable("@newValue")')
Where request_data.value(' (/request_data/segment)[1]', 'varchar(50)' ) like 'ta%'
05 April 2011
What is Contained Database in SQL Server
What is Contained Database ?
1.Contained database is a new feature launched in SQL Server 2011.
2.This model clearly separates the database application from the management of SQL Server. Users can connect to the database without authenticating a login at the Database Engine level.
3.Applications within contained databases can be isolated from other databases from the instance of SQL Server, on which they reside, simplifying development and management.
A Video Tutorial and a Presentation on Contained Databases can be downloaded from here http://sqlserver-training.com/video-sql-server-contained-database/-
Why Contained Databases feature was Required ?
One of the problems facing the existing database model is data being left behind when an application is moved from one instance to another. Some data connected with an application, (for example, login information and agent job information) is currently stored within the instance instead of inside the database and When you move a non-contained database application from one instance to another instance of SQL Server, this data is left behind.
Later On, DBA’s nee to identify the data which was left behind and move it with your application to the new instance of SQL Server. This process can be time consuming and difficult.
What all is being saved inside a Contained Database?
The contained database, keeps all necessary information and objects in the database, for example
•Tables
•Functions
•Constraints
•Schemas
•types.
It also stores all application-level objects in the database, including
•Logins
•Application-level agent jobs
•Persisted error messages
•Linked server information
•System settings
What are the Benefits of using Contained Database ?
Contained databases can be easily moved to another server and start working instantly without the need of any additional configuration like adding user, mapping SID’s again. As Contained database have no external dependencies.
What type of Authentication Modes are supported by Contained Database?
Contained Databases supports
•SQL Server Authentication
•Windows Based Authentication
A contained user is a user without a login in the master database which resides in a Contained Database and can connect to this database specifying its credentials in the connection string. This user doesn’t exists in master’s syslogins tables and will not even show up when you list all SQL Users.
How we create a Contained Database ?
Prior to create a Contained database we need to enable "contained database authentication" property at SQL Server Level. This can be enabled using the following code.
-- Enable contained database authentication on the instance of SQL Server
-- This can done by A member of the sysadmin fixed server role
sp_configure 'show advanced', 1;
RECONFIGURE WITH OVERRIDE;
go
sp_configure 'contained database authentication', 1;
RECONFIGURE WITH OVERRIDE;
go
How to create Users in Contained Databases ?
A Contained database can be created as a normal user database, but with a property named "CONTAINMENT".
Following code, will create a database named "TEST" as contained database.
-- To create contained db you have to specify CONTAINMENT property
CREATE DATABASE TEST
CONTAINMENT = PARTIAL;
go
How to create a user inside a Contained Database ?
A user can be created as a normal user in contained database. The following code, will create a new user name "usr_TEST" inside a TEST Contained database.
USE TEST;
go
-- Create a contained SQL Server Authentication user
CREATE USER usr_TEST
WITH PASSWORD = 'TEST@123$';
go
How to connect to Contained database using a user which exists in Contained Database
While making a connection to SQL Server, we need to specify the [Contained Database name] as default database during the connection.
How The Authentication Process works in case of Contained Database ?
Authentication process can be understood using the following flowchart.
Thanks to :- http://sqlserver-training.com/what-is-contained-database-in-sql-server/-
1.Contained database is a new feature launched in SQL Server 2011.
2.This model clearly separates the database application from the management of SQL Server. Users can connect to the database without authenticating a login at the Database Engine level.
3.Applications within contained databases can be isolated from other databases from the instance of SQL Server, on which they reside, simplifying development and management.
A Video Tutorial and a Presentation on Contained Databases can be downloaded from here http://sqlserver-training.com/video-sql-server-contained-database/-
Why Contained Databases feature was Required ?
One of the problems facing the existing database model is data being left behind when an application is moved from one instance to another. Some data connected with an application, (for example, login information and agent job information) is currently stored within the instance instead of inside the database and When you move a non-contained database application from one instance to another instance of SQL Server, this data is left behind.
Later On, DBA’s nee to identify the data which was left behind and move it with your application to the new instance of SQL Server. This process can be time consuming and difficult.
What all is being saved inside a Contained Database?
The contained database, keeps all necessary information and objects in the database, for example
•Tables
•Functions
•Constraints
•Schemas
•types.
It also stores all application-level objects in the database, including
•Logins
•Application-level agent jobs
•Persisted error messages
•Linked server information
•System settings
What are the Benefits of using Contained Database ?
Contained databases can be easily moved to another server and start working instantly without the need of any additional configuration like adding user, mapping SID’s again. As Contained database have no external dependencies.
What type of Authentication Modes are supported by Contained Database?
Contained Databases supports
•SQL Server Authentication
•Windows Based Authentication
A contained user is a user without a login in the master database which resides in a Contained Database and can connect to this database specifying its credentials in the connection string. This user doesn’t exists in master’s syslogins tables and will not even show up when you list all SQL Users.
How we create a Contained Database ?
Prior to create a Contained database we need to enable "contained database authentication" property at SQL Server Level. This can be enabled using the following code.
-- Enable contained database authentication on the instance of SQL Server
-- This can done by A member of the sysadmin fixed server role
sp_configure 'show advanced', 1;
RECONFIGURE WITH OVERRIDE;
go
sp_configure 'contained database authentication', 1;
RECONFIGURE WITH OVERRIDE;
go
How to create Users in Contained Databases ?
A Contained database can be created as a normal user database, but with a property named "CONTAINMENT".
Following code, will create a database named "TEST" as contained database.
-- To create contained db you have to specify CONTAINMENT property
CREATE DATABASE TEST
CONTAINMENT = PARTIAL;
go
How to create a user inside a Contained Database ?
A user can be created as a normal user in contained database. The following code, will create a new user name "usr_TEST" inside a TEST Contained database.
USE TEST;
go
-- Create a contained SQL Server Authentication user
CREATE USER usr_TEST
WITH PASSWORD = 'TEST@123$';
go
How to connect to Contained database using a user which exists in Contained Database
While making a connection to SQL Server, we need to specify the [Contained Database name] as default database during the connection.
How The Authentication Process works in case of Contained Database ?
Authentication process can be understood using the following flowchart.
Thanks to :- http://sqlserver-training.com/what-is-contained-database-in-sql-server/-
New Features of SQL Server "Denali"
1. SQL Server AlwaysOn
Denali's AlwaysOn includes features to help simplify high-availability environments and maximize hardware investments. One of these features is support for multi-site clustering, also referred to as multi-subnet clustering. Windows Server Core support is also included. It attempts to reduce SQL Server downtime by eliminating 50 to 60% of the reboots required by OS patching.
2. High Availability and Disaster Recovery
HADR, High Availability and Disaster Recovery, is also introduced with this latest version. With it comes the concept of Availability Groups. Availability Groups enable a set of one or more related SQL Server databases to define one or more secondary copies. The source set is referred to as the primary replica and the failovers as secondary replica. One of the secondary replicas can be active, allowing for read-only connections. Viola, instant reporting database! For more information on HADR, see http://msdn.microsoft.com/en-us/library/ff877884(v=SQL.110).aspx.
3. Juneau
Juneau is the code name for the new SQL Server Development Tools. Its purpose is to provide a single development environment for all database related project types including bringing BIDS (Business Intelligence Development Studio) and SSMS (SQL Server Management Studio) into the same IDE.
4. Apollo
Apollo is the codename for Column-based Query Accelerator. I have to admit that this is the feature I'm most excited about. I watched some impressive demos showing how much performance gain is realized on very large result set queries. Microsoft claims an approximate 10 times increase in query performance.
Columnstore Indexes are what is set up to provide for this increase in query performance. Essentially, instead of storing rows on pages, columns are stored individually on a set of pages. Algorithms can then be used to compress the redundancy out of the column. The whitepaper on the new Columnstore Index points out the following benefits:
• only the columns needed to solve a query are fetched from disk (this is often fewer than 15% of the columns in a typical fact table),
• it's easier to compress the data due to the redundancy of data within a column, and
• buffer hit rates are improved because data is highly compressed, and frequently accessed parts of commonly used columns remain in memory, while infrequently used parts are paged out.
5. Crescent
This cool new tool aimed at the business user is actually still yet to come. Project "Crescent" furthers Business Intelligence for everyone by providing a powerful and speedy data visualization tool in the browser. It has a feature called storyboarding which allows the user to create multiple snapshots of the data in order to tell a story about that data. Once the snapshots are created, the storyboard can be exported to PowerPoint where the data is displayed live inside the PowerPoint slides. The demos of this product are a must-see. Below are two links. The last is the keynote from Day 1 of the PASS Summit (by Ted Kummert, Microsoft Sr. Vice President, Business Platform Division) and contains a lot more than just Project Crescent.
Data Visualization Done Right: Project Crescent
PASS Summit 2010 Day One Live Streaming Keynote
6. FileTable
The new FileTable feature takes storing blob data to a whole new level. With Denali, you can now define a whole table as a FileTable and point it to a folder on a file system. When files are written to that folder, they are available within that FileTable. They can be queried, backed up, and restored right along with the rest of your application data.
7. Integration Improvements
Several improvements are coming in Denali to make creating SSIS packages more efficient. One of these changes is the new Undo and Redo buttons in the control and flow designers. There are also source and destination assistants available to help set up sources and destinations.
More importantly, though, new knowledge-driven data cleansing capabilities are being delivered as Data Quality Services. Additionally, Impact Analysis and Lineage can be used to predict changes and troubleshoot packages.
8. Contained Databases
http://www.youtube.com/watch?v=CKrH5x7KxfQ&feature=player_embedded#at=488
Contained Databases is a concept where databases are not tied to the instance they reside on. Everything about a database is contained in that database without today's coupling to the database engine (SQL Server instance) it's running under.
Specifically, users are no longer tied to logins on the instance. I'm actually pretty excited about this. I can't tell you how much time this will save, no longer needing to fix orphaned users in development, test, and stage when we pull down production databases to our non-production environments.
9. Spatial Features
SQL Server spatial features have been greatly expanded with this new release. Highlights include new CircularArc subtypes, support for objects bigger than a logical hemisphere (including a new FULLGLOBE data type), new methods for the geography type, spatial index improvements, Nearest Neighbor Query Plan, and support for persisted computes columns.
To get more detail on these features and more, the New Spatial Features Whitepaper can be downloaded from Microsoft.
Conclusion
Intrigued by the new features of SQL Server 11? You can download the CTP at http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9 to try it for yourself.
Denali's AlwaysOn includes features to help simplify high-availability environments and maximize hardware investments. One of these features is support for multi-site clustering, also referred to as multi-subnet clustering. Windows Server Core support is also included. It attempts to reduce SQL Server downtime by eliminating 50 to 60% of the reboots required by OS patching.
2. High Availability and Disaster Recovery
HADR, High Availability and Disaster Recovery, is also introduced with this latest version. With it comes the concept of Availability Groups. Availability Groups enable a set of one or more related SQL Server databases to define one or more secondary copies. The source set is referred to as the primary replica and the failovers as secondary replica. One of the secondary replicas can be active, allowing for read-only connections. Viola, instant reporting database! For more information on HADR, see http://msdn.microsoft.com/en-us/library/ff877884(v=SQL.110).aspx.
3. Juneau
Juneau is the code name for the new SQL Server Development Tools. Its purpose is to provide a single development environment for all database related project types including bringing BIDS (Business Intelligence Development Studio) and SSMS (SQL Server Management Studio) into the same IDE.
4. Apollo
Apollo is the codename for Column-based Query Accelerator. I have to admit that this is the feature I'm most excited about. I watched some impressive demos showing how much performance gain is realized on very large result set queries. Microsoft claims an approximate 10 times increase in query performance.
Columnstore Indexes are what is set up to provide for this increase in query performance. Essentially, instead of storing rows on pages, columns are stored individually on a set of pages. Algorithms can then be used to compress the redundancy out of the column. The whitepaper on the new Columnstore Index points out the following benefits:
• only the columns needed to solve a query are fetched from disk (this is often fewer than 15% of the columns in a typical fact table),
• it's easier to compress the data due to the redundancy of data within a column, and
• buffer hit rates are improved because data is highly compressed, and frequently accessed parts of commonly used columns remain in memory, while infrequently used parts are paged out.
5. Crescent
This cool new tool aimed at the business user is actually still yet to come. Project "Crescent" furthers Business Intelligence for everyone by providing a powerful and speedy data visualization tool in the browser. It has a feature called storyboarding which allows the user to create multiple snapshots of the data in order to tell a story about that data. Once the snapshots are created, the storyboard can be exported to PowerPoint where the data is displayed live inside the PowerPoint slides. The demos of this product are a must-see. Below are two links. The last is the keynote from Day 1 of the PASS Summit (by Ted Kummert, Microsoft Sr. Vice President, Business Platform Division) and contains a lot more than just Project Crescent.
Data Visualization Done Right: Project Crescent
PASS Summit 2010 Day One Live Streaming Keynote
6. FileTable
The new FileTable feature takes storing blob data to a whole new level. With Denali, you can now define a whole table as a FileTable and point it to a folder on a file system. When files are written to that folder, they are available within that FileTable. They can be queried, backed up, and restored right along with the rest of your application data.
7. Integration Improvements
Several improvements are coming in Denali to make creating SSIS packages more efficient. One of these changes is the new Undo and Redo buttons in the control and flow designers. There are also source and destination assistants available to help set up sources and destinations.
More importantly, though, new knowledge-driven data cleansing capabilities are being delivered as Data Quality Services. Additionally, Impact Analysis and Lineage can be used to predict changes and troubleshoot packages.
8. Contained Databases
http://www.youtube.com/watch?v=CKrH5x7KxfQ&feature=player_embedded#at=488
Contained Databases is a concept where databases are not tied to the instance they reside on. Everything about a database is contained in that database without today's coupling to the database engine (SQL Server instance) it's running under.
Specifically, users are no longer tied to logins on the instance. I'm actually pretty excited about this. I can't tell you how much time this will save, no longer needing to fix orphaned users in development, test, and stage when we pull down production databases to our non-production environments.
9. Spatial Features
SQL Server spatial features have been greatly expanded with this new release. Highlights include new CircularArc subtypes, support for objects bigger than a logical hemisphere (including a new FULLGLOBE data type), new methods for the geography type, spatial index improvements, Nearest Neighbor Query Plan, and support for persisted computes columns.
To get more detail on these features and more, the New Spatial Features Whitepaper can be downloaded from Microsoft.
Conclusion
Intrigued by the new features of SQL Server 11? You can download the CTP at http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9 to try it for yourself.
Subscribe to:
Posts (Atom)
-
ERROR: report server remote procedure call (RPC) end point. Verify that the Report Server Windows service is running, and then retry the o...
-
Error messages: · The process could not execute 'sp_repldone/sp_replcounters' on ' SCKNLSSI '. (Source:...
-
Error Message: Replication-Replication Distribution Subsystem: agent DBA\DBA-EPDW-EPDW-DB6C\DB6C-5 failed. The subscription(S) have been...