Add a database filegroup and file to an existing database using PowerShell

Majority of the examples I could find online to add database filegroups and/or files using PowerShell were for new databases. They made use of methods that didn’t work for what I was after. What I needed to do was add a database filegroup and file to an existing database. Examples, and until recently, documentation, I couldn’t find so easily online.

The below script is an example of how to create a database filegroup and file (with some additional options) on an existing database. It’s pretty straight forward.

$server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') 'localhost'
$database = $server.Databases['DemoDatabase']
$filegroup = New-Object ('Microsoft.SqlServer.Management.Smo.FileGroup') ($database, 'SecondaryFGName')

$data_file = New-Object ('Microsoft.SqlServer.Management.Smo.DataFile') ($filegroup, 'SecondaryFileName')
$data_file.FileName = 'C:\Data\DemoDatabase\SecondaryFileName.ndf'
$data_file.Size = [double]1024000.0
$data_file.GrowthType = 'KB'

Permissions required for SQLjobvis

SQLjobvis is a tool I’ve blogged about before. For more info on what SQLjobvis is and how it can help you you can read my blog post here.

A reader had commented on that post and mentioned that they didn’t have the necessary permissions to run SQLjobvis. But what are those permissions? The website doesn’t appear to mention required permissions and personally I like discovering what permissions are required for tools, apps or code.

After some playing around in a local development instance I discovered that only read access to some system tables in the msdb database are required. Below is the code I created. Note the database role is not required, however I find it best when granting permissions this way.

USE [msdb];
GRANT SELECT ON OBJECT::[dbo].[syscategories] TO [SQLJobVis];
GRANT SELECT ON OBJECT::[dbo].[sysjobactivity] TO [SQLJobVis];
GRANT SELECT ON OBJECT::[dbo].[sysjobhistory] TO [SQLJobVis];
GRANT SELECT ON OBJECT::[dbo].[sysjobs] TO [SQLJobVis];

What I Use – sp_WhoIsActive

Who is Active? Not I! 3 blog posts in 2014, all published in the beginning of the year. However in 2015 I’ll be looking to change all that. Back to SQL!

Who is Active, an awesome stored procedure I use on a daily basis to see what is going on, at that very moment, on SQL Servers I monitor. At the moment I only really use it to view percentage completed (for certain queries), blocked queries, query wait types and resource heavy queries. It is far more powerful though with filters, output columns and delta mode, just to name a few features. And it also provides loads of information you can use at your will.

At the time of writing this post, the latest version is v11.11 and you can download it from here. Do it, and add it to your arsenal! Happy New Year!

How to grant IMPERSONATE permissions

In order to execute queries under a different security context (such as another login) using the EXECUTE AS clause, you must be able to impersonate the user.

To grant impersonate permissions on a specific login, run the query below.

USE [master];

GRANT IMPERSONATE ON LOGIN::[Domain\Impersonatee] TO [Domain\Impersonator];

To test this works you can run the query below.

USE [master];

EXECUTE AS LOGIN = 'Domain\Impersonatee';

The EXECUTE permission was denied on the object ‘xp_cmdshell’, database ‘mssqlsystemresource’, schema ‘sys’

A user will usually receive this error message when trying to execute xp_cmdshell and does not have sufficient permissions.

To grant permissions to execute xp_cmdshell using TSQL, run the query below.

USE [master];

ON xp_cmdshell
TO [Domain\LoginName]

To test this works I usually run the query below.

EXECUTE xp_cmdshell 'DIR'

To test the granted execute permissions work for the intended user, I impersonate them to run the xp_cmdshell query under their security context. I have also written another post on how to impersonate a login and how to grant IMPERSONATE permissions on a login.

How to enable line numbers in SSMS

To enable line numbers in SQL Server Management Studio, click on the Tools menu and Options. In the left pane of the Options box, expand Text Editor and click on All Languages. On the right, under Display, you’ll see a check box for Line numbers. Check the check box to enable line numbers. I have uploaded a screenshot for reference. This was done in SSMS 2012.

Screen Shot 2014-02-21 at 1.24.38 pm

Permissions required for Redgate SQL Source Control

Redgate SQL Source Control is a SSMS plug-in which allows you to source control your database schema. Assuming you or your users have limited access, some permissions are required to use this application.

At a database scope the VIEW DEFINITION permission is required to view code changes which have not been committed. ALTER is also required when committing code changes to your version control system (subversion, git, etc).

USE [YourDatabase]
GRANT ALTER TO [jacobs];

At a server scope ALTER TRACE is useful but not required. This enables the user(s) to see who has made schema changes under the ‘Changed by’ column in the Commit Changes tab.

USE [master]

Note: This information is based off the SQL Server default trace. For busy SQL Servers the default trace can recycle often, if this is the case the ‘Changed by’ column will likely be Unknown. At present there is no way to change how much historical data the default trace contains or change which data source SQL Source Control references.

The server instance Witness rejected configure request

I noticed the following message in the SQL Server ErrorLog for one of the database servers which is in a database mirroring setup (currently the mirror).

Error: 1438, Severity: 16, State: 2

The server instance Witness rejected configure request; read its error log file for more information. The reason 1427, and state 31, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.

I ran the following TSQL on the mirror database server:

SELECT DB_NAME(database_id) AS 'DatabaseName'
, mirroring_role_desc AS 'DatabaseRole'
, mirroring_role_sequence AS 'FailoverCount'
, mirroring_partner_instance AS 'MirroringInstance'
, mirroring_state_desc AS 'MirroringState'
, mirroring_connection_timeout 'MirroringConnectionTimeoutInSeconds'
, mirroring_witness_name AS 'WitnessInstance'
, mirroring_witness_state_desc AS 'WitnessState'
FROM master.sys.database_mirroring
WHERE mirroring_guid IS NOT NULL

and noticed that one of the database’s WitnessState value was Disconnected. To resolve the issue I ran the following TSQL on the principal database server and the message stopped appearing in the Error Log.

SET WITNESS = 'TCP://ServerName.Domain.Com:5022'

Invoke-Sqlcmd : Object reference not set to an instance of an object.

Today I was working on some PowerShell scripts where I ran into the following error message:

Invoke-Sqlcmd : Object reference not set to an instance of an object.

Below is the PowerShell code which was throwing the error.

Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $Query

I discovered the culprit was the $Query parameter. The $Query parameter contents contained a TSQL query to create a SQL Server Agent Job. The problem was that this Agent Job made use of tokens, which I suspect PowerShell confuses for variables due to the dollar sign symbol (ie $(SRVR)). The work around was to assign the tokens to TSQL variables, in a concatenated format:

SET @Server = '$' + '(SRVR)'
SET @Message = 'The '+@Server+' SQL Server instance is unavailable.'

My PowerShell script would now run without errors.

Add .NET Framework with PowerShell

Before installing SQL Server 2008 R2 on Windows Server 2008 R2 SP1, the .NET Framework Feature must be added. To do this via PowerShell run the following:

Import-Module ServerManager
Add-WindowsFeature AS-NET-Framework | Out-Null

The Out-Null is optional but by piping the output means nothing will get returned to the PowerShell window.


Get every new post delivered to your Inbox

Join other followers