SQLServerPedia

Last week saw the long awaited release of SQL Server Data Tools for Business Intelligence. This version of SSDT  has the templates for creating Intelligence Services packages, Reporting Services reports, and Analysis Services projects both cubes and tabular, inside of Visual Studio 2012. That’s great news as a lot of developers, myself included, have been waiting for this functionality.

However the bad news is that it doesn’t include the Database Projects templates released last year. You’ll still need to install them separately. But they will work together.

So if you want just the BI templates for Visual Studio 2012 you only have to install the BI version of SSDT. If you also want the database projects you will need to install both the BI templates and the database templates. And if you want to use the test plans for your new database projects and create SSRS reports or SSIS packages you’ll need a full edition of VS 2012, either Premium or Ultimate, plus the database templates plus the BI templates.

You can get the database project templates for either VS2010 or VS2012 from the SSDT Team blog. The BI templates for VS2012 can be found as a Microsoft download. And you can read more about the added functionality on the SSRS Team blog, the SSIS Team blog, or the SSAS Team blog.

Do you need to track permissions on the SQL server databases that you are responsible for? Are you sure you know everyone who has sysadmin rights, or who can delete records from the production servers? How often should you perform a security audit?

In my case I don’t have to check permissions on a regular basis. But I still want to keep track of who can do what. So I run the following scripts to check both server and database level permissions as well as check the default trace for any new permissions or users I may not be aware of. They also help me easily compare permissions across servers.

I run these scripts on a mix of SQL 2005 and SQL 2008 servers. I haven’t had a chance to run on SQL 2008 R2 or SQL 2012 yet so I’m not guaranteeing that they’ll work there. Also they may return a lot of records, depending on the number of users in your databases. But they can be modified to filter by user. As always, though, please understand the scripts before running them on a production server.

One final note. I used a script I found on the internet a while back as a basis for the ones I’m using now. Unfortunately I don’t remember where I found them so I can’t properly give credit where it’s due.

This script is run against the server and has 2 parts. First it returns a list of server roles and who belongs to which role. The second part returns individual permissions for each account.

-- Server role members
SELECT
sp1.[name]
, sp1.type_desc
, sp2.[name]
, sp2.type_desc
, sp2.create_date
, sp2.modify_date
FROM sys.server_role_members AS srm
INNER JOIN sys.server_principals AS sp1
ON srm.role_principal_id = sp1.principal_id
INNER JOIN sys.server_principals AS sp2
ON srm.member_principal_id = sp2.principal_id
ORDER BY sp1.[name], sp2.[name]

-- Server users
SELECT
sp1.[name]
, sp1.type_desc
, sp1.create_date
, sp1.modify_date
, sp.state_desc AS 'Permission'
, sp.permission_name AS 'Action'
, sp.class_desc
, sc.class_desc
, sp2.[name]
FROM sys.server_permissions AS sp
INNER JOIN sys.server_principals AS sp1
ON sp.grantee_principal_id = sp1.principal_id
INNER JOIN sys.server_principals AS sp2
ON sp.grantor_principal_id = sp2.principal_id
LEFT JOIN sys.securable_classes AS sc
ON sp.major_id = sc.class
ORDER BY sp1.[name], sp1.type_desc

My next script does the same thing on a database level. Just change the name of the database to see who belongs to which database role or other individual rights.

USE <database_name, sysname, master>;

-- Database role members
SELECT
pr1.[name]
, pr1.type_desc
, pr2.[name]
, pr2.type_desc
, pr2.create_date
, pr2.modify_date
FROM sys.database_role_members AS rm
INNER JOIN sys.database_principals AS pr1
ON rm.role_principal_id = pr1.principal_id
INNER JOIN sys.database_principals AS pr2
ON rm.member_principal_id = pr2.principal_id
ORDER BY pr1.[name], pr2.[name]

-- Database users
SELECT
pr.[name]
, pr.type_desc
, pr.create_date
, pr.modify_date
, dp.state_desc AS 'Permission'
, dp.permission_name AS 'Action'
, CASE dp.class
WHEN 0 THEN 'Database::' + DB_NAME()
WHEN 1 THEN ISNULL(s.[name] + '.', '') + OBJECT_NAME(dp.major_id)
WHEN 3 THEN 'Schema::' + SCHEMA_NAME(dp.major_id)
END AS 'Securable'
, pr2.[name]
FROM sys.database_permissions AS dp
INNER JOIN sys.database_principals AS pr
ON dp.grantee_principal_id = pr.principal_id
INNER JOIN sys.database_principals AS pr2
ON dp.grantor_principal_id = pr2.principal_id
LEFT JOIN sys.objects AS o
ON dp.major_id = o.[object_id]
LEFT JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE dp.class IN (0, 1, 3, 4)
AND dp.minor_id = 0
ORDER BY pr.[name], pr.type_desc

