Microsoft makes it easier than ever to attend SQL Server 2008 training, at little to no cost, without any travel necessary.
It just needed a email account on hotmail or msn.
Subject matter includes:
· Database Administration
· Database architecture
· Database, programming and security.
Click the courses below to see full descriptions or visit Microsoft Learning to browse the course catalog
Use Command below to connect as DAC
SQLCMD -A |
It is on by Default for local server.
For remote(via network) enable it by running query below
sp_configure |
For Clusters, recommended to enable remote admin connections as no local DAC available
---- Query to Find if DAC - Dedicated Admin Connection used -----
select s1.session_id, s1.program_name,s1.host_name,t1.name from sys.tcp_endpoints t1 join on t1.endpoint_id = s1.endpoint_id where t1.name = --- End of the Query ----- |
--- Query to check the DAC ---- --- Scheduler_ID = 255 & Status = "VISIBLE ONLINE (DAC) --- select |
Usage:
when SQL Server hangs, SQL Server does not respond, when SQL Server does not accept new connection
Applies to | Version Range | Type | Description | Target Version | Release Date | Security Bulletins | KB | Download | |
SQL Server 2000 | Below 8.00.2039 | Service Pack | SQL Server 2000 SP4 | 8.00.2039 | 6-Jun-05 | ||||
SQL Server 2000 | 8.00.2039-8.00.2054 | GDR | SQL Server 2000 SP4 GDR | 8.00.2055 | 10-Feb-09 | ||||
SQL Server 2000 | 8.00.2148-8.00.2281 | QFE | SQL Server 2000 SP4 QFE | 8.00.2282 | 10-Feb-09 | ||||
SQL Server 2005 | Below 9.00.4035 | Service Pack | SQL Server 2005 SP3 | 9.00.4035 | 15-Dec-08 | N/A | |||
SQL Server 2005 | 9.00.4035-9.00.4052 | GDR | SQL Server 2005 SP3 GDR | 9.00.4053 | 12-Oct-09 | ||||
SQL Server 2005 | 9.00.4205-9.00.4261 | QFE | SQL Server 2005 SP3 QFE | 9.00.4262 | 12-Oct-09 | ||||
SQL Server 2008 | Below 10.00.2531 | Service Pack | SQL Server 2008 SP1 | 10.00.2531.00 | 27-Aug-09 | N/A |
| ||
It lists only the latest SQL Server Service Pack and Security Hot fixes as on 22nd April 2010 | |||||||||
Software Inventory Assistance
Whether we have just a few computers or a large network, a software inventory tool can make it easier to quickly know what we have and how it's being used.
The Microsoft Software Inventory Analyzer (MSIA) is a free tool available for download that can help us with our software inventory. It is built specifically to be a starting point to working with SAM.
We can use the MSIA to scan and inventory the Microsoft software that is installed on a single computer, or on multiple computers throughout a network. It generates a report that provides details of all installed Microsoft products, including the type and the number of licenses. The MSIA will work with networks that have 250 or fewer computers. The latest MSIA 5.1 features enable you to:
This information is useful for you, if you are taking care of the system administration, licensing and inventory of Microsoft software products in your organization.
BgInfo – Microsoft Windows SysInternal tool
How many times have you walked up to a system in your office and needed to click through several diagnostic windows to remind yourself of important aspects of its configuration, such as its name, IP address, or operating system version If you manage multiple computers you probably need BGInfo. It automatically displays relevant information about a Windows computer on the desktop's background, such as the computer name, IP address, service pack version, and more. You can edit any field as well as the font and background colors, and can place it in your startup folder so that it runs every boot, or even configure it to display as the background for the logon screen.
Below is the TSQL Query to find when the database last accessed for SQL Server 2005 / 2008
-- Query to find when the database last accessed on SQL Server 2005 / 2008 --- select d.name, x1 = (select X1= max(bb.xx) from ( select xx = max(last_user_seek) where max(last_user_seek) is not null union all select xx = max(last_user_scan) where max(last_user_scan) is not null union all select xx = max(last_user_lookup) where max(last_user_lookup) is not null union all select xx = max(last_user_update) where max(last_user_update) is not null) bb) FROM master.dbo.sysdatabases d left outer join sys.dm_db_index_usage_stats s on d.dbid= s.database_id group by d.name |
What would be the case. have you ever connected to a server, thought it was a testing or development system, and only a split second after you pressed "F5" to run that command, realized that it was the production server? Yeah….me neither :).
To make sure that's never you, when you start SQL Server 2008 Management Studio (SSMS), stop for a moment on the connection dialog and press "Options". You'll see that you can select a color for the connection – I set mine to red for production servers, yellow for testing servers, and green for development servers.
Once inside SSMS, you can also set the default color for all connections – I do this and set them all to red, so that if I forget, at least I'll have that visual indicator to make sure the change I'm making is on the right server.
It helps on classifying the SQL Server Instances by Production, Development, Test or QA etc.
Format the date or time without dividing characters, as well as concatenate the date and time string:
Sample statement | Output |
select replace(convert(varchar, getdate(),111),'/','') | 20100413 |
select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','') | 20100413 004426 |
Select cast(int, convert(char,getdate(),111)) | 20100413 |
When trying to connect using a remote desktop session you may get this Login Failure Message, because there are too many remote sessions
To resolve the exceeded connection issue execute "mstsc /admin" ( mstsc /console )command from the command prompt as shown below
If a terminal server has exceed the maximum number of sessions an "/admin" session can still be created and this will allow you to still connect to the server.
The /admin sessions don't count towards the session limit that may be configured on a terminal server to limit the number of remote sessions, so this will give you a back door into the server if you get the message "The terminal server has exceeded the maximum number of allows connections".
The SQL Server team is moving away from the current priority-driven hotfix release model to a scheduled delivery model. In the scheduled delivery model, a customer can receive a hotfix to address their most critical situations with a short turn-around time. Additionally, a customer can receive a fix that has undergone more testing and that is released on a schedule basis. Therefore, the SQL Server team has created the following delivery mechanisms.
Critical On Demand - COD | On Demand- OD | Cumulative Update - CU | General Distribution Release - GDR |
The hotfix is released on or before a mutually agreed upon date based on the customer's need | The hotfix is released on or before a mutually agreed upon date based on the customer's need | The update is released every 2 months. | A GDR addresses an issue that has a broad customer impact, that has security implications, or that has both. A GDR is determined and issued by Microsoft as appropriate and when appropriate. GDRs are kept to a minimum. |
It contains all previous critical on-demand hotfixes to date. |
ISM eliminates the need for hotfix rollup releases. The first CU after a new release contains the hotfix rollup, plus any additional QFEs.
A hotfix package does not replace a service pack. A hotfix package is optional. A hotfix package can be installed or uninstalled at any time. Additionally, hotfix packages are cumulative. Therefore, the latest OD hotfix package or CU hotfix package includes all previously released hotfixes.
Quick Fix Engineering - QFEs are used for the majority of fixes where the effects of the problem are not widespread or severe enough to warrant a GDR.
