Author Archives: brentm

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, … Continue reading

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 … Continue reading

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) … Continue reading

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 … Continue reading

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 … Continue reading

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 … Continue reading

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 … Continue reading

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 … Continue reading

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 … Continue reading

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 … Continue reading

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