Finally there’s a way to monitor who is adding users, or changing permissions. Those events are captured by the default trace that runs when the SQL service starts up. My script only checks the latest trace file, but it can be modified to look at older .trc files. If you’ve disabled the default trace then this won’t work.

USE master;

SET NOCOUNT ON;

DECLARE @enable int;
 SELECT TOP 1 @enable = CONVERT(int,value_in_use)
 FROM sys.configurations
 WHERE name = 'default trace enabled';

IF @enable = 1
 BEGIN
 DECLARE @d1 datetime;
 DECLARE @diff int;
 DECLARE @curr_tracefilename varchar(500);
 DECLARE @base_tracefilename varchar(500);
 DECLARE @indx int ;

SELECT @curr_tracefilename = path
 FROM sys.traces
 WHERE is_default = 1;

SET @curr_tracefilename = reverse(@curr_tracefilename);
 SELECT @indx = PATINDEX('%\%', @curr_tracefilename);
 SET @curr_tracefilename = reverse(@curr_tracefilename);
 SET @base_tracefilename = LEFT(@curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc';

SELECT
 t.LoginName
 , t.HostName
 , t.ApplicationName
 , t.StartTime
 , t.DatabaseName
 , t.TargetUserName
 , t.RoleName
 , e.[name] AS EventDesc
 FROM ::fn_trace_gettable(@base_tracefilename, default) AS t
 INNER JOIN sys.trace_events AS e ON t.EventClass = e.trace_event_id
 WHERE t.EventClass BETWEEN 104 AND 114
 END
 

I’d love to hear how you handle security on your servers. I’m always looking for ways to improve my processes.

Like a lot of fellow SQL Server DBA’s I have a number of production servers to manage. One of the tasks I have to perform every year is to create a new set of one-time schedules for an existing job. The date of the schedule varies from month to month due to different business logic. This task would be extremely time consuming if I had to use the SSMS interface to add the new schedules to the jobs. So I’ve prepared a T-SQL script I can run on multiple servers at once.

Here’s the script I use. The parameters are all the options you would set in the SSMS GUI. In my case this is a one time schedule not set to run via a recurring pattern (every Monday for example). I set @active_start_date and @active_start_time to the date and time I want the schedule to run on; in this case it’s set to February 4th 2013 at 8:50 PM.

EXEC msdb.dbo.sp_add_jobschedule
@job_name = 'My Job Name',
@name = 'Jan 2013',
@enabled = 1,
@freq_type = 1,
@freq_interval = 1,
@freq_subday_type = 0,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_start_date = 20130204,
@active_end_date = 99991231,
@active_start_time = 205000,
@active_end_time = 235959

I usually set a year’s worth of schedules at the same time. When I’m done I run the following script to insure that I’ve created the schedules correctly.

DECLARE @job_id UNIQUEIDENTIFIER
SET @job_id = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = 'My Job Name')

SELECT name, active_start_date, active_start_time
FROM msdb.dbo.sysschedules
WHERE schedule_id IN
(
SELECT schedule_id
FROM msdb.dbo.sysjobschedules
WHERE job_id = @job_id
)
AND enabled = 1

And that’s all there is to it. I hope this helps you but please, test the script first. Make sure you understand what it’s doing before running in production. If you screw something up I won’t be around to help you out as I plan on winning the next lottery!

I’ve written a few posts on using SQL Server Data Tools (SSDT) to build a database sandbox to play in. There’s a few ways to build out the schema, either by using database object scripts or directly from an existing database. The problem with those solutions is that it doesn’t include the data, though there are ways to populate the sandbox db. But if you use the Power Tools for SSDT you can quickly create and deploy a fully populated version.

First, go to the SSDT blog site to download and install the latest version of SSDT Power Tools. There’s one for Visual Studio 2010 and Visual Studio 2012, be sure to grab the right one. Also grab the latest SSDT update, if you haven’t already. After that it’s a simple 2 step process..

