Category Archives: sql server

Transactional replication – how to initialize a subscription from a backup

Today I had a chance to initialize my scubscription in transactional replication from backup. The database is quite big (120GB) and I thought it I would be better to use this method to start the replication process.

Unfortunatelly this is not possible from the replication wizard and you have to change the publication and subscription definition manually.

Here are the steps – if you work with new replication:

1) Create a publication script and update manually the  sp_addpublication command by changing the @allow_initialize_from_backup parameter  to “true”. In my case it was set to “automatic” as I used the wizard to generate the script. I think that it is usefull to set the other parameter: @immediate_sync to “true”, too. 

2) Run the script on the publisher server and then create a backup of the database 

3) Restore the database on the subscriber.

4) Go back to the publisher and create a subscription  script but dont run it. Look into it and add these parameters to the sp_addsubscription procedure:

@sync_type = “initialize with backup” 

@backupdevicetype = “disk”  (you could add “tape” or “logical” apart from “disk”)

@backupdevicename = “path to the backup”

when you yse the logical backup device then put the name od this device.

 

I know that most of you know that a replication can be started using that technique. I worte this blog post in order to not forget about that “feature” in  the future

Cheers

 

 

 

 

 

Code Navigation (10 days of SSDT – Day 10)

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server databases nearly every day since then. In that time I’ve learnt a lot about how to make best use of the tool and I want to share that experience in a new series of blog posts called “10 days of SSDT”. I shall be publishing a different blog post every day for 10 days, each day revealing something that you may not know about SSDT. I hope you enjoy the series and contribute via the comments!

One of the aspects of T-SQL development that really grates on me is that the features of both the language and tooling are light years behind that of our brethren that write in “higher order” languages like C#, VB, Java etc… One of the tooling features that folks who live in Visual Studio take for granted is code navigation, happily that feature is now available in Visual Studio for T-SQL developers when one installs SSDT.

Be aware that SSDT’s code navigation features only work when you are working offline using SSDT projects.

There are only two keyboard shortcuts you need to learn to master code navigation (and please do use keyboard shortcuts, don’t wimp out and use the mouse):

  • F12
  • Shift+F12

Go To Definition

Go To Definition allows you to position your cursor inside a reference to an object, press F12, and SSDT will show you the definition of that object. Sounds simple, and it is, but its wonderfully useful when you get into the swing of using it. The following screenshots are an attempt to convey it.

Notice here the cursor is positioned inside a reference to a table called [datawarehouse].[DimCurrency]:

SNAGHTML1325ff31

Simply pressing F12 at that point opens the definition of that table:

SNAGHTML1327b945

Find All References

Find All References does the opposite of Go To Definition, it shows everywhere that an object is referenced. If you’ve ever been afraid to remove an object from a database because you didn’t know what was using it (let’s face it, we’ve all been there) then this feature can be a lifesaver.

Take the previous example of table [datawarehouse].[DimCurrency]. If I again position the cursor inside a reference to that object and this time press Shift+F12 SSDT will search through my database model and present a list of all the objects where it is referred to:

SNAGHTML13318b6e

In the screenshot immediately above we see that this table is referred to 51 times. Those references may be in stored procedures, views, functions, triggers, constraints or extended properties. Double-clicking on any of the entries in that list takes you directly to the definition of the referring object.

Summary

That really is it. Two very simple features but ones which can have a massively positive effect on your productivity.

Hopefully this goes without saying but code navigation does not work if you are referencing objects using dynamic SQL, this is because SSDT sees that dynamic SQL string as just that, a string, rather than a reference to another object in the model. Probably just bear that in mind.

Code navigation is top of the list of features that drive my preference for developing in offline SSDT rather than online in SSMS.

@Jamiet 

If you want to learn more about SSDT then come along to my training course Introduction to SQL Server Data Tools in association with Technitrain on 23rd/24th February 2015 in London. There is a discount if you register before the end of 2014.

Earlier posts in this series:

