I have been loading large volumes of data into SQL Server. The requirement was to load an initially empty table, then repeatedly load large volumes of data (over 1 million rows a time) into the already populated table. I tested two approaches. First, I dropped the existing clustered index, populated the table and re-built the index. … Continue reading
Probably one of the main difficulties when learning clustering is the lack of a test platform. This series of posts will show you how to build a SQL Server 2008 cluster on a low-spec laptop. The approach that I am using is not the approach that I would use for a production system. This post … Continue reading
Reporting Services will not start. In SQL SERVER Configuration Manager, the service is shown as stopped. The event viewer shows the below error in the windows Logs – system: A timeout was reached (30000 milliseconds) while waiting for the SQL Server Reporting Services (MSSQLSERVER) service to connect. In my environment, the following steps (from http://support.microsoft.com/?kbid=884495) … Continue reading
I configured a transactional replication process to move data – including Word documents – to a subscriber. This process started to fail on an occasional basis. I re-created the error as follows. First, to demonstrate the limit on LOB data, I executed the below TSQL on a test system: insert [dbo].[Documents] ([DocID],[Document]) values (34599,replicate(cast(‘a’ as … Continue reading
When a database is replicated, several new objects are created on your system. This post will tell you what to expect to see. To start, I set up transactional replication between two servers. The Publication server acts as its own distributor. I am run all agents at the publisher. On my publisher, I have the … Continue reading
The use of Identity columns in a transactional replication topology is not supported. However, identity columns are commonly used in database design as keys. This blog will consider what can happen if a database with identity columns is replicated. To test what can go wrong, I created a table (T1) with an identity column (C1). … Continue reading
I have been running SQL Server on a virtual platform (vSphere from VMware) for several years. This post describes my approach to disk organisation. I have had to think about this post because I suspect that some of this will be controversial – it is my approach, and it seems to work for me. SQL … Continue reading
It may surprise some to know that a trigger does not require a change to the table to fire. This post will explore what happens when a trigger fires but no data is changed. First, I will create a table and a trigger. create table Table1(id int , Iname varchar(250)) go create trigger trg_insTable1 on … Continue reading
I recently had to access a server that has not been used for several years. No windows login or SQL Server logins gave me access. This post will describe how I used SQLCMD to add the windows administrator login to the sysadmin role. First, I change the SQL Server startup flags to use the -m … Continue reading
In SQL Server 2012, additions to the ORDER BY clause – OFFSET and FETCH – allows paging through data sets. I will briefly describe this feature used alongside the WITH clause and consider the performance impact of the tables sort order. First of all, I think that this is a useful enhancement – I have … Continue reading