Posted by arlene

The ability to script database objects is a wonderful mechanism for DBAs. For example, if we have two sites running, development and production, whenever we make a structural change in development we need to replicate that change to the production site. We can’t use the Copy Database Wizard because we do not want to lose the data that we have in the production database. Scripting the database objects allows us to bring the structural changes across without affecting the data in our production database.

Let’s try scripting one of our stored procedures. Open Enterprise Manager and navigate to the Stored Procedures folder. Find the Pe rsonSpy Insert stored procedure. Right-click the stored procedure, select All Tasks, and then Generate SQL Script.

With this screen we can actually script our whole database. We can generate SQL Scripts for

• Tables
• Views
• Stored procedures
• Defaults
• Rules
• User-defined data types
• User-defined functions

On the Formatting tab we can specify the format for our scripts, including whether to create DROP statements for each object.

The Option tab allows us to specify whether the permissions on the object(s) come across when the object is scripted. We can also script our database users.

On the General tab we can preview the script before we save it to disk, allowing us to verify that the script looks as it should.

When we save the file, it is saved with a . sql file extension. This file can be viewed in Notepad because it is only a text file. The Web and SQL Server 2000 are cool, aren’t they?

Scripting gives a lot of flexibility when migrating our changes from a development site to a live site. I am sure that you will make good use of this feature.

Using AFTER and INSTEAD OF Triggers

Let’s take a look at them again, except this time from the angle of what’s new and cool?

Earlier versions of SQL Server have supported triggers for quite a long while. But in this new version of SQL Server we see some enhancements to the way triggers fire and behave. The best thing is that we have even more control over our triggers.

SQL Server 2000 supports the following two types of triggers:

• AFTER triggers—These triggers fire after an event has occurred. For example, our trigger that we defined on the Spy table is an AFTER trigger. The trigger fires after the data has been inserted into the table. These triggers cannot be placed on a view. We are also allowed to specify multiple AFTER triggers on a table for a defined event (INSERT, UPDATE, or DELETE). We can then set the order in which those triggers fire.
• INSTEAD OF (BEFORE) triggers—These are the new addition to SQL Server 2000. With INSTEAD OF triggers, we capture an event before it is processed. An INSERT into our Spy table captures the data before the data is inserted into the table. We can then manipulate the data, execute stored procedures, and so on. These new triggers can be defined on views.

What does this mean for us? With the new enhancements we can move our business rules to SQL Server without having to get our user interface (in a two-tier model) to capture the rules. So if business rules change, we only need to modify them in one place.

Looking at Distributed Views

A distributed view is a cool new feature that allows us to horizontally partition a table across several servers.

Horizontally partitioning means to break up a table horizontally. For example, if we have a 10,000-row table and 10 servers, we place 1,000 rows on the first server, 1,000 on the second, 1,000 on the third, and so on.

How do we achieve this? We place rows 1-1,000 on the first server, 1,001-2,000 on the second server, and so on. To enforce this we use CHECK CONSTRAINTS to limit the value that a column can hold.

After the data has been partitioned we can create a view that links all the servers together as though we were looking at a single table. What’s so cool about that? When we place a restriction on the view, for example all rows less than 5,000, SQL Server 2000 will use the CHECK CONSTRAINTS of the servers to decide which servers to query for the data. So servers 5-10 will be excluded from the query immediately. This saves querying servers for data for no apparent reason..

Now isn’t that cool? This type of flexibility allows us to support the largest corporate environments or the busiest Web sites. In the future if we find that we need more resources, we just add another server. Talk about scalable!

Possibly related posts: (automatically generated)
Scripting Database Objects

• Primary, Secondary, and Caching-Only Name Servers
• What Role Will Your Server Perform?
• What Role Will Your Server Perform?
• The Active Directory and Dynamic DNS continue...
• Directory Replication
• Windows NT Domain Controllers and Member Servers
• Dedicated Server with Canadian Web Hosting
• What Is a Domain Tree? What Is a Forest?
• Dynamic DNS
• Active Directory Service Interfaces (ADSI)