MSSQL Administration Course Notes - Day 1

Check collation

SELECT SERVERPROPERTY('collation')
SELECT DATABASEPROPERTYEX('tempdb','collation')

OR SSMS > Server > Properties > Server Collation CI means case insensitive

Quick way to format an unformatted query http://www.dpriver.com/pp/sqlformat.htm

Restart SQL Services Use SQL Server Configuration Manager over Admin Tools > Services for making changes to SQL instances. In rare examples, it doesn’t work correctly like updating service/process user if using Administration Tools > Services (Services.msc).

Forcing port number on named instance SSCM > Network Configuration > INSTANCE > IP ADDRESSES > IPALL > Change port number

SQL Aliases SSCM > Native Client Configuration > Aliases Note - SSMS is a 32Bit application so need to create alias in SSCM > Native Client Configuration (32bit) > Aliases. This needs the SQL Server Browser to be started as this is what handles the direction. http://blogs.msdn.com/b/sqlro/archive/2009/06/16/how-to-deploy-sql-server-client-aliases-using-active-directory-gpo-adm.aspx

SQL Server Architecture - Sizing requirements Guess work really! Learn from experience as guidelines vary a lot!

SQLIO Testing IO Tools - unsupported though supplied by MS SQLIOSIM - GUI SQLIO - command line http://www.brentozar.com/archive/2008/11/storage-performance-testing-with-sqlio/

Install Logs %programfiles%microsoft sql server110setup bootstraplog

Move to new server SSIS could be used instead of backup and restore to new server. SQL 2005 or above.

Store/show files in the db though also on the file system - Need to look more at this Filestream + filetable

Alter log size

ALTER DATABASE tempdb
MODIFY FILE ( NAME = templog, SIZE = 10MB);
GO

 

Move system databases http://support.microsoft.com/kb/224071

Posted in Databases, DevOps with : SQL Server, Notes