[좐]군의 To-Be

(0) (0)

Posted by 좐군

[Install a certificate on a server with Microsoft Management Console (MMC)]

1. To open the Certificates snap-in, follow these steps:
   1. To open the MMC console, click Start, and then click Run. In the Run dialog box type:
      
      MMC
   2. On the Console menu, click Add/Remove Snap-in....
   3. Click Add, and then click Certificates. Click Add again.
   4. You are prompted to open the snap-in for the current user account, the service account, or for the computer account. Select the Computer Account.
   5. Select Local computer, and then click Finish.
   6. Click Close in the Add Standalone Snap-in dialog box.
   7. Click OK in the Add/Remove Snap-in dialog box. Your installed certificates are located in the Certificates folder in the Personal container.
2. Use the MMC snap-in to install the certificate on the server:
   1. Click to select the Personal folder in the left-hand pane.
   2. Right-click in the right-hand pane, point to All Tasks, and then click Request New Certificate....
   3. The Certificate Request Wizard dialog box opens. Click Next. Select Certificate type is "computer".
   4. In the Friendly Name text box you can type a friendly name for the certificate or leave the text box blank, and then complete the wizard. After the wizard finishes, you will see the certificate in the folder with the fully qualified computer domain name.
      
      
   5. If you want to enable encryption for a specific client or clients, skip this step and proceed to the Enable encryption for a specific client section of this article.
      
      If you want to enable encryption at the server, open the Server Network Utility on the server where the certificate is installed, and then click to select the Force protocol encryption check box. Restart the MSSQLServer (SQL Server) service for the encryption to take effect. Your server is now ready to use SSL encryption.

[Enable encryption for a specific client]

1. To export the server certificate's Trusted Root Certificate Authority (CA), follow these steps:
   1. Open MMC, and then locate your certificate in the Personal folder.
   2. Right-click the certificate name, and then click Open.
   3. Review the Certification Path tab. Note the top most item.
   4. Navigate to the Trusted Root Certification Authorities folder, and then locate the Certificate Authority noted in step c..
   5. Right-click CA, point to All Tasks, and then click Export.
   6. Select all the defaults, and then save the exported file to your disk where the client computer can access the file.
2. Follow these steps to import the certificate on the client computer:
   1. Navigate to the client computer by using the MMC snap-in, and then browse to the Trusted Root Certification Authorities folder.
   2. Right-click the Trusted Root Certification Authorities folder, point to All Tasks, and then click Import.
   3. Browse, and then select the certificate (.cer file) that you generated in step 1. Select the defaults to complete the remaining part of the wizard.
   4. Use the SQL Server Client Network Utility.
   5. Click to select the Force Protocol encryption option. Your client is now ready to use SSL encryption.

[How to test your client connection]

• Use the Query Analyzer Tool.
  
  -or-
  
  
• Use any ODBC application where you can change the connection string.

1. Use the SQL Server Client Network Utility.
2. Click to select the Force protocol encryption option.
3. Connect to the server that is running SQL Server 2000 by using Query Analyzer.
4. Monitor the communication by using Microsoft Network Monitor or a Network Sniffer.

1. Modify the ODBC or OLEDB connection string. For example:
   
   ODBC

   Driver=SQLServer;Server=ServerNameHere;UID=UserIdHere;PWD=PasswordHere;Network=DBNETLIB.DLL;Encrypt=YES

   OLEDB

   Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dbNameHere;Data Source=ServerNameHere;Use Encryption for Data=True

2. Connect to the server that is running SQL Server 2000, and then monitor the communication by using Microsoft Network Monitor or a Network Sniffer.

[Troubleshooting]

1. Open the Certificates snap-in. To do this, see step 1 in the "How to Configure the MMC Snap-in" section.
2. In the Certificates snap-in, expand Personal, and then expand Certificates.
3. In the right pane, locate the certificate that you installed.
4. Determine whether the certificate meets the following requirements:
   • In the right pane, the value in the Intended Purpose column for this certificate must be Server Authentication.
   • In the right pane, the value in the Issued To column must be the server name.
