Enable and Configure FILESTREAM with SQL Server 2008 R2

To enable and change FILESTREAM settings

1.On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.

You may get a "Cannot connect to WMI provider" error if your instance is an upgraded instance or an uninstalled instance.

To resolve this, locate the file "sqlmgmproviderxpsp2up.mof"

Most probably the location will be at

C:\Program Files (x86)\Microsoft\Microsoft SQL Server\100\Shared\
OR
C:\Program Files (x86)\Microsoft SQL Server\100\Shared

run the following command C:\Program Files (x86)\Microsoft SQL Server\100\Shared\mofcomp sqlmgmproviderxpsp2up.mof

2.In the list of services, right-click SQL Server Services, and then click Open.

3.In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.

4.Right-click the instance, and then click Properties.

5.In the SQL Server Properties dialog box, click the FILESTREAM tab.

6.Select the Enable FILESTREAM for Transact-SQL access check box.

7.If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.

8.If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.

9.Click Apply.

10.In SQL Server Management Studio, click New Query to display the Query Editor.

11.In Query Editor, enter the following Transact-SQL code:

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

12.Click Execute.

13.Restart the SQL Server service.

Run the following Query to confirm SELECT SERVERPROPERTY ('FilestreamConfiguredLevel'),SERVERPROPERTY ('FilestreamEffectiveLevel');

NON sa users get access denied in OLE DB query like "Ad hoc access to OLE DB provider 'MSOLAP' has been denied"

Error:

"Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'MSOLAP' has been denied. You must access this provider through a linked server.
"

OPENROWSET can be used to access remote data from OLE DB data sources
only when the "DisallowAdhocAccess" registry option is explicitly set to
0 for the specified provider, and the Ad Hoc Distributed Queries
advanced configuration option is enabled. When these options are not
set, the default behavior does not allow for ad hoc access.

In BOL, it also states "This option controls the ability
of non-administrators to run ad hoc queries. Administrators are not affected
by this option."

So the easiest way to make this "DisallowAdhocAccess" registry option is

Connect to the SQL instance using "Management Studio"
Right click and select the properties for the corresponding provider
under Server Objects -> Linked Servers -> Providers
In this example it is the MSOLAP provider...




Enable the Option "Disallow adhoc access"....
This will create a registry entry DisallowAdHocAccess with value 1 under
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\the sql install location\Providers

Open regedit and search for "DisallowAdHocAccess" , change the value from 1 to 0 for the
appropriate provider.....(in this example it is MSOLAP privider...

Installing a Cluster on Windows Server 2008

Installing a Cluster on Windows Server 2008

1. Select 'Add Features' from Server manager.



2. Select 'Failover Clustering'


3. Complete the Install



Setup 'Failover Clustering'

1. Select 'Failover Cluster Management' from Administrative Tools.



2. Select 'Validate a configuration'





3. Select all nodes that are participating in the Cluster. click 'Browse' to select nodes.





4. Complete the Validation.









5. Select 'Create a Cluster'




6. Clik 'Browse' to select all the nodes that are going to participate in the new Cluster







7. type the a name for the new cluster under 'Cluster Name'
Type an IP Address for the new Cluster under 'Address'


8. complete the install.










Moving the 'Cluster Group to another Node'


Since the GUI does not allow to move the 'Cluster Group' into the other node,
select command prompt and type the following command to mvoe the group
to another NODE.





Last Modified July 13 2010.

Multipathing(MPIO) Support in Windows Server 2008

A growing number of organizations require that their data be available 24x7, year round. To meet this requirement, centralized storage must be readily available and immune to outages at all times .The Microsoft Multipath I/O (MPIO) framework provides support for multiple data paths to storage to improve fault tolerance of connection to storage, and may in some cases provide greater aggregate throughput by using multiple paths at the same time:

Installing MPIO on Windows 2008:

From 'Server Manager', select 'Add Features'




Select 'Multipath I/O'







To configure MPIO for your Storage, Select MPIO from 'Administrative tools'



Specify the Device HW id for your Storage
(For this Example we are using the 3PAR Storage)





Configure the Load Balance policy for a LUN:

MPIO LUN load balancing is integrated with Disk Management. To configure MPIO LUN load balancing, start the Disk Management. Right click on the disk, then properties




Select the 'MPIO' tab




Change the 'Load Balance Policy' from 'Fail Over Only' to 'Round Robin'



Click OK to finish.



More details of MPIO on Windows:

http://download.microsoft.com/download/3/0/4/304083F1-11E7-44D9-92B9-2F3CDBF01048/MPIO.doc


Last Modified : 7th July 2010

How to create a Linked Server to MySQL from SQL Server 2008

Download Connector/ODBC for mySQL from

http://dev.mysql.com/downloads/connector/odbc/5.1.html


Install the Connector/ODBC driver on the Server where SQL Server 2008 installed.
Create an ODBC System DSN using the installed MySQL Driver



Select System DSN , Add



Select the MySQL Driver then Click 'Finish'



Fill out the appropriate information and click 'Test'






Create a Linked Server at SQL Server 2008 Management Studio.

Under 'Server Objects', right click on 'Linked Servers' and select 'new Linked Server'



At 'Link Server' type a Name
On 'Other data sources' , Select 'Microsoft OLE DB Provider for ODBC Drivers'
Type the DSN name you just created at 'Data Source'



at security , specify the appropriate login information



on 'Server options' select 'True' for both 'RPC' and 'RPC Out'




Modify the properties of the MSDASQL provider:




Use OPENQUERY to Query the Linked Server.

SELECT TOP 10 * FROM OPENQUERY(MKMYSQL, 'SELECT * FROM Users')

Last Modified : June 23, 2010

Connect to SSIS Service on Machine "SQLSErver" failed: Access is denied

Connect to SSIS Service on Machine "SQLSErver" failed: Access is denied




Connecting to a Remote Integration Services Server

Connecting to an instance of Integration Services on a remote server, from SQL Server Management Studio or another management application, requires a specific set of rights on the server for the users of the application

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.







Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in.




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.



Select the DCOM Config node, and then select MsDtsServer100 in the list of applications that can be configured



Right-click on MsDtsServer100 and select Properties.

In the MsDtsServer Properties dialog box, select the Security tab.





Under Launch and Activation Permissions, select Customize, then click Edit to open the Launch Permission dialog box.

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.









Click OK to close the dialog box

Under Access Permissions, repeat above step to assign the appropriate permissions to the appropriate users and groups.






Close the MMC snap-in

Restart the Integration Services service




Last modified : June 21, 2010