SQL SERVER – ​Building Technical Reference Library – Notes from the Field #048

[Note from Pinal]: This is a 48th episode of Notes from the Field series. How do you build a technical reference library? In other word, when you need help how do you create your own reference so you do not have to go out to look for further help. There are so many little tips and tricks one should know and Brian Kelley has amazing skills to explain this simple concept with easy words.

In this episode of the Notes from the Field series database expert Brian Kelley explains a how to find out what has changed in deleted database. Read the experience of Brian in his own words.


Do you have a technical reference library? If you’re not sure what I mean, a technical reference library is your collection of notes, code, configuration options, bugs you’ve hit that you think you’ll hit again, and anything else that you might need to retrieve again in the future related to what you do in IT. If you have a technical reference library (hereafter referred to as TRL), is it:

  • outside of email?
  • distributed across multiple locations/computers?
  • searchable?
  • fast?

With my TRL, I’m more efficient because I‘m not searching the Internet again and again for the same information. I also can ensure I handle strange cases, such as unusual configurations, which we seem to get a lot of in IT. It’s in my TRL, so I don’t have to go back through a vendor’s install document or go run someone down in the organization to get the information I need. I already have it if I put it in my TRL. Because of the efficiency that TRLs provide, most top performing IT professionals that I know have some sort of system.

Outside of Email:

I used to have a folder in email where I kept technical reference documents. Because I try to follow Inbox Zero, I do have a Reference folder, but it’s not for technical documents. My Reference folder is typically related to what that mailbox is for. For instance, my LP Reference folder is for keeping procedures related to Linchpin such as how/where to enter time, who to contact about various things, etc.

Why don’t I have my technical documents in email any longer? Let me ask a question in response to that question: What happens when email is down? When email is down, you have no access to your TRL. Email does go down. I was faced with a case where I was responsible for getting email back up and, you guessed it, my technical notes were in email. That doesn’t work.

A second question to ask: How searchable is your TRL if it’s in email?  If you keep a lot of email, especially if you don’t have a specific folder for your TRL, searching may prove to be painful. That was the other problem I started to face.

Given these two issues, I advise building your TRL outside of email.

Distributed:

If your TRL  is only on a single computer, you’re going to regret it someday. That day usually occurs when the computer in question crashes and all your notes are lost. If you have a backup, anything you put into the library after the backup is gone. Give the prevelance of cloud-based solutions nowadays, having a technical reference library which is distributed is easy. Here are some ideas:

  • Evernote
  • Microsoft OneNote
  • Microsoft SkyDrive
  • DropBox
  • Google Docs
  • Apple iCloud

I’m particular to the first two, Evernote and OneNote, because they aren’t simply “file systems.” They are designed to capture and catalog information for quick retrieval later.

All my examples will come from Evernote, because that’s the application I typically use. In fact, here’s my setup. I have a specific notebook for my TRL:

TRL Notebook

If I know exactly what I’m looking for or if I’ve added it recently, I should be able to find any note quickly in the list of notes for the notebook:

Note: SQL 2012 Slipstream

Searchable (and Fast!):

Even if what I’m looking for isn’t right there at the top of the list, I can search in Evernote (and OneNote, if I was using it) to quickly locate the document. For instance, by typing “Slipstream,” I quickly get to the article that I want:

Search of TRL

Products live Evernote and OneNote have specifically worked on Search in order to retrieve results quickly. They also provide options to search within a notebook, for instance. In my case here, since slipstream is such a specialized term compared with what else is in my Evernote notebooks, I didn’t feel the need to filter by notebook. However, I could have if I recevied a lot of hits back or if the search was taking too long.

Also note that I’ve not added any tags to this article. I’m hitting it using a text search as to the contents alone. The use of tags offers another option in order to locate the material I need quickly. If I had a lot of articles that came up for a particular search word or phrase, I could look at using tags to differentiate them better. It’s another reason to consider tools designed to hold information and make it quickly retrievable.

Build a System That Works for You:

