Press "Enter" to skip to content

Category: Administration

SQL Server’s Basic Installer

Derik Hammer walks through the new SQL Server Basic Installer:

When using the Basic Installer only the database engine and SQL Client Connectivity SDK are installed. I find this better than using the advanced installer’s Install with all defaults option. Typically any local user will not need Reporting Services, Integration Services, Analysis Services, or any other feature available.

I was a bit concerned about this when it was first announced—the default installer already allows you to make too many poor decisions—but I think it works within the use case Derik describes:  developers installing a local edition on their dev boxes.

Comments closed

Stretch Database

SQL Padre checks out Stretch database:

As you can see, we now have a new path in our query plan with an operator called “Remote Query”.  Basically the local server queries the remote query then using the local Primary key Concatenates them back together to produce the desired result. So can we update the data?

Nope, sure can’t.  Once the data lives in Azure, the data is READ ONLY.

Check it out.  He’s a bit more sanguine about Stretch than I am, so maybe it will fit your use cases.

Comments closed

Auditing Power Plan Settings

Drew Furgiuele writes a bit of Powershell to control power plan settings and expands upon this one-liner:

It’s a classic one-liner, but if you’re not used to reading it I’ll break it down for you. First, we useGet-ChildItem  to return a list of registered servers in our central management server (named PRO-CM-SQL in my example). This returns a series of objects that lists all the registered names on the central management server in each directory, so we need to filter out the directory names with a Where-Object  (objects that don’t have a “mode” value of “d” for directory). Once we have our list, we just select the names ( Select-Object ). Then we pipe the list of names over to a ForEach-Object  and execute the script each time. Finally, we tack on a Export-CSV  cmdlet to output the results to an easy to read file we can open in Excel (or notepad, or whatever).

Our script also doesn’t control output, so you leave that up to the user. They can put it on the screen or pipe it to a file. And that’s an important style point: never put your users on rails. You may like output one way, but someone else may not. Just think of the next person.Because some day you might be that next person.

This is a good post if you need to figure out how to find your servers’ current power settings, but a great post if you want to think about how to write helpful Powershell scripts.

Comments closed

More On DBCC CLONEDATABASE

Melissa Connors digs into the DBCC CLONEDATABASE command:

Finally, keep in mind that because the clone is a read-only, empty database, you should be able to test repeatedly without updating statistics and skewing your results. Since I wanted to see this for myself, I executed a set of updates and selects against the SQLSentryData and SQLSentryDataClone databases. As a result of the lack of data and read-only database status, there were no actual updates in SQLSentryDataClone. Consequently, the statistics were updated in the SQLSentryData database, but remained the same in the SQLSentryDataClone database:

Read the whole thing.

Comments closed

A Wish For Shutdown Procedures

Dave Mason wants shutdown procedures like how we can have startup procedures:

If ‘shutdown’ was valid for the @OptionName parameter, I know exactly what I’d do: configure sp_procoption to execute a stored proc that sends an email alert. It would be great to know when a SQL instance goes down, wouldn’t it? Looking at the log file, we can see entries indicating the instance was shut down:

Dave does mention potential failure scenarios, but I agree:  it’d be nice to have the ability to run procedures at shutdown to perform certain actions.

Comments closed

DBCC CLONEDATABASE

Kennie Pontoppidan talks about a new DBCC command:

This command enables you to take a copy of the schema and the statistics for an existing database into a new database. According to the documentation, DBCC CLONEDATABASE

  • Creates a new destination database that uses the same file layout as the source but with default file sizes as the model database.

  • Creates an internal snapshot of the source database.

  • Copies the system metadata from the source to the destination database.

  • Copies all schema for all objects from the source to the destination database.

  • Copies statistics for all indexes from the source to the destination database.

I knew there were ways of scripting out statistics, but DBCC CLONEDATABASE looks like a new and interesting beast.

Comments closed

Infrastructure Error

Suresh Kandoth explains the various causes of “Login-based server access validation failed with an infrastructure error” error messages:

The important piece of information from that message is the part about server access validation failed. So let’s examine that carefully. During the login process, the database engine has to perform several checks regarding the login and its various attributes before letting the application connect to the SQL Server instance. Out of that big list, related to this error condition, there are two checks to find out if this login is authorized to access this server instance. There are two permissions that come into play while performing these authorization checks:

– Does the login have the SERVER class permission named CONNECT SQL for this server instance?

– Does the login have the ENDPOINT class permission named CONNECT for the specific endpoint on which the client application established the connection?

Suresh lays out a number of scenarios and explains why they could cause this error to occur.

Comments closed

TempDB Configuration

Sander Stad looks at the new TempDB defaults in 2016:

The initial size is set to 8MB. I would never create TempDB data files with a size of 8 MB which in my opinion is really small for a data file even for small systems.

In most cases I would look at the size of the TempDB disk (because you create a desperate disk for TempDB right…) and fill up the entire disk with data files. In this case I would create 4 x 5 GB files to leave some space for the log file.

To properly set the initial size of the TempDB you still need to go through some checks how much your system might need.

Read the whole thing.

Comments closed

Buffer Pools And Availability Groups

Joey D’antoni answers the question, what happens to pages in the buffer pool when you have an AG failover?

Recently at SQL Saturday Philadelphia, we started discussing failover  as it relates to mirroring and Always On Availability Groups. Specifically, we were wondering what would happen if you had a relatively busy readable secondary replica (which would have a lot of pages in the buffer pool on the secondary instance) and if those pages would be flushed from cache or anything like that. So I reached out to the product group and Kevin Farlee from Microsoft was extremely helpful

The answer is not what I was expecting.

Comments closed