-->
SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Datawarehouse: SQL Server and Stories

Iain Galloway is a SQL Server DBA.

Policy Based Management and Powershell

Introduction


Policy Based Management (PBM) can be used to inform DBAs when an incorrect setting is being used on a SQL instance and in some cases, can restrict changes being made to the SQL instance. Microsoft explain it as follows...

“Policy-Based Management is a policy based system for managing one…

Read more

0 comments, 937 reads

Posted in Datawarehouse: SQL Server and Stories on 26 September 2017

TempDB Multiple Datafiles

Problem

Most DBAs are aware that (prior to SQL 2016) one of the most common, post installation changes that needs to be made in SQL Server is the addition of extra TempDB datafiles. What seems to be less well known is that ALL these files all need to be of…

Read more

3 comments, 3,233 reads

Posted in Datawarehouse: SQL Server and Stories on 14 March 2017

SQL Agent Permissions

Problem


I was contacted by a user asking if it was possible for his team to be able to execute specific SQL Agent jobs and check the job history. This was made possible by creating a SQL authenticated login and changing the job owner to this new account.

But this…

Read more

0 comments, 402 reads

Posted in Datawarehouse: SQL Server and Stories on 21 December 2016

Linked Server Security

Problem


When creating Linked Servers, security often gets overlooked.  This can lead to accounts on one instance having access, sometimes with elevated permissions, to a second instance.

Security Options


Linked Servers offer the following security options....























  • Local server to remote server mappings
  • Not be made
  • Be made without using a…

Read more

0 comments, 224 reads

Posted in Datawarehouse: SQL Server and Stories on 11 November 2016

Restore through FULL backup

Problem


A common misconception that i've heard a few times around backups is that a FULL backup invalidates previous TRANSACTION LOG backups. By this, I mean, that when a FULL backup is taken, TRANSACTION LOG backups cant be restored past that FULL backup.

Solution


I have created two SQL Agent…

Read more

0 comments, 65 reads

Posted in Datawarehouse: SQL Server and Stories on 4 October 2016

Using Logins and Certificates to evelate Permissions

Problem


A common question I get asked by SQL developers is to be able to query the sys.dm_db_index_usage_stats table to see what indexes are being used. As happy as I am that SQL developers are taking an interest in their deployments, this requires the VIEW SERVER STATE permission which opens…

Read more

0 comments, 103 reads

Posted in Datawarehouse: SQL Server and Stories on 16 September 2016

Tempdb monitoring - Free space alert

SQL Server uses tempdb for many features, including row versioning, service broker and query execution amongst others.  During the execution of queries, data used for sorts and hash joins can be “spilled” to tempdb if the memory granted to the query isn't enough to store the data.  If this happens…

Read more

0 comments, 226 reads

Posted in Datawarehouse: SQL Server and Stories on 7 July 2016

Recertifying SQL Server 2012 MCSE with Microsoft Virtual Academy

Do people still get certified? I don’t hear much about it in the SQL community these days so I asked my twitter friends.












Nobody replied :-) Maybe thats a sign of peoples interest (or just that I need better friends)

Anyway, I'm still interested and I decided it was time…

Read more

2 comments, 174 reads

Posted in Datawarehouse: SQL Server and Stories on 4 February 2016

TDE and Availability Groups

Caution must be exercised when mixing Transparent Data Encryption (TDE) and Availability Groups to ensure databases don't end up in a suspect state on the secondary node.

Adding TDE to a database in an Availability Group


If a database is already part of an Availability Group, the certificate used to…

Read more

0 comments, 148 reads

Posted in Datawarehouse: SQL Server and Stories on 7 October 2015

Backup Database

One of the most important processes for a DBA is backing up and restoring a database.  In its simplest form, a backup can be taken in the following way....


GUI


Right mouse click on the database name, choose tasks and Back up.
 


 












 
Check to confirm the backup…

Read more

0 comments, 89 reads

Posted in Datawarehouse: SQL Server and Stories on 7 October 2015