background preloader

SQL Server

Facebook Twitter

Error Handling in SQL Server – a Background. Configuring the Windows Firewall to Allow SQL Server Access. Firewall systems help prevent unauthorized access to computer resources.

Configuring the Windows Firewall to Allow SQL Server Access

If a firewall is turned on but not correctly configured, attempts to connect to SQL Server might be blocked. To access an instance of the SQL Server through a firewall, you must configure the firewall on the computer that is running SQL Server to allow access. The firewall is a component of Microsoft Windows. You can also install a firewall from another company. This topic discusses how to configure the Windows firewall, but the basic principles apply to other firewall programs.

Users familiar with the Windows Firewall item in Control Panel and with the Windows Firewall with Advanced Security Microsoft Management Console (MMC) snap-in and who know which firewall settings they want to configure can move directly to the topics in the following list: Microsoft SQL Server Shutting Down Every Night - Microsoft SQL Upgrade - Network Helpdesk. I ran across an issue on a client's SQL Server 2005 Express instance.

Microsoft SQL Server Shutting Down Every Night - Microsoft SQL Upgrade - Network Helpdesk

The problem was that at 4:01am every morning the SQL server would just shutdown with the event messages of Service Broker manager has shut down.SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. SQL Server: More light shed on “non-yielding scheduler” and Indexes stats. Understanding the error “An operation on a socket could not be performed because the system lacked sufficient buffer space or because a queue was full.” - SQL Protocols. This error message, which is associated with the Winsock error WSAENOBUFS and actually comes from Windows rather than SQL Server directly, has two well-understood root causes.

Understanding the error “An operation on a socket could not be performed because the system lacked sufficient buffer space or because a queue was full.” - SQL Protocols

However, it still stumps people today, probably because there is no single source which explains both root causes and offers solutions for both. Here is a description of the two common situations where you may see this error and quick solutions for each: OS runs out of memory for TCP buffers When a powerful client machine, especially one with lots of RAM, is running an x86 version of Windows, people use the /PAE switch in the c:\boot.ini file to allow applications on that machine to be able to address the full range of memory. One other switch often used to give more memory to applications is the /3gb switch in the boot.ini file. Solution: Remove the /3gb switch from c:\boot.ini. OS runs out of available TCP “ephemeral” ports. Scan network for SQL Server instances. Scan network for SQL Server instances by Ted Krueger (onpnt) on March 9, 2009 in category Database Administration Microsoft SQL Server Admin.

Scan network for SQL Server instances

Article views: 17,413 Instapaper Securing data services is part of a DBA’s critical tasks that should be performed. This goes beyond users, roles and schemas though. Given my change in careers and moving to the DBA side of things I quickly started realizing how bad this can be for the environment all together and honestly how bad some of the developers of these prepackaged solutions can really get. After saying all that I wanted to post how I audit the network for instances and maintain the knowledge of what I have out there and what may be potentially a problem.

Extended Events

Policy Based Management. Fix problems with programs that can't be installed or uninstalled. Installation - Manually removing sql server 2008. Upgrade from SQL 2005 to SQL 2008 might fail with MsiGetProductInfo error - Microsoft SQL Server Tips & Tricks. Consider a scenario where an upgrade from SQL 2005 to SQL 2008 fails with the following messages while installing Setup Support Files.

Upgrade from SQL 2005 to SQL 2008 might fail with MsiGetProductInfo error - Microsoft SQL Server Tips & Tricks

TITLE: SQL Server Setup failure. SQL Server Setup has encountered the following error: MsiGetProductInfo failed to retrieve ProductVersion for package with Product Code = '{0826F9E4-787E-481D-83E0-BC6A57B056D5}'. Error code: 1605.. From Detail_ComponentUpdate.txt - 2009-03-16 22:00:59 Slp: Inner exceptions are being indented. Unable to install SQL 2008 due to the remnants of a previous installation - SQLqueries. Sometime remnants of a previous installation/ a failed install can prevent us from reinstalling SQL server I have documented the consequence of one such instance which rendered us to be unable to install SQL 2008 from the server · When we launched the setup we got the following error :

