Multiple Devices per subscription and SQL-NS API communication

Hi,

I've got two questions, both targeting SQL-NS on SQL Server 2005.

1. What is the best way to handle a subscription that could have more than one device associated with it? For example, a subscription that should result in both an Email and a phone call (via custom delivery channel). I'm attempting to avoid needing to have two separate subscriptions that are identical other than device type. This could be accomplished with a child table, but what's the best method of incorporating this into SQL-NS?

2. How does the SQL-NS API communicate with the SQL Server? Is it simply a regular SQL connection over port 1433?

Thanks,
Peter

Hi,

1: The best way to support this scenario is by have separate subscriptions per device. While this may increase your space requirements, it does give the subscriber greater controller over their subscriptions and how they want to receive their notifications.

2: Yes the SQL-NS API uses a regular SQL connection over port 1433.

Thanks,
Anand|||

Depending on your requirements, you could investigate creating a custom delivery protocol that actually sends notifications via more than one protocol - for example, an email and an SMS notification. However this is almost certainly a bad idea. For instance, think about retry attempts after failures or partial failures.

I'd encourage you to look more into the seperate subscription for each device. You can even manage it behind the scenes for your subscribers. The subscription management app could simply ask for one or more ways to contact them and then it can create the necessary subscriptions.
--
Joe Webb
SQL Server MVP

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)

|||Above you said:
I'd encourage you to look more into the seperate subscription for each device. You can even manage it behind the scenes for your subscribers. The subscription management app could simply ask for one or more ways to contact them and then it can create the necessary subscriptions.
How do you handle keeping them in sync then if the database connection was to go away after you update the first subscription? Without some sort of transaction control this could become a very LARGE headache to maintain.|||

Hi -

Here's a copy of a posting I recently made in the newsgroup....HTH...

If you're using 2005, there are some new views that allow you to create subscribers, devices, and subscription data without going through the API. So you could create a connection to SQL Server and issue something like:

INSERT INTO NSInstance.NSSubscriberView (SubscriberId, Enabled)
VALUES (N'joew@.webbtechsolutions.com', 1)

You could create a transaction object to handle that aspect of it.

If you're using SQL Server NS 2000, then your options are more limited. You could use the API to create the the subscriber, device, and subscriptions and at the conclusion check to make sure everything was created appropriately. If not back out.

This scenario doesn't handle a crashed SQL Server, so you'd probably want to implement some kind of periodic checking of the subscriptions - a scheduled task to kick off periodically to make sure each subscriber has a subscription for each device. Not perfect, but probably as close as you can get without either 2005 or going through the sprocs.

HTH....

--
Joe Webb
SQL Server MVP

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)

|||

Shyam posted a very clever solution for this scenario in the newsgroups yesterday. Here's a link:

http://groups-beta.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/ef7e87acdd261802/01d8b36a6e2e85f9#01d8b36a6e2e85f9

Just wanted to make sure that people in both communities benefited from his insight. Thanks Shyam!

--
Joe Webb
SQL Server MVP

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)

|||I saw that, the orig NG post is mine as well. However, it still doesn't solve the lack of transaction support with in the API, and now I'll be going outside of the API to another table. Trying to keep this all in sync will require use of the stored procedures and a sql connection.

Any chance in the gold release of 2005 the connection can be exposed from within the API so that it can be used to create transactions?|||

Agreed, transactional support via the API is something that would be very useful. According to another forum thread ("Multiple Devices for a Subscription"), MS has this on their wishlist for a future release - post 2005.

In the meantime...If you're willing to wait for 2005 *and* you're talking about a simple event driven subscription, you use the new views to create your subscribers, devices, and subscriptions? Something like:

INSERT NSInstance.dbo.NSSubscriberView(SubscriberId, Enabled)
VALUES ('joew@.webbtechsolutions.com', 1)

INSERT NSInstance.dbo.NSSubscriberDeviceView (SubscriberId, Enabled, DeviceName, DeviceTypeName, DeviceAddress, DeliveryChannelName)
VALUES('joew@.webbtechsolutions.com', 1, 'EmailDevice', 'Email', 'joew@.webbtechsolutions.com', 'EmailChannel')

INSERT NSApplication.dbo.NS<SubscriptionClassName>View (SubscriberId, Enabled, SubscriberDeviceName, SubscriberLocale, PrType)
VALUES ('joew@.webbtechsolutions.com', 1, 'EmailDevice', 'en-us', 1)