Repost for 2014: Come to the PASS Summit

I originally posted this five years ago. It’s about my first PASS Summit ten years ago. I’ve made a couple edits and added some links. I hope it will encourage you to attend the PASS Summit 2014. :{> "What’s the big deal about the PASS Summit, Andy?" Allow me tell my story (again): "Hello. My name is Andy Leonard, and I am an accidental database professional." ("Hi Andy.") I jumped the fence from applications developer to database person in 2003. I thought I understood…(read more)

Declarative, Model Driven Development (10 days of SSDT – day 9)

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server databases nearly every day since then. In that time I’ve learnt a lot about how to make best use of the tool and I want to share that experience in a new series of blog posts called “10 days of SSDT”. I shall be publishing a different blog post every day for 10 days, each day revealing something that you may not know about SSDT. I hope you enjoy the series and contribute via the comments!

I’ve used the word “model” a few times in this series already without expanding on it too much but its worth doing because its actually fundamental to how SSDT works, a model underpins every that is done in SSDT.

In the context of SSDT a model is a representation of a database, pure and simple. In SSDT that model ostensibly exists in two places, in memory when one has an SSDT project open or one is working in connected development mode, and in a .dacpac file which gets produced when an SSDT project gets built. It may help to think of a .dacpac as a representation of the model, stored in a file. (If you want to learn more about dacpacs specifically refer to my January 2014 blog post Dacpac braindump – What is a dacpac?)

To illustrate the point if one takes a look inside a .dacpac (which is actually just a zip file with a different extension) one will notice a file called model.xml:

image

Open that up and you will see that its just a definition of lots of database objects represented as XML:

SNAGHTML8c747a6

There is an important point to make here. The model only states the definition of an object as it has been defined, it does not state how it should go about creating that object (there are no ALTER TABLE statement here for example). This notion of only defining the structure of an object, not how to create it, is known as a declarative approach to database development. One is declaring the intended state of a database, nothing else. The beauty of SSDT is that the tool can take your model and from that work out what it needs to do to get a given physical database into the same state as declared in the model. That is the fundamental underpinning of SSDT and is an important concept to grasp if one intends to use SSDT.

@Jamiet 

If you want to learn more about SSDT then come along to my training course Introduction to SQL Server Data Tools in association with Technitrain on 23rd/24th February 2015 in London. There is a discount if you apply register before the end of 2014.

Earlier posts in this series:

PASS SQL Saturday #356 Slovenia Pre-Conference Seminars

I am proud and glad I can announce two top pre-conference seminars at the PASS SQL Saturday #356 Slovenia conference. The speakers and the seminars titles are:

Both seminars will take place on Friday, December 12th, in the classrooms of our sponsor Kompas Xnet. The price for a seminar is € 149, with early bird price at € 119. Early bird price is valid until October 31st.

I am also using this opportunity to explain how and why we decided for these two seminars. The decision was made by the conference organizers, Matija Lah, Mladen Prajdič, and Dejan Sarka. There was a lot of discussion in different social networks about PASS Summit pre-conference seminars lately. If you have any objections for our seminars, please do not start big discussions in public; please tell them to the three of us directly.

First of all, unlike at the PASS Summit seminars, the speakers are not going to earn big money here, and therefore it is not really worth spending much time and energy on our decision. We think that any of the speakers who sent proposals for our SQL Saturday could present a top quality seminar. We would like to enable seminars for every speaker that wants to deliver one. However, in a small country, we will have already hard time to fill up the two seminar we have currently. Our intention is to reimburse at least part of the money the speakers spent on their own for travelling expenses and accommodation. In our opinion, it makes sense to do this for the speakers that spent the most for the travelling. Coming here from USA is expensive, and it also takes three days in both directions. That’s why we decided to organize the seminars for the first two speakers from USA.

Of course, this is not the last event. If everything goes well with SQL Saturday #356 and with the seminars, we will definitely try to organize more events in the future, and invite more speaker to deliver a seminar as well.

Thank you for understanding!

Connected development (10 days of SSDT – Day 8)

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server databases nearly every day since then. In that time I’ve learnt a lot about how to make best use of the tool and I want to share that experience in a new series of blog posts called “10 days of SSDT”. I shall be publishing a different blog post every day for 10 days, each day revealing something that you may not know about SSDT. I hope you enjoy the series and contribute via the comments!

In Day 1 – Online and Offline I explained how SSDT could be used to query and affect existing databases:

I can browse through the objects on those servers and interrogate them just as I would in SSMS. Within SSDT/SSOX however there is one important difference, when I make changes to the database I am not affecting the database directly, I am affecting an in-memory model of the database. This approach has two important advantages,

  • I can view the impact of a change (e.g. if I change a column name will it break any views that reference it) without actually making the change
  • I can make many changes to the model and then push them into my actual database en masse and SSDT will work out the best way of doing that. I don’t have to write the code that deploys my code.

These capabilities are worth exploring in more detail which I’ll do by comparing the schema editing experience in SSMS & SSDT. Here I show that I have a copy of venerable old Northwind which I’m going to use to demo this:

image

Let’s say, hypothetically, I want to change the name of the CustomerID field; SSMS has a UI that enables me to do this and when it does so it will alter the definition of all the affected objects such as foreign keys that reference that column. That’s quite useful however it does somewhat shield you, the developer, from knowing the intricacies of what SSMS is doing under the covers. In SSDT this scenario is a little different, we browse to the table in SQL Server Object Explorer (SSOX – its one of the integral components to SSDT) and select ‘View Designer’:

image

This launches us into SSDT’s Table Designer (which we have seen before in Day 4 – Table Designer) where we can go ahead and make our change:

image

Let’s explain what’s going on here. We’ve changed the name of a column and before we’ve actually saved our change SSDT shows us which objects will be impacted by that change. For example, the first error in that list is:

Computed Column: [dbo].[Quarterly Orders].[CustomerID] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[Customers].[CustomerID], [dbo].[Customers].[Customers]::[CustomerID] or [dbo].[Orders].[Customers]::[CustomerID].

Double clicking on that error opens up the definition of the affected object and gives us a red squiggly indicating exactly where the error is:

image

That’s two reasons why I like the SSDT way, (1) it shows you the implications of your change as you type rather than after you try and commit the change and (2) you can jump straight to affected objects so you can change them, plus you get nice red squigglies and intellisense too:

image

The third, and what I think of as the best, benefit though can’t easily be demonstrated with screenshots; that is, when I make any changes I’m not actually making changes to the physical database. In actuality SSDT has built an offline model of the database underneath the covers and it is that model to which I am making changes. I can go on making changes (probably by double clicking on all the errors in the error list) and each one of those changes is made to the offline model, not to the physical database.

Eventually I will reach a state where all the errors have been solved and I am ready to push all the model changes back to the actual database. To do that I simply hit the Update button that will appear at the top of each edited DDL script:

image

and when I do so a dialog appears with a pseudo code description of all the changes that I have made:

image

(Note that this is the same pseudo code that we talked about previously in Day 7 – Data Tools Operations Window)

I can hit the Generate Script button to build a SQL script that will make all those changes for me or simply hit Update Database to push all the changes up.

In summary, SSDT allows you to queue up a series of changes to a database by affecting an offline model rather than the database itself. If you’re making a simple change that doesn’t affect anything else then the benefit here is negligible but if your changes are more substantial than that then this can be a really useful feature.

The last note on this feature is that when SSDT was first released this feature was known as PowerBuffer although I haven’t heard that word mentioned much (if ever) since then. The only reason I mention it here is so that if you hear the word you will know what is being referred to. If you are interested there is useful PowerBuffer documentation on MSDN: How to: Update a Connected Database with Power Buffer.

@Jamiet

If you want to learn more about SSDT then come along to my training course Introduction to SQL Server Data Tools in association with Technitrain on 23rd/24th February 2015 in London. There is a discount if you register before the end of 2014.

Earlier posts in this series:

Data Tools Operations Window (10 days of SSDT – Day 7)

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server databases nearly every day since then. In that time I’ve learnt a lot about how to make best use of the tool and I want to share that experience in a new series of blog posts called “10 days of SSDT”. I shall be publishing a different blog post every day for 10 days, each day revealing something that you may not know about SSDT. I hope you enjoy the series and contribute via the comments!

SSDT provides feedback on the actions that it is undertaking via the Data Tools Operations window. This is a window that sits within SSDT’s Visual Studio shell and looks something like this:

image

Whenever SSDT publishes an SSDT project (in the offline development scenario) or makes changes to an existing database (in the connected development scenario) progress will be reported in the Data Tools Operations window. For each operation reported in the Data Tools Operations window you have the ability to view:

  • Preview – A  description of the changes that SSDT is intending to make to the target database
  • Script – The T-SQL script that SSDT builds
  • Results – Essentially the output from running the aforementioned script

Here is a typical screenshot of the Preview:

image

As you can it provides a readable, pseudo-code description of what actions SSDT is going to take.

The Data Tools Operations window is a very useful addition to SSDT and its worth highlighting it in this dedicated blog post so that people are aware of its capabilities and, importantly, that it even exists.

If you want to learn more about SSDT then come along to my training course Introduction to SQL Server Data Tools in association with Technitrain on 23rd/24th February 2015 in London. There is a discount if you register before the end of 2014.

Earlier posts in this series:

24 Hours of PASS (September 2014): Recordings Now Available!

Sessions of the event 24 Hours of PASS: Summit Preview Edition (which was held on last September 9th) were recorded and now they are available for online streaming!

If you have missed one session in particular or the entire event, you can view it or review your preferred sessions; you can find all details here.

What could you aspect from the next PASS Summit? Find it out on recorded sessions of this edition of 24 Hours of PASS.

SQL Server Object Explorer (10 days of SSDT – Day 6)

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server databases nearly every day since then. In that time I’ve learnt a lot about how to make best use of the tool and I want to share that experience in a new series of blog posts called “10 days of SSDT”. I shall be publishing a different blog post every day for 10 days, each day revealing something that you may not know about SSDT. I hope you enjoy the series and contribute via the comments!

SQL Server Object Explorer (SSOX) is a crucial feature of SSDT and is a window that sits within SSDT’s Visual Studio shell; it is made visible from from the View menu or by using the keyboard shortcut CTRL+, CTRL+S.

image

As a SQL Server developer that chooses to use SSDT I spend most of my time within SSOX. Think if it as a gateway to both your offline database source code and your physical deployed databases.

In Day 1 – Online and offline I said

SSDT supports two distinct ways of working, online and offline. Those two distinct usage scenarios are most obvious with SQL Server Object Explorer

This is demonstrated by the existence of two top-level nodes within SSOX, they are called:

  • SQL Server
  • Projects

You can see both in the screenshot above. The SQL Server node is a list of SQL Server instances that you have registered within SSOX (in the screenshot above I have registered one instance called “(localdb)Projects”) which you can then expand thus allowing you to browse all the databases and server level objects upon that instance; an easy way to think of this SQL Server node is as a hybrid of the Registered Servers and Object Explorer windows in SQL Server Management Studio (SSMS). You can register any edition of SQL Server (Express through to Enterprise) and also Windows Azure SQL Database (WASD) instances too.

The Projects node of SSMS provides a logical view over SSDT projects that you have open within the Solution Explorer window, I personally am a big fan of SSDT projects (see Day 3 – What is a Projects) so this Projects node is where I spend most of my time. The screenshot below depicts the interaction between Solution Explorer and SSOX:

image

Notice there is a project in Solution Explorer called Day04 – Table Designer and there is a node under Projects within SSOX of the same name (highlighted by the red arrow). That project has two files called Product.table.sql & Product.PrimaryKey.sql, both of which are open in the centre of the screen (note that SSDT does not stipulate that filenames must reflect the name of the object defined within them, but it is useful if they do). Notice also that table [dbo].[Product] and its primary key both appear in SSOX (depicted with the blue lines) however in there they appear as you might expect them to appear in SSMS. They are displayed in a hierarchical view of the database along with appropriate icons to signify the object type (table, primary key, etc…). This is the power of the Projects node in SSOX, it provides a logical view of the objects defined in the files of the project. If you are so inclined you can even define all of your objects within a single file in Solution Explorer (note I’m not recommending you do that) and SSOX will still show the same logical view over them all.

Displaying a logical view of your databases and database projects is the basic premise of SSOX but it does have a number of other features too. For example there is a button on the SSOX toolbar labelled Group by Schema:

SNAGHTML768d76e

This changes the logical view to make Schemas a high-level node from which you can drill in to find all your database objects contained therein:

SNAGHTML765169d6

If you have a lot of schemas in your database then this can be really useful (I just wish they had an option to hide all the built-in schemas such as db_accessadmin, db_backupoperator etc…). A nice side-effect of grouping by schema is that you can right-click on one of the nodes underneath a schema in order to create a new object and the DDL script for that object will place it into the respective schema rather than the default [dbo].

SSOX has a few other features that can be launched from the right-click menu but I’ll leave investigation of those as an exercise for the reader; we have covered the main features of SSOX herein, if you have any questions please feel free to ask them in the comments section below.

@Jamiet

If you want to learn more about SSDT then come along to my training course Introduction to SQL Server Data Tools in association with Technitrain on 23rd/24th February 2015 in London. There is a discount if you register before the end of 2014.

Earlier posts in this series:

Extended property support in SSDT (10 days of SSDT – Day 5)

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server databases nearly every day since then. In that time I’ve learnt a lot about how to make best use of the tool and I want to share that experience in a new series of blog posts called “10 days of SSDT”. I shall be publishing a different blog post every day for 10 days, each day revealing something that you may not know about SSDT. I hope you enjoy the series and contribute via the comments!

Extended properties are a feature of SQL Server that have existed since SQL Server 2000 although I assume that you probably don’t use them as I rarely see them being used on my travels. Nonetheless extended properties are a useful way of supplying extra information about your database. Most often they are used for documentation purposes however I have also used them to drive workflow in an ETL process (e.g. I toggle the loading of a table by changing one of its extended properties). SQL Server Data Tools (SSDT) includes good support for extended properties and in this blog post I’ll cover what support it provides.

First is SSDT’s table designer. Notice in this screenshot that we have the option to choose what information is displayed per column and that “Description” is one of those chosen columns:

SNAGHTML705b570

If I enter a value into that Description for a column then check out what happens:

SNAGHTML714660f

SSDT creates the DDL that creates an extended property named MS_Description on the column in question. You can create your own extended properties as well of course but unfortunately only extended properties called MS_Description will get surfaced in the UI (and even then only for columns). If you wish to create your own then simply create them as per the code above, using GO to separate them if you have more than one defined in the same script.

One nice aspect to this support is that SSDT treats extended properties as schema rather than data hence if I change the name of the column like so:

image

then an error appears, along with a red squiggly, informing you of an invalid reference in the extended property definition. Note that refactoring is supported for extended properties so if you change the name of a referenced object using refactor rename then the extended property will get updated accordingly.

At publish time SSDT will take care of creating the extended property if it doesn’t exist or modifying it if it does, just like any other schema object. Here’s the output from publishing my project to LocalDB:

image

image

OK, that’s pretty much everything there is to know about extended property support in SSDT. Any questions? Let me know.

@Jamiet

If you want to learn more about SSDT then come along to my training course Introduction to SQL Server Data Tools in association with Technitrain on 23rd/24th February 2015 in London. There is a discount if you register before the end of 2014.

Earlier posts in this series: