Memory: The default, which is generally recommended, is for SQL Server to adjust its memory usage based on the physical memory available in the system.
Use clustering for fault-tolerance.
2 or more SQL Servers (nodes) that are a single virtual server.
Active-Active cluster – have 2 different SQL Servers running, one on each node.
Active-Passive cluster – only one of the nodes has SQL Server running, the other does nothing until failover. Better performance, more expensive.
Two Node Clustering - uses Windows 2000 Advanced Server
Three/Four Node Clustering - uses Windows 2000 Datacenter
There is a bit of downtime (few mins) when the failover actually happens.
Must use VIRTUAL_SERVER\Instance-name to connect – SQL Server won’t listen on the actual IP address of the physical server.
Setting up a cluster:
Collations determine how character data is stored and sorted.
SQL Server 2000 supports multiple collations in a single installation / instance. Earlier versions did not.
Collations can be applied to databases, columns, variables and parameters.
SQL Server can run up to 16 named instances concurrently.
The default instance has the name of the computer you’re installing on. Further instances are referred to as COMPUTERNAME\INSTANCENAME. The INSTANCENAME can’t exceed 16 characters.
Each instance listens on a unique network address. The connecting application only needs to supply the COMPUTERNAME\INSTANCENAME.
A default instance of SQL Server 2000 listens on the same network addresses as earlier versions of SQL Server; therefore, applications using the client connectivity components of SQL Server version 7.0 or earlier can continue to connect to the default instance with no change.
Named instances listen on alternative network addresses, and client computers using the client connectivity components of SQL Server version 7.0 or earlier must be set up to connect to the alternative addresses.
It’s usually recommended to run with just one instance.
Running multiple instances of SQL Server on a single computer is best:
Default: Use same log on for both services, automatically start each service.v
If you specify the local System account, it does not have network permissions in NT 4, only in Windows 2000. So, you wouldn’t be able to configure replication in NT4.
Autostart – can only be on for SQL Server Agent if it’s on for SQL Server as well.
If services can’t start, it could be that the Administrator has disable the Log on as a service right. To get round this, you could configure the services to run under the local System account.
To upgrade from SQL Server 6.5, you must have a DEFAULT instance of SQL Server 2000. You can only upgrade one 6.5 Server. 6.5 is not removed.
In upgrading SQL Server 7, it is overwritten.
SQL Server 6.5, 7.0 and 2000 can co-exist on the same computer, but only one version at a time can be active.
To upgrade a cluster, you first have to uncluster it using the Cluster Wizard. You must do this before installing SQL Server 2000.
A linked server configuration allows SQL Server to execute commands against OLE DB data sources on different servers. Could link to an Oracle, Access or other SQL Server.
sp_addlinkedserver
Enterprise Manager – Security folder, Linked Servers.
If defining a SQL Server 7.0 database as a linked server in SQL Server 2000, you should replace the client tools and connectivity components on all the client computers with the SQL Server 2000 version.
These are two separate services used by SQL Server to handle mail.
SQL Mail is used by the MSSQLServer service, while SQLAgentMail is used by the SQLServerAgent service.
SQLAgentMail can use a separate domain account and mail profile if required. If you want both to use the same mail profile, then you need to configure both services to use the same domain account.
Email can be sent when:
SQL Mail uses stored procedures to receive e-mails received in the mailbox that you assign to it (to do this, you need to create a regularly scheduled job that uses the stored procedure xp_processmail), and to send e-mails from a trigger or other stored procedure, using xp_sendmail.
SQL Mail must have a mail profile created for the same domain account that is used to start SQL Server.
In Enterprise Manager, your server has a Support Services folder. If you double click on SQL Mail in this folder, you can configure SQL Mail to use the mail profile that you have set up for the SQL Server domain account.
A matching pair of SQL Server Net-Libraries must be installed on a client and server computer to support a particular network protocol (for example, client TCP/IP Sockets Net-Library and server TCP/IP Sockets Net-Library). Some Net-Libraries, such as Named Pipes and Multiprotocol, support several network protocols.
Setup installs all of the client and server Net-Libraries.
Defaults:
The Named Pipes Net-Library, as well as the TCP/IP Sockets Net-Library, supports TCP/IP, so you can disable one of these libraries when using TCP/IP to connect.
Named Instances of SQL Server only support:
Consider:
Microsoft's tool for "scaling out" doing this is the federated server. With federated servers, you split a table horizontally across as many database servers as necessary. That is, one server contains the first hundred thousand rows of the table, the next contains the next hundred thousand, and so on. SQL Server 2000 sports updateable distributed partitioned views, which is a fancy way to say that a single view can treat this collection of federated tables as a single table. With federated servers, there's no practical limit to the amount of data you can store and update in a single table. Scaling out has the added bonus of being less expensive than scaling up for equivalent power.
You can use the affinity mask option to improve the performance of computers with more than 4 processors, that handle a lot of processes. This way, you can limit SQL Server to using the CPUs that are not being used heavily by the NICs (the highest numbered CPUs. Eg 00111111 (dec 63) makes SQL Server stick to the first 6 CPUs.
To detach:
sp_detach_db @dbname='myDB', @skipchecks= 'true'
If you set skipchecks to true, then UPDATE STATISTICS is not run. This is useful if you're moving the database to read-only media. Only members of sysadmin can run this.
To attach:
sp_attach_db @dbname='myDB', @filename1='physical file name' filename2 etc.
Only members of sysadmin and dbcreator can run this.
If the db only has one data file and one transaction log file, you can attach it without the transaction log, as long as the db was closed cleanly, and SQL Server will create a new transaction log automatically when the data file is attached. Useful for emailing a db.
If you are detaching and attaching a db from a server configured for replication, you need to run sp_removedbreplication to remove replication. This can be done either before detaching or after attaching.
Data and log information are never mixed on the same file, and individual files are used only by one database.
Best write performance is obtained by putting the transaction log on a separate physical disk. But if you have to have the transaction log on a disk with either a data file or OS & SQL Server files, choose to have it with a data file as this will probably have less I/O than the O/S files etc.
To stop a transaction log from getting full (error 9002), back it up using the default options as this truncates the log after backing up. Or, set the db to use the Simple Recovery model - this truncates the transaction log after every checkpoint.
If best READ performance is the objective, and you only have two physical disks to play with, then it may be more beneficial to have two datafiles on separate disks, one of which contains the transaction log as well. But this will impact WRITE performance.
Triggers are a kind of stored procedure.
INSTEAD OF triggers run instead of the triggering action. They can be defined on the UPDATE, INSERT or DELETE of a table or view. These triggers are useful for updating base tables from a view.
AFTER triggers (this is the default) run after the triggering action has finished. They can be defined on the UPDATE, INSERT or DELETE of a table only.
FOR triggers are the same as AFTER triggers and are for backward compatibility only.
On first run, SQL Server compiles the Stored Procedure into an execution plan, stored on the server. This makes subsequent runs quicker and reduces network traffic.
If you make large changes to a db, like adding lots of data, adding or recreating indexes, or recalculating statistics, then an execution plan may become outdated and the stored procedures may run slowly. You should recompile them.
Rows can never span pages. How many rows on a page? Divide the size of a page (8K) by the estimated size of an average row. Thence you can work out the needed size of the table - but remember that any indexes will require additional space.
After restoring a full backup, you only need to restore the LATEST differential backup, not every single one since the full backup. You DO need to restore every single transaction log since the last differential backup, though.
When SQL Server writes data, the data first goes to the write cache on a disk controller and then to the disk. SQL Server considers data on the write cache to be written, but it will be lost if there is a power outage, making the db inconsistent. You can disable the write cache to prevent this, but this makes I/O slower. So, you could implement a battery-backed write cache.
When a db failure occurs, the first thing you should do, if possible, is back up the current transaction log. Use NO_TRUNCATE to prevent the inactive portion of the log from being truncated. With SQL Server 7.0, this was not possible if the Primary data file was lost, because the information about file locations was stored only in the primary data file. SQL Server 2000, though, stores file location information both in the primary data file and in the master db (the latter is where the information is mainly used).
If restoring master, you should start SQL Server in single-user mode (Sqlservr.exe -m)
Full-Text searching is installed by default with Standard and Enterprise edition installations.
Full-Text involves two features: the ability to issue queries against character data, and the creation and maintenance of the underlying Full-Text Indexes facilitating these queries.
Unlike regular indexes, full-text indexes are stored in the file system. Only one full-text index is allowed per table, and the full-text indexes for the database are stored together in one or more full-text catalogs.
Full-text indexes are NOT backed up or restored with the database. You should back them up separately, for instance using NT Backup.
If you restore your database to a new machine, it knows about the full-text catalogs it used to have, but they are no longer there in the file system!
In Enterprise Manager, Full-Text Catalogs window, right-click on each (non-existent) catalog and select Rebuild All Catalogs. The catalogs will contain no data after you have done this - you need to perform a full population of each catalog before you will be able to use them.
With log shipping, copies of a transaction log are fed from a source database to a destination database on a constant basis. This lets you keep the destination database as a warm standby, ready to replace the source database at any time. It also provides an easy way to separate OLAP analysis applications from OLTP data entry applications; let the data entry proceed at your primary server, then use log shipping to update a secondary server where you can run time-consuming analyses. The Database Maintenance Plan Wizard has been enhanced to include log shipping setup. A separate server within your organization can be designated as the Monitor Server to keep track of all log shipping operations.
DataBase Console Commands that check the physical and logical consistency of a database, and often can fix any problems that are detected.
Important examples include:
DBCC SHOWCONTIG - displays information about fragmentation of the table's data and indexes. If table fragmentation is OK, then Scan Density will be high and Logical Scan Fragmentation will be low. If this is reversed, then you should rebuild the table's clustered index to improve performance.
DBCC DBREINDEX - Rebuilds the clustered index without having to drop the index first. This also means that any non-clustered indexes are rebuilt, because they use the clustered index keys as pointers to the data. This DBCC command has the same effect as using the CREATE INDEX statement with the DROP_EXISTING option.
DBCC SHRINKFILE (myfile, desiredsize) - Reclaim free space. This CAN be run while users are using the db. Also, you can enable the autoshrink option, but you don't know when SQL Server will run the shrink operation, and it may interfere with your backups.
DBCC CHECKDB (myDB, REPAIR_ALLOW_DATA_LOSS - to repair consistency and allocation errors with possible loss of data.
DBCC CHECKCATALOG - checks system tables. Does no repairs.
DBCC CHECKALLOC - corrects allocation errors only. Can be run against a db.
DBCC CHECKTABLE(myTable, REPAIR_ALLOW_DATA_LOSS) - as it's run against a table, can only fix consistency errors, not allocation errors. With REPAIR_REBUILD, rebuilds including rebuilding indexes etc, but no data loss.
DBCC DBREPAIR - This DROPS a db!! ONLY for backward compatibility so don't use it!
DBCC CHECKIDENT ('myTable', NORESEED | RESEED, [new reseed value] - Checks and if necessary corrects the current identity value for the table.
DBCC FREEPROCCACHE - clears the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache.
You can capture data about SQL Server and save to a trace (.trc) file. You can also save a template (.tdf) and run that trace any time.
A trace can only monitor one instance of SQL Server.
Multiple traces can be run simultaneously.
Important uses:
The easiest way to copy whole databases is to use the DTS Import/Export Wizard - it can copy all the db objects like constraints etc etc. You can specify a source and destination, or else specify a new destination db to be created. You can run the package, save it or save and schedule it.
Users can access a db, perform queries and update data through a Web Browser.
To set up:
When you want to use a DTS package to import data and you want to do it quickly, you can create a Bulk Insert task. This is the fastest because it doesn't allow transforming of the data. You CAN specify columns, their order and column names. Format files are useful when the data file contains more or less columns than the destination table, or the columns are ordered differently.
Bulk copy operations can be fully logged or minimally logged.
bcp can be run against a table, view or query. When you run bcp to export data from a query, you must use the queryout parameter to specify the direction of data flow.
bcp CAN'T copy db objects like indexes or constraints.
If you issued a query in bcp that returned multiple result sets, only the first would be exported.
You could also run a SELECT INTO query, specifying a temporary table, and then run bcp against the resulting table. But it would need to be a global temporary table (##mytable), not a local temporary table(#mytable) because if you create a local temporary table in Query Analyzer, the table is only available to Query Analyzer and not to bcp.
The TABLOCK hint will make it load faster.
If you know there is no duplicate data, you can load multiple files simultaneously from multiple client computers. This is especially useful if your server has multiple processors.
For those maintaining data in heterogeneous sources, the new OPENROWSET function in T-SQL will come as a distinct improvement over the old OPENQUERY. OPENROWSET allows you to specify OLE DB connection information directly in an ad-hoc query. On a practical level, this means that you can use a rowset from any OLE DB provider in place of a native SQL Server base table.
If you are running a DTS package using an OLE DB provider on a CLIENT computer, then the provider needs to be installed on that computer.
DTS packages can be saved to:
Using CAST:
CAST ( expression AS data_type )
Using CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
SET DATEFORMAT is used only in the interpretation of character strings as they’re converted to dates. The display is NOT affected!
E.G.
SET DATEFORMAT mdy
GO
DECLARE @datevar datetime
SET @datevar = '12/31/98'
SELECT @datevar
GO
Returns:
1998-12-31 00:00:00.000
You can change the data type of a column quickly in Enterprise Manager. If it is a foreign key that you are changing, or a column referenced by foreign keys in other tables, then you will get the Data Type Change Required dialog. If you choose Yes, then the schema change will be populated to all the other related tables - this is true for length changes as well as data type changes.
Note: ON UPDATE CASCASE is only for update statements, NOT schema changes.
Assigning a domain user to the MSSQLServer service lets SQL Server communicate with other computers on the network. After you assign this, the domain user is automatically granted the Log on as a service right.
SQL Server 2000 replication lets you copy, distribute and modify data across your enterprise.
You can configure a single publication with vertical or horizontal partitions.
Snapshot Replication
Schema, data and db objects are sent to the Subscribers in a replication scheme. A snapshot image is put in a snapshot folder, by default created on the Distributor. You can reduce load on the Distributor by setting the snapshot folder to sit on a network share, and by configuring pull subscriptions rather than push subscriptions.
Merge Replication
Setting the @keep_partition_changes parameter using the sp_addmergepublication stored procedure can reduce the amount of time required by the Merge Agent to determine whether certain rows that were recently changed should be send to subscribers.
Transactional Replication
Where dbs have to be exactly the same all the time - e.g. where inventory levels must be consistent - use Transactional Replication and configure replication to occur continuously. Either push or pull can be used, and the changes will be applied to the subscribers in the same order that they occurred at the publisher.
If the changes can also come from the Subscribers, you need to have immediate-updating subscribers. The servers must be connected with a high-speed, reliable link. 2-phase commit ensures that changes at the subscribers will be applied immediately to the publisher.
If you need to change the data in a Foreign Key column, then do an ALTER TABLE on the table you’re updating, and specify an ON UPDATE clause with the CASCADE option on the column you’re updating.
USE myDB
EXEC sp_revokedbaccess User
EXEC sp_droplogin Login
If the SQLServerAgent service is assigned to a user account that is a member of the Administrators group on the SQL Server computer, then users who aren’t members of sysadmin will be able to create jobs that include CmdExec and ActiveScript steps!
Use an application role to accommodate complex security requirements or to enable users to only access data via an application and not directly.
Application roles have no members.
Application roles bypass all existing permissions for a connection.
The application must provide a password, by hard coding or in another way, for instance using an encrypted key in the registry.
To create:
EXEC sp_addapprole ‘approlename’, ‘password’
To change the password:
EXEC sp_approlepassword, ‘approlename’, ‘newpassword’
To use the application role from an application:
EXEC sp_setapprole ‘approlename’, ‘password’
db_accessadmin - can add & remove db user accounts but not fully manage the db.
Data can be updated through a view if it only has one base table. If the WITH CHECK OPTION is specified in the definition of the view, then rows can’t be modified in a way that would cause them to disappear from the view.
If there are multiple base tables, you can use an INSTEAD OF trigger to update the base tables.
When a user logs on to a database, what they can do is determined by the permissions of their log in an those of the roles (or groups) that their log ins belong to.
Object permissions allow the user to work with data and execute procedures.
Statement permissions allow the user to create a db or an object in a db.
GRANT ALL TO user_id gives the user permission to execute any statement.
GRANT CREATE DATABASE TO user_id does what it says!
Implied permissions are granted when a user is added to a role.
DROP TABLE is only allowed for members of the db_owners, sysadmin and ddl_admin fixed db roles, and can’t be explicitly granted to other users in the database.
SQL Server auditing can be enabled or modified by members of sysadmin.
There are several categories of events that can be audited using SQL Profiler, such as:
A torn page is when an I/O fails to complete successfully. For this to be detected by the recovery process, Torn Page Detection db option must be on. If torn pages are found, the db is marked as suspect and you will need to restore it from a backup.
If you didn’t have Torn Page Detection on, you could run DBCC CHECKDB PHYSICAL_ONLY to check physical consistency.
You can turn off the suspect flag using sp_resetstatus but this doesn’t repair anything!
SQL Server events are written to the Windows application log.
SQL Server agent monitors the application log for SQL Server events, and looks for ones that you’ve defined to:
If you disable the query govenor cost limit option, then SQL Server will let any query execute, no matter how long it will take.
Multi-server jobs - if you manage multiple instances of SQL Server on different computers. First, configure a master server and one or more target servers. The target servers connect to the master server at regular intervals, download the jobs, disconnect, execute the jobs then send the execution results back to the master.
If you use Enterprise Manager to create and maintain the multi-server jobs, then you don’t need to notify the targets of any changes.
If you use the sp_update_jobstep stored procedure, then you need to run sp_post_msx_operation stored procedure in the msdb database. This updates the sysdownloadlist system table, which contains job information for the target servers. They will then download the full job definition again
.
sp_msx_enlist enlists a new target server.
sp_msx_defect defects the current server so it’s no longer a target server.
|
Cassie Simons (MCSE, MCSD, MCDBA) is the author of "How to Pass Your MCSE/MCSD Quickly and Easily". Visit www.ExamsAreEasy.com today and subscribe to a unique free "Bootcamp" ezine giving you important tips and secrets to help you to certification success! |