Learning expert Cynthia Tobias was once helping a teacher who asked her students to keep a reference notebook for assignments and handouts in class, an academic version of the TRL I’ve described thus far. The teacher balked at one student’s notebook because it was messy. The teacher couldn’t imagine how the student could locate anything in the notebook and was going to give the student a poor score. Tobias asked the teacher, “What’s the point?” The point, the teacher indicated, was to be able to retrieve an assignment or handout quickly. Tobias challenged the teacher to check to see if the student could retrieve quickly (within a minute, for instance). If the student could, the teacher should leave the student alone. If the student couldn’t, then work with the student to improve the reference system.

That’s what you want to do. You want to develop a reference system that’s efficient for you. I’ve given you a snapshot of what works for me. It may not work for you. That’s okay. Start with something. If you’re starting from scratch, I would recommend starting with Evernote or OneNote. Put some notes in that you’ll need again. See how well you can retrieve those notes, especially as the number of notes increases. Make tweaks as you have to for performance sake. Most of all, build your TRL and become a better professional.

If you want to get started with performance tuning and database security with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – A Practical Use of Backup Encryption

 Backup is extremely important for any DBA. Think of any disaster and backup will come to rescue users in adverse situation. Similarly, it is very critical that we keep our backup safe as well. If your backup fall in the hands of bad people, it is quite possible that it will be misused and become serious data integrity issue. Well, in this blog post we will see a practical scenario where we will see how we can use Backup Encryption to improve security of the bakcup.

Feature description

Database Backup Encryption is a brand new and long expected feature that is available now in SQL Server 2014. You can create an encrypted backup file by specifying the encryption algorithm and the encryptor (either a Certificate or Asymmetric Key).

The ability to protect a backup file with the password has been existing for many years. If you use SQL Server for a long time, you might remember the WITH PASSWORD option for the BACKUP command. The option prevented unauthorized access to the backup file.

However this approach did not provide reliable protection. In that regard, Greg Robidoux noted on MSSQLTIPS: “Although this does add a level of security if someone really wants to crack the passwords they will find a way, so look for additional ways to secure your data.

To protect a backup file, SQL Server 2008 introduced the transparent data encryption (TDE) feature. Thus, a database had to be transparently encrypted before backup. Therefore, start with SQL Server 2012 the PASSWORD and MEDIAPASSWORD parameters are not used while creating backups. Even so, data encryption and backup files encryption are two different scenarios.

In case a database is stored locally, there is no need to encrypt it before backup. Fortunately in SQL Server 2014 there are two independent processes. Along with data encryption it is possible to encrypt a backup file based on a certificate or an asynchronous key. Supported encryption algorithms are:

  • AES 128
  • AES 192
  • AES 256
  • Triple DES

Practical use

To illustrate above mentioned, I will create an encrypted backup of the Adventureworks database. Also, you can back up directly to Azure. If needed, you may restore the encrypted back up file on Azure.

I will use dbForge Studio for SQL Server to create the encrypted backup file.

To protect the backup file we need to create an encryptor: either a Certificate or Asymmetric Key. Then, we need to pass this encryptor to the target SQL Server to restore the backup. For this, the encryptor must be exported from the source SQL Server and imported to the target SQL Server. There are no problems with the certificate in this regard. It is more complicated with asymmetric keys.

Taking into account that the BACKUP ASYMMETRIC KEY command is not available, and we can not just create a duplicate for an asymmetric key (compared to symmetric key), the only approach is to create the asymmetric key outside the SQL Server. Then we can use the sn.exe utility to transfer it inside SQL Server (CREATE ASYMMETRIC KEYkeynameFROM FILE = ‘filename.snk‘). After that we can use this asymmetric key to encrypt the backup file on the source instance. Further we need to use the same *.snk file to create the asymmetric key on the target instance (and restore the backup file).

In our example we will not use asymmetric keys. We will use a certificate. Moreover the certificate (behind the scene) is the pair of open/closed keys.

Let’s create the server certificate and use it to encrypt the backup file.

The certificate will be protected with the database master key, because we didn’t specify the ENCRYPTION BY statement.

