OLTP: (ERP, TX system, Client Server Architecture, Desktop application)
OLAP: (Data warehouse application -- MOLAP, ROLAP, HOLAP)
Difference:
OLTP
Current data
Short database transactions
Online update/insert/delete
Normalization is promoted
High volume transactions
Transaction recovery is necessary
Less Index
More Joins
Adopts an entity relationship(ER)
Customer-oriented, used for data analysis and querying by clerks, clients and IT professionals
OLAP
Current and historical data
Long database transactions
Batch update/insert/delete
Denormalization is promoted
Low volume transactions
Transaction recovery is not necessary
More Index
Less Joins
Adopts star, snowflake or fact constellation model and a subject-oriented database design
Market-oriented, used for data analysis by knowledge workers( managers, executives, analysis)
--OR--
Online transactional processing (OLTP) is designed to efficiently process high volumes of transactions, instantly recording business events (such as a sales invoice payment) and reflecting changes as they occur.
Online analytical processing (OLAP) is designed for analysis and decision support, allowing exploration of often hidden relationships in large amounts of data by providing unlimited views of multiple relationships at any cross-section of defined business dimensions.
--OR--
OLTP System (Operational System):
Operational data; OLTPs are the original source of the data.
To control and run fundamental business tasks
Reveals a snapshot of ongoing business processes
Short and fast inserts and updates initiated by end users
Relatively standardized and simple queries Returning relatively few records
Typically very fast
Can be relatively small if historical data is archived
Highly normalized with many tables
Backup religiously; operational data is critical to run the business, data loss is likely to entail significant monetary loss and legal liability
OLAP System (Data Warehouse):
Consolidation data; OLAP data comes from the various OLTP Databases
To help with planning, problem solving, and decision support
Multi-dimensional views of various kinds of business activities
Periodic long-running batch jobs refresh the data
Often complex queries involving aggregations
Depends on the amount of data involved; batch data refreshes and complex queries may take many hours; query speed can be improved by creating indexes
Larger due to the existence of aggregation structures and history data; requires more indexes than OLTP
Typically de-normalized with fewer tables; use of star and/or snowflake schemas
Instead of regular backups, some environments may consider simply reloading the OLTP data as a recovery method
--OR--
OLTP:
Current data
Short database transactions
Online update/insert/delete
Normalization is promoted
High volume transactions
Transaction recovery is necessary
OLAP:
Current and historical data
Long database transactions
Batch update/insert/delete
Denormalization is promoted
Low volume transactions
Transaction recovery is not necessary
Tuesday, November 2, 2010
Thursday, October 28, 2010
How to set SSRS Subscriptions
Reports can be pulled from the Report Manager by finding the report the user wants to have. The way to deliver reports at a specific time, in a specific format to the user is to use subscriptions. A subscription is a standing request to deliver a report at a specific time or in response to an event, and then to have that report presented in a way that you define. Subscriptions provide an alternative to running a report on demand. On-demand reporting requires that you actively select the report each time you want to view the report. In contrast, subscriptions can be used to schedule and then automate the delivery of a report.
Report subscriptions are processed on the Report Server. They reach end users through delivery extensions that are deployed on the Report Server.
There are two kinds of subscriptions:
• Standard /Event-Driven Subscriptions
• Data-Driven Subscriptions
Standard subscriptions are created and managed by individual users. A standard subscription consists of static values that cannot be varied during subscription processing.
Data-driven subscriptions are dynamic in that the presentation, delivery, and parameter values are retrieved at run time from a data source. You might use data-driven subscriptions if you have a very large recipient list or if you want to vary report output for each recipient. To use data-driven subscriptions, you must have expertise in building queries and an understanding of how parameters are used. Report server administrators typically create and manage these subscriptions
Subscriptions use delivery extensions to determine how to distribute a report and in what format. When a user creates a subscription, he or she can choose one of the available delivery extensions to determine how the report is delivered. Reporting Services includes support for e-mail delivery and delivery to a file share. Developers can create additional delivery extensions to route reports to other locations. Another delivery method is called the null delivery provider. This method is not available to users. Null delivery is used by administrators to improve report server performance by preloading the cache. Subscription information is stored with individual reports in a report server database. You cannot manage subscriptions separately from the report to which they are associated.
E-Mail Delivery in Reporting Services:
Reporting Services includes an e-mail delivery extension that provides a way to e-mail a report to individual users or groups. The e-mail delivery extension is configured through the Reporting Services Configuration Manager tool and by editing the Reporting Services configuration files.
Select the Email Settings--> Sender Address, Current SMTP Delivery Method and SMTP Server.
To distribute or receive a report by e-mail, you define either a standard subscription or a data-driven subscription.
Note: You can subscribe to or distribute only one report at a time. You cannot create a subscription that delivers multiple reports in a single e-mail message. You click the “New Subscription” button, ready to go add an email address.
An E-Mail will be generated and sent to the email addresses found in the recipients’ field.
To:, Cc: and Bcc: – fairly standard fields. You can enter the alias if you’re sending to an internal address, and must type the full email address if sending to an external address.
Reply-To: – The email will be sent from the address specified in SQL Server Reporting Services Configuration Manager on the server. However, if you would like replies to be redirected, simply enter a different address. I have a generic email , reports@company.com, that all reports are sent from.
Subject: – what will appear in the subject line of the email. This can be edited to anything you would like. The two parameters provided, @ReportName and @ExecutionTime, are the only parameters that are allowed in this field.
Include Report – This option, if checked, will attach the report in the format you specify. Consider the size of the report, and the format you want to send it in, when choosing this option. If you’re going to be sending a large report, consider mail size limits on the recipient’s mail server. You may need to send a link, instead.
Render Format – If you have chosen to include the report, you can pick a format here. The formats you can pick from vary based on your version of Reporting Services.
• XML file with report data – the user receives an .xml file as an attachment.
• CSV (comma delimited) – the user receives a .csv file as an attachment.
• Acrobat (PDF) file – the user receives a .pdf file as an attachment.
• MHTML (web archive) – the user receives the report embedded in the body of the email, rather than as an attachment. (I was really excited when I discovered this!)
• Excel – the user receives an .xls file as an attachment.
• TIFF file – the user receives a .tiff file as an attachment.
• Word – the user receives a .doc file as an attachment. This is new in 2008.
Include Link – This option, if checked, will embed a link to the report in the body of the email. Note that if you do this, the report parameters will be embedded in the link.
Priority: – This sets a priority of High, Normal or Low for Outlook. High is the little red exclamation point, and Low is the little blue down arrow.
Comment: – This field controls what will be in the body of the email. Comments will appear above the report, if you selected MHTML, and above the link, if you chose to include it.
Schedule the subscription by clicking Schedule Details: Here you can select hourly, daily, weekly, monthly and many more options. Ok and here now your subscription is created. Wait for the particular time which you schedule.
Another place where you can see the subscription delivery is in the Microsoft SQL Server Management Studio under SQL Server Agent under Jobs Activity monitor: Connect to SQL Server --> SQL Server Agent -->Job Activity
Windows File Share Delivery in Reporting Services:
Reporting Services includes a file share delivery extension so that you can deliver a report to a folder. The file share delivery extension is available by default and requires no additional configuration. To distribute a report to a file share, you define either a standard subscription or a data-driven subscription. You can subscribe to and request delivery for only one report at a time. When defining a subscription that uses file share delivery, you must specify an existing folder as the target folder. The report server does not create folders on the file system. The folder that you specify must be accessible over a network connection. When specifying the target folder in a subscription, use Uniform Naming Convention (UNC) format that includes the computer's network name. Do not include trailing backslashes in the folder path. The example illustrates a UNC path: \\ShareFolderName
File Name: – This is the name the file will be saved as. Just as a note, you can’t use the same parameters that you can with email. I’ve tested it, and they just appear as @ReportName and @TimeExecuted in the file name. You can, however, use the parameter @timestamp. This will add the time the report was saved to the file name. This is useful for creating uniquely named files.
Add a file extension when the file is created – If this is selected, the file will be assigned a 3-letter extension corresponding to the file format you select. If this is not selected, the 3-letter extension isn’t assigned to the file, although it will still open in the selected format.
Path: – The location on the network the file will be saved to. The folder must already exist – Reporting Services won’t create it. Also, you must use the UNC format: file://servername/foldername/subfolder.
Render Format: – The file format the report will be saved in. The formats you can pick from vary based on your version of Reporting Services.
• XML file with report data – the report is saved as an .xml file.
• CSV (comma delimited) – the report is saved as a .csv file.
• Acrobat (PDF) file – the report is saved as a .pdf file.
• HTML 4.0 – the report is saved as an .html file. Note: if there are images in your report, they will not show in this format.
• MHTML (web archive) – the report is saved as a .mhtml file.
• Excel – the report is saved as an .xls file.
• RPL Renderer – the report is saved as an .rpl file. What is .rpl? RPL (Report Page Layout Format) is a rending format used in newer Reporting Services version for forcing the client to share some of the workload of rendering viewer controls. This enhances performance in the loading process of reports
• TIFF file – the report is saved as a .tif file.
• Word – the report is saved as a .doc file.
Credentials used to access the file share: – This must be a network account, with access to the folder you are saving the file to.
Overwrite options: – These are options for what will happen if the report has previously been saved to the network location.
• Overwrite an existing file with a newer version – Just what it says! If a file with the same name and extension exists in that folder, it will be overwritten. If I save “Sales Report.xls” at 10:00 AM on Monday, then save “Sales Report.xls” at 10:00 AM on Tuesday, only the file from Tuesday will exist.
• Do not overwrite the file if a previous version exists – Again, this is just what it says! If a file with the same name and extension exists in that folder, the file will not save. If you look at the report subscription Status, you will see the message, “A file named “ReportName” already exists in the destination folder. To continue, do one of the following: remove the existing file, specify a different name, or set overwrite options to allow the replacement of the existing file with a newer”.
• Increment file names as newer versions are added – The first time the file is saved, it is as “Report Name.doc”. The next time, it is “Report Name_1.doc”, then “Report Name_2.doc”, etc. Another way to achieve the same effect is to use the @timestamp parameter in the File Name, and set the Overwrite option to “Do not overwrite…”
Schedule the subscription by clicking Schedule Details: Here you can select hourly, daily, weekly, monthly and many more options.
After the scheduled time expires verify the status as you did in the email delivery example. Also go and verify that the report has arrived in the shared folder.
CREATING A DATA-DRIVEN SUBSCRIPTION:
A data-driven subscription provides a way to use dynamic subscription data that is retrieved from an external data source at run time. A data-driven subscription can also use static text and default values that you specify when the subscription is defined. You can use data-driven subscriptions to do the following:
• Distribute a report to a fluctuating list of subscribers. For example, you can use data-driven subscriptions to distribute a report throughout a large organization where subscribers vary from one month to the next, or use other criteria that determines group membership from an existing set of users.
• Filter the report output using report parameter values that are retrieved at run time.
• Vary report output formats and delivery options for each report delivery.
Data-driven subscriptions send out reports to not just one individual or two but to a large number of recipients whose desired format (among other details) are maintained in a database on the server. This is report delivery targeted at mass distribution. In order to work with this hands-on exercise you need to have a database of recipients ready. When the subscription is processed, the Report Server customizes the output for each of the recipients maintained on the database.
Note: To use Data Driven Subscriptions, you must have Evaluation, Developer or Enterprise version of SQL Server. This feature is not supported in Standard or Express.
Create a Subscription database in SQL Server 2008
Before you can send out the email to a number of recipients you need to have information about their email addresses in the correct format in your SQL Server database which stores subscriber information
Connect to SQL Server Management StudioCreate New database Subscribers
CREATE TABLE [dbo].[RecipientInfo]
(
[SubscriptionID] [int] NOT NULL PRIMARY KEY,
[RecipientID] [int] ,
[Email] [nvarchar] (50) NOT NULL,
[FileType] [bit],
[Format] [nvarchar] (20) NOT NULL ,
)
INSERT INTO [dbo].[RecipientInfo] (SubscriptionID, RecipientID,
Email, FileType, Format) VALUES ('101', '289', ' EmailAddress.com', '1', 'Excel')
INSERT INTO [dbo].[RecipientInfo] (SubscriptionID, RecipientID, Email, FileType, Format) VALUES ('201', '284', ' EmailAddress.com','2', 'Word')
INSERT INTO [dbo].[RecipientInfo] (SubscriptionID, RecipientID, Email, FileType, Format) VALUES ('301', '275', EmailAddress.com','3', 'PDF')
Creating the data-driven subscription by email
Open a report and go to the Subscriptions tab. Click “New Data-driven Subscription”.
Step 1
The first step is to establish a description, delivery method and data source for this subscription.
Description – provide a name, which you will see on the Subscriptions tab for the report. I usually put in something that tells me how often this subscription is run and who or where is it being delivered to.
Specify how recipients are notified – This will determine how the report is delivered.
E-mail – The report will be delivered via email.
Specify a data source that contains recipient information:
Specify a shared data source – choose a data source that exists in the Reporting Services installation. I always use this option, as it makes administration much easier.
Specify for this subscription only – you can set up a data source for this item only.
Step 2
If you chose to use a shared data source, you will see this screen, where you can navigate your folders to find your data source.
Note: - If you get an error saying could not valid then go back and uncheck the use windows credential box or check your connection and credentials.
Step 4
In this step, you will specify your email delivery information.
• To – who the email will be delivered to. Set a static email, or choose a value from your query.
• Cc – who the email will be cc’ed to. Set a static email, or choose a value from your query.
• Bcc – who the email will be bcc’ed to. Set a static email, or choose a value from your query.
• Reply to – by default, replies will go the account the reports are sent from. You can set a different email address here. Set a static email, or choose a value from your query.
• Include Report – you can set a static or dynamic option of True or False.
• Render Format – you can set a static option, get the value from your query, or choose no format.
• Priority – this sets the priority in Outlook. You can set a static option, get the value from your query, or choose no value.
• Subject – the subject line of the email. You can set a static subject, or get the value from your query.
• Comment – this will appear in the body of the email. You can set a static option, get the value from your query, or choose no value.
• Include link – determines if you want to include a link to the report in the body of the email. You can set a static option, get the value from your query, or choose no value.
There are so many creative ways to use this! Perhaps you want the report to have a Priority of High if sales are above or below a certain dollar value. The Subject could be dependent on any field in the query. Perhaps you don’t include the report and the link if certain criteria are not met.
Step 5
If your report includes parameters, you will set them here.
Step 6:
The final step is to define when the subscription will be processed.
• When the report data is updated on the report server – the subscription will be processed when the snapshot is refreshed, if the report is set up to use a snapshot.
• On a schedule created for this subscription – create a schedule for this one report subscription.
• On a shared schedule – use a pre-defined schedule, created in Site Settings.
Your data Driven Subscription got created and waits for to completion of the schedule and see the results that it should send a mail to all recipient which we stored in database.
Null Delivery Provider – The What and Why
A feature of Reporting Services that can be used to improve performance is report caching. To sum up what could be a future post of its own: caching will save a copy of the report in the ReportServerTempDB, which reduces the time it takes for a report to render for a user. This can be very useful if you have a report that takes a long time to generate, and is run frequently with no parameters or the same parameters.
When you set up a report to use caching, the cache is created the first time a user runs it. You set the cache to expire after a certain amount of time. So, say you have a report that takes 5 minutes to generate, and 10 users will run it every day, with the first one coming in at 7:00 AM. You set the report to cache a temporary copy, and expire it every day at 7:00 PM. The first user would come in and run it, and it would take 5 minutes. Every other user that runs it between the time of the first user and the expiration would notice a significant decrease in rendering time.
But, how can you help the users by reducing the time it takes the first user to run it in the morning?
The answer is to set up a report subscription using the Null Delivery Provider. This saves a copy of the report in cache at a time you specify – in this example, perhaps 6:00 AM. When the first user comes in to run the report, the rendering time is reduced.
Data-driven Subscriptions by Null Delivery Provider
Open a report and go to the Subscriptions tab. Click “New Data-driven Subscription”.
Step 1
The first step is to establish a description, delivery method and data source for this subscription.
Description – provide a name, which you will see on the Subscriptions tab for the report. I usually put in something that tells me how often this subscription is run and who or where is it being delivered to.
Specify how recipients are notified – This will determine how the report is delivered.
Choose Null Delivery Provider
Specify a data source that contains recipient information:
Specify a shared data source – choose a data source that exists in the Reporting Services installation. I always use this option, as it makes administration much easier.
Specify for this subscription only – you can set up a data source for this item only.
Step 2
If you chose to use a shared data source, you will see this screen, where you can navigate your folders to find your data source. I usually store mine in the “Data Sources” folder – that makes them easy to find!
If you choose to specify a data source for this subscription only, you will see this screen. Enter a connection string and credentials for your data source.
Step 3
In this step, you define the query that can return parameter values for you.
When using caching for a report, be aware that it works best when you have no parameters, or very few choices. Every distinct combination of parameters will create a different version of the report in cache.
I am going to set my start date, end date, and folder parameters for this report in this step.
You have the option to specify a time-out, in seconds.
You can click the Validate button to ensure the query will run against your data source.
Step 4
There are no delivery settings for Null Delivery Provider. The report is always “delivered” to ReportServerTempDB.
Step 5
If your report includes parameters, you will set them here. You can set a static value for each, or, as with the case below, choose a value from the query.
Step 6
The final step is to define when the subscription will be processed.
When the report data is updated on the report server – the subscription will be processed when the snapshot is refreshed, if the report is set up to use a snapshot.
On a schedule created for this subscription – create a schedule for this one report subscription.
On a shared schedule – use a pre-defined schedule, created in Site Settings.
One thing to keep in mind with Null Delivery is that the copy of the report in cache must be expired before you create a new copy using this method. I would suggest setting up the report cache to expire on a specific schedule, shortly before this subscription runs.
This explanation of Null Delivery Provider is just one piece of report caching. Caching can be a way to increase performance for your users, if used correctly. Consider using both caching and data-driven subscriptions to improve your environment.
Report subscriptions are processed on the Report Server. They reach end users through delivery extensions that are deployed on the Report Server.
There are two kinds of subscriptions:
• Standard /Event-Driven Subscriptions
• Data-Driven Subscriptions
Standard subscriptions are created and managed by individual users. A standard subscription consists of static values that cannot be varied during subscription processing.
Data-driven subscriptions are dynamic in that the presentation, delivery, and parameter values are retrieved at run time from a data source. You might use data-driven subscriptions if you have a very large recipient list or if you want to vary report output for each recipient. To use data-driven subscriptions, you must have expertise in building queries and an understanding of how parameters are used. Report server administrators typically create and manage these subscriptions
Subscriptions use delivery extensions to determine how to distribute a report and in what format. When a user creates a subscription, he or she can choose one of the available delivery extensions to determine how the report is delivered. Reporting Services includes support for e-mail delivery and delivery to a file share. Developers can create additional delivery extensions to route reports to other locations. Another delivery method is called the null delivery provider. This method is not available to users. Null delivery is used by administrators to improve report server performance by preloading the cache. Subscription information is stored with individual reports in a report server database. You cannot manage subscriptions separately from the report to which they are associated.
E-Mail Delivery in Reporting Services:
Reporting Services includes an e-mail delivery extension that provides a way to e-mail a report to individual users or groups. The e-mail delivery extension is configured through the Reporting Services Configuration Manager tool and by editing the Reporting Services configuration files.
Select the Email Settings--> Sender Address, Current SMTP Delivery Method and SMTP Server.
To distribute or receive a report by e-mail, you define either a standard subscription or a data-driven subscription.
Note: You can subscribe to or distribute only one report at a time. You cannot create a subscription that delivers multiple reports in a single e-mail message. You click the “New Subscription” button, ready to go add an email address.
An E-Mail will be generated and sent to the email addresses found in the recipients’ field.
To:, Cc: and Bcc: – fairly standard fields. You can enter the alias if you’re sending to an internal address, and must type the full email address if sending to an external address.
Reply-To: – The email will be sent from the address specified in SQL Server Reporting Services Configuration Manager on the server. However, if you would like replies to be redirected, simply enter a different address. I have a generic email , reports@company.com, that all reports are sent from.
Subject: – what will appear in the subject line of the email. This can be edited to anything you would like. The two parameters provided, @ReportName and @ExecutionTime, are the only parameters that are allowed in this field.
Include Report – This option, if checked, will attach the report in the format you specify. Consider the size of the report, and the format you want to send it in, when choosing this option. If you’re going to be sending a large report, consider mail size limits on the recipient’s mail server. You may need to send a link, instead.
Render Format – If you have chosen to include the report, you can pick a format here. The formats you can pick from vary based on your version of Reporting Services.
• XML file with report data – the user receives an .xml file as an attachment.
• CSV (comma delimited) – the user receives a .csv file as an attachment.
• Acrobat (PDF) file – the user receives a .pdf file as an attachment.
• MHTML (web archive) – the user receives the report embedded in the body of the email, rather than as an attachment. (I was really excited when I discovered this!)
• Excel – the user receives an .xls file as an attachment.
• TIFF file – the user receives a .tiff file as an attachment.
• Word – the user receives a .doc file as an attachment. This is new in 2008.
Include Link – This option, if checked, will embed a link to the report in the body of the email. Note that if you do this, the report parameters will be embedded in the link.
Priority: – This sets a priority of High, Normal or Low for Outlook. High is the little red exclamation point, and Low is the little blue down arrow.
Comment: – This field controls what will be in the body of the email. Comments will appear above the report, if you selected MHTML, and above the link, if you chose to include it.
Schedule the subscription by clicking Schedule Details: Here you can select hourly, daily, weekly, monthly and many more options. Ok and here now your subscription is created. Wait for the particular time which you schedule.
Another place where you can see the subscription delivery is in the Microsoft SQL Server Management Studio under SQL Server Agent under Jobs Activity monitor: Connect to SQL Server --> SQL Server Agent -->Job Activity
Windows File Share Delivery in Reporting Services:
Reporting Services includes a file share delivery extension so that you can deliver a report to a folder. The file share delivery extension is available by default and requires no additional configuration. To distribute a report to a file share, you define either a standard subscription or a data-driven subscription. You can subscribe to and request delivery for only one report at a time. When defining a subscription that uses file share delivery, you must specify an existing folder as the target folder. The report server does not create folders on the file system. The folder that you specify must be accessible over a network connection. When specifying the target folder in a subscription, use Uniform Naming Convention (UNC) format that includes the computer's network name. Do not include trailing backslashes in the folder path. The example illustrates a UNC path: \
File Name: – This is the name the file will be saved as. Just as a note, you can’t use the same parameters that you can with email. I’ve tested it, and they just appear as @ReportName and @TimeExecuted in the file name. You can, however, use the parameter @timestamp. This will add the time the report was saved to the file name. This is useful for creating uniquely named files.
Add a file extension when the file is created – If this is selected, the file will be assigned a 3-letter extension corresponding to the file format you select. If this is not selected, the 3-letter extension isn’t assigned to the file, although it will still open in the selected format.
Path: – The location on the network the file will be saved to. The folder must already exist – Reporting Services won’t create it. Also, you must use the UNC format: file://servername/foldername/subfolder.
Render Format: – The file format the report will be saved in. The formats you can pick from vary based on your version of Reporting Services.
• XML file with report data – the report is saved as an .xml file.
• CSV (comma delimited) – the report is saved as a .csv file.
• Acrobat (PDF) file – the report is saved as a .pdf file.
• HTML 4.0 – the report is saved as an .html file. Note: if there are images in your report, they will not show in this format.
• MHTML (web archive) – the report is saved as a .mhtml file.
• Excel – the report is saved as an .xls file.
• RPL Renderer – the report is saved as an .rpl file. What is .rpl? RPL (Report Page Layout Format) is a rending format used in newer Reporting Services version for forcing the client to share some of the workload of rendering viewer controls. This enhances performance in the loading process of reports
• TIFF file – the report is saved as a .tif file.
• Word – the report is saved as a .doc file.
Credentials used to access the file share: – This must be a network account, with access to the folder you are saving the file to.
Overwrite options: – These are options for what will happen if the report has previously been saved to the network location.
• Overwrite an existing file with a newer version – Just what it says! If a file with the same name and extension exists in that folder, it will be overwritten. If I save “Sales Report.xls” at 10:00 AM on Monday, then save “Sales Report.xls” at 10:00 AM on Tuesday, only the file from Tuesday will exist.
• Do not overwrite the file if a previous version exists – Again, this is just what it says! If a file with the same name and extension exists in that folder, the file will not save. If you look at the report subscription Status, you will see the message, “A file named “ReportName” already exists in the destination folder. To continue, do one of the following: remove the existing file, specify a different name, or set overwrite options to allow the replacement of the existing file with a newer”.
• Increment file names as newer versions are added – The first time the file is saved, it is as “Report Name.doc”. The next time, it is “Report Name_1.doc”, then “Report Name_2.doc”, etc. Another way to achieve the same effect is to use the @timestamp parameter in the File Name, and set the Overwrite option to “Do not overwrite…”
Schedule the subscription by clicking Schedule Details: Here you can select hourly, daily, weekly, monthly and many more options.
After the scheduled time expires verify the status as you did in the email delivery example. Also go and verify that the report has arrived in the shared folder.
CREATING A DATA-DRIVEN SUBSCRIPTION:
A data-driven subscription provides a way to use dynamic subscription data that is retrieved from an external data source at run time. A data-driven subscription can also use static text and default values that you specify when the subscription is defined. You can use data-driven subscriptions to do the following:
• Distribute a report to a fluctuating list of subscribers. For example, you can use data-driven subscriptions to distribute a report throughout a large organization where subscribers vary from one month to the next, or use other criteria that determines group membership from an existing set of users.
• Filter the report output using report parameter values that are retrieved at run time.
• Vary report output formats and delivery options for each report delivery.
Data-driven subscriptions send out reports to not just one individual or two but to a large number of recipients whose desired format (among other details) are maintained in a database on the server. This is report delivery targeted at mass distribution. In order to work with this hands-on exercise you need to have a database of recipients ready. When the subscription is processed, the Report Server customizes the output for each of the recipients maintained on the database.
Note: To use Data Driven Subscriptions, you must have Evaluation, Developer or Enterprise version of SQL Server. This feature is not supported in Standard or Express.
Create a Subscription database in SQL Server 2008
Before you can send out the email to a number of recipients you need to have information about their email addresses in the correct format in your SQL Server database which stores subscriber information
Connect to SQL Server Management StudioCreate New database Subscribers
CREATE TABLE [dbo].[RecipientInfo]
(
[SubscriptionID] [int] NOT NULL PRIMARY KEY,
[RecipientID] [int] ,
[Email] [nvarchar] (50) NOT NULL,
[FileType] [bit],
[Format] [nvarchar] (20) NOT NULL ,
)
INSERT INTO [dbo].[RecipientInfo] (SubscriptionID, RecipientID,
Email, FileType, Format) VALUES ('101', '289', ' EmailAddress.com', '1', 'Excel')
INSERT INTO [dbo].[RecipientInfo] (SubscriptionID, RecipientID, Email, FileType, Format) VALUES ('201', '284', ' EmailAddress.com','2', 'Word')
INSERT INTO [dbo].[RecipientInfo] (SubscriptionID, RecipientID, Email, FileType, Format) VALUES ('301', '275', EmailAddress.com','3', 'PDF')
Creating the data-driven subscription by email
Open a report and go to the Subscriptions tab. Click “New Data-driven Subscription”.
Step 1
The first step is to establish a description, delivery method and data source for this subscription.
Description – provide a name, which you will see on the Subscriptions tab for the report. I usually put in something that tells me how often this subscription is run and who or where is it being delivered to.
Specify how recipients are notified – This will determine how the report is delivered.
E-mail – The report will be delivered via email.
Specify a data source that contains recipient information:
Specify a shared data source – choose a data source that exists in the Reporting Services installation. I always use this option, as it makes administration much easier.
Specify for this subscription only – you can set up a data source for this item only.
Step 2
If you chose to use a shared data source, you will see this screen, where you can navigate your folders to find your data source.
Note: - If you get an error saying could not valid then go back and uncheck the use windows credential box or check your connection and credentials.
Step 4
In this step, you will specify your email delivery information.
• To – who the email will be delivered to. Set a static email, or choose a value from your query.
• Cc – who the email will be cc’ed to. Set a static email, or choose a value from your query.
• Bcc – who the email will be bcc’ed to. Set a static email, or choose a value from your query.
• Reply to – by default, replies will go the account the reports are sent from. You can set a different email address here. Set a static email, or choose a value from your query.
• Include Report – you can set a static or dynamic option of True or False.
• Render Format – you can set a static option, get the value from your query, or choose no format.
• Priority – this sets the priority in Outlook. You can set a static option, get the value from your query, or choose no value.
• Subject – the subject line of the email. You can set a static subject, or get the value from your query.
• Comment – this will appear in the body of the email. You can set a static option, get the value from your query, or choose no value.
• Include link – determines if you want to include a link to the report in the body of the email. You can set a static option, get the value from your query, or choose no value.
There are so many creative ways to use this! Perhaps you want the report to have a Priority of High if sales are above or below a certain dollar value. The Subject could be dependent on any field in the query. Perhaps you don’t include the report and the link if certain criteria are not met.
Step 5
If your report includes parameters, you will set them here.
Step 6:
The final step is to define when the subscription will be processed.
• When the report data is updated on the report server – the subscription will be processed when the snapshot is refreshed, if the report is set up to use a snapshot.
• On a schedule created for this subscription – create a schedule for this one report subscription.
• On a shared schedule – use a pre-defined schedule, created in Site Settings.
Your data Driven Subscription got created and waits for to completion of the schedule and see the results that it should send a mail to all recipient which we stored in database.
Null Delivery Provider – The What and Why
A feature of Reporting Services that can be used to improve performance is report caching. To sum up what could be a future post of its own: caching will save a copy of the report in the ReportServerTempDB, which reduces the time it takes for a report to render for a user. This can be very useful if you have a report that takes a long time to generate, and is run frequently with no parameters or the same parameters.
When you set up a report to use caching, the cache is created the first time a user runs it. You set the cache to expire after a certain amount of time. So, say you have a report that takes 5 minutes to generate, and 10 users will run it every day, with the first one coming in at 7:00 AM. You set the report to cache a temporary copy, and expire it every day at 7:00 PM. The first user would come in and run it, and it would take 5 minutes. Every other user that runs it between the time of the first user and the expiration would notice a significant decrease in rendering time.
But, how can you help the users by reducing the time it takes the first user to run it in the morning?
The answer is to set up a report subscription using the Null Delivery Provider. This saves a copy of the report in cache at a time you specify – in this example, perhaps 6:00 AM. When the first user comes in to run the report, the rendering time is reduced.
Data-driven Subscriptions by Null Delivery Provider
Open a report and go to the Subscriptions tab. Click “New Data-driven Subscription”.
Step 1
The first step is to establish a description, delivery method and data source for this subscription.
Description – provide a name, which you will see on the Subscriptions tab for the report. I usually put in something that tells me how often this subscription is run and who or where is it being delivered to.
Specify how recipients are notified – This will determine how the report is delivered.
Choose Null Delivery Provider
Specify a data source that contains recipient information:
Specify a shared data source – choose a data source that exists in the Reporting Services installation. I always use this option, as it makes administration much easier.
Specify for this subscription only – you can set up a data source for this item only.
Step 2
If you chose to use a shared data source, you will see this screen, where you can navigate your folders to find your data source. I usually store mine in the “Data Sources” folder – that makes them easy to find!
If you choose to specify a data source for this subscription only, you will see this screen. Enter a connection string and credentials for your data source.
Step 3
In this step, you define the query that can return parameter values for you.
When using caching for a report, be aware that it works best when you have no parameters, or very few choices. Every distinct combination of parameters will create a different version of the report in cache.
I am going to set my start date, end date, and folder parameters for this report in this step.
You have the option to specify a time-out, in seconds.
You can click the Validate button to ensure the query will run against your data source.
Step 4
There are no delivery settings for Null Delivery Provider. The report is always “delivered” to ReportServerTempDB.
Step 5
If your report includes parameters, you will set them here. You can set a static value for each, or, as with the case below, choose a value from the query.
Step 6
The final step is to define when the subscription will be processed.
When the report data is updated on the report server – the subscription will be processed when the snapshot is refreshed, if the report is set up to use a snapshot.
On a schedule created for this subscription – create a schedule for this one report subscription.
On a shared schedule – use a pre-defined schedule, created in Site Settings.
One thing to keep in mind with Null Delivery is that the copy of the report in cache must be expired before you create a new copy using this method. I would suggest setting up the report cache to expire on a specific schedule, shortly before this subscription runs.
This explanation of Null Delivery Provider is just one piece of report caching. Caching can be a way to increase performance for your users, if used correctly. Consider using both caching and data-driven subscriptions to improve your environment.
Creating a Deployment Manifest
SSIS - Creating a Deployment Manifest:
Using a Deployment Manifest in SSIS allows you to deploy a set of packages to a target location using a wizard for installing your packages. The benefit to using it is the nice user interface that a wizard provides. The disadvantage of using it is it’s lack of flexibility. When using the Deployment Manifest keep in mind that it’s an all or nothing deployment, which means you cannot choose to just deploy one or two packages at a time from your SSIS project. Using the Deployment Manifest will deploy all your packages that are in your project. Creating the manifest file is a very simple process that I will walk you through.
Creating a Deployment Manifest
With the Solution Explorer open Right-Click on the project name and click Properties
This will open the Project Properties Pages
Select the Deployment Utility from the page
Change the CreateDeploymentUtility property to True
After you’ve gone through these steps the next time you build your project it will create the file (YourProjectName).SSISDeploymentManifest. This file is located in the same folder as your packages in the bin\Deployment folder.
If you run this file it will open the Package Installation Wizard that will allow you to deploy all your packages that were located in the project to a desired location.
Using a Deployment Manifest in SSIS allows you to deploy a set of packages to a target location using a wizard for installing your packages. The benefit to using it is the nice user interface that a wizard provides. The disadvantage of using it is it’s lack of flexibility. When using the Deployment Manifest keep in mind that it’s an all or nothing deployment, which means you cannot choose to just deploy one or two packages at a time from your SSIS project. Using the Deployment Manifest will deploy all your packages that are in your project. Creating the manifest file is a very simple process that I will walk you through.
Creating a Deployment Manifest
With the Solution Explorer open Right-Click on the project name and click Properties
This will open the Project Properties Pages
Select the Deployment Utility from the page
Change the CreateDeploymentUtility property to True
After you’ve gone through these steps the next time you build your project it will create the file (YourProjectName).SSISDeploymentManifest. This file is located in the same folder as your packages in the bin\Deployment folder.
If you run this file it will open the Package Installation Wizard that will allow you to deploy all your packages that were located in the project to a desired location.
Wednesday, October 27, 2010
What is Agile
Twelve principles:
1. Customer satisfaction by rapid delivery of useful software
2. Welcome changing requirements, even late in development.
3. Working software is delivered frequently (weeks rather than months)
4. Working software is the principal measure of progress
5. Sustainable development, able to maintain a constant pace
6. Close, daily cooperation between businesspeople and developers.
7. Face-to-face conversation is the best form of communication (co-location)
8. Projects are built around motivated individuals, who should be trusted
9. Continuous attention to technical excellence and good design
10. Simplicity
11. Self-organizing teams
12. Regular adaptation to changing circumstances
Characteristics:
There are many specific agile development methods. Most promote development, teamwork, collaboration, and process adaptability throughout the life-cycle of the project.
Agile methods break tasks into small increments with minimal planning, and do not directly involve long-term planning. Iterations are short time frames (timeboxes) that typically last from one to four weeks. Each iteration involves a team working through a full software development cycle including planning, requirements analysis, design, coding, unit testing, and acceptance testing when a working product is demonstrated to stakeholders. This minimizes overall risk and allows the project to adapt to changes quickly. Stakeholders produce documentation as required. An iteration may not add enough functionality to warrant a market release, but the goal is to have an available release (with minimal bugs) at the end of each iteration. Multiple iterations may be required to release a product or new features.
Team composition in an agile project is usually cross-functional and self-organizing without consideration for any existing corporate hierarchy or the corporate roles of team members. Team members normally take responsibility for tasks that deliver the functionality an iteration requires. They decide individually how to meet an iteration's requirements.
Agile methods emphasize face-to-face communication over written documents when the team is all in the same location. Most agile teams work in a single open office (called a bullpen), which facilitates such communication. Team size is typically small (5- 12 people) to simplify team communication and team collaboration. Larger development efforts may be delivered by multiple teams working toward a common goal or on different parts of an effort. This may require a coordination of priorities across teams. When a team works in different locations, they maintain daily contact through videoconferencing, voice, e-mail, etc.
No matter what development disciplines are required, each agile team will contain a customer representative. This person is appointed by stakeholders to act on their behalf and makes a personal commitment to being available for developers to answer mid-iteration problem-domain questions. At the end of each iteration, stakeholders and the customer representative review progress and re-evaluate priorities with a view to optimizing the return on investment (ROI) and ensuring alignment with customer needs and company goals.
Most agile implementations use a routine and formal daily face-to-face communication among team members. This specifically includes the customer representative and any interested stakeholders as observers. In a brief session, team members report to each other what they did the previous day, what they intend to do today, and what their roadblocks are. This face-to-face communication exposes problems as they arise.
Agile development emphasizes working software as the primary measure of progress. This, combined with the preference for face-to-face communication, produces less written documentation than other methods. The agile method encourages stakeholders to prioritize wants with other iteration outcomes based exclusively on business value perceived at the beginning of the iteration.
Comparison with other methods:
Agile methods are sometimes characterized as being at the opposite end of the spectrum from "plan-driven" or "disciplined" methods. This distinction may be misleading, as agile methods are not necessarily "unplanned" or "undisciplined". Agile teams may employ highly disciplined formal methods. A more accurate distinction is that methods exist on a continuum from "adaptive" to "predictive". Agile methods lie on the "adaptive" side of this continuum.
Adaptive methods focus on adapting quickly to changing realities. When the needs of a project change, an adaptive team changes as well. An adaptive team will have difficulty describing exactly what will happen in the future. The further away a date is, the more vague an adaptive method will be about what will happen on that date. An adaptive team can not report exactly what tasks are being done next week, but only which features are planned for next month. When asked about a release six months from now, an adaptive team may only be able to report the mission statement for the release, or a statement of expected value vs. cost.
Predictive methods, in contrast, focus on planning the future in detail. A predictive team can report exactly what features and tasks are planned for the entire length of the development process. Predictive teams have difficulty changing direction. The plan is typically optimized for the original destination and changing direction can require completed work to be started over. Predictive teams will often institute a change control board to ensure that only the most valuable changes are considered.
Formal methods, in contrast to adaptive and predictive methods, focus on computer science theory with a wide array of types of provers. A formal method attempts to prove the absence of errors with some level of determinism. Some formal methods are based on model checking and provide counter examples for code that cannot be proven. Generally, mathematical models (often supported through special languages see SPIN model checker) map to assertions about requirements. Formal methods are dependent on a tool driven approach, and may be combined with other development approaches. Some provers do not easily scale. Like agile methods, manifestos relevant to high integrity software have been proposed in Crosstalk.
Agile methods:
Well-known agile software development methods include:
1. Agile Modeling
2. Agile Unified Process (AUP)
3. Dynamic Systems Development Method (DSDM)
4. Essential Unified Process (EssUP)
5. Extreme Programming (XP)
6. Feature Driven Development (FDD)
7. Open Unified Process (OpenUP)
8. Scrum
9. Velocity tracking
For More Details please visit: http://en.wikipedia.org/wiki/Agile_software_development
Monday, October 25, 2010
What is SQL Azure
•Definition
–Providing IT resources, as a service, in a dynamic and scalable manner over a network
•What does this mean?
–Access from any device (PC, Phone, Multi-Function Devices, TV…)
–Hosted centrally managed software and data
–Unlimited processing, CPU, storage, memory, data centers
–IT on demand
•Five essential characteristics of the Cloud:
–On-demand self-service
–Broad network access
–Resource pooling
–Rapid elasticity
–Measured service
HighLights:
SQL Azure is the first cloud service completely supporting the relational database model.
SQL Azure is fully committed to supporting T-SQL, SQL query, stored procedures, data views, and so on.
SQL Azure supports the traditional Windows user/password security model.
The first release of SQL Azure is almost fully compatible with all existing relational development tools and IDEs, such as SQL Server Management Studio (SSMS) and Visual Studio.
SQL Azure allows all SQL developers and IT staff to seamlessly migrate from an on-premises environment to a cloud environment with almost no learning curve. All knowledge and skills for SQL developers and IT staff can be applied to SQL Azure. This should remove a big concern from when the Azure framework was announced in October 2008 regarding potential job losses for the IT industry (worries brought on because the infrastructure and data storage hardware for an organization no longer need to exist in the organization).
SQL Azure supports PHP, which makes SQL Azure more friendly to Internet applications.
The most important thing is that SQL Azure is the first platform supporting the rational database model and database as a service running in the cloud. The major changes are not in the physical domain but in the logical virtual domain.
Since SQL Server and T-SQL have been in the market for decades, there is a lot of information that can be found on them. Please bear in mind that SQL Azure does not support object browsing from SQL Server Management Studio. To connect to the SQL Azure database in the cloud using SQL Server Management Studio, launch SQL Server Management Studio.
The authentication type that should be selected is SQL Server Authentication. The login name and password are the same as the ones you used to redeem your invitation code.
The connection to SQL Azure will be automatically deactivated if there is no activity detected by SQL Azure. The error message will be thrown. A custom table can be created in the SQL Azure cloud database by using SQL Server Management Studio in a way that is pretty similar to creating a database on a server running in an on-premises environment. One difference, for example, is that the USE statement is not supported when using SQL Azure since we have used New Query to connect to the specific database already. If you need to access a different database, you can start a new query by clicking the New Query button.
Note: Since SQL Azure does not support object browsing from SQL Server Management Studio. Convert UDDTs to Base Type: This option needs to be set to true since SQL Azure does not support userdefined types. They need to be converted into underlying SQL Azure portable types.
TIPS:
Script extended properties: This option needs to be set to false since SQL Azure does not support extended properties.
Script USE DATABASE: This option needs to be set to false since SQL Azure does not support the USE statement.
Script Data: This option needs to be set to false since we do not care about the data at this moment. If you run the generated script without any modification, then you will get an error message. Two radio buttons can be found to the left of the button Test Connection, which allows users to quickly switch the connection back and forth between the SQL Azure cloud service and on-premises SQL workstation environments. There are two ways to execute a script. One way is to click the Execute button, and the other way is to select and highlight the text and press F5. If there is a script selected, the caption of the Execute button turns to Execute Selected, and the background color of the button changes,The caption of the Execute button turns to Execute Selected, and the background color turns to gold. Azure table storage supports the Language Integrated Query (LINQ) extensions for .NET 3.0 or later versions, ADO.NET Data Services, and representational state transfer (REST), which allows applications developed using non-.NET languages to access table storage via the Internet. There is no limit on the number of tables and entities or on the table size. There is no need for table storage developers to handle the data access transactions, optimistic concurrency for updates, and deletes. Also, there is no need for developers to worry about performance, since cloud storage is highly scalable. Especially for long queries or queries that encounter a time-out, partial results will be returned and the unfinished queries can be continued with a return continuation token.
Challenges Facing Enterprise IT:
Provisioning, deploying and managing servers at scale
Enabling faster, more efficient development of applications with existing knowledge and toolsets
Reducing IT hardware and infrastructure costs
SQL Azure Database: (The first and only true relational database as a service)
Self-Managed:
Easy provisioning and deployment
Auto high-availability and fault tolerance
Self-maintaining infrastructure; self-healing
No need for server or VM administration
Elastic Scale:
Database utility; pay as you grow
Flexible load balancing
Business-ready SLAs
Enable multi-tenant solutions
World-wide presence
Developer Agility:
Build cloud-based database solutions on consistent relational model
Leverage existing skills through existing ecosystem of developer and management tools
Explore new data application patterns
Challenges Today:
Promotions, events, ticket selling businesses are “bursts – bound” by nature
Capacity constraints limit business agility
High costs of entry into new business
Difficult to roll out extra capacity quickly
Idle capacity “off-bursts” is cost prohibitive
Solution (SQL AZURE):
Elastic scale – database as a service
Pay as you grow and shrink
Easy to provision and manage database
No hardware, no manual database administration required
Best Integration – Office 2010 & Other Tools.
Focus on combining the best features of SQL Server running at scale with low friction.
Highly scaled out relational database as a service
Relational database service
SQL Server technology foundation
Highly symmetrical
Highly scaled
Database “as a Service” – beyond hosting
Customer Value Props
Self-provisioning and capacity on demand
Symmetry on-premises database platform
Automatic high-availability and fault-tolerance
Automated DB maintenance (infrastructure)
Simple, flexible pricing – “pay as you grow”
Application Topologies:
SQL Azure access from within MS Datacenter (Azure compute) -- Also Know as "Code Near"
SQL Azure Access from outside MS Datacenter (On-premises) -- Also Know as "Code Far"
SQL Azure Access from within and outside MS Datacenter (On-premises & Azure Compute) -- Also Know as "Hybrid"
Partitioning, when do I need it?: (Concept of X (Storage), Y (Transactions) and Z axis from Low to high)
Single Database, No Partitioning
Partitioned Data. Partitioning Based on Application Requirements (Storage)
Partitioned Data, Partitioning based on Application Requirements (IOPS)
Partitioned Data, Partitioning based on Application Requirements (IOPS, Storage or both)
Developing on a local SQL Express instance has some advantages
Easy to get started, you already know how to do it!
Full fidelity with the designer and debugging tools
Reduces latency when using local Azure development tools
Reduces bandwidth and databases costs for development
Some caveats
Remember to alter your VS build settings to switch the connection string when deploying
Use tools (like SQLAzureMW) to keep you within the supported SQL Azure features
Always test in SQL Azure before deploying to production
Connecting to SQL Azure:
SQL Azure connection strings follow normal SQL syntax
Applications connect directly to a database
“Initial Catalog =” in connection string
No support for context switching (no USE)
Some commands must be in their own batch
Create/Alter/Drop Database & Create/Alter/Drop Login, & Create/Alter USER with FOR/FROM LOGIN
Encryption security
Set Encrypt = True, only SSL connections are supported
TrustServerCertificate = False, avoid Man-In-The-Middle-Attack!
Format of username for authentication:
ADO.Net:
Data Source=server.database.windows.net;
User ID=user@server;Password=password;
Setup your firewall rules first!
Resilient Connection Management:
Connections can drop for variety of reasons
Idleness (greater than 30 minutes)
Throttling
Long running transactions > 5 minutes
Resource Management
Database failover
Hardware failure
Load Balancing
Upgrade
What to do on connection failure?
Wait, then retry if it is a transient failure
Change your workload if throttled, i.e. break up your transaction
SELECT * INTO temp tables: (DOES NOT SUPPORT)
SELECT *INTO #Destination FROM Source WHERE [Color] LIKE 'Red‘
To work around this you need to create your destination table then call INSERT INTO. Here is an example:
CREATE TABLE #Destination (Id int NOT NULL, [Name] nvarchar(max), [Color] nvarchar(10))
INSERT INTO #Destination(Id, [Name], [Color])
SELECT Id, [Name], [Color] FROM Source WHERE [Color] LIKE 'Red';
SQL Azure Firewall:
What is it?
A way to restrict access to your database
How does it work?
UI
Programmatic access
Common scenarios
I need my development machine to be able to access it
I need access from my laptop when I am not in the office
I want to restrict access to only my application running in Windows Azure
Data Access APIs:
Supported APIs:
ADO.Net .Net 3.5 SP1 and 4.0
ODBC - SNAC 10
Entity Framework .Net 3.5 SP1 and 4.0
SQL Server 2008 Driver for PHP v1.1
Unsupported:
OleDB
Connection String:
ADO.Net
Encrypt=True and add @servername to User ID
ODBC
Encrypt=yes and add @servername to Uid
Protocols:
Supported:
TCP/IP over port 1433
UnSupported:
Dynamic ports
Named pipes
Shared memory
Authentication Mode:
Supported:
SQL Auth
UnSupported:
Windows Auth
Feature Parity:
Administration Surface:
Physical Server Properties does not apply in SQL Azure
You have a master database but no access to server level constructs such as
sp_configure, endpoints, DBCC commands, server level DMVs and System Views.
Programmability Surface:
Certain Features are partially available today:
USE, XML processing, deprecated T-SQL etc.
List is available here; http://msdn.microsoft.com/en-us/library/ee336267.aspx
Some features are not available today:
Full-text Search, Remote Data Access and Linked Servers, Distributed Transactions,
Change Tracking, Service Broker etc.
List is available here; http://msdn.microsoft.com/en-us/library/ee336253.asp
Migration Assistant for MySql and Access:
Scenario
Auto porting of schema, database code and data from MySql and Access to SQL Azure
SQL Server Migration Assistant for MySql and Access
Supports MySQL 4.1 and up
Support Access v 97 and up
SQL Server versions supported (all editions)
SQL Azure, SQL Server 2005, SQL Server 2008 and 2008 R2
Transferring Large Data:
Best Practices for efficient Data movement to SQL Azure
Use the right tools is key
BCP
SSIS
Network performance considerations
Latency vs Bandwidth
Use the right tools (BCP & Bulk Copy APIs):
Scenario
High speed programmable data import and export
Best Practices
Optimize Databases for Data Import
Disable/Delay creation of none Clustered Indexes
Order Data on the Clustered Index
Disable Triggers and Constraint checking
-N Native mode so no data type conversion needed.
-c Character mode for interoperability
-b batchsize will let you control transaction size
-h”TABLOCK, ORDER(…)” optimize writes
Use the right tools (SSIS – Best of Breed Data Transformation Utility):
SSIS Design Surface - Data Flow Task
Diverse Source and destinations
To/From Flat Files, ADO.Net, OleDB
Fully programmable flow
Loops, Sorts, Conditional operators, XML/WebServices Processing etc
VS Debugging support with data viewers, watches and conditional breakpoints
Best Practices – Data Flow Task
Remember; Optimize Databases for Data Import
Disable/Delay creation of none Clustered Indexes
Order Data on the Clustered Index
Disable Triggers and Constraint checking
Batch Size: Adjust the transaction size
Buffer and Blob temp storage area; spread over to multiple drives for large data transfers
Parallelization – based on execution trees, task will auto parallelize
Use the right tools (Import and Export Wizard):
Scenario
Simplified wizard for migrating schema and data through SSIS
Pros
Great performance out of the box
Allows ‘save as package’ for full control
To know more details please visit and watch the videos: http://www.microsoft.com/en-us/sqlazure/videos/default.aspx
–Providing IT resources, as a service, in a dynamic and scalable manner over a network
•What does this mean?
–Access from any device (PC, Phone, Multi-Function Devices, TV…)
–Hosted centrally managed software and data
–Unlimited processing, CPU, storage, memory, data centers
–IT on demand
•Five essential characteristics of the Cloud:
–On-demand self-service
–Broad network access
–Resource pooling
–Rapid elasticity
–Measured service
HighLights:
SQL Azure is the first cloud service completely supporting the relational database model.
SQL Azure is fully committed to supporting T-SQL, SQL query, stored procedures, data views, and so on.
SQL Azure supports the traditional Windows user/password security model.
The first release of SQL Azure is almost fully compatible with all existing relational development tools and IDEs, such as SQL Server Management Studio (SSMS) and Visual Studio.
SQL Azure allows all SQL developers and IT staff to seamlessly migrate from an on-premises environment to a cloud environment with almost no learning curve. All knowledge and skills for SQL developers and IT staff can be applied to SQL Azure. This should remove a big concern from when the Azure framework was announced in October 2008 regarding potential job losses for the IT industry (worries brought on because the infrastructure and data storage hardware for an organization no longer need to exist in the organization).
SQL Azure supports PHP, which makes SQL Azure more friendly to Internet applications.
The most important thing is that SQL Azure is the first platform supporting the rational database model and database as a service running in the cloud. The major changes are not in the physical domain but in the logical virtual domain.
Since SQL Server and T-SQL have been in the market for decades, there is a lot of information that can be found on them. Please bear in mind that SQL Azure does not support object browsing from SQL Server Management Studio. To connect to the SQL Azure database in the cloud using SQL Server Management Studio, launch SQL Server Management Studio.
The authentication type that should be selected is SQL Server Authentication. The login name and password are the same as the ones you used to redeem your invitation code.
The connection to SQL Azure will be automatically deactivated if there is no activity detected by SQL Azure. The error message will be thrown. A custom table can be created in the SQL Azure cloud database by using SQL Server Management Studio in a way that is pretty similar to creating a database on a server running in an on-premises environment. One difference, for example, is that the USE statement is not supported when using SQL Azure since we have used New Query to connect to the specific database already. If you need to access a different database, you can start a new query by clicking the New Query button.
Note: Since SQL Azure does not support object browsing from SQL Server Management Studio. Convert UDDTs to Base Type: This option needs to be set to true since SQL Azure does not support userdefined types. They need to be converted into underlying SQL Azure portable types.
TIPS:
Script extended properties: This option needs to be set to false since SQL Azure does not support extended properties.
Script USE DATABASE: This option needs to be set to false since SQL Azure does not support the USE statement.
Script Data: This option needs to be set to false since we do not care about the data at this moment. If you run the generated script without any modification, then you will get an error message. Two radio buttons can be found to the left of the button Test Connection, which allows users to quickly switch the connection back and forth between the SQL Azure cloud service and on-premises SQL workstation environments. There are two ways to execute a script. One way is to click the Execute button, and the other way is to select and highlight the text and press F5. If there is a script selected, the caption of the Execute button turns to Execute Selected, and the background color of the button changes,The caption of the Execute button turns to Execute Selected, and the background color turns to gold. Azure table storage supports the Language Integrated Query (LINQ) extensions for .NET 3.0 or later versions, ADO.NET Data Services, and representational state transfer (REST), which allows applications developed using non-.NET languages to access table storage via the Internet. There is no limit on the number of tables and entities or on the table size. There is no need for table storage developers to handle the data access transactions, optimistic concurrency for updates, and deletes. Also, there is no need for developers to worry about performance, since cloud storage is highly scalable. Especially for long queries or queries that encounter a time-out, partial results will be returned and the unfinished queries can be continued with a return continuation token.
Challenges Facing Enterprise IT:
Provisioning, deploying and managing servers at scale
Enabling faster, more efficient development of applications with existing knowledge and toolsets
Reducing IT hardware and infrastructure costs
SQL Azure Database: (The first and only true relational database as a service)
Self-Managed:
Easy provisioning and deployment
Auto high-availability and fault tolerance
Self-maintaining infrastructure; self-healing
No need for server or VM administration
Elastic Scale:
Database utility; pay as you grow
Flexible load balancing
Business-ready SLAs
Enable multi-tenant solutions
World-wide presence
Developer Agility:
Build cloud-based database solutions on consistent relational model
Leverage existing skills through existing ecosystem of developer and management tools
Explore new data application patterns
Challenges Today:
Promotions, events, ticket selling businesses are “bursts – bound” by nature
Capacity constraints limit business agility
High costs of entry into new business
Difficult to roll out extra capacity quickly
Idle capacity “off-bursts” is cost prohibitive
Solution (SQL AZURE):
Elastic scale – database as a service
Pay as you grow and shrink
Easy to provision and manage database
No hardware, no manual database administration required
Best Integration – Office 2010 & Other Tools.
Focus on combining the best features of SQL Server running at scale with low friction.
Highly scaled out relational database as a service
Relational database service
SQL Server technology foundation
Highly symmetrical
Highly scaled
Database “as a Service” – beyond hosting
Customer Value Props
Self-provisioning and capacity on demand
Symmetry on-premises database platform
Automatic high-availability and fault-tolerance
Automated DB maintenance (infrastructure)
Simple, flexible pricing – “pay as you grow”
Application Topologies:
SQL Azure access from within MS Datacenter (Azure compute) -- Also Know as "Code Near"
SQL Azure Access from outside MS Datacenter (On-premises) -- Also Know as "Code Far"
SQL Azure Access from within and outside MS Datacenter (On-premises & Azure Compute) -- Also Know as "Hybrid"
Partitioning, when do I need it?: (Concept of X (Storage), Y (Transactions) and Z axis from Low to high)
Single Database, No Partitioning
Partitioned Data. Partitioning Based on Application Requirements (Storage)
Partitioned Data, Partitioning based on Application Requirements (IOPS)
Partitioned Data, Partitioning based on Application Requirements (IOPS, Storage or both)
Developing on a local SQL Express instance has some advantages
Easy to get started, you already know how to do it!
Full fidelity with the designer and debugging tools
Reduces latency when using local Azure development tools
Reduces bandwidth and databases costs for development
Some caveats
Remember to alter your VS build settings to switch the connection string when deploying
Use tools (like SQLAzureMW) to keep you within the supported SQL Azure features
Always test in SQL Azure before deploying to production
Connecting to SQL Azure:
SQL Azure connection strings follow normal SQL syntax
Applications connect directly to a database
“Initial Catalog =
No support for context switching (no USE
Some commands must be in their own batch
Create/Alter/Drop Database & Create/Alter/Drop Login, & Create/Alter USER with FOR/FROM LOGIN
Encryption security
Set Encrypt = True, only SSL connections are supported
TrustServerCertificate = False, avoid Man-In-The-Middle-Attack!
Format of username for authentication:
ADO.Net:
Data Source=server.database.windows.net;
User ID=user@server;Password=password;
Setup your firewall rules first!
Resilient Connection Management:
Connections can drop for variety of reasons
Idleness (greater than 30 minutes)
Throttling
Long running transactions > 5 minutes
Resource Management
Database failover
Hardware failure
Load Balancing
Upgrade
What to do on connection failure?
Wait, then retry if it is a transient failure
Change your workload if throttled, i.e. break up your transaction
SELECT * INTO temp tables: (DOES NOT SUPPORT)
SELECT *INTO #Destination FROM Source WHERE [Color] LIKE 'Red‘
To work around this you need to create your destination table then call INSERT INTO. Here is an example:
CREATE TABLE #Destination (Id int NOT NULL, [Name] nvarchar(max), [Color] nvarchar(10))
INSERT INTO #Destination(Id, [Name], [Color])
SELECT Id, [Name], [Color] FROM Source WHERE [Color] LIKE 'Red';
SQL Azure Firewall:
What is it?
A way to restrict access to your database
How does it work?
UI
Programmatic access
Common scenarios
I need my development machine to be able to access it
I need access from my laptop when I am not in the office
I want to restrict access to only my application running in Windows Azure
Data Access APIs:
Supported APIs:
ADO.Net .Net 3.5 SP1 and 4.0
ODBC - SNAC 10
Entity Framework .Net 3.5 SP1 and 4.0
SQL Server 2008 Driver for PHP v1.1
Unsupported:
OleDB
Connection String:
ADO.Net
Encrypt=True and add @servername to User ID
ODBC
Encrypt=yes and add @servername to Uid
Protocols:
Supported:
TCP/IP over port 1433
UnSupported:
Dynamic ports
Named pipes
Shared memory
Authentication Mode:
Supported:
SQL Auth
UnSupported:
Windows Auth
Feature Parity:
Administration Surface:
Physical Server Properties does not apply in SQL Azure
You have a master database but no access to server level constructs such as
sp_configure, endpoints, DBCC commands, server level DMVs and System Views.
Programmability Surface:
Certain Features are partially available today:
USE, XML processing, deprecated T-SQL etc.
List is available here; http://msdn.microsoft.com/en-us/library/ee336267.aspx
Some features are not available today:
Full-text Search, Remote Data Access and Linked Servers, Distributed Transactions,
Change Tracking, Service Broker etc.
List is available here; http://msdn.microsoft.com/en-us/library/ee336253.asp
Migration Assistant for MySql and Access:
Scenario
Auto porting of schema, database code and data from MySql and Access to SQL Azure
SQL Server Migration Assistant for MySql and Access
Supports MySQL 4.1 and up
Support Access v 97 and up
SQL Server versions supported (all editions)
SQL Azure, SQL Server 2005, SQL Server 2008 and 2008 R2
Transferring Large Data:
Best Practices for efficient Data movement to SQL Azure
Use the right tools is key
BCP
SSIS
Network performance considerations
Latency vs Bandwidth
Use the right tools (BCP & Bulk Copy APIs):
Scenario
High speed programmable data import and export
Best Practices
Optimize Databases for Data Import
Disable/Delay creation of none Clustered Indexes
Order Data on the Clustered Index
Disable Triggers and Constraint checking
-N Native mode so no data type conversion needed.
-c Character mode for interoperability
-b batchsize will let you control transaction size
-h”TABLOCK, ORDER(…)” optimize writes
Use the right tools (SSIS – Best of Breed Data Transformation Utility):
SSIS Design Surface - Data Flow Task
Diverse Source and destinations
To/From Flat Files, ADO.Net, OleDB
Fully programmable flow
Loops, Sorts, Conditional operators, XML/WebServices Processing etc
VS Debugging support with data viewers, watches and conditional breakpoints
Best Practices – Data Flow Task
Remember; Optimize Databases for Data Import
Disable/Delay creation of none Clustered Indexes
Order Data on the Clustered Index
Disable Triggers and Constraint checking
Batch Size: Adjust the transaction size
Buffer and Blob temp storage area; spread over to multiple drives for large data transfers
Parallelization – based on execution trees, task will auto parallelize
Use the right tools (Import and Export Wizard):
Scenario
Simplified wizard for migrating schema and data through SSIS
Pros
Great performance out of the box
Allows ‘save as package’ for full control
To know more details please visit and watch the videos: http://www.microsoft.com/en-us/sqlazure/videos/default.aspx
Subscribe to:
Posts (Atom)