Tag Archives: Jugal Shah

Using PowerShell to Register All SQL Instances Into Central Management Server

Problem

Managing multiple SQL Servers has its challenges. One of the big challenges is having to collect data from each of the servers you manage and figure out which servers need attention. SQL Server has introduced a new feature called Central Management repository which can be used to manage multiple instances. One of the challenges to using CMS is that you have to register all the SQL Servers manually into CMS.  The below article will guide you on how to register hundreds of servers in a second for SQL Server CMS.

Solution

http://www.mssqltips.com/sqlservertip/2658/using-powershell-to-register-all-sql-instances-into-central-management-server/

How to connect the SQL Server running on the different TCP/IP port?

In case you have configured SQL Instance to use the static TCP/IP port number. You can connect SQL Server as below using SSMS.

Script to create a Database with different collation

One of my blog reader has asked how to create the database with different collation. You can create the database with different collation using below script.

create database SQLDB collate Latin1_General_CS_AS;
go
create database SQLDB1 collate Latin1_General_CI_AS;
go

SELECT DATABASEPROPERTYEX('SQLDB', 'Collation') SQLDB;
SELECT DATABASEPROPERTYEX('SQLDB1', 'Collation') SQLDB1;

SQL Server Agent Job Owner

It is a best practice to keep the SQL Agent Job owner to SA or Service Account. Please don’t make the job owner to any user’s windows ID. If the employee left the company his ID will be terminated and access will be revoked.
You can find out the jobs owner by executing the below query.

SELECT 
j.name,
l.[name] as [JobOwner]
FROM MSDB.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
ORDER BY j.[name]

You can change the job owner by executing the below query.

EXEC MSDB.dbo.sp_update_job 
@job_name = 'Job Name', 
@owner_login_name = 'sa'

Steps to Save/Store file into Database

You can use the below scripts to store/save the file into SQL Server database table. Please note it is not recommended to store file into database. You can store the file on file system and path in the database.

use sqlDBPool
--documents table will store files into varbinary field
--drop table documents
create table documents
(
	documentID int identity(1,1),
	doctype char(5),
	document varbinary(max)
)


--script to store/save document into table
insert into documents 
 Select 'xls', (SELECT * FROM OPENROWSET(BULK N'C:\JSpace\book1.xls', SINGLE_BLOB) AS document) document
go

select * from documents