This is exactly what we need. Only certificates signed with the database master-key can be used for the encryption purposes. Otherwise, If we for instance, protect the certificate with the password ENCRYPTION BY PASSWORD = ‘strongpassword‘, the following error appears while attempting to encrypt the backup file:

“Cannot use certificate ‘CertName’, because its private key is not present or it is not protected by the database master key.”

Encrypted backups (along with usual backups) can be traditionally created locally on the hard drive and in Azure Storage.

Instead of writing tons of SQL code I will use the convenient dbForge Studio for SQL Server Back Up wizard. The wizard allows to create the database backup in several clicks.

Step 1: Setup the DB Connection and the backup file location.

Step2: Setup mediaset

Step 3: Select the encryption algorithm and certificate.

In case you don’t want to pay extra attention to transferring the backup file to the Windows Azure, you can backup directly to Azure.

After the script execution in the required container the blob (with the backup) appears.

In case you had already created a backup with the same name in the same container, you can get the following error: There is currently a lease on the blob and no lease ID was specified in the request.

Further, you can restore the back up file on the Windows Azure.

Summary: 

Obviously, it is a good practice to encrypt a backup file while transferring. This, for instance, allows to avoid data leak while transferring backups from one DPC to another.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – ​Finding Out What Changed in a Deleted Database – Notes from the Field #041

[Note from Pinal]: This is a 41th episode of Notes from the Field series. The real world is full of challenges. When we are reading theory or book, we sometimes do not realize how real world reacts works and that is why we have the series notes from the field, which is extremely popular with developers and DBA. Let us talk about interesting problem of how to figure out what has changed in the DELETED database. Well, you think I am just throwing the words but in reality this kind of problems are making our DBA’s life interesting and in this blog post we have amazing story from Brian Kelley about the same subject.

In this episode of the Notes from the Field series database expert Brian Kelley explains a how to find out what has changed in deleted database. Read the experience of Brian in his own words.


Sometimes, one of the hardest questions to answer is, “What changed?” A similar question is, “Did anything change other than what we expected to change?”

The First Place to Check – Schema Changes History Report:

Pinal has recently written on the
Schema Changes History report
and its requirement for the Default Trace to be enabled. This is always the first place I look when I am trying to answer these questions.

There are a couple of obvious limitations with the Schema Changes History report. First, while it reports what changed, when it changed, and who changed it, other than the base DDL operation (CREATE, ALTER, DELETE), it does not present what the changes actually were. This is not something covered by the default trace. Second, the default trace has a fixed size. When it hits that
size, the changes begin to overwrite. As a result, if you wait too long, especially on a busy database server, you may find your changes rolled off.

But the Database Has Been Deleted!

Pinal cited another issue, and that’s the inability to run the Schema Changes History report if the database has been dropped. Thankfully, all is not lost.

One thing to remember is that the Schema Changes History report is ultimately driven by the Default Trace. As you may have guess, it’s a trace, like any other database trace. And the Default Trace does write to disk. The trace files are written to the defined LOG directory for that SQL Server instance and have a prefix of log_:

Therefore, you can read the trace files like any other.

Tip: Copy the files to a working directory. Otherwise, you may occasionally receive a file in use error.

With the Default Trace files, if you ask the question early enough, you can see the information for a deleted database just the same as any other database.

Testing with a Deleted Database:

Here’s a short script that will create a database, create a schema, create an object, and then drop the database. Without the database, you can’t do a standard Schema Changes History report.

CREATE DATABASE DeleteMe;
GO
USE DeleteMe;
GO
CREATE SCHEMA Test AUTHORIZATION dbo;
GO
CREATE TABLE Test.Foo (FooID INT);
GO
USE MASTER;
GO
DROP DATABASE DeleteMe;
GO

This sets up the perfect situation where we can’t retrieve the information using the Schema Changes History report but where it’s still available.

Finding the Information:

I’ve sorted the columns so I can see the Event Subclass, the Start Time, the Database Name, the Object Name, and the Object Type at the front, but otherwise, I’m just looking at the trace files using SQL Profiler. As you can see, the information is definitely there:

Therefore, even in the case of a dropped/deleted database, you can still determine who did what and when. You can even determine who dropped the database (loginame is captured). The key is to get the default trace files in a timely manner in order to extract the information.