Step One is to right click on the database you’re creating the copy of and choose Extract Data-Tier Application. Enter the name and file path for the extract. Next choose the Extract Settings. You have the option to only extract the schema, or you can include the data. If you choose to include the data you can select one or more tables whose data you want to include. Finally check the options you want, like also scripting logins and permissions. When you’re done your screen will look something like the one below. Go ahead and click OK when you’re done.

You can watch the progress while the extract is running. The time required depends on the size of the database. In my little demo I’m using a small database so it only took 30 seconds. The process creates a more detailed log that you can read if you want to see more than just the summary.

Step Two is just Step One in reverse. This time right click on the Databases node of the SQL instance where you’re deploying to and choose Publish Data-Tier Application. Select the .dacpac file you created in Step One, enter a database name, edit the Target database connection if you need to, and choose whatever other options you want. Click Publish when you’re ready. In this case I’m publishing back to the same SQL instance where the original lives, but with a different database name. But you will most likely be publishing it on a different instance.

Again, you can watch the progress of your deploy, and view the log for more detailed info. My little sample only took 20 seconds.

After that you can go play in your new database sandbox. Not counting mouse clicks it only took me 50 seconds!

Like most DBAs, I collect different statistics on the SQL servers I’m responsible for. I want to be able to track database growth over time. I also want a way to quickly check how much space is available. At first I wrote a few local scripts and saved to tables in a local database. While learning SSIS for the 70-448 exam I created a package to move the data from the local servers to a central repository and output the last two collections to an Excel spreadsheet. That works pretty well. But I’m always looking for new and possibly better ways to perform my day to day tasks. And I just got a list of new SQL servers to monitor, and I wasn’t able to fully port my SSIS solutions to include some of those servers. So I decided to switch over to PowerShell.

This post isn’t meant to be an overview or PowerShell, or even PowerShell for SQL. Those topics have been covered elsewhere and much more thoroughly than I can here. If you need a starting point for PowerShell I’ll put some links to sites to get you started at the end of this post. Today I’m going to cover two specific tasks; getting total and free space per drive and space used by each database for a list of servers. The results will be stored in a central database that I can query later for trends.

For this post my central repository is called “MONITOR” and the database is called “ServerStats”. Inside ServerStats I’ve created the two tables below;

CREATE TABLE dbo.DBStats(
ServerName sysname NOT NULL,
RunDate datetime NOT NULL,
DBName sysname NOT NULL,
Name sysname NOT NULL,
[FileName] nchar(520) NOT NULL,
TotalSize float NOT NULL,
UsedSpace float NOT NULL,
FreeSpace float NOT NULL,
FileID int NOT NULL
)

CREATE TABLE dbo.DriveSpace(
ServerName sysname NOT NULL,
Drive char(3) NULL,
TotalSpaceInGB numeric(6, 2) NULL,
FreeSpaceInGB numeric(6, 2) NULL,
RunDate datetime NULL
)

To run the PowerShell scripts you’ll need to get a few things. In my profile I load the SQLPSX snap-ins as well as Invoke-SQLCMD2, Write-DataTable, and Out-DataTable. See the Links and Downloads section at the bottom of this post for more information on these modules.

My first script is for getting the drive size. I simply loop through an array of servers, then query WMI for hard drive information with Get-WMIObject. I don’t want info on CD drives so I only filter for where the drive type is 3. I send the result to a data table object ( | Out-DataTable). Finally I write the data table back to my monitoring server with Write-DataTable. Notice I declare the variable $unit as “GB”. PowerShell then gives me my data in gigabytes. I could change this to “MB” if I want to see the size in megabytes instead.

$srvlist = @(get-content ".\ServerList.txt")
$unit = "GB"
$measure = "1$unit"
$wmiQuery = "
SELECT SystemName, Name, DriveType, FileSystem, FreeSpace, Capacity, Label
FROM Win32_Volume WHERE DriveType = 3
"@

foreach ($instance in $srvlist)
{
$dt = Get-WmiObject -ComputerName $instance -Query $wmiQuery |
Select-Object SystemName, Name, @{Label"TotalSpaceIn$unit"; Expression={"{0:n2}" -f ($_.Capacity/$measure)}}, @{Label="FreeSpaceIn$unit"; Expression={"{0:n2}" -f ($_.FreeSpace/$measure)}} |
Where-Object {$_.Name -NotLike '\\?\*'} |
Sort-Object Name | Out-DataTable

Write-DataTable -ServerInstance "MONITOR" –Database ServerStats-TableName DriveSpace -data $dt
}

