How to backup a certificate and populate pvt_key_last_backup_date

Below shows some steps to test populating the pvt_key_last_backup_date of the sys.certificates DMV.

 

 

 The code for testing is here:

 

USE testdb;

-- ***** master key created so a certificate can be created *****'
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$trongPa55';

-- ***** certificate created *****
CREATE CERTIFICATE testcert WITH SUBJECT='testcertsub';

-- ***** show pvt_key_last_backup_date *****
SELECT name,pvt_key_encryption_type_desc,pvt_key_last_backup_date FROM sys.certificates;

-- ***** now to populate pvt_key_last_backup_date column *****
BACKUP CERTIFICATE testcert TO FILE = 'c:\test\testcert.cer'
WITH PRIVATE KEY ( FILE = 'c:\test\dmk_tesdb.pvk', ENCRYPTION BY PASSWORD = 'another$trongPa55');

-- ***** show pvt_key_last_backup_date *****
SELECT name,pvt_key_encryption_type_desc,pvt_key_last_backup_date FROM sys.certificates;

-- ***** clean-up drop certificate so we can drop the master key *****
DROP CERTIFICATE testcert;

-- ***** clean-up drop master key *****
DROP MASTER KEY;

Configuring SQL Server 2008 R2 Express Edition for remote access

Often I see questions in forums about how to configure SQL Server Express Edition for remote access.

 

Below are the steps to allow remote access to a SQL Server 2008 R2 x64 Express Edition instance after the default install steps where performed.

 

Also included are the additional configuration steps for SQL Server 2008 R2 x64 Express Edition with Advanced Services.

 

The steps were performed on  Windows Server 2008 R2 64bit.

 

 

 

 

 

Below you can see that by default, SQL Server Express allocates a Dynamic port when SQL Server starts.

 

You can either keep this setting or change SQL Server to listen on a fixed TCP port e.g. TCP 1433.  This can be achieved by removing 0 from all the ‘TCP Dynamic Ports’ rows and placing the fixed TCP port you want to use on all the ‘TCP Port’ rows below it.

 

For this demo I will keep SQL Server allocating a Dynamic port on start-up and therefore I have not changed anything on this tab.

 

 

 

 

 

Connect to SQL Server Express on the server you installed it. 

 

As below you can see the instance when SQL Server Express is installed is call SQLEXPRESS if you did not change it.

 

 

The below should already be enabled, but if it isn’t, then enable it and restart the SQL Server service.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The Port rule  I am creating below is for if you installed SQL Server Express with Advanced Services and selected the SQL Server Reporting Services feature.

 

 

 

 

 

 

If you changed the HTTP TCP port from the default on 80, then you should enter the port you are using here.

 

 

 

 

 

 

 

 

 These steps show you how to give a user access to Reporting Services.

 

 

 

As below, make sure you use localhost and not the name of the SQL Server.

 

 

 

 

 

 

 

Add the relevent windows user with the permissions they require.

 

 

 

 

Now test that you are able to connect from a remote computer.

 

 

 

 

 

Below shows a remote connection to Reporting Services.  Above I used localhost when on the SQL Server, but remotely you should use the SQL Server name.

 

 

Resolve log_reuse_wait_desc is REPLICATION

Occasionally I come across instances with  the following symptoms:

 

  • The log file is unable to be shrunk
  • The log file log_reuse_wait_desc is REPLICATION
  • There is no replication setup on the SQL Server instance

 

The below is an example:

 

 

These are the steps you can take to resolve this:

 

 

 

Select the database with the issue

 

 

The Snapshot publication is a good option as schema changes are not made

 

 

Selecting any sproc is an easy option

 

 

You will notice the below objects have now been created and the log_reuse_wait_desc is still REPLICATION

 

 

 

 

The replication created objects are now gone and the REPLICATION message is gone.

 

Query and update an Excel 2010 spreadsheet via a linked server

This took a while to get working so I thought I’d list the steps taken to achieve manipulating an Excel 2010 spreadsheet via a linked server.

 

The SQL Server I used was SQL Server 2008 R2 (64-bit) and the Excel 2010 spreadsheet had the below data:

 

 

First  I downloaded and installed the ‘AccessDatabaseEngine_x64.exe’ file on the SQL Server from http://www.microsoft.com/download/en/details.aspx?id=13255. No restart was required.

 

Next I started SQL Server Management Studio as ‘Administrator’.

 

 

Next I enabled the ‘Allow inprocess’ option for the Microsoft.ACE.OLEDB.12.0 provider.

 

 

The linked server was created with this script:

 

