Move SQL database

http://msdn.microsoft.com/en-us/library/ms345408.aspx

Managed Service Account–SQL Server 2012

As you know there are so many security measures are taken during every release of SQL Server. This version of SQL Server will be using Managed Service account to run SQL server related services. In other terms earlier versions we used Local system account as service account for all SQL Server related services however in this version we be will using individual local accounts for all SQL server related services. In this case whatever application runs on local system are allowed to access SQL Server. So to overcome this MS team has started using managed service accounts in SQL Server.

Let me give you a brief about Manage service account then we will discuss about the new changes related to service account in SQL Server 2012.

What is Managed Service Account?

Managed Service Account (MSA) is a new type of account that’s supported in Windows 7 & Windows 2008 R2. Main reason for this new account type is to isolate one service from another. In earlier operating systems we will run the services either in Localsystem or Domain accounts. Let’s assume you run SQL Server using Local system that means any other service running in Local system can connect to SQL Server and retrieve the data, this seems to be a loop hole when you run it in localsystem. We can use domain account to avoid this scenario however maintaining these domain accounts will take considerable time. In addition to this most of the organizations won’t change domain service account password in a period of time that means if someone knows the password he will be able to connect to SQL Server anytime as the password remains the same forever.

Considering all these things in mind they have added the new account feature in Windows 7 & Windows 2008 R2. Below are the advantages of Managed service account

• Automatic password management. Password for MSA will be automatically changed every 30 days. However there is a known issue when the password gets changed it can cause a failed authentication attempt using old password, to overcome this you need to install a patch, refer KB article http://support.microsoft.com/kb/2494158 for more details.
• SPN management is made simpler (domain need to be in Windows 2008 R2 functional level) for these accounts, which allows service administrators to set SPN’s for these accounts. For windows 2003 and windows 2008 functional level, you can update the schema to support this.

You can also create MSA for your own application service. Crucial part is that you don’t have a GUI to create \ configure a MSA for your service, you need to work with powershell cmdlets. For further information on MSA check the KB articles below
http://technet.microsoft.com/en-us/library/dd548356.aspx
http://technet.microsoft.com/en-us/library/ff641729%28v=ws.10%29.aspx

What’s added in SQL Server 2012

In earlier versions (from SQL 2005) of SQL Server, as part of Service account security standards they created local groups in the computer and granted necessary permission for the groups where ever applicable. However in SQL Server 2012 that’s not applicable, they will create individual managed service account for each SQL Server service. This means all SQL Server related services will run on their own service account there by isolating each services. You can see from the image below each service have different service account and each of these account have their own SID

Naming convention for the service accounts is as below

Service Name	Instance	Service Account Naming Convention
Database Engine	Default	MSSQLServer
Agent Service	Default	SQLSERVERAgent
Reporting Server	Default	ReportServer
Analysis Service	Default	MSSQLServerOLAPService
Fulltext Service	Default	MSSQLFDLauncher
Database Engine	InstanceName	MSSQL$InstanceName
Agent Service	InstanceName	SQLAgent$InstanceName
Reporting Server	InstanceName	ReportServer$InstanceName
Analysis Service	InstanceName	MSOLAP$InstanceName
Fulltext Service	InstanceName	MSSQLFDLauncher$InstanceName
Integration Service	Not applicable	MSDtsServer110

I have a curiosity to check admin permission associated to these accounts, I could see SA access granted only to SQL agent service account and not to database engine, that’s a classic example how they grant only required permission

Finally I’ve checked the administrators group in computer to check is any SQL related groups got created. Yes there are couple of groups still get created in computer for Browser service and analysis services. Analysis service account and browser service account are part of  analysis group & browser group respectively.

From the screenshot above it’s clear that analysis groups are created per instance. Thus MSA is a added security hardening step in SQL Server 2012 to isolate all SQL server related services, thus no more access using local system account.

http://sql-articles.com/articles/general/managed-service-accountsql-server-2012/

Read more about service account http://msdn.microsoft.com/en-us/library/ms143504.aspx#VA_Desc

Shrink SQL 2000 log file

BACKUP LOG MyDatabaseName WITH TRUNCATE_ONLY

DBCC SHRINKFILE(MyDatabaseName_log, FileSize)

(where MyDatabaseName_log is the logical file name, and is the target size in MB)

The log file has now been shrunk.

Note that the log file will start expanding in normal operation, depending on the way the database is defined. Make sure that the transaction log file is big enough to store all transactions for the normal period between backups. Makes sure that you take peak system loads into consideration when determining the target size.
 

Regular log file maintenance
In normal operation, you would normally never shrink the log file. Regularly backing up the log file will remove the data used in it, and maintain free space for new transaction.