If you want to get started with performance tuning and database security with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Difference Between Login and User – SQL in Sixty Seconds #070

“What is the difference between Login and User in SQL Server?”

This is a very common question I often receive. Yesterday when I was browsing Facebook, I once again noticed this question once again asked in SQLBangalore group. My very good friends – Vinod Kumar and Balmukund Lakhani had already answered the question there. However, I every time, I read this question, I realize that not everyone knows the basics of these two concepts. If I have to explain the difference between them, it may take a long time, but I will try to explain it at a very basic level.

Basic Explanation:

SQL Server Login is for Authentication and SQL Server User is for Authorization. Authentication can decide if we have permissions to access the server or not and Authorization decides what are different operations we can do in a database. Login are created at the SQL Server instance level and User is created at SQL Server database level. We can have multiple user from different database connected to a single login to a server.

I hope this is clear enough. If not, I strongly suggest you watch following quick video where I explain this concept in extremely simple words.

Action Item

Here are the blog posts I have previously written on the subject of security. You can read it over here:

You can subscribe to my YouTube Channel for frequent updates.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – What is Biml and How Can it Help Me with SQL Server BI? – Notes from the Field #036

[Note from Pinal]: This is a 36th episode of Notes from the Field series. One of the common question I receive every other day is – I have learned BI but I feel that there is no automation in the BI field, what should I do? How do I go beyond learning BI? How can I fill the gap between BIDS and SSDT? If you have been reading this blog, when it is about BI, I always take help from LinchPin People who are BI experts. I requested Reeves from LinchiPin people to help me answer this unique question.

In this episode of the Notes from the Field series database expert Reeves Smith explains what is Biml and How Can it Help Me with SQL Server BI?. Read the experience of Reeves in his own words.


With all of the new technologies to learn and implement, I wanted to introduce you to some of the benefits of spending the time to learn Business Intelligence Markup Language (Biml). You can use this XML-based language to create and automate Microsoft SQL Server Business Intelligence (BI) objects. Biml currently supports BI objects like SSIS packages and SSAS dimensions, measures, and cubes.

After a quick overview of Biml, I’ll demonstrate how to use it by providing a walkthrough example.

Overview

You can use Biml to create tables, flat files, SSIS packages, and SSAS models. This language is human readable, unlike the XML that is represented within dtsx files and SSAS objects. This readability can help reduce the learning curve required for most programming languages.

Biml will not teach you SQL Server Integration Services (SSIS) or SQL Server Analysis Services (SSAS) but can enhance and increase your productivity with these tools with certain use cases. Biml is not the silver bullet that can solve all of the SSIS/SSAS development project problems, but I will discuss some of the scenarios where Biml excels.

The Biml languages is supported within two Integrated Development Environments (IDEs): Business Intelligence Development Studio (BIDS) and SQL Server Data Tools (SSDT), with the BIDS Helper add-in (free) or the Mist application from Varigence (purchased product).  Currently BIDS Helper is supported with SQL Server 2005, 2008, and 2008 R2 BIDS, and SQL Server 2012 SQL Server Data Tools. This article will focus on the BIDS Helper add-in and its capabilities with SSIS.

Simple Walk Through

This walkthrough will show how to create a Biml file that creates an SSIS package to move data from the AdventureWorks database to a staging database. The following script file will create all of the needed objects. If you have the AdventureWorks database installed and a table called HumanResources.Department, you can skip Listing 1.