EXEC master.dbo.sp_addlinkedserver
 @server = N'test',
 @srvproduct=N'ACE 12.0',
 @provider=N'Microsoft.ACE.OLEDB.12.0',
 @datasrc=N'c:\test\test.xlsx',
 @provstr=N'Excel 12.0;HDR=Yes'

 

 

I could now query the spreadsheet with:

 

select * from test...[sheet1$]

 

 

To add data I used:

 

insert into test...[sheet1$] values ('server2','blue')

 

To modify data I found that I had to first enable the below:

 

 

Then to update a record I used

 

UPDATE OPENROWSET('Microsoft.Ace.OLEDB.12.0',
  'Excel 12.0;DATABASE=c:\test\test.xlsx', 'Select * from [Sheet1$]')
  SET type = 'green' WHERE server = 'server2'

 

 

To insert a row I used:

 

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',  
 'Excel 12.0;Database=C:\test\test.xlsx;',
 'SELECT server,type FROM [Sheet1$]')
 VALUES ('server3','yellow')

 

 

To delete 1 row I used the below work around which set the column values to null.

 

UPDATE OPENROWSET('Microsoft.Ace.OLEDB.12.0',
 'Excel 12.0;DATABASE=c:\test\test.xlsx',
 'SELECT server,type from [Sheet1$]')
 SET server = null, type = null
 WHERE server like 'server2'

 

 

SQL Server 2008 R2 server collation change issue

When you attempt to change a SQL Server 2008 R2 server collation using the instructions in Setting and Changing the Server Collation (shown below), this may fail with an invocation error.

 

Setup /QUIET /ACTION=REBUILDDATABASE

/INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts

/[ SAPWD= StrongPassword ] /SQLCOLLATION=CollationName

 

You could try resolving this issue by running the command using setup.exe from the installation media.

Update rsreportserver.config file using PowerShell

I’m sure there is an easier way in to achieve this in PowerShell, but here is 1 way to add and set the WorkingSetMaximum and WorkingSetMinimum entries in the rsreportserver.config file (ref: Configuring Available Memory for Report Server Applications)

 

$xfile=’E:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config’
[xml]$xml=gc $xfile
$new=$xml.CreateElement(‘WorkingSetMaximum’)
$new.InnerText=204800
$xml.Configuration.Service.AppendChild($new)
$new=$xml.CreateElement(‘WorkingSetMinimum’)
$new.InnerText=102400
$xml.Configuration.Service.AppendChild($new)
$xml.Save($xfile)

 

 

SQL Server 2012 Professional-level certification will require recertification every three years

There is a new recertification change to be aware of when obtaining your SQL Server 2012 Professional-level certifications.

 

Q. What is the recertification requirement?

A.

In order to ensure that our certifications are meaningful and valuable indicators of candidates’ skills, Microsoft has implemented a recertification requirement for MCPD: Windows Azure Developer and MCPD: Windows Phone Developer. This requirement will also apply to the upcoming Professional-level SQL Server 2012 certifications. Windows Azure and Windows Phone certifications will require recertification every two years, while SQL Server 2012 certifications will require recertification every three years. Initially, the recertification requirement will be one or two exams.

 

The recertification of your Professional-level SQL Server 2012 certification will need to occur within three years or your transcript will show that the certification is Inactive.

 

This requirement does not appear to apply for MCTS SQL Server 2012 certifications, or fortunately, all existing certifications.

 

Interesting to note, is that the new Professional-level certifications will not reference a product version in the title, as when you recertify yourself, you are demonstrating continued competence with the technology.

msmdsrv.ini – ‘Access is denied’ error

I came across a strange permissions issue while attempting to edit the SQL Server Analysis Services file msmdsrv.ini  locally on a SQL Server (Ref: Recommended settings from Optimal configuration settings for high concurrency in SSAS)

 

My account had local administrator access on the SQL Server but I received ‘Access is denied’ when attempting to edit the file with notepad.

 

 

I could get around this issue by either:

 

  • Opening notepad with ‘Run as administrator’ from the start menu and then locating and opening msmdsrv.ini

 

 

  • Editing msmdsrv.ini remotely from another computer e.g. \\sqlservname\drive$\…\msmdsrv.ini

 

Hopefully this info will save you some time if you come across this scenario.

Welcome

Welcome to the first Blog entry of AKAWN LIMITED.

 

To start the Blog off, there is a useful start-up option  –P which when used with sqlservr.exe, it allows you to specify how many CPU schedulers should be shown to SQL Server.

 

The number specified can be higher than what you have in your test machine and therefore it is extremely  handy for testing affinity mask settings and/or the effects of multiple schedulers on parallel execution plans when you have limited CPUs available to you.