Moving from Server Core to Minimal Server Interface

To install Server Graphical Management Tools & Infrastructure on Server Core:

Run PowerShell, then run the following command: Install-WindowsFeature Server-Gui-Mgmt-Infra  -Restart

A restart is required and this command will restart the computer at the end of the update. If you omit the -Restart, you can instead run Restart-Computer while still in PowerShell

  • To revert back to Server Core: Run servermanager and then go Manage > Remove roles and Features
Posted in SQL on Server Core | Comments Off on Moving from Server Core to Minimal Server Interface

Granting permissions for SQL Server

To operate correctly, SQL Server requires permissions on the file system. In the past, running under local administrator it wasn’t necessary but nowadays we always run the SQL Server services under minimal privileges. This means that SQL needs permissions on the file system where the data and log files are located.

The standard path is <drive letter>:\MSSQL\Data

You need to grant permissions on the Data folder. Full control is necessary. This is required where you add drives or change paths as setup.exe provides all the necessary permissions.

Under server core this is achieved with the following command:

icacls E:\MSSQL\Data /Grant “NT Service\MSSQLServer”:(OI)(CI)F

The account “NT Service\MSSQLServer” maps to the service SID and is used under SQL2012 and SQL2014. Under SQL2008 you should use the group that is created under Computer Management and called like SQLServerMSSQLUser$<computer_net_bios_name>$SQL2008 (for a named instance called SQL2008)

 

Posted in SQL on Server Core | Comments Off on Granting permissions for SQL Server

Managing SQL Services from command Line

Under Windows Server core, if you can’t control the services remotely, the commands to change the services are as follows:

sc config <service_name> start= <mode>

auto = automatically started at boot time (automatic)

demand = manually start the service (manual)

disabled = Service can’t be started (disabled)

Start and stop services using:

net stop mssqlserver (default instance)

net stop sqlserveragent (SQL Server agent default instance)

net start mssql$sql2014 (SQL Server instance called SQL2014)

net start sqlagent$sql2014 (SQL Server agent on instance called SQL2014)

 

 

Posted in SQL on Server Core | Comments Off on Managing SQL Services from command Line

Changes to SSMS to open files in new tab, not new instance

On a new clean install, and because SSMS is built upon Visual Studio, the default behaviour is to open a new SSMS instance when you double click on a .sql file.

To fix this, so that a each double click on a .sql file opens in a new tab, modify the following registry key:

HKEY_CLASSES_ROOT\ssms.sql.12.0\Shell\Open\Command

There is only a single key which refers to the location of SSMS. Modify this to put “%1″ at the end (including the quotes)

Note: I’m running SSMS 2014

Posted in Tips | Comments Off on Changes to SSMS to open files in new tab, not new instance

SQL Server on Windows Server Core

Yesterday was day 1 of the SQL PASS conference in Charlotte, North Carolina, USA. I attended a full day session on Always On Availability Groups and here are some findings from that session.

As you might already know, from SQL Server 2012, Windows Server Core edition is a supported operating system. Core edition of Windows Server has been available since Windows Server 2008 R2 but because of complexities around the .net framework, it hasn’t been until the release of SQL Server 2012 that you could actually install SQL on core edition. There are a lot of benefits to using this OS and SQL server is ideally suited to running on Server Core. This is mainly because as a DBA, we always work remotely i.e. Don’t have to log onto the server as part of our daily job. The other big reasons are the lighter footprint and the 60% reduction in patching. The reduction in patching is a big deal for me as a DBA because patches require reboots and as almost every application has a database back end, we want the highest uptime possible from our database servers.

It is possible to install SQL Server on Core edition and still have the full SQL installation UI. There is a switch that is called as part of setup and that is /UIMODE=EnableUIOnServerCore. The only caveat to this that none of the hyperlinks in the UI will work.

Server Core has three operating modes… Core with no UI, Minimal mode and Full UI. Server Core is quite flexible in that you can transition between core, minimal mode and full UI and back again. There are other complexities around this that I won’t detail here but suffice to say that running wsus in your environment causes some problems that related to patch versions and the ability to add and remove features. It is safest to build your server with the full UI and wind it back.