CREATE DATABASE [AdventureWorks]
GO
USE [AdventureWorks]
GO
CREATE SCHEMA [HumanResources] AUTHORIZATION [dbo]
GO
CREATE TABLE [HumanResources].[Department]
(
[DepartmentID] [smallint] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[GroupName] [nvarchar](50) NOT NULL,
[ModifiedDate] [datetime] DEFAULT (GETDATE()) NOT NULL
)
ON [PRIMARY]
GO
INSERT INTO [HumanResources].[Department] ([Name], [GroupName]) VALUES (N'Engineering',N'Research and Development')
INSERT INTO [HumanResources].[Department] ([Name], [GroupName]) VALUES (N'Tool Design',N'Research and Development')
INSERT INTO [HumanResources].[Department] ([Name], [GroupName]) VALUES (N'Sales',N'Sales and Marketing')
INSERT INTO [HumanResources].[Department] ([Name], [GroupName]) VALUES (N'Marketing',N'Sales and Marketing')
INSERT INTO [HumanResources].[Department] ([Name], [GroupName]) VALUES (N'Purchasing',N'Inventory Management')
INSERT INTO [HumanResources].[Department] ([Name], [GroupName]) VALUES (N'Research and Development',N'Research and Development')
INSERT INTO [HumanResources].[Department] ([Name], [GroupName]) VALUES (N'Production',N'Manufacturing')
INSERT INTO [HumanResources].[Department] ([Name], [GroupName]) VALUES (N'Production Control',N'Manufacturing')
INSERT INTO [HumanResources].[Department] ([Name], [GroupName]) VALUES (N'Human Resources',N'Executive General and Administration')
INSERT INTO [HumanResources].[Department] ([Name], [GroupName]) VALUES (N'Finance',N'Executive General and Administration')
GO

asdfas
Listing 1 – AdventureWorks Database Objects

CREATE DATABASE [AdventureWorksStage]
GO
USE [AdventureWorksStage]
GO
CREATE SCHEMA [HumanResources] AUTHORIZATION [dbo]
GO
CREATE TABLE [HumanResources].[Department]
(
[DepartmentID] [smallint] PRIMARY KEY NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[GroupName] [nvarchar](50) NOT NULL,
[ModifiedDate] [datetime] NOT NULL
)
ON [PRIMARY]
GO

Listing 2 – AdventureWorksStage Database Objects

To check that you have BIDS Helper installed within your Visual Studio environment, select the Tools menu option and select Options.The BIDS Helper option will display on the left dropdown window if it is installed, as you can see in Figure 1. If you need help installing the BIDS Helper add-in see: Step 2 in the Stairway to Biml: Biml Basics – Level 2 (link).

Figure 1 – Options windows with BIDS Helper installed

After verifying the BIDS Helper is installed, open a new SSIS Project from the File menu, and select New, then Project as Figure 2 shows.

Figure 2 – New Integration Services Project

Due to a Visual Studio limitation, right clicking on the Miscellaneous folder will not bring up the Biml context menu, which you see in Figure 3. Instead, from the Project Window, select the Project folder or the SSIS Packages folder, right click and select Add New Biml File. A new Biml file (BimlScript.biml) is added to the Miscellaneous folder.

Figure 3 – Biml Context Menu

Replace all of the code within the Biml File with the code from Listing 3.

<Biml
 xmlns="http://schemas.varigence.com/biml.xsd">
 <Connections>
 <OleDbConnection Name="AdventureWorks" ConnectionString="Provider=SQLNCLI10;Integrated Security=SSPI;Initial Catalog=AdventureWorks;Data Source=localhost;" />
 <OleDbConnection Name="AdventureWorksStage" ConnectionString="Provider=SQLNCLI10;Integrated Security=SSPI;Initial Catalog=AdventureWorksStage;Data Source=localhost;" />
 </Connections>
 <Packages>
 <Package Name="HumanResources_Department_Biml" ConstraintMode="Linear" >
 <Tasks>
 <Dataflow Name="Data Flow Task">
 <Transformations>
 <OleDbSource Name="OLE DB Source" ConnectionName="AdventureWorks">
 <ExternalTableInput Table="[HumanResources].[Department]" />
 </OleDbSource>
 <OleDbDestination Name="OLE DB Destination" ConnectionName="AdventureWorksStage">
 <InputPath OutputPathName="OLE DB Source.Output" />
 <ExternalTableOutput Table="[HumanResources].[Department]" />
 </OleDbDestination>
 </Transformations>
 </Dataflow>
 </Tasks>
 </Package>
 </Packages>
</Biml>

Listing 3 – Sample Biml code