You can wrap this up in a transaction or better yet, put them in a stored procedure with transactional support that your Sub Mgt App can call.

Note however that you cannot insert scheduled subscriptions or subscriptions that use condition actions using the NS<SubscriptionClassName>View view.

HTH....

--
Joe Webb
SQL Server MVP

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)

Multiple devices for a subscription

We have a need to send events out to multiple devices for a single subscription, the user may be able to decide what devices with what subscriptions. So far we have the following two ideas for accomplishing this:

1) create a multiple subscriptions, one for each device the user wants the events to go to
2) create a multiple unique device entries for each subscription

So far as I’ve been able to tell there is no transaction support when using the notification services API, so having to update multiple records for a single subscription can lead to further maintenance issues if the updating were to fail part way through.

Is there a simpler way to have events for a single subscription to go out to multiple devices?

Typically you want to separate out subscriber management from subscription management in your application.

In subscriber management you would want the subscribers to configure which devices they have. This typically would happen only once and this list of devices is maintained only once in the instance database/schema. This list of subscribers and their respective devices is common across all applications and maintained only once per instance.

Yes, during subscription management the subscriber would need to create a subscription for each of their devices (your option 1), to ensure the notification is sent to each device.

You correctly cite, there is no transaction support when adding devices or subscriptions. We will evaluate support for for this in next release as we have received this request before in different forms.

Thanks,
Anand

|||

Next release? you mean the 2005 gold release? or next version of SQL server and notification services?

We need the ability to show the user one subscription with multiple devices, so we will handle creating the devices and multiple subscriptions on the back end, and to abtain transaction support we may have to go directly to the stored procedures for the time being.

|||Sorry, I should have been more clear. By next realease I meant the release AFTER SQL Server 2005.|||

Check out this related thread. It links to a posting from Shyam that offers a clever alternative for the current version.

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=72056
--
Joe Webb
SQL Server MVP

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)

Multiple data files in a filegroup

I have a storage subsystem which allows five independant RAID 10 devices. I am creating five filegroups each with 2 data files distributed across the devices in a smart round robin fashion.

I have assigned tables which are commonly joined between the filegroups. For example, Table A is in Filegroup 1 while Table B is in Filegroup 2. The most common and high volume query is the join between these two tables.

My question is this: I understand SQL Server is theaded by data file, not filegroup. But for filegroups which have 2 or more data files, do they both grow evenly? Or once one data file is full, SQL Server then writes all new data to the new data file in the same filegroup.

Hopefully this is not too confusing.

Thanks.

BryanSQL Server will write data to the members of a filegroup evenly, so if you create 2 100MB files in a filegroup, then insert 150MB of data into this filegroup, you will have 75 MB in each. Figuring out what you have after you delete 80 MB of data is virtually impossible, however. Hope this helps.

Multiple backup job schedules for one database to different devices

