Often, you will need to check for SQL Server versions and service packs. Some of the features that you are using are only available from a service pack onward.
Microsoft provides the function server property to query for that kind of information.
SELECT
CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
AS VARCHAR(17)) AS 'Computer physical name',
CAST(SERVERPROPERTY('ServerName')
AS VARCHAR(17)) AS 'Server name',
CAST(SERVERPROPERTY('MachineName')
AS VARCHAR(17)) AS 'Machine name',
CAST(SERVERPROPERTY('Edition')
AS
VARCHAR(25)) AS 'Server edition',
CAST(SERVERPROPERTY('InstanceName')
AS VARCHAR(17)) AS 'Instance name',
CAST(SERVERPROPERTY('LicenseType')
AS VARCHAR(15)) AS 'License type',
CAST(SERVERPROPERTY('ProductVersion')
AS VARCHAR(15)) AS 'Version',
CAST(SERVERPROPERTY('ProductLevel')
AS VARCHAR(15)) AS 'Service pack';
Go
If you want to check whether the databases are configured for mirroring or not, then below script would be a great help.
SELECT
A.name,
CASE
WHEN B.mirroring_state is NULL THEN 'Mirroring is not configured'
ELSE 'Mirroring is configured'
END as MirroringState
FROM
sys.databases A
INNER JOIN sys.database_mirroring B
ON A.database_id=B.database_id
WHERE a.database_id > 4
ORDER BY A.NAME
When you restart Microsoft SQL Server or SQL Server Agent, the service may fail to start with the following error message:
Error 1069: The service did not start due to a logon failure.
Cause
This problem occurs because the password for the SQL Server startup service account or the SQL Server Agent startup service account is not correct. This problem can occur when the password for the account is changed but the password information was not updated for the SQL Server service or the SQL Server Agent service.
Workaround
To solve this problem, type the correct password in the Microsoft SQL Server service account on the SQL Server host computer.
To correct the password in Microsoft Windows 2000 Server and Microsoft Windows XP:
- Click Start, point to Settings, and then click Control Panel.
- Double-click Administrative Tools, and then double-click Services.
- Use one of the following steps based on your instance type:
- For a default instance of SQL Server, double-click MSSQLServer, and then click the Log On tab.
- For a named instance of SQL Server, double-click MSSQL$YourInstanceName, and then click the Log On tab. Replace YourInstanceName with the actual name of the SQL Server instance.
- For a default instance of SQL Server Agent, double-click SQLAgent, and then click the Log On tab.
- For a named instance of SQL Server Agent, double-click SQLAgent$YourInstanceName, and then click the Log On tab. Replace YourInstanceName with the actual name of the SQL Server instance.
- Type the correct password in the Password and Confirm password textbox, and then click OK.
- To correct the password in Microsoft Windows NT 4.0:
- Click Start, point to Settings, and then click Control Panel.
- Use one of the following steps based on your instance type:
- For a default instance of SQL Server, double-click MSSQLServer, and then click the Log On tab.
- For a named instance of SQL Server, double-click MSSQL$YourInstanceName, and then click the Log On tab. Replace YourInstanceName with the actual name of the SQL Server instance.
- For a default instance of SQL Server Agent, double-click SQLAgent, and then click the Log On tab.
- For a named instance of SQL Server Agent, double-click SQLAgent$YourInstanceName, and then click the Log On tab. Replace YourInstanceName with the actual name of the SQL Server instance.
- Type the correct password in the Password and Confirm password textbox, and then click OK.
- NOTE: Changing the password through the Services applet allows SQL Server to start successfully; however, if Microsoft Search (Full-Text Service) is installed, it requires a subsequent password change through SQL Enterprise Manager (SEM).