Note: The connection information in the Biml file must point to the SQL Server instances that contain the AdventureWorks and AdventureWorksStage databases. If you are using another version of SQL Server the provider information might need to be changed: Provider=SQLNCLI10;

After replacing the code in the Biml file with the code in Listing 3, right click the file and choose the Check Biml for Errors menu option shown in Figure 4.

Figure 4 – Check Biml for Errors

If everything is configured correctly, you will receive the message shown in See Figure 5.

Figure 5 – No errors

If you receive any errors, you will need to correct them before selecting the Generate SSIS Package menu option.

Select the Generate SSIS Package menu option to create a new integration services package. A working package will be created within the Packages folder named: HumanResources_Department_Bimlas defined within the Package Name parameter within the Biml file.

Additional Walk-Through Examples

To add an Execute SQL task to truncate the stage table prior to the load add the following code after the <Tasks> node and before the <DataflowName=”Data Flow Task”>

<ExecuteSQLName="Truncate Table"ConnectionName="AdventureWorksStage">
 <DirectInput>TRUNCATE TABLE [HumanResources].[Department]</DirectInput>
</ExecuteSQL>

At this point in the walk-through Biml has not saved you much effort, but what if you wanted to create additional SSIS packages for all for the HumanResources tables within the Adventure works database? How much development effort would it take to update the Biml file to point to the each of the tables in the HumanResources schema and select Generate SSIS Package? Would those packages be consistent with the packages built prior? Would the prior testing of the previous package have a lot of benefit to the newly created package?

At this point copy and paste the Biml code within a new Biml file and move data from other tables. All of the table objects will need to exist, so you might have to add some staging tables to your stage database.

Practical Use

The first question that comes up after starting a discussion on Biml is, “Why would I us Biml?” I can create all of the SSIS packages I need in the current IDE.

One use case for Biml is the automation of similar design patterns like adding more packages to the walkthrough above. SSIS packages typically move data from a source to a destination. This pattern is repeated for each source and destination and can produce multiple SSIS packages. With some SSIS design patterns, the only items that change are the source table name and the destination table name.  What would it be like to spend the time to properly design and test a package to move data from one table to another and then duplicate that package with ease and only change the relevant information?  Biml enables this type of development.  With Biml, you are able to focus the development effort on design and remove some of the repetitive work.

As the variation of the packages increases between each package, Biml can become a less viable option. With Biml Script (a scripting language within Biml), you are able to create designs that vary and can adapt to changing Extract-Transform-Load (ETL) requirements.  Biml Script enables you to programmatically change items within the file without using cut and paste. (Biml Script was not demonstrated within this article.)

Looking Ahead

Biml is much more robust than what was demonstrated in this article but I wanted to start the discussion with a simple example to get you familiar with Biml. Adding Biml Script can create programmatic solutions that can automate SSIS package development. In a future article I will demonstrate how to add Biml Script to the existing code to increase package automation.

To enable follow along with the upcoming articles, install the AdventureWorks database from the following URL:. This database will also give you additional options to test and experiment with.

Side Note: The Mist IDE is able to extend Biml even further by updating multiple SSIS packages. This enables a maintenance option that has never been available within the SSIS development environment. It’s really cool, but something I will have to discuss in the future article

Stay Tuned.

If you want to get started with BIML with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Automate Database Operations for Success – Notes from the Field #035

[Note from Pinal]: This is a 35th episode of Notes from the Field series. Doing this faster and easier is always our goal. We all want to do things which generates maximum return of investment with least efforts. This is a catch 22 situation quite often when it is about database administrators.

In this episode of the Notes from the Field series database expert Brian Kelley explains a how to automate various database administrator tasks for success of business and our efforts. Read the experience of Brian in his own words.


In the Linchpin People mindset, it’s not about how busy you are, but how valuable you are. You don’t increase your value to your organization by your busyness. You do so by accomplishing the work. There are two parts to this:

  • Accomplish more work.
  • Accomplish more important work.