Unable to install SQL 2008 due to the remnants of a previous installation - SQLqueries

How to: Uninstall an Existing Instance of SQL Server (Setup) This article describes how to uninstall a stand-alone instance of SQL Server.

How to: Uninstall an Existing Instance of SQL Server (Setup)

By following the steps in this topic, you also prepare the system so that you can reinstall SQL Server. Consider the following important information before you uninstall SQL Server: Before you remove SQL Server components from a computer that has the minimum required amount of physical memory, make sure that the page file size is sufficient. The page file size must be equal to two times the amount of physical memory. DROP DATABASE (Transact-SQL) Removes one or more user databases or database snapshots from an instance of SQL Server or user databases from the Windows Azure SQL Database server.

DROP DATABASE (Transact-SQL)

Transact-SQL Syntax Conventions -- SQL Server Syntax DROP DATABASE { database_name | database_snapshot_name } [ ,...n ] [;]

Clusters

Day 1 of 31 Days of Disaster Recovery: Does DBCC Automatically Use Existing Snapshot? 31 Days of Disaster Recovery Welcome to my series on Disaster Recovery.

Day 1 of 31 Days of Disaster Recovery: Does DBCC Automatically Use Existing Snapshot?

I will spend the entire month of January focusing on all things related to disaster recovery including topics like corruption, data integrity, data loss, DBCC commands, and more. For my first post of this month, I want to take a look at the myth that the DBCC CHECK commands will automatically use an existing database snapshot if one exists for the database. This is a myth I believed myself at one time and told to others.

Storage Engine

Indexes. CodePlex. Trimming More Transaction Log Fat. In my previous post on streamlining the operations of the transaction log, I discussed two of the most common causes of extra log records being generated: dead weight from unused nonclustered indexes and page split operations (that cause index fragmentation).

Trimming More Transaction Log Fat

Assuming you’ve read that post, I mentioned that there are more subtle problems that can be detrimental to transaction log performance, and I’m going to cover these here. Many, Many Tiny Transactions Every so often SQL Server will flush a portion of the transaction log to disk. A log flush occurs whenever: A transaction commit log record is generated.A transaction abort log record is generated at the end of a transaction roll back.60KB of log records have been generated since the previous log flush. The smallest log flush possible is a single 512-byte log block. In both cases, making transactions longer (which is very counter-intuitive!) Trimming the Transaction Log Fat. In many SQL Server workloads, especially OLTP, the database’s transaction log can be a bottleneck that adds to the time it takes a transaction to complete.

Most people assume that the I/O subsystem is the real bottleneck, with it not being able to keep up with the amount of transaction log being generated by the workload. Transaction Log Write Latency The latency of write operations to the transaction log can be monitored using the sys.dm_io_virtual_file_stats DMV and correlated with the WRITELOG waits that are occurring on the system.

I recorded a demo video of analyzing transaction log I/O back in 2011 so I won’t repeat all of that in this post. You can get the video here and the demo code here (suitable for running in production right away). If the write latency is higher than you’d expect for your I/O subsystem then the I/O subsystem cannot keep up, as is the general supposition.

Merrill Aldrich : Visualizing Data File Layout I. Part 1 of a blog series visually demonstrating the layout of objects on data pages in SQL Server Some years ago a gentleman called Danny Gould created a free tool called Internals Viewer for SQL Server. I’m a visual sort of guy, and I always thought it would be fun and educational to make a simple visualizer, like the one he created, in order to view how objects are laid out in SQL Server files, and to use it to demonstrate how operations like re-index and shrink affect the layout of files. To that end, and a little bit reinventing the wheel truth be told, I spent this past holiday creating a simple .NET app that renders the file layout of a database into a color-coded bitmap: Fig 1. Merrill Aldrich : Visualizing Data File Layout II.

