Basic settings for SQL Server 2012/2014, part 4

Part 4 – scripts for the previous parts Below are scripts for most of the SQL Server specific settings I discussed in the previous three parts. Please note that not quite everything is included (trace flags, instant file instantiation, etc.).   — Set memory amount sp_configure ‘show advanced options’, 1; GO RECONFIGURE; GO sp_configure ‘max server memory’, 40000; GO RECONFIGURE; GO — Turn on optimize for adhoc workloads EXECUTE sp_configure ‘show advanced options’, 1; GO RECONFIGURE; GO EXECUTE sp_configure ‘optimize for ad hoc workloads’, 1; GO RECONFIGURE — Turn on DAC EXECUTE sp_configure ‘remote admin connections’, 1; GO RECONFIGURE; GO — Adjusting TEMPDB ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N’tempdev’, SIZE = 100MB , FILEGROWTH = 50MB); GO — Script to create TEMPDB files like the one existing USE [master] go […]

Migrating databases, part 2

Having gone through the truly offline ways of transferring data, let’s take a look at log shipping and database mirroring. These are fairly old techniques as both of them came into being back in SQL Server 2005. The concept is very simple – every once in a while data from the transaction logs are sent […]

Migrating databases, part 1

Migrating databases Most of us poor souls that for some reason enjoy this work called “database administration” get to meet “Mr. Migration” from time to time. I’ve lost count of the number of customers in need of moving one or more (most often A LOT) of databases from one server to another. The reasons for […]

Basic settings for SQL Server 2012/2014, part 3

Part 3: simplifying the life of the DBA When I’ve got the environment set up quite the way I like it, I usually set up a repository where I keep all my scripts. I designate one server as the “master” for my purposes, and set up a database called DBADB. In here I put all my […]

Double Whammy

So there I was, gearing up for a trip to a customer 90 minutes away and my audiobook had just finished. Having listened to Matan Yungman at SQL Pass in Copenhagen, I vaguely remembered him saying something about SQL Server Radio, so I decied to check it out. Turns out they’ve been doing it for […]

Basic settings for SQL Server 2012/2014, part 2

Updated: 2016-11-07 Part 2: settings inside SQL Server When the server is all good and installed, it’s time to go through and set up a bunch of server settings. First of all – dedicated admin connections. This is used as a “get out of jail for free”-card for a DBA if all the schedulers are […]

Dude, where’s my disk?

Today I took a look at a clients 10g Oracle RAC environment where one node apparently had started misbehaving quite some time ago. The scenario was that both nodes were up, cluster ready services was up, nodeapps were up, ASM was up but the instance was down. Starting from the top with the database alert […]

Basic settings for SQL Server 2012/2014, part 1

Whenever I set up an SQL Server I usually do the same things. I’ve collected a list of best practice settings over the years (and most often put them in a script, as most of my friends are scripts). Throughout a few blog posts I’ll go over the settings I do use and discuss a […]

Failover on Oracle Standard Edition?

We have all heard (and gnashed our teeth over) that in order to get a proper automatic failover environment for Oracle (i.e Data Guard) we need Enterprise Edition. Oracle Enterprise Edition (or as Brent Ozar so succinctly put it: Expensive Edition) is hideously expensive, especially if you’re only ever going to use the Data Guard […]