Initially, if your path follows most people, you’ll be asked to do more. This is your opportunity to accomplish more work. As you succeed with the additional work that you receive, you will likely be given opportunities to take on more important work. If this never happens, you’re probably at the wrong organization. Let’s assume you are at a good one and you’re given more important tasks. Obviously, if you succeed at the more important work, you’ll continue to be given more opportunities. And you’ll likely be given tasks and projects that are more and more important to your organization. This is how you become a linchpin.

So how do you complete more work? One part of the answer is automation. Since we’re IT professionals, automation should be near and dear to us. I recently wrote about being a “lazy” DBA. I used the word lazy was to indicate there are some manual tasks we don’t want to repeat. A “lazy” engineer or IT pro tries to automate these tasks in order to reduce the time spent with these tasks.  That frees up the IT pro to have more time for the more important work.

Let’s look at some things that we should automate as DB Pros:

Database Administration:

Build scripts that can do the following:

  • Check disk space on servers.
  • Check database available space.
  • Check security on key servers and databases.
  • Verify backups were taken properly.
  • Perform test restores of key backups.
  • Parse the SQL error log on each SQL Server for important information (failed logins, use of sp_configure, etc.).

For instance, if I want to check backups to ensure a full backup has run on every database within the last 24 hours, I might automate running the following query to report back the datbases where I do not have the proper backup:

SELECT D.name,
BS.database_name,
Isnull(CONVERT(VARCHAR, BS.lastbackup, 106), ‘No recent backup’) AS
LastBackup
FROM   master.sys.databases AS D
LEFT JOIN (SELECT database_name,
Max(backup_finish_date) AS LastBackup
FROM   msdb.dbo.backupset
WHERE  type = ‘D’
GROUP  BY database_name) ASBS
ON D.name = BS.database_name
WHERE  BS.database_name IS NULL
OR BS.lastbackup < ( Dateadd(hour, -24, Getdate()))
ORDER  BY D.name; 

We should also use automation like policy based management or custom scripts to enforce settings. Some examples that we should consider:

  • database ownership
  • recovery models
  • membership in key roles (sysadmin, securityadmin, db_owner, etc.)

And here if I knew every user database on a given server should be in full recovery mode, I can ensure that if I schedule the following script:

DECLARE cursdbs CURSOR fast_forward FOR
SELECT name
FROM   sys.databases
WHERE  state_desc = ‘ONLINE’
AND recovery_model_desc <> ‘FULL’
AND name NOT IN ( ‘master’, tempdb, msdb, ‘model’);
DECLARE @DBName SYSNAME;
DECLARE @SQL NVARCHAR(max);
OPEN cursdbs;
FETCH next FROM cursdbs INTO @DBName;
WHILE ( @@FETCH_STATUS = 0)
BEGIN
PRINT ‘ALTERING DATABASE: ‘ + @DBName;
SET @SQL = ‘ALTER DATABASE [‘ + @DBName
+ ‘] SET RECOVERY FULL;’;
EXEC (@SQL);
FETCH next FROM cursdbs INTO @DBName;
END
CLOSE cursdbs;
DEALLOCATE cursdbs; 

You do want to review that output. After all, if you just switched the DB to full recovery mode, you want to ensure you restart the log backup chain with a full or differential database backup.

Database Development:

Encourage continuous integration methods to include database code. This will require tests to validate no new code “breaks the build.” Make sure that these builds come directly from source control.

If you are doing tests that require restores of databases and the application of scripts, write the automation to do these tasks. It makes the tasks repeatable, it reduces the possibility of error, and it frees you up so you don’t have to manually run each step.

With that said, write scripts for anything you will have to repeat when developing a solution. For instance, you might need scripts to:

  • Add, delete, or change data.
  • Change security on a database or server.
  • Encrypt / decrypt setup data.

Can you automate too much?

Yes, you can. Note that in both cases I did include some reporting. If you build automation where you’re not doing any checking, that’s wrong. Automation eliminates you from having to do tedious steps. It doesn’t remove your responsibility/accountability. If you don’t have anything to check, you don’t actually know if the work was completed successfully. Don’t assume. Check.

If you want to get started with performance tuning and database security with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Windows File/Folder and Share Permissions – Notes from the Field #029

