Working on SharePoint 2010 installations requires many different supporting technologies, such as the .NET framework and SQL Server. And each of these technologies has its own setup requirements. Take SQL Server for example, in SQL Server each service represents a process or a set of processes to manage authentication of SQL Server operations with Windows. There is a default configuration of services in SQL Server 2008 , and configuration options for SQL Server services that you can set during SQL Server installation. If you do not configure SQL Server properly, you have no chance of getting SharePoint Server installed or working properly. So, here’s a great link to help configure SQL Server service accounts. I’ve also provided the information here, just in case, by some slim chance, the link moved and I am unable to locate the information.

Each service in SQL Server represents a process or a set of processes to manage authentication of SQL Server operations with Windows. This topic describes the default configuration of services in this release of SQL Server, and configuration options for SQL Server services that you can set during SQL Server installation.

Depending on the components that you decide to install, SQL Server Setup installs the following services:

• SQL Server Database Services – The service for the SQL Server relational Database Engine.
• SQL Server Agent – Executes jobs, monitors SQL Server, fires alerts, and enables automation of some administrative tasks.

  Note
  For SQL Server and SQL Server Agent to run as services in Windows, SQL Server and SQL Server Agent must be assigned a Windows user account. For more information about how to customize account information for each service, see How to: Install SQL Server 2008 R2 (Setup).

• Analysis Services – Provides online analytical processing (OLAP) and data mining functionality for business intelligence applications.
• Reporting Services – Manages, executes, creates, schedules, and delivers reports.
• Integration Services – Provides management support for Integration Services package storage and execution.
• SQL Server Browser – The name resolution service that provides SQL Server connection information for client computers.
• Full-text search – Quickly creates full-text indexes on content and properties of structured and semistructured data to provide document filtering and word-breaking for SQL Server.
• SQL Server Active Directory Helper – Publishes and manages SQL Server services in Active Directory.
• SQL Writer – Allows backup and restore applications to operate in the Volume Shadow Copy Service (VSS) framework.

  Important

  Always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the SQL Server or SQL Server Agent services, or to change the password for the account. In addition to changing the account name, SQL Server Configuration Manager performs additional configuration such as setting permissions in the Windows Registry so that the new account can read the SQL Server settings. Other tools such as the Windows Services Control Manager can change the account name but do not change associated settings. If the service cannot access the SQL Server portion of the registry, the service may not start properly.

Important
For Analysis Services instances that you deploy in a SharePoint farm, always use SharePoint Central Administration to change the server accounts for PowerPivot service applications and the Analysis Services service. Associated settings and permissions are updated to use the new account information when you use Central Administration.

The remainder of this topic is divided into the following sections:

• Configuring Service StartupType
• Using Startup Accounts for SQL Server Services
• Identifying Instance-Aware and Instance-Unaware Services
• Reviewing NT Rights and Privileges Granted for SQL Server Service Accounts
• Reviewing Access Control Lists Created for SQL Server Service Accounts
• Reviewing Windows Permissions for SQL Server Services
• Reviewing Additional Considerations
• Localized Service Names

As developers, we are called upon to connect to many different sources of data. Sometimes it is a simple database, sometimes a shopping cart, sometimes and XML file. But in all of these cases we are accessing the data itself and not really affecting the data store. Occasionally, it is necessary to also perform administrative tasks on these data stores. With SQL Server there is a set of object for this specific purpose, the SQL Server Management Objects (SMO).

SMO can be used to examine a database’s objects and perform a variety of administrative tasks such as database backups, restores, and backup verification. This first example will show you how to list the available SQL Server installations on a network. This code gets a list of SQL Server instances and returns them to an ADO.NET DataTable.

DataTable dt = SmoApplication.EnumAvailableSqlServers(false);
foreach(DataRow dr in dt.Rows)
{ string sqlServerName = row[“Server”].ToString();
if(row[“Instance”] != null && row[“Instance”].ToString().Length > 0)
sqlServerName += @”\” + row[“Instance”].ToString();
}

The next step is to connect to the SQL Server that you want, and there is no special method for this. You can create a connection to the database using either Windows authentication or SQL Server authentication.

Once you connect to the server you may need to iterate through the databases on that server. To do that you will need to iterate through the DatabaseCollection. In this example you can simply create a List<string> to contain the names of the databases.

public List<string> GetDatabaseNames()
{
List<string> dbList = new List<string>();
foreach(Database db in server.Databases)
dbList.Add(db.Name);
return dbList;
}

Database Backup

Now that you have the names of your databases you will simply need to pass that name of the database to your backup or restore procedure. To backup a database you will need to perform the following steps:

1.Create an instance of the Backup class, and set the Database, Incremental, Initialize, and LogTruncation properties.

2. Set the Action property of the BackupActionType

3. Specify the filename and path of the backup file

4. Create a BackupDeviceItem and add it to the Backup class’s Devices list.

5. Finally, call the SqlBackup method of the Backup class

Sample code:

public void SQLDatabaseBackup(string dbName)
{
Backup bkDatabase = new Backup();
bkDatabase.Action = BackupActionType.Database;
bkDatabase.Database = dbName;
bkDatabase.Incremental = false;
bkDatabase.Initialize = true;
bkDatabase.LogTruncation = BackupTruncateLogType.Truncate;
string fileName = string.Format(“{0}\\{1}.bak”, backupFolder, dbName);
BackupDeviceItem dbItem = new BackupDeviceItem(fileName, DeviceType.File);
bkDatabase.Devices.Add(dbItem);
bkDatabase.SqlBackup(sqlServerName);
}

Database Restore

Now that you have a backup of your database the next last logical step is to restore the database. To restore a database you will need to perform the following steps:

1. Create an instance of the Restore class

2. Specify the filename and path of the backup file

3. Create a BackupDeviceItem and add it to the Restore class’s Devices list.

4. Set the Database and ReplaceDatabase properties of the Restore class

4. Finally, call the SqlRestore method of the Restore class

Sample code:

Public void SQLDatabaseRestore(string dbName)
{
Restore rstDatabase = new Restore();
string filename = string.Format(dbName + “.bak”);
rstDatabase.Devices.Add(new BackupDeviceItem(fileName, DeviceType.File));
rstDatabase.Database = dbName;
rstDatabase.ReplaceDatabase = true;
rstDatabase.SqlRestore(sqlServerName);
}

Verify Backup

Finally you can verify the restore of the database with the following code:

Restore rstDatabase = new Restore();
rstDatabase.DeviceType = DeviceType.File;
rstDatabase.Devices.Add(“@c:\MyBackUpDatabase.bak”);
Boolean backupOK = rstDatabase.SqlVerify(sqlServer, out msg);

During my limited time spent in development these days, I was having an issue with a .NET application that was driving me crazy. It was a typical scenario where the application was running fine on my development machine. Then my client made a change to the database I was working with and sent me a .BAK copy of the updated database. All of the sudden the application stopped working. Of course when I went to look for help on the internet I was taken off in many different directions. I did find a solution that ended up working in this case. In SQL Server I added the user NT AUTHORITY\NETWORK SERVICE and then grant it the correct permissions, which in this case were public, db_datareader, and db_datawriter. So for now I’m back in business.