DBA WORLD

03 May 2014

WMI Provider Error - Access is denied - while changing SQL Server account password

To give this right:
Login with administrative privileges to SQL Cluster nodes.
Click Start, type mmc in the Search programs and files box, and then press ENTER.
On the File menu, click Add/Remove Snap-in.
Under Available snap-ins, double-click WMI Controls, and then:

Select the computer that you manage. Connect to: select Local Computer, click Finish. Click OK.
On Console Root tree, select WMI Controls, right click, select Properties, then go to Security tab, locate root\Microsoft\SqlServer\ ComputerManagement10 (or 11 for SQL Server 2012) namespace then click Security.
Add new domain user to Group or Users Names
Select new domain user in Group or Users Names and Allow Read Security for the Permissions of new account, click OK.

29 April 2014

Reset SA Password For Multiple Instance

Power Shell Script :-

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop
Foreach ($list In $(Get-Content -Path p:\serverList.txt)) {
   $server = $list.split(":")
   $srvObject = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $server[0]
   $srvObject.Logins.Item('sa').ChangePassword($server[1])
   $srvObject.Logins.Item('sa').Alter()
}

The above uses the file "ServerList.txt", where we neeed to store the instance names and corresponding password in the following pattern,

[Instance Name]:{PASSWORD}
[Instance Name2]:{PASSWORD}
......

22 April 2014

no connection could be made because the target machine actively refused SQL Server OLAP

ERROR :- No connection could be made because the target machine actively refused it sql server olap

Event Viewer :- Failed to initialize SQLSQM timer. One of the following can be the source of the problem: registry doesn't contain all necessary information, server instance name can not be retrieved correctly, failure during timer creation, etc.

Solution :-

The only supported way to connect to a clustered SSAS instance is to use the “virtual server name” (also called network name). Usage of the instance name like in “VirtualServerName\InstanceName” is not supported! One reason being that all clustered instances will always run on port 2383 and thus we don’t need to specify the instance name. The other reason being that the SSAS part of SQL Browser service functionality (OLAP redirector service) is not cluster-aware and we cannot guarantee that the content of “msmdredir.ini” is always updated correctly.
In this context “not supported” does not mean that connectivity via instance name does not work. In fact it usually does work. But we cannot guarantee that it will always work in a stable fashion. Therefore our strong recommendation is to always use “VirtualServerName” (=Network name) for establishing connections to a clustered SSAS instance.

More Info :- http://blogs.msdn.com/b/as_emea/archive/2014/01/08/connecting-to-clustered-ssas-instances.aspx

15 March 2014

Server UpTime OR Online Duration

net stats srv | find "Statistics"

11 March 2014

Data Compression

Syntax:-
=================

USE [student]
GO
Select
'ALTER TABLE ['+B.Name+'].['+ A.Name+ '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'
from sys.objects A INNER JOIN
sys.schemas B on A.Schema_id=B.Schema_id
where type = 'U' and A.name not like 'dtproperties'

ALTER TABLE [dbo].[Category] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)

For All Tables :-
===============
EXEC sp_MSforeachtable @command1 = 'alter table ? REBUILD WITH (DATA_COMPRESSION = PAGE);'

To Verify:-
========

SELECT
SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName]
,OBJECT_NAME(sys.objects.object_id) AS [ObjectName]
,[rows]
,[data_compression_desc]
,[index_id] as [IndexID_on_Table]
FROM sys.partitions
INNER JOIN sys.objects
ON sys.partitions.object_id = sys.objects.object_id
WHERE data_compression > 0
AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS'
ORDER BY SchemaName, ObjectName