Microsoft Word - Managing SQL Server Express with SQL Server 2005 Management Studio Express Edition.doc Managing SQL Server Express with SQL Server 2005 Management Studio Express Edition SQL Server Technical Article Writers: Eric Brown, Senior Consultant Quilogy Inc. Published: March 2006 Applies To: SQL Server 2005 Summary: Learn how to manage SQL Server 2005 Express Edition by using the free graphical management tool, SQL Server 2005 Management …
An Introduction to SQL Server Management Studio Express Edition SQL Server 2005 Management Studio Express Edition (SSMSE) provides the database developer and administrator with a robust set of tools for working with Microsoft® SQL Server™ Express Edition. Based on the same technology and functionality as that in SQL Server Management Studio, SSMSE uses Microsoft Visual Studio® .NET 2005 user interfaces and IDE layout, so that switching between SSMSE and Visual Studio .NET 2005 easy. This familiar interface allows .NET developers to be productive more quickly. The features in SSMSE are limited to those that are needed to manage a SQL Server 2005 Express Edition database. While you can use SSMSE to manage other editions of SQL Server 2005, you cannot manage components of the server that are not in SQL Server Express. For example, you can connect only to the relational database engine only by using SSMSE because other services are not installed with SQL Server Express. SSMSE and SQL Server Management Studio (SSMS) cannot be installed together. If multiple editions of SQL Server are installed on your computer, you can only use SSMS and not SSMSE. Getting started The next sections provide a tour of the features in SSMSE. Because of its graphical user interface, many SSMSE features can be accessed simply by right-clicking. You can accomplish many tasks by using both script and dialog boxes. The beginning developer can use dialog boxes to accomplish basic tasks. By design, SQL Server always creates a default state that is secure and functional. Changes to default settings are for the purpose of setting up the database for the unique needs of users and applications. Assuming that SQL Server 2005 Management Studio Express Edition is installed, let’s start by connecting to an instance. Connecting to servers This section covers how to connect to an instance of SQL Server Express. The Connect to Server dialog box allows users to provide both logon credentials and specific connection properties. You can use it to connect directly to SQL Server Express. For the authentication method, you can choose either SQL Server Authentication or Windows Authentication. By default, this is set to Windows Authentication. There are two tabs on the Connect to Server dialog box: the Login tab and the Connection Properties tab. To view the Connection Properties tab, click the Options button. Use the Connection Properties tab (shown in Figure 1) to input authentication credentials and server name….. 10 Microsoft Corporation ©2006 Figure 10: Specify Values for Template Parameters window Now, you have a basic understanding of how to create the basic objects that make up a database. In the next sections, we look at managing the database and server by using SSMSE. Basics of Database Administration Whether you are a beginning developer or a budding database administrator, you need to have some understanding of database administration. Following are the basic functions of database administration. • System management. Making sure the appropriate services and features are accessible and working. With SQL Server 2005, some features are turned off by default. Moreover, SQL Server Express is configured to not listen on the network via TCP/IP. • Security management. Managing access to the data. This starts at the operating system and ends with the database. This paper focuses on security topics related to users and logins. • Disaster preparedness. Recovering from user error and hardware and software failure. This paper covers backing up databases, understanding recovery models, and working with scripts to allow for the re-creation of database objects and data. In the following sections, we look at how to handle these database administration tasks by using SQL Server Browser, SQL Configuration Manager, and the SQL Surface Area Configuration tool. We review system management tasks, security settings, and backup and recovery models. System management When SQL Server Express is installed using the default settings, it is configured to be as secure as possible. Microsoft calls this configuration secure by default. After SQL Server Express is installed, you might need to reconfigure parts of the server based on your expected usage. For example, by default SQL Server Express allows only local connections. If you need to connect to SQL Server Express through your network, configure SQL Server Express to allow remote connections. A number of tools are available for accomplishing various configuration tasks. SQL Server Surface Area Configuration tool The Surface Area Configuration tool provides a scriptable interface for setting global security states for SQL Server features and services. Because SQL Server 2005 follows the security principle of secure by default, many features are turned off by default. Moreover, unless you indicate otherwise, the services you choose to install do not start automatically after installation is complete. Use the Surface Area Configuration tool to configure SQL Server and to manage SQL Server security. The Surface Area Configuration tool can be used to turn on and off services and features as well as to change configuration settings. It provides two basic sorting methods—View by Instance and View by Component. Use View by Component to quickly access and manage a certain grouping of features. View by Instance allows you to look over a specific instance of SQL Server and make changes
Saturday, November 13, 2010
SQL Server 2005 Management Studio Express Edition
Using UNIX on Your MacBook
Pick up a copy of the great book of lore entitled UNIX For Dummies, 5th Edition, written by John R. Levine and Margaret Levine Young (Wiley). Go where no trackpad has gone before The … Chapter 1: Using UNIX on Your MacBook In This Chapter Why use UNIX? Doing things with the keyboard Introducing UNIX commands Creating text files Exploring deep inside Mac OS X A s I mention in the first chapter of the Book II, UNIX lurks deep beneath the shiny Aqua exterior of Mac OS X. UNIX is a tried-and-true operating system that’ …
Why Use the Keyboard? Automate to elevate If all these benefits are beginning to excite you, hold on to your socks! Not only can you perform complex commands with the command line, you can go even one step further: automation. If you find yourself using the same set of commands more than once, you’re a likely candidate for using automation to save time. Instead of typing the list of commands each time, you can save them to a text file and execute the entire file with only one command. Now that’s power, right up there with the dynamic duo of AppleScript and Automator! Of course, you probably don’t like doing housekeeping tasks while you’re busy on other things, so schedule that list of commands to run in the middle of the night while you’re fast asleep. UNIX lets you do that, too, using the launchd command. Note that automation of UNIX commands is totally separate from automation of Mac OS X applications with AppleScript and Automator, which I cover in Book IX, Chapter 2. Remote control “So, Mark, the command line is the cat’s meow for efficiently accessing and working with files on my MacBook, and I can use it to automate many operations. Anything else?” I’m glad you asked! By using the command line, you can also send commands to another computer anywhere in the world (as long as you know the right login and password). After you log into another computer, you can use the same commands for the remote computer. UNIX was created with multiple users in mind. Because computers used to be expensive (and honking huge machines to boot), UNIX was designed so that multiple users could remotely use the same machine simultaneously. In fact, if Mac OS X is your first encounter with UNIX, you might be surprised to know that many UNIX beginners of the past weren’t even in the same room, building, state, or even country as the computer that they were using. Not only can you work with a computer that’s in a different physical location, but it’s also very fast to do so. Instead of the bandwidth hog that is the Internet, the command line is lean and mean. This permits you to use a remote computer nearly as fast as if it were sitting on the desk in front of you. (This is a great advantage for road warriors who need to tweak a Web or an e-mail server from a continent away.)…
SSL and TLS: A Beginners Guide
SANS Institute InfoSec Reading Room SSL and TLS: A Beginners Guide … SSL accelerators are PCI cards sold by several companies (Cisco, Broadcom, etc) to … SANS Institute 2003, Author retains full rights Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46 Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46© SANS Institute 2003, As part of the Information Security Reading Room. Author retains full rights. Holly Lynne McKinley GSEC Practical v.1.4b SSL and TLS: A …
Examples of these attacks are: active wiretapping, masquerading and spoofing. These attacks can occur while information is being transmitted through the pathways of the Internet. One way of mitigating a potential attack during a user’s session would be to use a secure communication protocol to encrypt data in transit between the user and the server on which the sensitive information resides. Two of these communication protocols will be explained within this paper: Secure Sockets Layer (SSL) and Transport Layer Security (TLS). Secure Sockets Layer Protocol Definition of SSL SSL is the secure communications protocol of choice for a large part of the Internet community. There are many applications of SSL in existence, since it is capable of securing any transmission over TCP. Secure HTTP, or HTTPS, is a familiar application of SSL in e-commerce or password transactions. (Viega, 10) According to the Internet Draft of the SSL Protocol , the point of the protocol “is to provide privacy and reliability between two communicating applications.” (Freier, 3.) The protocol release further explains that three points combine to provide connection security. These points are: • Privacy - connection through encryption • Identity authentication - identification through certificates, and • Reliability -dependable maintenance of a secure connection through message integrity checking. The current version of SSL is version 3.0, released by Netscape in 1999. The Internet Engineering Task Force (IETF) has created a similar protocol in an attempt to standardize SSL within the Internet community. This protocol, the Transport Layer Security (TLS) protocol, will be discussed later in this paper. Using a series of nine messages (explained later), the server authenticates itself to a client that is transmitting information. Though it is a good idea for the user to hold a digital certificate, it is not required for the SSL connection to be established. Keep the following scenario in mind, as it shows a common application of SSL: A user without a certificate wishes to check her e-mail on a web-based e-mail system. Since she has requested a secure connection from the e-mail web page, she expects to send her username and password to the e-mail site. The identification of the e-mail server to her current workstation is critical. To the e-mail server though, it is not critical that the user has an identifying certificate on her machine because she can check her e-mail from any computer. For this reason, SSL does not require a client certificate. Other practical applications of SSL communications are found in e-mail and financial transaction communications. Application to a Web System The need to send sensitive information over the Internet is increasing, and so is the necessity to secure information in transit through the Internet. A common application of SSL with a web system is an online store where a client machine is…
Getting to Know the SQL Server Management Studio
HOUR 3 Getting to Know the SQL Server Management Studio The Microsoft SQL Server Management Studio Express is the new interface that Microsoft has provided for management of your SQL … … Server database. It is the main tool that you will use when maintaining your databases and the objects they contain. In this hour you’ll learn:. The system databases that ship with SQL Server 2005 Express. What is available under the Security node of the Management Studio. The types of server objects that are available. What is available under …
Microsoft SQL Server Management StudioExpress 45 This not only provides you with standardization between databases, but provides you with a great jump start on creating the databases you need. If you modify Model, you do not affect any existing databases. All new databases will be affected by your changes. The MSDB Database The MSDB (Microsoft Database) databaseis used by SQL Server, SQL Server Management Studio, and SQL Server Agent. All three of them use it to store data, including scheduling information and backup and restore history information. For example, SQL Server maintains a complete backup and restore history in MSDB. There are several ways that you can add to or modify information stored in the MSDB database. They include . Scheduling tasks . Maintaining online backup and restore history . Replication The TempDB Database TempDB is asystem database that acts as a resource to all users working with a particular instance of SQL Server. TempDB holds the following objects: . Temporary user objects such as temporary tables, temporary stored procedures, temporary table variables, or cursors . Internal objects used by the database engine to perform tasks such as sorting . Row versions that are generated in data modification transactions The Security Node As its name implies, the Security Node enables you to manage SQL Server security. Using the Security Node, you can work with logins, add to and remove people from server roles, and create credentials. This chapter provides an introduction to security. For more information, see Hour 21, “SQL Server Authentication,” and Hour 22, “SQL Server Permissions Validation.” The Logins Node Logins representthe users and roles that have access to your system. Note in Figure 3.2 that two types of icons appear under the Logins node. One is granting a role access to the database, and the other is granting a user access to the database.
46 HOUR 3 Getting to Know the SQL Server Management Studio The Server Roles Node Server Roles arepredefined roles, supplied by SQL Server. Each Server Role possesses a pre-defined set of rights. Figure 3.3 shows the available Server Roles. You cannot add or remove Server Roles. FIGURE 3.2 Notice that Administrators is a role, and sa is a user. FIGURE 3.3 Each Server Role possesses a pre-defined set of rights.
Microsoft SQL Server Management StudioExpress 47 The Credentials Node Credentialsare new to SQL Server 2005. A credential is a record that contains the authentication information required for SQL Server to connect to an outside resource. Most credentials are made up of a Windows login and password. Server Objects Server Objects refer to a set of objects used at the server level (not at the database level). These objects include Backup Devices, Linked Servers, and Server Triggers. Backup Devices Backup devicesinclude the tapes and disks that you use to back up or restore your SQL Server. When creating a backup, you must designate the backup device that you want to use (see Figure 3.4). You select from a list of backup devices that you have created. FIGURE 3.4 When creating a backup, you must first designate the backup device you want to use. Linked Servers Linked serversenable you to work with other SQL Servers, as well as databases other than SQL Server databases, right from within Management Studio. This offers a few advantages: The Replication Node Data replication is the capability of a system to make copies of its data and application objects automatically in remote locations. You easily can propagateany changes to the original or data changes to the copies to all the other copies. Data replication enables users to make changes to data offline at remote locations. SQL Express synchronizes changes to either the original or the remote data with other instances of the database. The original database is referred to as the design master . You can make changes to definitions of tables or other application objects only at the design master. You use the design master to make special copies called replicas . Although there is only one design master, replicas can make other replicas. The process of the design master and replicas sharing changes is referred to as synchronization . To see an example of data replication at work, imagine that you have a team of salespeople who are out on the road all day. At the end of the day, each salesperson logs on to one of the company’s servers through Terminal Services. The replication process sends each salesperson’s transactions to the server. If necessary, the process sends any changes to the server data to the salesperson. Management The Management node contains tools that help you to manage your SQL Server. These tools include the capability to view both the SQL Server Logs and the Activity Monitor….. The Process Info page contains information about the connections. The Locks by Process page sorts the locks by the connection. The Locks by Object page sorts the locks by the object name. Creating a SQL Server Database Before you can build tables, views, stored procedures, triggers, functions, and other objects, you must create the database in which they will reside. A database is a collection of objects that relate to one another. An example would be all the tables and other objects necessary to build a sales order system. To create a SQL Server database, followthese steps: 1. Right-click the Databases node and select New Database. The New Database dialog appears (see Figure 3.5). 2. Enter a name for the database. 3. Enter a path for the database (see Figure 3.6). 4. Click to select the Options page and change any options as desired (see Figure3.7). 5. Click OK to close the New Database dialog and save the new database. The database now appears under the list of databases (see Figure 3.8) under the Databases node of SQL Server Management Studio. If the database does not appear, right-click the Databases node and select Refresh….
Context and Learning to Programming of Intelligent Equipment
.. of Automation of Production in Machine Building, … and Computational Intelligence in development of programming languages for industrial applications such as industrial robots … Context and Learning based Approach to Programming of Intelligent Equipment Andrey V. Gavrilov Department of Automation of Production in Machine Building, Novosibirsk State Technical University Karl Marx Av., 20, Novosibirsk 92, 630092 Russia Andr_gavrilov@yahoo.com Abstract In this paper novel approach for programming of intelligent equipment …
One of most actual problems in development of intelligent systems for manufacturing is human- machine interface. Two kinds of such interfaces are known, oriented on programming and learning respectively. Programming is used usually for industrial robots and other technological equipment. Learning is more oriented for service and toy robotics. There are many different programming languages for different kinds of intelligent equipment, for industrial robots-manipulators, mobile robots, technological equipment [1, 2]. The re-programming of industrial robotic systems is still a difficult, costly, and time consuming operation. In order to increase flexibility, a common approach is to consider the work- cell programming at a high level of abstraction, which enables a description of the sequence of actions at a task-level. A task-level programming environment provides mechanisms to automatically convert high- level task specification into low level code. Task-level programming languages may be procedure oriented [3] and declarative oriented [4, 5, 6, 7] and now we have a tendency to focus on second kind of languages. But in current time practically all programming languages for manufacturing are deterministic and not oriented on usage of learning and fuzzy concepts like in service or military robotics. But it is possible to expect in recent future reduce of difference between industrial and service applications. For example, known plans of Toyota Inc. to employ humanoid robots in automobile manufacturing. So it seems interesting and perspective to apply achievements of AI and Computational Intelligence in development of programming languages for industrial applications such as industrial robots or technological equipment. In this paper we suggest novel approach to programming of robots and technological equipment based on usage of context and learning and so oriented on usage of natural language for learning. In this approach we do not distinguish learning and programming combining in one system declarative (description of context) and procedural knowledge (routines for processing of context). Also here new Context Based Language for Robot (CBLR) is proposed and elements of one are described. This suggested approach is based on research of author during development of system software for transportation industrial robots based on learning [8, 9] and development of software for searching of documents by natural language query [10]. 2. Previous example of usage of similar approach for programming of transportation industrial robots… The language BLRP consisted of following groups of commands: 1) For assignment of context, 2) For control of moving, 3) For control of execution of routines, 4) For definition of conditions, 5) For determination of points in environment (service area) of robot, 6) For description of state of robot and technological equipment, 7) Serving commands. Architecture of software developed for robot consists of subsystems for programming, checking and execution of routines (figure 1). Context included main following parameters: 1) X, Y, Z - coordinates of point, 2) Description of condition operator, 3) State of equipment, 4) State of robot, 5) Number of link of robot, 6) Number of cell of equipment, 7) Number of object. Number in these parameters means identifier. Fig. 1. Architecture of software for transport robot. Programming in this system was replaced by learning. Result of one was knowledge base with associations between words of natural language and sequences of commands for description of context and….
Friday, November 12, 2010
Wonderful Unix SAS Scripts: SAS Quickies for Dummies
Microsoft Word - Wonderful Unix SAS Scripts - SAS Quickies for Dummies 5.doc 1 Wonderful Unix SAS Scripts: SAS Quickies for Dummies Raoul Bernal, Amgen Inc., Thousand Oaks, CA ABSTRACT Repeated simple SAS jobs can be executed online, and in real-time to avoid having to directly execute SAS command files, producing immediate results directly into any Unix display window. The trick is to simulate SAS using a Unix script, …
CREATING A SIMPLE UNIX SCRIPT A simple Unix script is composed of a simple command executable in the Unix operating system, with optional parameters. An example script would one that lists all files in a current directory, by time: ls -last *.* Instead of typing this command, a simple executable script that contains this line, say, fl (for f ile l ist) can be saved in utilities folder so that typing fl essentially produces the same results as ls -last *.*. . CREATING SIMPLE SAS UNIX SCRIPTS Creating a SAS Unix script would be more complicated than the simplest Unix commands because we need to specify: a) the SAS procedure, b) the dataset to apply the procedure to, and optionally, c) any condition and option to apply to the dataset. Moreover, we need to create a temporary “behind-the-scenes” SAS job to execute in the background after entering the SAS Unix script in the command line. The objective is to create a SAS Unix script, which, when executed with required dataset, parameters and condition, would be equivalent to executing a SAS job using the same SAS procedure. Examples of SAS Unix scripts will be discussed in detail. A. SAS UNIX SCRIPT pp Command syntax: pp dataset condition 2 This script pp (short for p roc p rint), when executed with parameters dataset and condition, would be equivalent to the execution of the following PROC PRINT code in SAS: p roc p rint data= dataset width=min ; where ( condition ) ; run ; As an example, printing the demo.sas7bdat dataset for patient id 190001001 would simply require the following command: pp demo ” cohort = ‘ A1 ‘ ” which yields the following result: The straightforward Unix script for pp would be: #!/bin/csh -f # pp - prints out all or part of a UNIX dataset using PROC PRINT # The first argument is the dataset set dataset=”$1? # The second argument is for setting condition to subset the data set condition=”$2? # Find the dataset file set pwd=`pwd` if (-f $dataset.sas7bdat == 1) then set sasdir = “$pwd” else echo “Dataset: $dataset.sas7bdat not found. Exiting … ” exit endif # Begin constructing the SAS program in the temporary directory /tmp echo “%*include ‘init.sas’ ;” >! /tmp/pp$$.sas echo “libname sasdata ‘$sasdir’ ;” >> /tmp/pp$$.sas # Continue constructing the program echo “options nodate nocenter nofmterr pagesize=74 linesize=132 ;” >> /tmp/pp$$.sas echo “proc printto file=’/tmp/pp$$. lis’ ;” >> /tmp/pp$$.sas echo “title Printing dataset: %nrbquote($dataset) Date: &sysdate. Time: &systime.;” >> /tmp/pp$$.sas echo “data $dataset; set sasdata.$dataset ;” >> /tmp/pp$$.sas if (”$2? != ”) then echo ” where $condition;” >> /tmp/pp$$.sas endif echo “run;” >> /tmp/pp$$.sas echo ” ” >> /tmp/pp$$.sas echo “proc print data=$dataset;” >> /tmp/pp$$.sas echo “run;” >> /tmp/pp$$.sas echo ” ” >> /tmp/pp$$.sas echo “Running proc print on dataset: $dataset ($sasdir)” # Run the program in the temporary directory ./tmp cd /tmp sas -913 pp$$.sas echo ” ” >> /tmp/pp$$.lis grep -i “sas stopped” /tmp/pp$$.log …
Download Wonderful Unix SAS Scripts: SAS Quickies for Dummies.pdf
The UNIX- HATERS Handbook
For Dummies ” series has over 7 million copies in print with translations in more than 20 … 1 Unix The World’s First Computer Virus “Two of the most famous products of Berkeley are … iv IDG Books Worldwide, Inc. An International Data Group Company San Mateo, California Indianapolis, Indiana Boston, Massachusetts The UNIX-HATERS Handbook Published by IDG Books Worldwide, Inc. An International Data Group Company 155 Bovet Road, Suite 310 San Mateo, CA 94402 Copyright ? 1994 by IDG Books Worldwide. All rights reserved. No part …
Neuhaus, who saw the project through to its completion. Amy Pedersen was our Imprint Manager. The UNIX-HATERS cover was illustrated by Ken Copfelt of The Stock Illustration Source. Typographical Conventions In this book, we use this roman font for most of the text and a different sans serif font for the horror stories from the UNIX-HATERS mailing list. We’ve tried to put command names, where they appear, in bold , and the names of Unix system functions in italics . There’s also a courier font used for computer output, and we make it bold for information typed by the user. That’s it. This isn’t an unreadable and obscure computer manual with ten different fonts in five different styles. We hate computer manuals that look like they were unearthed with the rest of King Tut’s sacred artifacts. This book was typeset without the aid of troff , eqn , pic , tbl , yuc , ick , or any other idiotic Unix acronym. In fact, it was typeset using FrameMaker on a Macintosh, a Windows box, and a NeXTstation….. Standardizing Unconformity 11 at NeXTWORLD asked me what the difference was between AIX and A/UX. “AIX is Unix from IBM. A/UX is Unix from Apple.” “What’s the difference?” he asked. “I’m not sure. They’re both AT&T System V with gratuitous changes. Then there’s HP-UX which is HP’s version of System V with gratuitous changes. DEC calls its system ULTRIX. DGUX is Data General’s. And don’t forget Xenix—that’s from SCO.” NeXT, meanwhile, calls their version of Unix (which is really Mach with brain-dead Unix wrapped around it) NEXTSTEP. But it’s impossible to get a definition of NEXTSTEP: is it the window system? Objective-C? The environment? Mach? What? Originally, many vendors wanted to use the word “Unix” to describe their products, but they were prevented from doing so by AT&T’s lawyers, who thought that the word “Unix” was some kind of valuable registered trademark. Vendors picked names like VENIX and ULTRIX to avoid the possibility of a lawsuit. These days, however, most vendors wouldn’t use the U-word if they had a choice. It isn’t that they’re trying to avoid a lawsuit: what they are really trying to do is draw a distinction between their new and improved Unix and all of the other versions of Unix that merely satisfy the industry standards. It’s hard to resist being tough on the vendors. After all, in one breath they say that they want to offer users and developers a common Unix environment. In the next breath, they say that they want to make their own trademarked version of Unix just a little bit better than their competitors: add a few more features, improve functionality, and provide better administrative tools, and you can jack up the price. Anybody who thinks that the truth lies somewhere in between is having the wool pulled over their eyes….
Microsoft SQL Server 2000 Index Defragmentation Best Practices
Microsoft SQL Server 2000 Index Defragmentation Best Practices Author: Mike Ruthruff Microsoft Corporation February 2003 Summary: As Microsoft® SQL Server™ 2000 maintains indexes to … … reflect updates to their underlying tables, these indexes can become fragmented. Depending on workload characteristics, this fragmentation can adversely affect workload performance. This white paper provides information to help you determine whether you should defragment table indexes to benefit workload performance. To defragment indexes, …
Poorly Performing Queries When investigating any performance related problems, you must identify which queries in the workload are performing poorly. Some of the information discussed here is also used later in the paper to determine which indexes to focus on for defragmentation. You can use SQL Profiler to identify poorly performing queries. (For more information, see the topic “SQL Profiler” in SQL Server Books Online.) Running SQL Profiler introduces some overhead; however, monitoring only the following events should allow you to collect needed information with minimal impact on performance (this varies, but generally less than 10 percent impact to CPU utilization). SQL Profiler provides a trace template named SQLProfilerTSQL_Duration , which captures relevant events. You can use this to identify poorly performing queries quickly. You can also manually create a SQL Profiler trace that captures these events: • TSQL: SQLBatchCompleted • Stored Procedures: RPC:Completed The length of time you need to run SQL Profiler depends on the server workload. For the trace to be most useful, it should represent a typical workload or, at least, the part of the workload that exhibits poor performance. After the trace has been captured, examine the trace log focusing on the duration column. This is a measure in milliseconds of the duration of each batch or query. Identifying the Poorest Performing Queries Here are some suggestions to best determine the poorest performing queries in the trace: • Group the trace by query duration. Focus first on the “Top 10? worst performing queries. • If your application uses stored procedures extensively, consider using the SQLProfilerSP_Counts trace template to identify the most commonly called stored procedures. Focus on the worst performing stored procedures that are used most frequently. • Consider saving the collected data into SQL Server tables. This allows you to query the tables and perform more detailed analysis (for example, average duration, max duration, and so on) of the workload performance. Underlying Schema After you have identified the longest running/worst performing queries, you must ensure that the underlying schema is optimal for these. Ensure that appropriate indexes exist and are being used appropriately by the queries. Using SQL Query Analyzer to display and review query plans allows you to see what indexes are being used by queries in the workload. When the execution plan of a query is graphically …. Monitoring Fragmentation Levels Regularly monitoring fragmentation levels on indexes is good practice. For a sample script that demonstrates how to automate the capture and rebuild of heavily fragmented indexes, see the “DBCC SHOWCONTIG” topic in SQL Server Books Online. Consider using the DBCC SHOWCONTIG TABLERESULTS option and importing this information into tables at regular intervals. Doing this allows you to monitor the fragmentation levels over time. Also consider using the WITH FAST option when running DBCC SHOWCONTIG on a busy server. Using the WITH FAST option allows DBCC SHOWCONTIG to avoid scanning the leaf pages of an index. This can result in faster performance of DBCC SHOWCONTIG; however, because it does not scan the leaf pages of the index, it cannot report page density numbers. Table 1 shows the amount of time it took to run DBCC SHOWCONTIG against all the indexes in the small-scale and large-scale DSS environments. The ALL_INDEXES and TABLERESULTS options were used for each test. Table 1 DBCC SHOWCONTIG performance DBCC SHOWCONTIG options Total number of index pages (all indexes) Run time (minutes) Small-Scale Environment Not using the WITH FAST option 1,702,889 5.02 Using the WITH FAST option 1,702,889 0.90 Large-Scale Environment
Embarcadero DB Optimizer 1.5 SQL Profiler User Guide
embarcadero technologies > embarcadero® db optimizer™ 1.5 sql profiler user guide 1 using profiling … USING PROFILING > RUNNING A PROFILING SESSION EMBARCADERO TECHNOLOGIES > EMBARCADERO® DB OPTIMIZER™ 1.5 SQL PROFILER USER GUIDE 8 •The Events Tab displays information about wait events profiled by the execution process. •The Sessions Tab displays information about sessions profiled by the execution process. •The I/O Tab is Oracle-specific and will not appear in the Top Activity Section unless the data source being profiled is an Oracle platform. This tab displays information about the I/O profiled by the execution process. When you select any item from the Top Activity tabs, details are displayed in the Profiling Details view. Depending on the nature of the Top Activity item that you selected, the tabs that compose the Profiling Details view will change to reflect that item’s specific session information. Note: When right-clicking on a SQL statement in the Top Activity Section in Profiling, if the SQL statement is run by a different user than the user who is running DBO, than the User Mismatch dialog appears, with an example of the following message: “This query was executed by [SOE] and you are currently connected as [system]. We recommend you reconnect as [SOE] to tune the SQL. Would you like to continue anyway?” This message indicates that the statement is being tuned by a user other than the user who originally ran the query, and tables may be missing based on the different schemas. Click OK to run the query, or click Cancel and run tuning under the original user. SQL Tab The Profile editor’s SQL tab shows a representation of all SQL statements that are executing or waiting to execute over the length of the profiling session or within the currently selected graph bars. Statements can be grouped by type by right-clicking the view and selecting Organize > By Type . The following statement types are organized: INSERT , SELECT , DELETE , and UPDATE TIP: Statements are grouped when they differ only by their clause values. This enables the roll-up of SQL statements that only differ by a variable value. For example: select * from emp where empno=1; and select * from emp where empno=2. A ‘+’ symbol appears beside rollup statements. You can click the symbol to expand and view the different statement predicates. Additionally, the SQL tab displays two other groupings: Group Description OTHER Includes all recognized statements other than INSERT, SELECT, UPDATE, and DELETE statements.
Thursday, November 11, 2010
Microsoft SQL Server Black Book
Brief Full Advanced Search Search Tips To access the contents, click the chapter and section titles. Microsoft SQL Server Black Book (Publisher: The Coriolis Group) Author(s): Patrick … … Dalton ISBN: 1576101495 Publication Date: 07/01/97 Search this book: Introduction What’s on the CD-ROM Dedication Chapter 1—Preinstallation Considerations What Is A Device? What, Then, Is A Database? What Are Character Sets And Sort Orders? What Is The Recommended System Configuration? Where Should The Microsoft SQL Server Be Installed? What …
up Microsoft SQL Server on a primary or backup domain controller. Keep shared file access off your database server. Having users copy files to and from the server will move the disk heads unnecessarily. Disk I/O is the slowest thing your data server will do. Do everything you can to keep it to a minimum. Also avoid sharing printers, modems, or like services on your Microsoft SQL Server. All of these processes are burst performance-related loads; Murphy’s Law will always ensure that one of the biggest file transfers or print jobs will hit your server at the same time a large query is running, causing the whole system to appear to hang. As you might be noticing, Microsoft appears to be moving toward a distributed server network. All the servers do not have to be on independent machines, but this configuration will help distribute the load across your network, allowing you to put lighter-weight and lower-cost servers in place for mail and file services and put your money where production is, such as on data services. This distribution can be a good thing, but many companies fail to recognize this until they have put all their eggs (applications, services, and files) in one or two baskets (servers). Plan for growth. By definition, databases will grow given even normal use. Over time any system that is being used in production will expand not only in feature and function, but in the amount of data as well. If possible, place Microsoft SQL Server on a machine by itself. Install it as a server that is part of a domain (provided you are using the Microsoft domain model). Place any other applications on separate machines when possible. If multiple applications are running on the same machine, you are complicating the process unnecessarily. In addition, beware of disk-intensive applications running on your database machine. If an application is writing to disk and Microsoft SQL Server is writing to disk, these processes will compete for disk I/O and slow down both applications…..
Microsoft SQL Server 2008 Database Engine Common Criteria Evaluation
Security Target SQL Server 2008 Team Author: Roger French Version: 1.2 Date: 2009-01-23 Abstract This document is the Security Target (ST) for the Common Criteria certification … database engine of Microsoft® SQL Server® 2008. Keywords CC, ST, Common Criteria, SQL, Security Target Security Target Microsoft SQL Server 2008 Database Engine Common Criteria Evaluation Page 2/56 This page intentionally left blank Security Target Microsoft SQL Server 2008 Database Engine Common Criteria ….
Security Target Microsoft SQL Server 2008 Database Engine Common Criteria Evaluation Page 9/56 Figure 1: TOE As seen in Figure 1 the TOE internally comprises the following logical units: The Communication part is the interface for programs accessing the TOE. It is the interface between the TOE and clients performing requests. All responses to user application requests return to the client through this part of the TOE. The Relational Engine is the core of the database engine and is responsible for all security relevant decisions. The relational engine establishes a user context, syntactically checks every Transact SQL (T-SQL) statement, compiles every statement, checks permissions to determine if the statement can be executed by the user associated with the request, optimizes the query request, builds and caches a query plan, and executes the statement. The Storage Engine is a resource provider. When the relational engine attempts to execute a T-SQL statement that accesses an object for the first time, it calls upon the storage engine to retrieve the object, put it into memory and return a pointer to the execution engine. To perform these tasks, the storage engine manages the physical resources for the TOE by using the Windows OS. The SQL-OS is a resource provider for all situations where the TOE uses functionality of the operating system. SQL-OS provides an abstraction layer over common OS functions and was designed to reduce the number of context switches within the TOE. SQL-OS especially contains functionality for Task Management and for Memory Management. For Task Management the TOE provides an OS-like environment for threads, including scheduling, and synchronization —all running in user mode, all (except for I/O) without calling the Windows Operating System….. Security Target Microsoft SQL Server 2008 Database Engine Common Criteria Evaluation Page 11/56 Table 1: Hardware and Software Requirements CPU ? Pentium III compatible at 1 GHz or faster (for the 32 bit edition) ? AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, Intel Pentium IV with EM64T support at 1.4 GHz or faster 1 RAM 512 MB Hard Disk Approx 1500 MB of free space Other DVD ROM drive, display at Super VGA resolution, Microsoft mouse compatible pointing device, keyboard Software Windows Server 2008 Enterprise Edition (in 64 or 32 bit) , English version, version 6.0.6001 .NET Framework 3.5 SP 1 Windows Installer 4.5 The following guidance documents and supportive information belong to the TOE: ? SQL Server 2008 Books Online: This is the general guidance documentation for the complete SQL Server 2008 platform ? SQL Server Guidance Addendum / Installation / Startup: This document contains the aspects of the guidance that are specific to the evaluated configuration of SQL Server 2008 The website https://www.microsoft.com/sql/commoncriteria/2008/EAL1/default.mspx contains additional information about the TOE and its evaluated configuration. Also the guidance addendum that describes the specific aspects of the certified version can be obtained via this website. The guidance addendum extends the general guidance of SQL Server 2008 that ships along with the product in form of Books Online. This website shall be visited before using the TOE. 1.3.3 Architecture of the TOE The TOE which is described in this ST comprises one instance of the SQL Server 2008 database engine but has the possibility to serve several clients simultaneously. 1.3.4 Logical Scope and Boundary of the TOE SQL Server 2008 is able to run multiple instances of the database engine on one machine. After installation one default instance exists. However the administrator is able to add more instances of SQL Server 2008 to the same machine. The TOE comprises one instance of SQL Server 2008. Within this ST it is referenced either as “the TOE” or as “instance”. The machine the instances are running on is referenced as “server” or “DBMS-server”.
Tutorial Working with SQL Profiler
This tutorial complements the article “Tracing ArcSDE Queries with SQL Profiler,” by Shannon Shields and Joseph Buckles which appeared in the January-March 2002 issue of ArcUser … magazine. It provides an introduction to tracing queries with SQL Profiler and using SQL Query Analyzer and will teach you how to create a trace template to capture events passed from ArcMap and ArcSDE to Microsoft SQL Server. You will also learn how toÜ Choose events and data columnsÜ Set filters in SQL ProfilerÜ Evaluate query statements …
SQL Profiler and SQL Query Analyzer are performance monitoring tools that are included with SQL Server. These tools can be used by a database administrator to diagnose problems and optimize performance. This tutorial introduces only a small portion of the functionality available with SQL Profiler and SQL Query Analyzer. More information on both tools can be found in the HTML Help file, SQL Server Books Online, available from the Microsoft Web site (www.microsoft.com/sql/techinfo/ productdoc/2000/). To learn more about administering SQL Server with ArcSDE, enroll in ArcSDE Administration for SQL Server, a five-day instructor-led course offered by ESRI. Visit the ESRI Web site (www.esri.com) for information about this course and other resources. Data for This Tutorial The data for this tutorial, based on a dataset containing land-use parcels for Wilson County, North Carolina, consists of a personal geodatabase feature class containing parcel data. Download the archived data file, Parcels.zip, from the ArcUser Online Web site. Important: After downloading and unzipping Parcels.zip, open the parcels personal geodatabase. Copy or import the parcels feature class into ArcSDE. If you do not have permission to load data into ArcSDE, get your ArcSDE administrator to copy the data and grant you SELECT privileges for the feature class. Tutorial Overview Before beginning this tutorial, verify that the ArcSDE server is running and that your Working with SQL Profiler HTML Help file, SQL Server Books Online, available from the Microsoft Web site provides, information on SQL Profiler and SQL Querry Analyzer login to SQL Server has appropriate permissions to view the data. In this tutorial you will Ü Create a SQL Profiler trace template Ü Connect to ArcSDE from ArcMap Ü Perform an attribute query Ü View the results of the query in SQL Profiler Ü Copy the traced query to SQL Query Analyzer Ü View query statistics Ü Create a column index in ArcCatalog Ü View query statistics using the new index Step 1: Create a SQL Profiler trace template This step defines a new trace definition in SQL Profiler by specifying which events to trace and what type of information to record for each event. A trace template must be created first to store the trace definition. 1. From the Start menu, choose Programs > Microsoft SQL Server > Profiler to start SQL Profiler. 2. From the SQL Profiler menu, choose File > New > Trace Template. 3. Click Save As to save the trace template. 4. Save the Template as tutorial.tdb in the default directory for SQL Server. 5. In the Trace Properties dialog box, click the Events tab. 6. In the Available Event Classes list, expand the Stored Procedures event category by clicking on the plus sign (+) next to it. 7. Select SP:StmtCompleted. 8. Click the Add button to add it to the Selected Event Classes list. 9. Click the Data Columns tab. 10. From the Unselected Data column, add the TextData, Duration, and Reads columns to the Selected data list. 11. Click Save to create the template but do not close SQL Profiler. Step 2: Connect to ArcSDE from ArcMap In this step you will query the Parcels feature class using ArcMap to find Railway right-of-way features. During this step a trace running in SQL Profiler will capture the…….
Microsoft SQL Server 2000 Security
The SANS Institute Page 1 of 6 Microsoft SQL Server 2000 Security The following is a checklist of security guidelines to follow in securing Microsoft SQL Server 2000. It is divided into categories: 1) Setup and General Issues, 2) Authentication, Users and Logins, 3) Permissions, 4) Logging, and 5) Applications. Within each category, the guidelines are described as Mandatory, Recommended or Paranoid; these labels are intended to help you prioritize your actions and to weigh the relative importance of each …
Setup and General Issues • [Mandatory] The Internet firewall should block all packets going to or coming from the database servers. Consider regulating traffic from the internal LAN as well. SQL Server primarily uses TCP 1433 for data sessions and UDP 1434 for pre-session handshaking. But be aware that SQL Server can also use UDP 137, TCP 139 and TCP 135. • [Mandatory] Stay abreast of the latest Service Packs and hotfixes, and install them as soon as feasible; however, do not blindly install Service Packs or hotfixes without first testing them on non-production servers, and without first backing up the production server. Keep in mind that SQL Server has Service Packs separate from the operating system’s Service Packs. • [Mandatory] Physically secure database servers and their backup media in locked, access-controlled rooms. Provide electronic surveillance when appropriate. • [Mandatory] In general, disable services or features of SQL Server that you are not currently using, such as SQL Mail. • [Mandatory] Scan the database server with the Microsoft Baseline Security Analyzer (MBSA) or similar auditing tool to reveal common insecurities. • [Mandatory] Regularly audit all shared folders on the database server to ensure that all permissions are minimal and that none of the shares are unnecessary. Be especially wary of shared folders that permit Write access. • [Mandatory] When replicating databases over the Internet, set up a router-to- router Virtual Private Networking (VPN) tunnel to connect the two LANs. When replicating over untrusted internal networks, use IPSec to encrypt the replication traffic. • [Mandatory] Assign a long and complex password to the SQL Server service account (preferably 25 characters or more, with 15 characters the minimum)….. • [Mandatory] The setup program for SQL Server should have done this already, but set the NTFS permissions on all database-related files, including the SQL Server binaries, to permit only Read & Execute to Authenticated Users, and Full Control for System, Administrators and the SQL Server service account. If you change the SQL Server service account, do it using Enterprise Manager so that permissions will be changed automatically. • [Mandatory] Use NTFS audit settings (SACLs) to track failed access to database-related files. • [Mandatory] The setup program for SQL Server should have done this already, but, on the following registry keys, remove all permissions for the Everyone group and grant Full Control to the SQL Server service account and local Administrators, then audit all failed access and all successful writes to them as well: HKLM\Software\Microsoft\MSSQLServer (for a default instance) or HKLM\Software\Microsoft\Microsoft SQL Server\ Instancename (for a named instance, if any). • [Mandatory] Delegate authority over the server and its databases by utilizing fixed server and database roles appropriately, or by creating your own custom roles, so that excessive power is not being placed in the hands of those who don’t need it. In short, do not simply place everyone in the sysadmin role out of convenience. • [Mandatory] All database and server management scripts should be stored in an access-controlled NTFS folder. Audit all access. Scripts should not contain hard- coded passwords. Consider digitally signing these scripts using the Windows Script Host (WSH) 5.6 or later, and warn on the attempted execution of scripts that fail signature verification. • [Recommended] Disable cross-database ownership chaining (KB810474). It is disabled by default after SP3 is applied to SQL Server 2000. • [Recommended] Regularly audit what the Public role has access to. This role is roughly equivalent to the Everyone group in the operating system. Remove or limit the rights of the Public role whenever possible, but do not explicitly deny permissions for it (simply remove them). • [Recommended] Regularly audit who has Execute permission on all stored procedures, especially xp_cmdshell. The Guest and Public logins rarely need the Execute permission. When in doubt, grant permission only to the sysadmin role. • [Recommended] When using UDL files, assign restrictive NTFS permissions and audit all access to the files. Avoid storing passwords in UDL files by using Integrated Windows authentication.
Configuring ViewPoint with Microsoft SQL Server 2000
VIEWPOINT Configuring ViewPoint with Microsoft SQL Server 2000 Introduction Many customers inquire whether Viewpoint will work with Microsoft SQL Server 2000 (MS SQL Server). Viewpoint can be configured to run with MS SQL Server, but SonicWALL does not provide any technical support for this configuration . Due to the number of inquiries, this document is provided only as a courtesy to our advanced SQL Server savvy customers that want to use this configuration. This document presents a procedure to remove the MSDE …
9) Edit \Tomcat\webapps\sgms\WEB-INF\web.xml and modify the following lines:
dbhost 127.0.0.1\SNWL dburl jdbc:inetdae7a:127.0.0.1/SNWL dbuser BAB7E1B 9875 CF081 dbpassword EE80851182B4B962FC3E0EDF1 F00275A to
SQL Server Integration Services (SSIS) Standards and Best Practices
SQL Server Integration Services Standards *The ideas provided in this document are not … several mechanisms to store log records generated from SSIS including Text Files, SQL Profiler … SQL Server Integration Services Standards*The ideas provided in this document are not “set in stone” and should be adapted to fit any specific business and technology needs. SQL Server Integration Services (SSIS) Standards and Best Practices Integration Services is an ETL (Extraction, Transformation, and Loading) tool designed to aid developers
o Generally, Dataflow source and destination item names should reflect the source or destination to which they are specifically connected. This could be a table or stored procedure within a database, or a flat file, etc ? Database Table Ex. dimEmployee ? Stored Procedure Ex. uspGetAllEmploye es • Connection Manager Names o Similar to Dataflow Sources and Destinations, the name of a Connection Manager should reflect the actual connection that they are making. ? Database Ex. ABCDataWarehouse ? Flat File Ex. NewOrders.csv o It is important to note that, by default, SQL Server will append the Host/Instance name to the beginning of the Connection Manager’s name. Generally, it is better to remove this as this may not accurately reflect the true name of the Host/Instance once a package is migrated from one environment to another. • Package Variable Names o Package variable names should describe their contents and use • Running Packages As a SQL Agent Job o When setting up a package to run on a schedule via a SQL Server Agent job, it is important to name it in such a way that a system administrator can easily identify it and, to some degree, be able to tell its overall function and/or to which project, program, or process it belongs. Package Documentation Thorough in-package documentation greatly aids in allowing more than one developer to understand the purpose of a given SSIS package and how it functions. It conveys meanings and ideas that simply interpreting the structure of a package or following along with the code cannot. • In Task Descriptions o Each Control Flow Task and Data Flow Item allows a user defined description to be written as part of the Task or Item properties. These Description properties should be utilized to further describe the functionality taking place within the task or item, any special circumstances, or really any other bit of information the developer feels would facilitate another developer interpreting and taking over development of a given SSIS package. • Variable Descriptions o As part of the properties associated with a package variable, a description field is made available. A developer should use this field to identify what the role of the variable will be and what data should be contained within it……