The big gotcha with running SQL Server on Server Core is that, while you can wind Server Core back up to a full UI, with SQL Server this is a one way operation. You cannot wind back from a full UI – it is not supported by Microsoft. Therefore, given I’ve said the safest method to build a Server Core system is full UI, I recommend that you wind it down to minimal mode to install SQL Server and then wind back to Core edition. If for some reason you have wound from Core to full UI with SQL server installed then your only option from this point on is to uninstall SQL Server, wind back to minimal mode or Core and then reinstall SQL Server.

Posted in SQL on Server Core | Comments Off on SQL Server on Windows Server Core

Permissions for certificates and symmetric keys

I’ve taken this example from BOL and worked it a bit to get a desirable solution…

The code is the first code block  from this URL… http://technet.microsoft.com/en-us/library/ms179331(v=sql.100).aspx

Here is the first code block… (This is using AdventureWork2008)

USE AdventureWorks;
GO

--If there is no master key, create one now. 
IF NOT EXISTS 
 (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
 CREATE MASTER KEY ENCRYPTION BY 
 PASSWORD = '23987hxJKL969#ghf0%94467GRkjg5k3fd117r$$#1946kcj$n44nhdlj'
GO

CREATE CERTIFICATE HumanResources037
 WITH SUBJECT = 'Employee Social Security Numbers';
GO

CREATE SYMMETRIC KEY SSN_Key_01
 WITH ALGORITHM = AES_256
 ENCRYPTION BY CERTIFICATE HumanResources037;
GO

USE [AdventureWorks];
GO

-- Create a column in which to store the encrypted data.
ALTER TABLE HumanResources.Employee
 ADD EncryptedNationalIDNumber varbinary(128); 
GO

-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY SSN_Key_01
 DECRYPTION BY CERTIFICATE HumanResources037;

-- Encrypt the value in column NationalIDNumber with symmetric 
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
GO

One thing that is missing here is the CLOSE statement…

CLOSE SYMMETRIC KEY SSN_Key_01

Now, the next block of code, wrap this in a stored procedure like so…

CREATE PROC dbo.myEncryptedProc AS 
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data.
OPEN SYMMETRIC KEY SSN_Key_01
 DECRYPTION BY CERTIFICATE HumanResources037;


-- Now list the original ID, the encrypted ID, and the 
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
SELECT NationalIDNumber, EncryptedNationalIDNumber 
 AS 'Encrypted ID Number',
 CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) 
 AS 'Decrypted ID Number'
 FROM HumanResources.Employee;

CLOSE SYMMETRIC KEY SSN_Key_01
GO

We need a user to test this with so create a SQL login and call it mySQLUser

CREATE LOGIN mySQLUser WITH PASSWORD = 'Pa$$w0rd1', DEFAULT_DATABASE = AdventureWorks2008
USE AdventureWorks2008
CREATE USER mySQLUser FROM LOGIN mySQLUser
EXEC sp_addrolemember db_datareader, mySQLUser
EXEC sp_addrolemember db_datawriter, mySQLUser
GRANT EXEC ON dbo.myEncryptedProc TO mySQLUser

Open a new connection running as mySQLUser and call the stored proc and see what happens.

 

You need to grant additional permissions on the key and the certificate. The minimal permissions required are:

GRANT REFERENCES ON SYMMETRIC KEY::SSN_Key_01 TO mySQLUser
GRANT CONTROL ON CERTIFICATE::HumanResources037 TO mySQLUser

Everything should work now.

 

Posted in Encryption | Comments Off on Permissions for certificates and symmetric keys

Mirroring suspends with Assertion error

Assume the following scenario: You are performing a Bulk Insert or BCP into a table of a database that is the principal in a database mirroring pair. The BCP or Bulk Insert succeeds but the mirror is now in a suspended state and a severity 20 Assertion error is thrown.

What is happening here is that when a BCP or Bulk Insert occurs, the default setting (when not explicitly specified) is to turn off the Check Constraints option. This causes a lock compatibility on the mirror as this isn’t replicated.

The mirror can’t be resumed using the Alter Database command. You have to perform a full re-sync.

If you try to repeat the same Bulk Insert or BCP operation it will succeed this time and not cause the same error. This is because the constraints are in an “untrusted” state. This is not the same as enabled/disabled.

To determine the state of the constraints run the following query:

SELECT * FROM sys.check_constraints
WHERE is_not_trusted = 1

Re-enable the constraint using the following:

ALTER TABLE table_name WITH CHECK CHECK CONSTRAINT constraint_name

This is a known issue as documented in the following knowledge base article:

