I was curious to look at the effect of database options, on the SQL server plan cache.
I have restored the Adventure works sample database onto my SQL Server 2008 R2 RTM instance four times as AW1, AW2, AW3 and AW4.
I have used, and slightly modified, a query from… Read more
In order to query the configuration settings of a server you can run this sproc:
On a default install of sql server this will return 16 rows. In order to see all rows you need to run this:
exec sp_configure 'show advanced options',1 reconfigure
Now when you run… Read more
Replication is one of the more complex of the SQL native “HA/DR” technologies. There are a lot of moving parts. Here are the steps to configure a basic transactional replication topology, with a publisher and distributor on the same instance and using push subscriptions. Most of the stored procedures have… Read more
I was working with a group of students on a design brief. The brief was to partially implement a database design based on the LinkedIn web site. A discussion came up around the possibility of implementing the contact preferences (essentially a set of boolean values) as bitwise data, so I… Read more
This is a very cool and useful tip if you find yourself locked out of the sysadmin role in sql server.
I have a SQL Server instance with the sa account disabled and all other logins removed to simulate a situation were I am locked out of SQL Server. My… Read more
I came across an issue today where a backup process was in a blocked state with a wait type of MSSEARCH. The issue boiled down to an issue with communication/contention between the SQL Server engine service and the SQL Server Full Text Search service.
An attempt to kill the backup… Read more
So I decided to put a demo together to test out the security risks involved.
I have a default instance and… Read more
While researching the steps required to service pack SQL Server on an active active cluster I came across a lot of conflicting information. Here are the steps I ultimately followed for an error and issue free update.
1. Make a note of all node names (and/or IP addresses), SQL Server… Read more
The other day I was asked to restore a production database into a development environment and then check the database permissions. So after restoring the database I ran
use 'database name' exec sp_change_users_login 'report'
to see if there were any orphaned users. This came back clean so I informed the… Read more