Part 2 of a blog series visually demonstrating the layout of objects on data pages in SQL Server Part 1 In Part 1 of this series, I introduced a little demo app that renders the layout of pages in SQL Server files by object. Today I’ll put that app through its paces to show, in vivid color (well, teal, anyway) the destructive power of the famous Re-Index Then Shrink anti-pattern for index maintenance. Error on install : The INSTANCESHAREDWOWDIR command line value was not specified. I am installing on Windows Server 2008 64 bit. We had to use the command line option, and the server installed fine, but during the installation of the tools, the setup stopped with Unknown error. Here is the extract from my log file(s): Error message in the SQL Server Errorlog file after you upgrade SQL Server 2000 to SQL Server 2005 on a server that is running Windows Server 2003: "Unable to update password policy"

SQL Server Trace Flags. Disables lock escalation based on the number of locks. However, memory pressure can still activate lock escalation. The Database Engine escalates row or page locks to table (or partition) locks if the amount of memory used by lock objects exceeds one of the following conditions: · 40% of the memory that is used by Db Engine, exclusive of memory allocation using Address Windowing Extension (AWE). SAP on SQL Installation on Mount Points - Running SAP Applications on SQL Server - Site Home - MSDN Blogs. Several customers have recently asked about extending a disk online and installing SAP systems on Windows Mount Points. "UNDOCUMENTED" "UNDOCUMENTED"Definition: "Not supported by written evidence" ... (in BOL) Since the very first version of SQL Server, there are many undocumented functions, store procedures, and features... Many of them are only there for testing and deep debugging purposes (product support).

Others are left so that the SQL Server product developers could freely change those features in later releases or even service packs. SQL Servers Virtual Columns and Row Cracking.

Locks and Waits

My saved files on SlideShare. Sp_dbmmonitorresults (Transact-SQL) Returns status rows for a monitored database from the status table in which database mirroring monitoring history is stored and allows you to choose whether the procedure obtains the latest status beforehand. Transact-SQL Syntax Conventions sp_dbmmonitorresults database_name , rows_to_return , update_status database_name Specifies the database for which to return mirroring status. Database mirroring. SQL Server and the “Lock pages in memory” Right in Windows Server. Another Windows setting you might want to enable is “Lock pages in memory”. There is some controversy within the SQL Server community and within Microsoft about whether and when you should enable this setting. Before you decide whether you want to do this, let me give you some background. How to tell if you have instant initialization enabled? As you may already know, instant initialization is a way to prevent data (not log) file create and grow operations having to zero-initialize the new space before allowing it to be used.

This can vastly speed up these operations as zero-initialization can take a *long* time for large files. This is especially useful in disaster recovery operations as the first phase of a restore is always to create the requisite files, if they don't already exist. Cutting minutes or even hours from this phase can significantly reduce downtime. Using AWE Memory in SQL Server 2000. If you are using SQL Server 2000 Standard Edition under Windows NT 4.0 or Windows 2000 (any version), or are running SQL Server 2000 Enterprise Edition under Windows NT 4.0 or Windows 2000 Server, or if your server has 4GB or less of RAM, the “awe enabled” option should always be left to the default value of 0, which means that AWE memory is not being used. SQL Server 2005/2008/2012 Setup Checklist.

Over the last few years, I’ve put together a list of best practices for installing Microsoft SQL Server 2005 and 2008 and 2012. Other folks do more, but for small businesses, this is a good start. Great SQL Server Debates: Lock Pages in Memory. SetFileIoOverlappedRange Can Lead to Unexpected Behavior for SQL Server 2008 R2 or SQL Server 2012 (Denali) - CSS SQL Server Engineers. Scripting out SQL Server Logins. How to transfer logins and passwords between instances of SQL Server. Changing the default SQL Server backup folder. Sp_BLITZ – SQL Server Takeover Script. Immerse Yourself In SQL Server.