My second script, for getting database sizes, is similar. I still loop through an array of servers. But this time for each instance I run a dynamic SQL script by calling Invoke-Sqlcmd2. That script creates a temp table, then loops through each database for the sizes. I’m using a trusted connection but if I wasn’t I could supply –UserName and -Password to Invoke-Sqlcmd2.

$srvlist = @(get-content ".\SQLServerList.txt")

foreach ($instance in $srvlist)
{
$dt = Invoke-Sqlcmd2 -ServerInstance $instance -Database master "CREATE TABLE #dbStats(
[Servername] [sysname] NULL,
[RunDate] [datetime] NULL,
[DBName] [sysname] NULL,
[Name] [sysname] NULL,
[FileName] [nchar](520) NULL,
[TotalSize] [float] NULL,
[UsedSpace] [float] NULL,
[FreeSpace] [float] NULL,
[FileID] [int] NULL)

EXEC sp_msforeachdb 'USE [?]

DECLARE @PageSize float
SELECT @PageSize = v.low /1024.0 FROM master.dbo.spt_values v WHERE v.number = 1 AND v.type = ''E''

INSERT INTO #dbStats
SELECT @@servername AS ServerName,
CONVERT(DATETIME, CONVERT(CHAR(12), GETDATE(), 101)) AS RunDate,
''?'' AS DBName,
RTRIM(s.name) AS [Name],
RTRIM(s.filename) AS [FileName],
(s.size * @PageSize) AS [TotalSize],
CAST(FILEPROPERTY(s.name, ''SpaceUsed'') AS float)* CONVERT(float, 8) AS [UsedSpace],
(s.size * @PageSize) - CAST(FILEPROPERTY(s.name, ''SpaceUsed'') AS float) * CONVERT(float, 8) AS [FreeSpace],
CAST(s.fileid AS int) AS [ID]
FROM sysfiles AS s'

SELECT * FROM #dbStats"
Write-DataTable -ServerInstance "MONITOR" –Database ServerStats –TableName DBStats -Data $dt
}

And that’s just about it. I’ve got a SQL Agent job on the monitoring server that runs both PowerShell scripts once a week. The nice thing, at least so far, is that I can run these scripts against different SQL servers and OS versions. My list has servers running Windows 2000 to 2008 R2, and SQL instances in the same time frame. There’s even a few Express editions in there. My next step will be to create an Excel spreadsheet to view the data. I’m thinking PowerView for that.

I’d be interested in hearing how you work with SQL and PowerShell. Just leave a comment. Or let me know how I can improve my scripts.

Links and Downloads

• Get the SQL Extensions for PowerShell from Codeplex.
• Invoke-sqlcmd2, Write-DataTable, and Out-DataTable were also written by Chad Miller (Blog | Twitter) and are available on the TechNet site. All these modules are well documented.
• There are a lot of good sites that can get you started with PowerShell. A simple Google search will turn up plenty. But start with Hey Scripting Guy! Also follow Ed Wilson and his Scripting Wife Teresa on Twitter
• One of my favorites sites is Allen White’s (Blog | Twitter). Allen also presents at numerous SQLSaturday events. Find one near you where he’s speaking. For that matter, SQLSaturdays usually have at least one session dedicated to PowerShell.
• Another good PowerShell site is is the Stairway to PowerShell series on SQLServerCentral, written by Ben Miller.

Just about the time I was wrapping up my earlier posts on SQL Server Data Tools the SSDT team announced that new versions and new functionality were available. So, sooner than I thought, here’s a little more on the September 2012 SSDT.