I want to know if it is possible to create backups on differnet
schedules of the same database? And if not, if there some solution
that can fit what I am trying to do?
I want to backup more aggressively to a locally attached disk, and less
aggressively to a network device. Specifically a virtual device in SQL
server using NetBackup SQL Connectors. For example, can we have a
backup schedule that creates a dull backup of a database everyday, and
have TLog backups every 30 minutes both to local disk. But we also
want to have a seperate schedule for the network device that does a
weekly fully, and say hourly differentials or TLog backups thru the
week. Would the checkpointing done by the BACKUP commands somehow
clobber one another?
It would be like:
Monday:
3:00AM - Full Backup -> Local Disk (daily)
3:30AM - TLog Backup -> Local Disk
4:00AM - Full Backup -> Network Disk (weekly)
4:00AM - TLog Backup -> Local Disk
4:30AM - TLog Backup -> Local Disk
5:00AM - TLog Backup -> Network Disk
5:00AM - TLog Backup -> Local Disk
5:30AM - TLog Backup -> Local Disk
6:00AM - TLog Backup -> Network Disk
6:00AM - TLog Backup -> Local Disk
....
Tuesday:
3:00AM - Full Backup -> Local Disk (daily)
3:30AM - TLog Backup -> Local Disk
4:00AM - TLog Backup -> Network Disk
4:00AM - TLog Backup -> Local Disk
4:30AM - TLog Backup -> Local Disk
5:00AM - TLog Backup -> Network Disk
...
I don't think this will work, but could it? And if not, would a
differential backup work for the Network Disk? The goal is to minimize
bandwidth because we can't backup several hundred gigabytes every
morning to a remote site. But we need near live backups incase we had
a total meltdown at the other site.
Regards,
--
Mike BrancatoHi Mike
You probably don't want to do this, as it would make finding the necessary
files when rolling forward the transaction log more difficult. You may want
to consider the alternative of always backing up to local disc and then
selectively copy to the network drive (all log backups) when required.
To get uneven intervals you can schedule your backup jobs to have multiple
schedules one for each period, although this would not overcome the change of
backup device or backup type.
John
"mike.brancato@.acs-inc.com" wrote:
> I want to know if it is possible to create backups on differnet
> schedules of the same database? And if not, if there some solution
> that can fit what I am trying to do?
> I want to backup more aggressively to a locally attached disk, and less
> aggressively to a network device. Specifically a virtual device in SQL
> server using NetBackup SQL Connectors. For example, can we have a
> backup schedule that creates a dull backup of a database everyday, and
> have TLog backups every 30 minutes both to local disk. But we also
> want to have a seperate schedule for the network device that does a
> weekly fully, and say hourly differentials or TLog backups thru the
> week. Would the checkpointing done by the BACKUP commands somehow
> clobber one another?
> It would be like:
> Monday:
> 3:00AM - Full Backup -> Local Disk (daily)
> 3:30AM - TLog Backup -> Local Disk
> 4:00AM - Full Backup -> Network Disk (weekly)
> 4:00AM - TLog Backup -> Local Disk
> 4:30AM - TLog Backup -> Local Disk
> 5:00AM - TLog Backup -> Network Disk
> 5:00AM - TLog Backup -> Local Disk
> 5:30AM - TLog Backup -> Local Disk
> 6:00AM - TLog Backup -> Network Disk
> 6:00AM - TLog Backup -> Local Disk
> .....
> Tuesday:
> 3:00AM - Full Backup -> Local Disk (daily)
> 3:30AM - TLog Backup -> Local Disk
> 4:00AM - TLog Backup -> Network Disk
> 4:00AM - TLog Backup -> Local Disk
> 4:30AM - TLog Backup -> Local Disk
> 5:00AM - TLog Backup -> Network Disk
> ...
> I don't think this will work, but could it? And if not, would a
> differential backup work for the Network Disk? The goal is to minimize
> bandwidth because we can't backup several hundred gigabytes every
> morning to a remote site. But we need near live backups incase we had
> a total meltdown at the other site.
> Regards,
> --
> Mike Brancato
>|||The problem with copying the local files is that the SQL backup process
may take a while and hold a lock on the large databse files. also, the
local backups are several hundred gigs every day, but we would like to
backup remotely only Tlogs, etc thru the week becasue we cannot handle
that much bacndwidth from our servers every day.
Is there any way of doing this?
John Bell wrote:
> Hi Mike
> You probably don't want to do this, as it would make finding the necessary
> files when rolling forward the transaction log more difficult. You may want
> to consider the alternative of always backing up to local disc and then
> selectively copy to the network drive (all log backups) when required.
> To get uneven intervals you can schedule your backup jobs to have multiple
> schedules one for each period, although this would not overcome the change of
> backup device or backup type.
> John
> "mike.brancato@.acs-inc.com" wrote:
> > I want to know if it is possible to create backups on differnet
> > schedules of the same database? And if not, if there some solution
> > that can fit what I am trying to do?
> >
> > I want to backup more aggressively to a locally attached disk, and less
> > aggressively to a network device. Specifically a virtual device in SQL
> > server using NetBackup SQL Connectors. For example, can we have a
> > backup schedule that creates a dull backup of a database everyday, and
> > have TLog backups every 30 minutes both to local disk. But we also
> > want to have a seperate schedule for the network device that does a
> > weekly fully, and say hourly differentials or TLog backups thru the
> > week. Would the checkpointing done by the BACKUP commands somehow
> > clobber one another?
> >
> > It would be like:
> >
> > Monday:
> > 3:00AM - Full Backup -> Local Disk (daily)
> > 3:30AM - TLog Backup -> Local Disk
> > 4:00AM - Full Backup -> Network Disk (weekly)
> > 4:00AM - TLog Backup -> Local Disk
> > 4:30AM - TLog Backup -> Local Disk
> > 5:00AM - TLog Backup -> Network Disk
> > 5:00AM - TLog Backup -> Local Disk
> > 5:30AM - TLog Backup -> Local Disk
> > 6:00AM - TLog Backup -> Network Disk
> > 6:00AM - TLog Backup -> Local Disk
> > .....
> > Tuesday:
> > 3:00AM - Full Backup -> Local Disk (daily)
> > 3:30AM - TLog Backup -> Local Disk
> > 4:00AM - TLog Backup -> Network Disk
> > 4:00AM - TLog Backup -> Local Disk
> > 4:30AM - TLog Backup -> Local Disk
> > 5:00AM - TLog Backup -> Network Disk
> > ...
> >
> > I don't think this will work, but could it? And if not, would a
> > differential backup work for the Network Disk? The goal is to minimize
> > bandwidth because we can't backup several hundred gigabytes every
> > morning to a remote site. But we need near live backups incase we had
> > a total meltdown at the other site.
> >
> > Regards,
> >
> > --
> > Mike Brancato
> >
> >|||Hi
With multiple jobs and schedules you can do just about anything. If you have
a highspeed dedicated lan between the backup server and your database server
it should not cause too much of a problem. Without trying it you will not
know!
If you have a dedicated backup server with high speed dedicated lan it may
be better to do everything to the backup server rather than the local server!
You database will not be "locked" while you are backing up.
SAN technologies have methods that can move large volumes of data instantly,
if you are looking at significant amounts of data it would probably worth
investigating this option.
John
"mike.brancato@.acs-inc.com" wrote:
> The problem with copying the local files is that the SQL backup process
> may take a while and hold a lock on the large databse files. also, the
> local backups are several hundred gigs every day, but we would like to
> backup remotely only Tlogs, etc thru the week becasue we cannot handle
> that much bacndwidth from our servers every day.
> Is there any way of doing this?
> John Bell wrote:
> > Hi Mike
> >
> > You probably don't want to do this, as it would make finding the necessary
> > files when rolling forward the transaction log more difficult. You may want
> > to consider the alternative of always backing up to local disc and then
> > selectively copy to the network drive (all log backups) when required.
> >
> > To get uneven intervals you can schedule your backup jobs to have multiple
> > schedules one for each period, although this would not overcome the change of
> > backup device or backup type.
> >
> > John
> >
> > "mike.brancato@.acs-inc.com" wrote:
> >
> > > I want to know if it is possible to create backups on differnet
> > > schedules of the same database? And if not, if there some solution
> > > that can fit what I am trying to do?
> > >
> > > I want to backup more aggressively to a locally attached disk, and less
> > > aggressively to a network device. Specifically a virtual device in SQL
> > > server using NetBackup SQL Connectors. For example, can we have a
> > > backup schedule that creates a dull backup of a database everyday, and
> > > have TLog backups every 30 minutes both to local disk. But we also
> > > want to have a seperate schedule for the network device that does a
> > > weekly fully, and say hourly differentials or TLog backups thru the
> > > week. Would the checkpointing done by the BACKUP commands somehow
> > > clobber one another?
> > >
> > > It would be like:
> > >
> > > Monday:
> > > 3:00AM - Full Backup -> Local Disk (daily)
> > > 3:30AM - TLog Backup -> Local Disk
> > > 4:00AM - Full Backup -> Network Disk (weekly)
> > > 4:00AM - TLog Backup -> Local Disk
> > > 4:30AM - TLog Backup -> Local Disk
> > > 5:00AM - TLog Backup -> Network Disk
> > > 5:00AM - TLog Backup -> Local Disk
> > > 5:30AM - TLog Backup -> Local Disk
> > > 6:00AM - TLog Backup -> Network Disk
> > > 6:00AM - TLog Backup -> Local Disk
> > > .....
> > > Tuesday:
> > > 3:00AM - Full Backup -> Local Disk (daily)
> > > 3:30AM - TLog Backup -> Local Disk
> > > 4:00AM - TLog Backup -> Network Disk
> > > 4:00AM - TLog Backup -> Local Disk
> > > 4:30AM - TLog Backup -> Local Disk
> > > 5:00AM - TLog Backup -> Network Disk
> > > ...
> > >
> > > I don't think this will work, but could it? And if not, would a
> > > differential backup work for the Network Disk? The goal is to minimize
> > > bandwidth because we can't backup several hundred gigabytes every
> > > morning to a remote site. But we need near live backups incase we had
> > > a total meltdown at the other site.
> > >
> > > Regards,
> > >
> > > --
> > > Mike Brancato
> > >
> > >
>