5. Double-click the certificate, and then determine whether the certificate meets the following requirements:
   • On the General tab, you receive the following message:
     You have a private key that corresponds to this certificate.
   • On the Details tab, the value for the Subject field must be server name.
   • The value for the Enhanced Key Usage field must be Server Authentication (<number>).
   • On the Certification Path tab, the server name must appear under Certification path.

(0) (0)

Posted by 좐군

One of the more visible changes we made in connectivity space for SQL Server 2005 was enhancing error messages reported to the user in case of connection failures.  I have seen several cases over the last several months, and in this post I would like to list some of them with the root cause that caused them.  The cause may not be the only one triggering a particular error message, and there may be other error messages of interest. 

This is a semi-random selection from real cases I investigated.  Likely I will post another batch some time in the future, and I would be happy to see replies with additional errors, particularly if the root cause is unclear. 

The examples are from various client stacks – ODBC or OLEDB from SQL Native Client or from managed SqlClient. 

The exact formatting of the messages will depend on the application you use.  Most of the examples below used OSQL, SQLCMD, or SQL Server Management Studio. 

• Connecting to a server by the server’s name from SqlClient, the server name is aliased to TCP, the server is up and running but it does not listen on TCP (or is not running at all):

An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)

• Local connection from SqlClient; server is not running:

(a) default instance:

An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

(b) named instance:

An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

• Remote connection from ODBC, Windows Firewall is turned on on the server machine but there is an exception for File and Printer Sharing:

 [SQL Native Client]Unable to complete login process due to delay in opening server connection

• A successful TCP connection from SqlClient to the server was broken due to a TCP keep-alive heartbeat failure (typically indicating problems with the underlying network infrastructure):

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

Possibly:

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

• OBDC connection attempt when server is not ready to process a new local connection, possibly due to overload: 

[SQL Native Client]Shared Memory Provider: Timeout error [258].
[SQL Native Client]Login timeout expired
[SQL Native Client]Unable to complete login process due to delay in prelogin response

[SQL Native Client]Shared Memory Provider: Could not open a connection to SQL Server [121].
[SQL Native Client]Login timeout expired
[SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

• There is a space after server name in the connection string:

osql -E -S "<serverName> ,1433"
Login failed for user ''. The user is not associated with a trusted SQL Server connection.

The server ERRORLOG/EventLog shows something similar to:

2005-08-11 12:46:04.29 Logon       Error: 17806, Severity: 20, State: 2.
2005-08-11 12:46:04.29 Logon       SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: <IP address>]
2005-08-11 12:46:04.29 Logon       Error: 18452, Severity: 14, State: 1.
2005-08-11 12:46:04.29 Logon       Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: <IP address>]

• Remote OLEDB connection using TCP to a server that is blocked by Firewall

HResult 0x274C, Level 16, State 1
TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
Error: Microsoft SQL Native Client : Login timeout expired.

• Remote OLEDB connection using TCP to a server that is either not running or does not have TCP/IP protocol enabled for incoming connections (but is not blocked by the Firewall on the server machine):

HResult 0x274D, Level 16, State 1
TCP Provider: No connection could be made because the target machine actively refused it.

Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
Error: Microsoft SQL Native Client : Login timeout expired.

출처 : http://blogs.msdn.com/sql_protocols/archive/2005/09/28/474698.aspx

(0) (0)

Posted by 좐군

With windows connection color indicator you always know to which server you're connected to. The color strip can be docked to any side of the window and it's color can be easily changed from either it's context menu or from server coloring options. Coloring for each server can be added, removed or just disabled.

Query Execution History (Soft Source Control) and Current Window History

Every SQL statement that you run is logged in a file on your disk or in a table in a database you specify with a connection string. This way, if you're working on some script you can get the full history of every change you've made between check-out and check-in of your file. SQL Statements are saved in a list that is written to a file and/or a database with a timer which you can set to a desired interval. Older query history logs can be auto deleted. Time after the logs are deleted is user settable.

Current window history is a dockable window that show queries executed in a currently active window. There is also a search box at the top that filters results as you type.

Search Table or Database Data

Simple search for a search term through all non-binary columns in a single table or in every table of a database. You can search in four ways: Contains, Starts with, Ends with and Equals.

Uppercase/Lowercase keywords and proper case Database Object Names

Set all keywords to uppercase or lowercase letters. Custom keywords can be added. Format all database objects to their proper case sensitive name.

Run one script on multiple databases

Run selected or full window text on selected databases on the currently connected server. A new window is opened that contains one script for all databases.

Copy execution plan bitmaps to clipboard

Copy selected or all execution plans to a bitmap that is saved on the clipboard. Large execution plans that don't fit in the window are also copied in full. Width of a picture containing all execution plans is equal to the width of the widest execution plan.

Search Results in Grid Mode and Execution Plans

Find all occurrences of your search string in the execution plans or in the results in datagrid mode.

Generate Insert statements for a single table, the whole database or current resultsets in grids

Insert statements for the whole database are generated by the order of PK-FK relationships. Top tables with no FK's are scripted first. Binary data is by default fully scripted. If you wish you can also set the scripting data limit between 0 and 10 Mb. Larger values then the limit are then scripted as NULL. Insert statements for the data in result grids are scripted into a new temporary table for each grid. For example from 5 result grids insert statements for 5 temporary tables get created.

Text document Regions and Debug sections

Regions behave in the same way as in Visual Studio. You can collapse them and expand them. Debug sections are sections that get commented or deleted when you change your script to Release configuration. A debug section is also a collapsable region. If you deploy a script in debug mode with added debug sections it will fail when run from SSMS without SSMS Tools Pack installed. You can of course comment those sections yourself by simply searching for start and end text of the debug sections.

Running custom scripts from Object explorer's Context menu

You can specify a custom script text, its name and node on Object Explorer on which you want to run it from. Node name, current database, current connection string and current server can also be specified in the custom script with keywords that are replaced with their respective values at script runtime.

CRUD (Create, Read, Update, Delete) stored procedure generation

CRUD stored procedure generation for tables based on fully customizable templates that you can change to suit your needs.

New query template

When opening a new query window you can specify a template that will be displayed.

(0) (0)

Posted by 좐군

(0) (0)

Posted by 좐군

(0) (0)

Posted by 좐군

Storage Top 10 Best Practices

원문 : http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx

Proper configuration of IO subsystems is critical to the optimal performance and operation of SQL Server systems. Below are some of the most common best practices that the SQL Server team recommends with respect to storage configuration for SQL Server.

Understand the IO characteristics of SQL Server and the specific IO requirements / characteristics of your application.

In order to be successful in designing and deploying storage for your SQL Server application, you need to have an understanding of your application’s IO characteristics and a basic understanding of SQL Server IO patterns. Performance monitor is the best place to capture this information for an existing application. Some of the questions you should ask yourself here are:

To understand the core characteristics of SQL Server IO, refer to SQL Server 2000 I/O Basics.

More / faster spindles are better for performance

Try not to “over” optimize the design of the storage; simpler designs generally offer good performance and more flexibility.

Validate configurations prior to deployment

Always place log files on RAID 1+0 (or RAID 1) disks. This provides:

Isolate log from data at the physical disk level

Consider configuration of TEMPDB database

Lining up the number of data files with CPU’s has scalability advantages for allocation intensive workloads.

Don’t overlook some of SQL Server basics

Don’t overlook storage configuration bases

(0) (0)

Posted by 좐군

원문 : http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dw_perf_top10.mspx

Relational Data Warehouse or Reporting work loads are characterized by low volumes of very large transactions. These applications are often identified as having predominately read workloads (e.g. Decision Support, Analysis, and Reporting) with periodic feeds or batch loads. It is important to keep these characteristics in mind as we examine the significance of database design, resource utilization and system performance. The top performance bottlenecks or gotchas to avoid for Data Warehouse or Reporting applications are outlined below.

Database Design issue if….

CPU gotchas….

Memory bottleneck if….

IO bottleneck if…

Blocking bottleneck if….

Network bottleneck if….

