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}
......

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

Introducing LocalDB, an improved SQL Express

http://blogs.msdn.com/b/sqlexpress/archive/2011/07/12/introducing-localdb-a-better-sql-express.aspx

Data Compression -Page Level

http://social.technet.microsoft.com/wiki/contents/articles/23866.data-compression.aspx

Server UpTime OR Online Duration

net stats srv | find "Statistics"

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

Retore T-Log With Move

Here in the syntax we can you with move.



RESTORE LOG [Student] FROM
DISK =  N'P:\student_after_secfile.trn' WITH  FILE = 1,NORECOVERY,

MOVE N'Student_new' TO N'P:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Student_new2.ndf',
NOUNLOAD, STATS = 10
GO



Another Scenario While Restoring Tlog and Space Crunch:-

RESTORE LOG [DB] FROM
DISK = N’D:\DB.trn’
WITH FILE = 1,
STANDBY = N’D:\ROLLBACK_UNDO_DB.BAK’,
MOVE N’DBFILENAME′ TO N’P:\Data\DB.ndf’,
NOUNLOAD, STATS = 10
GO