[Note from Pinal]: This is a 29th episode of Notes from the Field series. Security is the task which we should give it to the experts. If there is a small overlook or misstep, there are good chances that security of the organization is compromised. This is very true, but there are always devils’s advocates who believe everyone should know the security. As a DBA and Administrator, I often see people not taking interest in the Windows Security hiding behind the reason of not expert of Windows Server. We all often miss the important mission statement for the success of any organization – Teamwork. In this blog post Brian tells the story in very interesting lucid language. Read On!

In this episode of the Notes from the Field series database expert Brian Kelley explains a very crucial issue DBAs and Developer faces on their production server. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of Brian in his own words.


When I talk security among database professionals, I find that most have at least a working knowledge of how to apply security within a database. When I talk with DBAs in particular, I find that most have at least a working knowledge of security at the server level if we’re speaking of SQL Server. One area I see continually that is weak is in the area of Windows file/folder (NTFS) and share permissions.

The typical response is, “I’m a database developer and the Windows system administrator is responsible for that.” That may very well be true – the system administrator may have the primary responsibility and accountability for file/folder and share security for the server. However, if you’re involved in the typical activities surrounding databases and moving data around, you should know these permissions, too. Otherwise, you could be setting yourself up where someone is able to get to data he or she shouldn’t, or you could be opening the door where human error puts bad data in your production system.

File/Folder Permission Basics:

I wrote about file/folder permissions a few years ago to give the basic permissions that are most often seen. Here’s what you must know as a minimum at the file/folder level:

  • Read – Allows you to read the contents of the file or folder. Having read permissions allows you to copy the file or folder.
  • Write  – Again, as the name implies, it allows you to write to the file or folder. This doesn’t include the ability to delete, however, nothing stops a person with this access from writing an empty file.
  • Delete – Allows the file/folder to be deleted. If you overwrite files, you may need this permission.
  • Modify – Allows read, write, and delete.
  • Full Control – Same as modify + the ability to assign permissions.

File/Folder permissions aggregate, unless there is a DENY (where it trumps, just like within SQL Server), meaning if a person is in one group that gives Read and antoher group that gives Write, that person has both Read and Write permissions.

As you might expect me to say, always apply the Principle of Least Privilege. This likely means that any additional permission you might add does not need Full Control.

Share Permission Basics:

At the share level, here are the permissions.

  • Read – Allows you to read the contents on the share.
  • Change – Allows you to read, write, and delete contents on the share.
  • Full control – Change + the ability to modify permissions.

Like with file/folder permissions, these permissions aggregate, and DENY trumps.

So What Access Does a Person / Process Have?

Figuring out what someone or some process has depends on how the location is being accessed:

  • Access comes through the share (\\ServerName\Share) – a combination of permissions is considered.
  • Access is through a drive letter (C:\, E:\, S:\, etc.) – only the file/folder permissions are considered.

The only complicated one here is access through the share. Here’s what Windows does:

  1. Figures out what the aggregated permissions are at the file/folder level.
  2. Figures out what the aggregated permissions are at the share level.
  3. Takes the most restrictive of the two sets of permissions.

You can test this by granting Full Control over a folder (this is likely already in place for the Users local group) and then setting up a share. Give only Read access through the share, and that includes to Administrators (if you’re creating a share, likely you have membership in the Administrators group). Try to read a file through the share. Now try to modify it. The most restrictive permission is the Share level permissions. It’s set to only allow Read. Therefore, if you come through the share, it’s the most restrictive.

Does This Knowledge Really Help Me?

In my experience, it does. I’ve seen cases where sensitive files were accessible by every authenticated user through a share. Auditors, as you might expect, have a real problem with that. I’ve also seen cases where files to be imported as part of the nightly processing were overwritten by files intended from development. And I’ve seen cases where a process can’t get to the files it needs for a process because someone changed the permissions.

If you know file/folder and share permissions, you can spot and correct these types of security flaws. Given that there are a lot of database professionals that don’t understand these permissions, if you know it, you set yourself apart. And if you’re able to help on critical processes, you begin to set yourself up as a linchpin (link to .pdf) for your organization.

If you want to get started with performance tuning and database security with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)