• If you installed SSDT from the SQL 2012 or VS 2012 media, or from a web download before 9/25/2012, you’ve got the old version of SSDT. That version opened the VS2010 shell, even if you had VS 2012 installed, one of the biggest complaints about SSDT that I’ve heard. But now you have a choice. You can download either the VS 2012 OR VS 2010 version. The new version will upgrade the existing one, you don’t have to uninstall it first. And if you’re installing the VS 2010 version it will also install SP1. But if the computer you’re installing on already has VS 2010 you need make sure SP1 is there before continuing with SSDT.
• One of the biggest changes to SSDT was in how use the databases you create in database projects. Before the update you’d see a new instance for every project you created. Talk about server sprawl! Now you get two instances. The first instance is (localdb)\V11.0 and this is the SQL Express LocalDB instance. You can download and install this separately from SSDT, just go to the SQL Server 2012 Express page to get it. The second instance is called (localdb)\Projects. All the databases you create in SSDT projects will run from this instance. Much cleaner from an organizational standpoint.
• One of my biggest disappointments about SSDT is that it doesn’t include the Business Intelligence templates. If you need to create SSIS packages or SSRS reports you’ll still need Visual Studio. And I really wish they’d bring back the Data Generator tool. I’m keeping a full VS 2010 version around just for the “Data Dude” features that haven’t made it over to SSDT yet.

Links

• A good source of info on SSDT is available on the SQL Server Data Tools Team Blog. This is where they announce the availability of upgrades. You’ll also find links to the September 2012 SSDT upgrades for VS 2012 and VS 2010.

If you just want the LocalDB database engine go to the Microsoft SQL Server 2012 Express page.

Just about the time I was wrapping up my earlier posts on SQL Server Data Tools the SSDT team announced that new versions and new functionality were available. So, sooner than I thought, here’s a little more on the September 2012 SSDT.

• If you installed SSDT from the SQL 2012 or VS 2012 media, or from a web download before 9/25/2012, you’ve got the old version of SSDT. That version opened the VS2010 shell, even if you had VS 2012 installed, one of the biggest complaints about SSDT that I’ve heard. But now you have a choice. You can download either the VS 2012 OR VS 2010 version. The new version will upgrade the existing one, you don’t have to uninstall it first. And if you’re installing the VS 2010 version it will also install SP1. But if the computer you’re installing on already has VS 2010 you need make sure SP1 is there before continuing with SSDT.
• One of the biggest changes to SSDT was in how use the databases you create in database projects. Before the update you’d see a new instance for every project you created. Talk about server sprawl! Now you get two instances. The first instance is (localdb)\V11.0 and this is the SQL Express LocalDB instance. You can download and install this separately from SSDT, just go to the SQL Server 2012 Express page to get it. The second instance is called (localdb)\Projects. All the databases you create in SSDT projects will run from this instance. Much cleaner from an organizational standpoint.
• One of my biggest disappointments about SSDT is that it doesn’t include the Business Intelligence templates. If you need to create SSIS packages or SSRS reports you’ll still need Visual Studio. And I really wish they’d bring back the Data Generator tool. I’m keeping a full VS 2010 version around just for the “Data Dude” features that haven’t made it over to SSDT yet.

Links

• A good source of info on SSDT is available on the SQL Server Data Tools Team Blog. This is where they announce the availability of upgrades. You’ll also find links to the September 2012 SSDT upgrades for VS 2012 and VS 2010.

If you just want the LocalDB database engine go to the Microsoft SQL Server 2012 Express page.

This post will wrap up my introduction to SQL Server Data Tools (SSDT). In my first post I wrote about my using SSDT to set up a developer sandbox of a SQL database. My second post covered using SSDT to build a prototype database using LocalDB instead of a SQL instance. I added a third post to clear up some facts I didn’t cover correctly, about LocalDB. Today I’m going to show what the LocalDB looks like, as well as a little more on those .dacpac files created when you take a snapshot of your project.

When you create your first SQL database project SSDT will start SQL but not as a service. You can verify this by opening Task Manager and looking at the running processes. You should see a process sqlservr.exe running under your name. If you’re also running a SQL instance you’ll see a second sqlservr.exe process running under whatever the startup account is. Notice that the location of the executable is C:\Program Files\Microsoft SQL Server\110\LocalDB\Binn.

Sqlservr.exe will also create a new set of system databases for your new project. You’ll find these at C:\Users\your name\AppData\Local\Microsoft\Microsoft SQL Server LocalDB\Instances. You’ll see an instance for every SQL Database project you create.  Confirm this by querying sysfiles. Open that location and you’ll also see the error logs and blackbox traces you’d normally see in SQL instances. Open either to see how your instance is behaving.

Open the location where you saved your SSDT project. You should see a folder called  Sandbox. Inside you’ll see a .mdf and .ldf file for your database. You can detach it and reattach it, or backup and restore it, to another server when you’re ready to move it.