http://support.microsoft.com/kb/2700641

 

 

Posted in Mirroring, Troubleshooting | Comments Off on Mirroring suspends with Assertion error

Corrupting a database page

To corrupt a database page for the purpose of testing DBCC and page restores etc, this is the process I use which I find easiest…

1. Download a copy of AdventureWorksLT2008 sample database… http://msftdbprodsamples.codeplex.com/releases/view/93587

2. Attach the database to your SQL Server instance (run SSMS as administrator)

EXEC sp_attach_db AdventureWorksLT2008
, 'D:\MSSQL\Data\AdventureWorksLT2008_Data.mdf'
, 'D:\MSSQL\Data\AdventureWorksLT2008_Log.ldf'

3. Take a backup of the database so that you have something to restore (unless you have another copy of the files)

BACKUP DATABASE AdventureWorksLT2008
TO DISK = 'D:\MSSQL\Backup\bkup_AdventureWorksLT2008.bak'
WITH init

4. Find a page in the database to corrupt

To do this run the following command:

DBCC IND (AdventureWorksLT2008, 'SalesLT.Customer', 4 )

This will return all the database pages used by the SalesLT.Customer table and 4 is the index id. See another post on using DBCC IND here… http://www.sqldba.co.nz/blog/?p=62

For this example, I chose page 636.

5. Put the database into a state where you can perform page restores i.e. Full recovery (only necessary if you want to do this)

ALTER DATABASE AdventureWorksLT2008 SET COMPATIBILITY_LEVEL = 100
ALTER DATABASE AdventureWorksLT2008 SET RECOVERY FULL
ALTER AUTHORIZATION ON DATABASE::AdventureWorksLT2008 TO sa

6. Download a HEX editor for the purpose of corrupting a page. I use XVI32 which is here…

http://www.chmaas.handshake.de/delphi/freeware/xvi32/xvi32.htm

This is a stand-alone application that doesn’t get installed – just run the .exe

7. Take AdventureworksLT2008 database offline so you can edit the mdf

ALTER DATABASE AdventureWorksLT2008 SET OFFLINE

8. Run XVI32.exe

9. Open the AdventureWorksLT2008 main data file (.mdf)

10. Go to the page that you want to corrupt. Do this by select Address on the menu and Goto. The location that you want to go to is the page number (which is 636) multiplied by 8192 which equals 5210112

11. Corrupt the page by entering 00 in the byte.

12. Save the change to the .mdf

13. Bring the database back online

ALTER DATABASE AdventureWorksLT2008 SET ONLINE

14. Run CheckDB to highlight the corruption

DBCC CHECKDB (AdventureworksLT2008) WITH NO_INFOMSGS, ALL_ERRORMSGS

15. Note the output which will have page 636 in the error message or lookup the corruption in the suspect_pages table…

SELECT * FROM msdb..suspect_pages

From here you can perform a page restore or do what ever you like.

Posted in Corruption | Comments Off on Corrupting a database page

Recovering from Disaster

After a recent incident in the workplace, I decided it was a good idea to write a document on recovering from disaster. Hopefully this link to my SkyDrive will always work and feel free to take a copy of this and use it in your workplace.

http://sdrv.ms/12FLC3g

8 May 2013 – Added Offline page restores

12 May 2013 –  Added TDE restore to another instance

15 May 2013 – fixed some errors in the commands and improved clarity in parts

 

Posted in Disaster Recovery | Comments Off on Recovering from Disaster

Enabling Service Broker on a database

To check the status of service broker on a database run the following (in the database)…

SELECT is_broker_enabled
FROM master.sys.databases
WHERE database_id = DB_ID()

If the broker is enabled it will return a 1, otherwise it will return 0.

The catch…

You need exclusive access to the database to perform this task. That will mean a potential outage, no matter how small. You can run the following command to perform this in a single operation…

 

ALTER DATABASE <my_database> SET SINGLE_USER WITH ROLLBACK AFTER 5
ALTER DATABASE <my_database> SET ENABLE_BROKER
ALTER DATABASE <my_database> SET MULTI_USER


If you want to do this to a mirrored database, you can’t. You have to break the mirror, enable the broker and then reinstate the mirror from scratch. There is no other way.

This has other side effects too because if you are using the failover partner in your connection string, breaking the mirror is likely to cause connection errors.

Posted in Service Broker | Comments Off on Enabling Service Broker on a database