Microsoft Certified Technology Specialist: Sql Server 2008…

mctsrgb

Passed my Microsoft Cerfification Exam on first attempt on Tuesday June 2nd! Score: 814

The certificate says l’m now recognized as a MCTS – Microsoft Certified Technology Specialist: SQL Server 2008, Implementation and Maintenance. The exam is the MCTS 70-432. Next goal is the 70-450 which has no study book in print, and the only material l can find so far is the CBT-NUGGETS training video on Exam 70-450 which is very good.

Materials l used to study other than multiple SQL Server instances on my laptop, Virtual PC Windows 2008 Machines with SQL Server 2008 installed – for database replication, mirroring and failover clustering practise!!!
– MCTS Self-Paced Training Kit (Exam 70-432): Microsoft® SQL Server® 2008 – Implementation and Maintenance by Mike Hotek (Very good, in-depth all round material, lab oriented book with real life stories). This seems to be the only main book out there.
– Self Test Software for MCTS 70-432, bought the download version of this excellent practise test program.
– Microsoft Sql Server 2008 Books on line (download the latest update from Microsoft)
– Took the official Microsoft 6231 course at SQLSoft (Beaverton)
– Books on Microsoft SQL Server 2005 exam 70-431

Microsoft Sql Server Administration…

Useful commands I’ve found from various sources for Microsoft Sql Server 2005/2008 include:

EXEC sp_configure
GO
–Instance level principals.
SELECT * FROM sys.asymmetric_keys
SELECT * FROM sys.certificates
SELECT * FROM sys.credentials
SELECT * FROM sys.linked_logins
SELECT * FROM sys.remote_logins
SELECT * FROM sys.server_principals
SELECT * FROM sys.server_role_members
SELECT * FROM sys.sql_logins
SELECT * FROM sys.endpoints
GO

–Database level principals.
SELECT * FROM sys.database_principals
SELECT * FROM sys.database_role_members
GO
ALTER LOGIN sa WITH NAME = hiddenSaAccount
GO
–Check your user execution context
SELECT SUSER_SNAME(), USER_NAME()
GO
–View the list of objects in the database
SELECT * FROM sys.objects
GO
–Change user context
EXECUTE AS USER = ‘AnotherUserName’
GO
–Return to your login
REVERT
GO
–explore database files
select * from AdventureWorks.sys.database_files
select * from master.sys.database_files
select * from msdb.sys.database_files
select * from tempdb.sys.database_files
–explore endpoints
select * from sys.endpoints
select * from sys.tcp_endpoints
select * from sys.http_endpoints
select * from sys.database_mirroring_endpoints
select * from sys.service_broker_endpoints

–Security

SELECT * FROM sys.symmetric_keys
GO

SELECT * FROM sys.certificates
GO

Error: Connect to SSIS Service on machine “” failed:

On a new instalaltion of SQL Server 2008, running on Windows Server 2008, l cannot connect to SSIS from SSMS (Sql server management console) 2008 running on a client workstation. I can connect from the local server end.
I tried adding the users Group to the permissions option on Component Services/MsdtsServer100 as suggested by many sites since this problem is common also in SSIS/Sql Server 2005. This didnt work.
Solution was to add the user group to the Windows Server Distributed COM Users group.
See also: Technet link