I was recently discussing with one of my peers the fact that following a periodical reindexing/index rebuild, the overall database performance was better when the old syntax was executed. To put you in the picture, until the SQL Server 2000 version, index fragmentation was handled using the DBCC DBREINDEX and DBCC INDEXDEFRAG commands. Since SQL Server 2005 the new ALTER INDEX syntax was introduced and Microsoft have been notifying all users of the SQL Server Books Online that the old command will
Posted in Database Administration, Performance
Tagged Code Samples, Coding Practices, Data Maintenance, Indexes, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, Upgrade
Two weeks ago, in the Data Driven Account Management – part 1 article, I demonstrated how a number of basic account management functions can be implemented for an SQL Server (2005 and later) database without granting elevated priviliges to end users. The article and attached script showed how logins, users and roles could be created and users added as members of the roles. The next logical step is to grant object permissions to the roles, permissions which would then be inherited by the database
When implementing and configuring a Two-Node Active/Active SQL Server Cluster you have to ensure that the memory allocation will suffice for the operating system and other services (a minimum of 2GB is reccomended), as well as for the eventuality when all instances would be on the same physical node. This means that if, for example, you purchased each of your nodes with 32GB of RAM, memory will be allocated as shown in the below table.
Posted in Database Administration, Performance, Virtualization
Tagged Architecture, Code Samples, DMV, Linked Servers, Microsoft Cluster, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server Agent, Windows
In previous posts I mention that Windows Authentication should be used and account management functions should be delegated to the domain (e.g. Active Directory). This however is not possible in all cases. Sometimes, due to specific application requirements, SQL Authentication has to be used. Account names (logins/users) should preferably follow a standard naming convention however this is not absolutely necessary. The maximum length allowed for a login/user name is 128 characters of Unicode text.
I am usually quite cautious when mentioning third-party tools since I don't have any affiliations with any company or product mentioned. In this case I want to make an exception. I recently came across an SSMS addin called SQL Code Guard.
The addin is free and, according to the author works with SSMS 2005, 2008, and 2008 R2 (confirmed...). Once installed three new menu items appear under the Tools menu as shown in the below figure.
The tool works by reviewing the schema of a database
Posted in Database Administration, Database Design, SQL Tools, T-SQL Programming
Tagged Audit, Code Samples, Coding Practices, Database Documentation, Development, SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server errors, T-SQL Programming, Testing, Upgrade