Powershell - Remote

Powershell to remote to computers to get SQL server versions

$($servers = Import-Csv c:\centers.csv
$errorlog = "C:\Error.txt"
foreach($server in $servers){
Try
{
$con = ("server=" + $server.Host_Name + "\sqlexpress;database=master;Integrated Security=sspi")
$cmd = "select serverproperty('servername') as Name,serverproperty('productversion') as Version,serverproperty('productlevel') ServicePack,serverproperty('edition') as Edition"
$da = New-Object System.Data.SqlClient.SqlDataAdapter($cmd,$con)
$dt = New-Object System.Data.DataTable
$da.fill($dt) | out-null
$dt
}
Catch
{
"Fail to remote to $server :$_" |add-Content $errorlog
}
})| Export-csv C:\SQLServerVersion.csv -NoTypeInformation

Daily DBA Cheatsheet

1. Creating linked server with local name

EXEC master.dbo.sp_addlinkedserver @server = N'mylocalname', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'servername'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'mylocalname,@useself=N'False',@locallogin=NULL,@rmtuser=N'dblink',@rmtpassword='########'

2. SQL Server Data Type Conversion Chart

http://www.microsoft.com/en-us/download/details.aspx?id=35834

3. Filtered Index

I want to create a unique key with multiple null. To fulfil this, I can create unique filtered index.

Create Unique NonClustered Index NonClustomer_Emailaddress on Customer
(emailaddress)where emailaddress is not null

4. sys.processes -  Find how many processes running on a instance

SELECT DB_NAME(dbid) as 'Database Name', COUNT(dbid) as 'Total Connections' FROM master.dbo.sysprocesses WITH (nolock)WHERE dbid > 0GROUP BY dbidSELECT
@@MAX_CONNECTIONS AS 'Max Allowed Connections'

5. Server Edition & Version

select
SERVERPROPERTY('Edition'),
SERVERPROPERTY('ProductLevel'),
SERVERPROPERTY('BUildClrVersion'),
SERVERPROPERTY('ProductVersion')


6. Row Count ( The following code is from http://www.sqlservercentral.com/articles/T-SQL/67624/)

-- Shows all user tables and row counts for the current database 
-- Remove is_ms_shipped = 0 check to include system objects 
-- i.index_id < 2 indicates clustered index (1) or hash table (0) 
SELECT o.name, 
 ddps.row_count 
FROM sys.indexes AS i 
 INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID 
 INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID 
 AND i.index_id = ddps.index_id 
WHERE i.index_id < 2 
 AND o.is_ms_shipped = 0 
ORDER BY o.NAME 

7. DBCC

The following DBCC, DMVs and sp commands help me.

dbcc inputbuffer(SPID)

dbcc sqlperf(logspace)

sp_who2

sp_configure

sp_spaceused

select * from sys.sysprocesses

SELECT file_id, name, physical_name, (size * 8 /1024.0) AS SizeMB FROM sys.database_files

select * from sys.masterfiles

msdb.dbo.sp_help_job

exec master.dbo.xp_sqlagent_enum_jobs 1,garbage 

SQL Server 2012 on Wins 2012

SQL Server 2012 standard edition on Windows Server 2012 Data Center

Today, I received a brand new virtual machine installed with Windows Server 2012 Data Center edition from network administrator.

Here is specs of the new machine:

Operating system : 64 - bit Windows server 2012 Data Center

Processor: 2 Dual core processors (4 virtuals CPUS, 2 Sockets)

Memory : 12 GB

Local Drive : 80 GB

Data Drive : 200 GB (RAID 5)

Log Drive : 100 GB (RAID 10)

I was surprised by windows server 2012 UI.Window Server 2012 looks like it was truly made for the computer illiterate. Sad!  To make my life easy on the box, I pinned all tools/apps I needed on Task bar.

First, I will need to install SQL Server 2012 standard edition in my new server.
Before installing SQL Server 2012, I installed .net 3.5 from add features via server manager. net.3.5 installation is very important step prior to SQL Server 2012 is installed. Because it will fail SQL Server installation.

Install .net 3.5 from add and remove features. We will need to have window 2012 installer to point for installation. After installing .net 3.5, run windows update. Then, restart the server.

The server came back up. I have maunted SQL Server 2012 iso and it is ready for me to install. So, I clicked on the setup.exe and I recived .net error.

An error occurred creating the configuration section handler for userSettings/Microsoft.SqlServer.Configuration.LandingPage.Properties.Settings: Could not load file or assembly 'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies.

Repairing the .NET 4.0 framework didn't solve the issue. Neither did removing all SQL stuff through 'Add or Remove Programs'. Looking into the error a bit further...
 The system cannot find the file specified. (C:\Documents and Settings\_USERNAME_\Local Settings\Application Data\Microsoft_Corporation\LandingPage.exe_StrongName_ryspccglaxmt4nhllj5z3thycltsvyyx\10.0.0.0\user.config line 5) ---> System.IO.FileNotFoundException: Could not load file or assembly 'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. The system cannot find the file specified.

So the problem probably wasn't that System.dll couldn't be found, but some user.config file for the landingpage executable.

The solution is as simple as it is radical: remove the entire (temporary) folder 'C:\Documents and Settings\_USERNAME_\Local Settings\Application Data\Microsoft_Corporation' (where USERNAME_ is, of course, the current username). After this the setup should start up without any problems.

After deleting Microsoft _Corporation folder, the error is fixed.

SQL Server 2012 installation continues...

I left reporting service at last since I want to make sure Database engine is installed successfully. After DB engine is done successfully, reporting service installation continues..

To test client connection, try to connect from your local machine to database server. I recived the error.

To fix this, enable TCP port 1433 in windows firewall.

SQL server 20012 SP1

Service pack 1 can be downloaded from microsoft web site.

http://www.microsoft.com/en-us/download/details.aspx?id=35575

So, I downloaded service pack1 from microsoft and applied it. Now, I will need to restart the server. Go to settings, and click on power button to restart the server.

The server came back up. To check the database is applied service pack1 , I run the following script.

Next, I want to make sure all the services are running on service account  and automatic start up. I added the service account in administrator group.

Last, I am ready to migrate my database from development to this brand new server. I would like to thank to network administrator to rebuild this machine with windows server 2012 data center edition.