There’s a few things about the snapshots I’d like to mention. There’s other ways to use then instead of just with Schema Compare. You can pass it the latest .dacpac file from your project to another developer who can create a new project based on your snapshot. You can open .dacpacs created in one version of Visual Studio in another; 2012 to 2010 for example. And you can also open it to see what it contains.

A .dacpac file is like a compressed folder. If you open it you can extract the files to a new directory. Inside you’ll see some .xml files with configuration information; database settings for example. You’ll also see one model.sql file that has a create object for all objects in your database. There’s no INSERT statements, though.

Finally, the SSDT team announced an update last Friday. There’s some new stuff there, like opening VS 2012 and only creating one instance on LocalDB instead of one for each project. Check it out on their blog. And Google/Bing for SSDT, there’s a lot of good information out there.

I’m still putting my final post on SSDT together but I need to clarify a few things that I wrote in my Introduction first. I stated that you couldn’t download SQL LocalDB, that it came with SSDT, which you could download. That is not true. With the release of SQL 2012 Microsoft has added the LocalDB as an Express Edition. The documentation says that it is a lightweight, fast, zero configuration database that runs in user-mode. It was specifically designed for developers. I’ll test LocalDB but I have to build an environment for it first.

Also there are other ways of installing SSDT. I mentioned that you can download it from Microsoft, and I said how it’s a feature when installing SQL Server 2012. I just realized it’s also an option when installing Visual Studio 2012.

Finally, the SSDT Team announced that they are releasing an update for both Visual Studio 2010 and Visual Studio 2012, on September 14th. You can read more here.

You can read more about LocalDB on Books On-Line. And you can download it here.

Sorry for any confusion.

In my last post I wrote about some of the functionality of the new SQL Server Data Tools (SSDT), the replacement for Business Intelligence Development Studio (BIDS). Today I’m going to look at a few things I didn’t cover before.

Today’s first point is that you’re not tied to Visual Studio 2010, you can also use the new 2012 version if you prefer. SSDT will still open VS 2010 even if both are installed. To use VS 2012 you’ll have to open it manually. But everything else is there. Today’s demos will be in VS 2012.

In my last post I created a new local database based on an existing database attached to my local instance. I also said that wasn’t the only way to create a localdb. Back in VS (either version) create a new project, choose Other Languages > SQL Server > SQL Server Data Project. After VS finishes creating the project you’ll see the LocalDB running in the SQL Object Explorer with a database that has the same name as your just created project. You might see other databases if you’ve created other database projects, and you might also see a second LocalDB if you created any in a different VS version. You can always disconnect any server if it becomes too distracting.

While LocalDB is not a real SQL instance you still can control the properties of the server and any database you create. To see the server properties right click on LocalDB and choose Properties. The properties will open, though the properties here seem mostly to be read only. But you can use sp_configure to change them. For the database properties right click on the project name in the Solution Explorer and choose Properties. The project page opens as in the screenshot below. You can change the Target Option to any SQL 2005, 2008, 2012, or Azure. You can change database settings, like the collate option, by clicking the Database Settings button.

Now you need to create your database objects, and again there’s a few ways you can go. You can import an existing schema from another database,  a .dacpac snapshot created by another project, or a .sql script. These options would come in handy if you want to create a local sandbox with a copy of a production database. You can grab the scripts from your source control (you ARE using source control, right?), or maybe there’s a .dacpac file that can be shared among developers. To import  right click the project name in the Solution Explorer, chose Import, then the import method.

But maybe you’re building a demo database from scratch and you don’t have any scripts created yet. You can build the objects inside the project. Again right click the project name and this time select Add, then the type of object you’re going to create. In my case I’m going to create a table; creating other objects are similar.

When you create the table you’ll give it a name. After the dialog screen closes the table designer will open in a new window. The window is split, with the T-SQL script in one half and the visual designer n another. You can swap them left to right, top to bottom, whatever you’re comfortable with. By default it will create an Id column as the primary key but you can change that. Go ahead and add columns to your table, either in the T-SQL side or the designer side. The cool thing is that, wherever you make your change, it’s made automagically on the other side.

You can add indexes, keys, constraints, and triggers by right clicking the object type then Add New. Build and deploy your new objects by pressing F5.

I’ll have one more post on using SSDT projects.