Part 9 – BSD FreeNAS: Create a Shared Storage Device for your fail over cluster SQL Nodes.

At this point we need to create a shared storage device where both Nodes (1-2) will write. On these nodes we will install SQL. Both nodes will access the same storage device. So if Node1 fails for example, Node2 will be aware of the state of the data because it will keep reading the data on the shared storage device.

We will use FreeNAS to create this shared storage. This too will be a virtual server. We will dedicate 40Gigs of hard drive space for the actual Operating System to be installed. We will also dedicate 400Gigs of hard drive space that the Nodes will be using.

In addition to that we will dedicate another 500MBs  of Hard Drive space that will be used as a Quorum. The Quorum contains data that both Nodes read in order for all nodes to be aware of the state of the Cluster.

Continue reading

Share Button

.NET Runtime Optimization Service (clr_optimization_v2.0.50727_32) – Failed to compile: DataProjects.dll – External Component has thrown an exception

In my experience this so far has happened on Windows 2008 R2 64 bit platforms.

Upon installing SQL 2008 SP1 64bit I would encounter the following error.

Continue reading

Share Button

Dynamic Management Objects – sys.dm_exec_sessions

Dynamic Management Objects (DMOs) are server objects which are being referenced in the SYS schema. These objects hold information regarding the internal activities of SQL. DMOs are presented either as Dynamic Management Views (DMVs) which are actually views or as Dynamic Management Functions (DMFs) which are in table values functions. As such both can be used as views and functions respectevely.

Continue reading

Share Button

Buffer Pool and Memory Pressure in SQL 2008 Enterprise 64-bit

The overall philosophy of Microsoft is to create a database solution with low TCO (Total Cost of Ownership).  SQL-2005 and SQL-2008 dynamically adjust their buffer pool. MS SQL it self will claim as much physical memory as it needs with out releasing it back unless requested to do so. Even if the server it self is not active SQL will keep claiming memory. It will do so up to the point before paging (Windows Page file). It is very typical to see a Windows server running SQL to be using about 500MBs of memory right after a reboot only to see that number increase to the range of GBs after few hours even when the server it self is not busy.

Continue reading

Share Button

MS SQL System Databases

SQL Instances:

Each instance of MS-SQL is a unique instance and shares nothing with the other instances residing on the same or different server. If more than once instances are running on the same server only one can be the default instance. In order to connect to a named instance you have to specify the name of the server and the name of the instance.

DATABASES in MS SQL
Are a collection of tables, stored procedures, triggers, views and so on.  One instance of a SQL Server can operate several databases.  Upon install MS_SQL creates several databases that it needs for its operations such as: Continue reading

Share Button

How to Change Instance Name of a MS-SQL Server

This command shows you how to change the instance name on an MS-SQL server. Use the following commands

To find the current server name:

sp_helpserver
select @@servername

Then run the following statement to change the instance name:

sp_dropserver  'old_name'
go
sp_addserver 'new_name','local'
go

Continue reading

Share Button

MSXML6 Prerequisites not found missing – UNTESTED FIX

The machine might have two different errors running the MSXML6 installer.

Error 1 – MSXML6 (msxlm6.msi) install fails because “the system administrator has set policies to prevent this installation …”

Error 2 – “Installation of MSXML 6.0 Parser failed because a higher version is already exits on the machine. To proceed, uninstall the higher version and then run MSXML 6.0 Parser Setup again.”

So here’s the steps that fixed both, and allowed msxml6.mxi to be inastlled… and subsequently sql …

Continue reading

Share Button

MSXML6 Prerequisites not found missing – MS-SQL 2005

This is a know bug and Miscrosoft has not released a fix nor does it wish to release one in the future

Please note that this will not fully resolve your install issue but it might help you proceed with install the packages needed to attach the workstation on Replication. This fix is not guaranteed to work. Try to avoid RDP if you are going to follow this fix.

Continue reading

Share Button

How to unlock an account in MS-SQL

You will need to type the following command:

ALTER LOGIN name-of-account-you-want-to-unlock

WITH PASSWORD =’what-ever-the-password-is’  UNLOCK

Share Button