Wait statistics gotchas...

Indexing gotchas.

Watch out for fragmentation.

Consider Table Partitioning for fast loads

(0) (0)

Posted by 좐군

/*[테이블설계]*/

CREATE TABLE TreeMenu(
   menu_cd        int         ,	--메뉴코드
   menu_nm        varchar(50) ,	--메뉴명
   parent_cd      int	         ,	--상위메뉴코드
   CONSTRAINT PK_TreeMenu PRIMARY KEY (menu_cd)
)


/*[샘플데이터]*/

insert into TreeMenu VALUES(1, '시작'	   , 0)
insert into TreeMenu VALUES(2, '프로그램'	   , 1)
insert into TreeMenu VALUES(3, '설정'	   , 1)
insert into TreeMenu VALUES(4, '보조프로그램', 2)
insert into TreeMenu VALUES(5, '제어판'	   , 3)
insert into TreeMenu VALUES(6, '네트워크환경', 5)
insert into TreeMenu VALUES(7, '계산기'	   , 4)
insert into TreeMenu VALUES(8, '그림판'	   , 4)
GO

/*[핵심함수 구현]*/

/***********************************************
   트리구조에서 
   각 노드(행)의 절대위치를 Binary계산하는 함수
   최대 64 Level 지원
***********************************************/

go
CREATE FUNCTION FN_TreeOrderBy(
	@root	int,	/*루트값*/
	@menu_cd	int	/*현재값*/
) RETURNS varbinary(256)
BEGIN
	DECLARE @parent_cd	int ,
		@level_bin	varbinary(256)
 	IF @menu_cd = @root 
	BEGIN
		RETURN 0
	END

	select @level_bin	= CAST(@menu_cd AS varbinary(4))	--값 초기화
	/*루프로 menu_cd위 절대위치 계산*/
	WHILE 1 = 1
	BEGIN
		SELECT 	@parent_cd = parent_cd 
		FROM 	TreeMenu WITH(NOLOCK)
		WHERE	menu_cd = @menu_cd

		IF (@root=@parent_cd or @parent_cd=0 or @parent_cd is null) 
		BEGIN
			BREAK
		END

		SELECT @menu_cd = @parent_cd
		SELECT @level_bin =  CAST(@parent_cd AS varbinary(4)) + @level_bin

	END
	RETURN  @level_bin
END 
go

/*[함수사용하여 조회]*/

SELECT 	a.*	,
	dbo.FN_TreeOrderBy(1, a.menu_cd) absolute_pos
FROM 	TreeMenu A WITH(NOLOCK)
ORDER BY   absolute_pos    --계산된 열로 정열

/*
속도를 더 빨리 하기 위한 방법으로는 삭제/수정/등록시 
절대위치(absolute_pos)를 계산해서 저장하는 
방법입니다. 저장된 절대위치에 클러스트인덱스 설정하면 검색속도가 더욱 빠릅니다
그래도 대용량 쿼리할 때 이용하기엔 쫌 부담이 되겠네요 -ㅅ-
*/

(0) (0)

Posted by 좐군

DECLARE    @cursor   CURSOR
,	@sql	NVARCHAR(1000)
,	@params	NVARCHAR(100)
DECLARE @id	INT
,	@name	NVARCHAR(200)

SELECT	@sql =N'SET @p_cur = CURSOR FORWARD_ONLY READ_ONLY FOR 
	SELECT top 10 id, name FROM dbo.sysobjects WITH(NOLOCK) WHERE id < 10;
	OPEN @p_cur'
SELECT	@params = N'@p_cur CURSOR OUTPUT'

EXEC sp_executesql
	@sql
,	@params
,	@p_cur	= @cursor	OUTPUT



FETCH NEXT FROM @cursor INTO @id, @name
WHILE (@@FETCH_STATUS=0)
BEGIN
	PRINT CAST(@id AS VARCHAR(10)) + ':' + @name
	FETCH NEXT FROM @cursor INTO @id, @name
END

CLOSE @cursor
DEALLOCATE @cursor

(0) (0)

Posted by 좐군