Importing and Exporting Data

1. Importing and Exporting Data Importing and Exporting Data Exam 70-432 1 / 30

2. Importing and Exporting Data BulkCopyProgram (BCP) If you are exporting data using BCP, the account that BCP is running under needs only SELECT permissions on the table or view. If you are importing data, the account that BCP is running under needs SELECT, INSERT, and ALTER TABLE permissions.

3. Importing and Exporting Data BCP is a utility that you execute from the command line and has the following syntax: bcp {[[database_name.][owner].]{table_name | view_name} | "query"} {in | out | queryout | format} data_file [-mmax_errors] [-fformat_file] [-x] [-eerr_file] [-Ffirst_row] [-Llast_row] [-bbatch_size] [-n] [-c] [-w] [-N] [-V (60 | 65 | 70 | 80)] [-6] [-q] [-C { ACP | OEM | RAW | code_page } ] [-tfield_term] [-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size] [-Sserver_name[\instance_name]] [-Ulogin_id] [-Ppassword] [-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"] 3 / 30

4. Importing and Exporting Data CAUTION CASE SENSITIVITY All command-line switches for BCP are case-sensitive. For example you use –e to specify anerror fi le, yet –E tells BCP to preserve identity values during an import. Examples: bcpAdventureWorks.HumanResources.Department out c:\test\department.txt -n -SHOTEK –T bcpAdventureWorks.HumanResources.Department in c:\test\department.txt -c -SHOTEK -U<login> -P<password> bcp "SELECT Name, GroupName FROM HumanResources.Department" queryout c:\test\d epartment.txt -n -SHOTEK –T 4 / 30

5. Importing and Exporting Data EXAM TIP The exam tests you on whether you know which import/export option is most appropriate to a givensituation. NOTE ENFORCING CHECK CONSTRAINTS AND TRIGGERS When you import data into a table using BCP, triggers and check constraints are disabled by default. If you want to enforce check constraints and fi re triggers during the import, you need to use the –h switch. If you do not disable triggers and check constraints during an import, you do not need ALTER TABLE permissions. 5 / 30

6. Importing and Exporting Data The BULK INSERT command -BULK INSERT cannot export data. -BULK INSERT is a T-SQL command and does not need to specify the instance name or login credentials. 6 / 30

7. Importing and Exporting Data BULK INSERT [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ] FROM 'data_file' [ WITH ( [ [ , ] BATCHSIZE = batch_size ] [ [ , ] CHECK_CONSTRAINTS ] [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] [ [ , ] DATAFILETYPE = { 'char' | 'native'| 'widechar' | 'widenative' } ] [ [ , ] FIELDTERMINATOR = 'field_terminator' ] [ [ , ] FIRSTROW =first_row] [ [ , ] FIRE_TRIGGERS ] [ [ , ] FORMATFILE = 'format_file_path' ] [ [ , ] KEEPIDENTITY ] [ [ , ] KEEPNULLS ] [ [ , ] KILOBYTES_PER_BATCH =kilobytes_per_batch ] [ [ , ] LASTROW = last_row ] [ [ , ] MAXERRORS = max_errors ] [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ] [ [ , ] ROWS_PER_BATCH = rows_per_batch ] [ [ , ] ROWTERMINATOR = 'row_terminator' ] [ [ , ] TABLOCK ] [ [ , ] ERRORFILE = 'file_name' ] )] 7 / 30

8. Database Configurationand Maintenance EXAM TIP The exam tests you on whether you know which import/export option is most appropriate to a givensituation. NOTE ENFORCING CHECK CONSTRAINTS AND TRIGGERS When you import data into a table using BCP, triggers and check constraints are disabled by default. If you want to enforce check constraints and fi re triggers during the import, you need to use the –h switch. If you do not disable triggers and check constraints during an import, you do not need ALTER TABLE permissions. 8 / 30

9. Importing and Exporting Data The SQL Server Import and Export Wizard Youaccess the wizard by right-clicking a database within Object Explorer, selecting Tasks, and then selecting either Import Data or Export Data. - Import and Export Wizard can use any data source that is recognized by SSIS, such as Microsoft Office Excel, Microsoft Office Access, or Extensible Markup Language (XML) fi les. - Import and Export Wizard supports any data source or destination for which you have an Object Linking and Embedding Database (OLE DB) provider - Import and Export Wizard does not require a SQL Server instance to be either the source or destination - Wizard can move data from multiple tables or fi les in a single operation 9 / 30

10. Importing and Exporting Data ChapterSummary -BCP is a program that allows you to import data from a fi le into a table as well as export data from a table to a fi le. -BULK INSERT is a T-SQL command that allows you to import data from a fi le into a table. -The Import and Export Wizard uses a subset of the SSIS feature set to move data between a source and destination 10 / 30

11. Importing and Exporting Data 1. You want to import data into the Orders table. The table has triggers and check constraints that you want to be checked to guarantee integrity. You choose to use the BCP utility and specify the -h “CHECK_CONSTRAINTS, FIRE_TRIGGERS” hint to accomplish your task. Which of the following permissions must be in place? A. SELECT permission on the Orders table B. ALTER TABLE on the Orders table C. INSERT permission on the Orders table D. A member of the bulkadmin role 11 / 30

12. Importing and Exporting Data 1. Correct Answers: A and C A. Correct: If you are importing data, you need SELECT, INSERT, and ALTER TABLE permissions on the destination. However, if you do not disable triggers and check constraints, you do not need ALTER TABLE permission. B. Incorrect: If you are importing data, you need SELECT, INSERT, and ALTER TABLE permissions on the destination. However, if you do not disable triggers and check constraints, you do not needALTER TABLE permission. C. Correct: If you are importing data, you need SELECT, INSERT, and ALTER TABLE permissions on the destination. However, if you do not disable triggers and check constraints, you do not need ALTER TABLE permission. D. Incorrect: If you are importing data, you need SELECT, INSERT, and ALTER TABLE permissions on the destination. However, if you do not disable triggers and check constraints, you do not needALTER TABLE permission. 12 / 30

13. Importing and Exporting Data 2. You are performing a migration on the Order database at Contoso from Oracle to SQL Server. The Order database contains several hundred tables. The CustomerAddress table has an XML column named AddressBook. What is the most efficient, least intrusive way to move the data to the new SQL Server database? A. Move the Order database from Oracle to SQL Server using replication. B. Unload the data using Oracle utilities and load the data into SQL Server using BCP. C. Move the Order database using the Import and Export Wizard. D. Move the data from Oracle to SQL Server using the OPENROWSET function. 13 / 30

14. Importing and Exporting Data 2. CorrectAnswer: C A. Incorrect: Although it is possible to publish the tables from Oracle to SQL Server, it is more intrusive than using the Import and Export Wizard to move the data. B. Incorrect: You need to code a BCP command for each table that you are importing data into, which is less efficient than using the Import and Export Wizard. C. Correct: The Import and Export Wizard allows you to connect to Oracle, select multiple tables, and move the data to SQL Server in a single operation. D. Incorrect: The OPENROWSET can move a single table at a time, which is less efficient than using the Import and Export Wizard. 14 / 30

15. Designing Policy Based Management DesigningPolicy Based Management Exam 70-432 15 / 30

16. Designing Policy Based Management Policy Based Management introduces the following new objects that are used to design and check for compliance: - Facets - Conditions - Policies - Policy targets - Policy categories 16 / 30

17. Designing Policy Based Management Facets Facets define the type of object or option to be checked, such as database, Surface Area, and login. SQL Server ships with 74 facets get a list of the facets available by querying the dbo.syspolicy_management_facets table 17 / 30

18. Designing Policy Based Management Conditions You defi ne the conditions that you want to check or enforce for a policy by defining criteria for the properties of a facet. =, <>, LIKE, NOT LIKE, IN, or NOT conditionmust be executed manually and cannot be scheduled. 18 / 30

19. Designing Policy Based Management Policy Targets Conditions can also be used to specify the objects to compare the condition against, called policy targeting or target sets. You can target a policy at the server level, such as instances that are SQL Server 2005 or SQL Server 2008. You can also target a policy at the database level, such as all user databases orall system databases 19 / 30

20. Designing Policy Based Management Policies The execution mode can be set as follows: -On demand Evaluates the policy when directly executed by a user -On change, prevent Creates data definition language (DDL) triggers to prevent a change that violates the policy -On change, log only Checks the policy automatically when a change is made using the eventnotificationinfrastructure -On schedule Creates a SQL Server Agent job to check the policy on a defined schedule 20 / 30

21. Designing Policy Based Management Policy Categories Policy categories can be used to group one or more policies into a single compliance unit. If not specified, all policies belong to the DEFAULT category. Each policy category has a Mandate property that applies to databases 21 / 30

22. Designing Policy Based Management Policy Compliance Because you cannot set all policies to enforce compliance, you need to check policies manually that cannot be enforced on a regular basis. You view policies that apply to an instance by right-clicking the name of the instance within Object Explorer and selecting Policies, View You can check policies that apply to an instance by right-clicking the name of the instance within Object Explorer and selecting Policies, Evaluate. 22 / 30

23. Designing Policy Based Management Central Management Server Import and Export Policies SQL Server ships with 53 policies that are located in the Microsoft SQL Server\100\Tools\Policies folder. There are 50 policies for the database engine, 2 policies for Reporting Services, and 1 policy for Analysis Services. 23 / 30

24. Designing Policy Based Management 1. You have defined several policies that you want applied to all databases within an instance. How do you ensure that a database owner is not allowed to avoid the policy check with the least amount of administrative effort? A. Create a condition that checks all databases. B. Add the policy to a user-defined policy category and set the Mandate property. C. Add the policy to the default policy category. D. Check the policies manually against the instance. 24 / 30

25. Designing Policy Based Management 1. CorrectAnswer: C A. Incorrect: Even if you create a condition that checks all databases, a database owner can choose not to subscribe to a policy unless you have mandated compliance. B. Incorrect: While you could create a policy category that has the Mandate property enabled and add the policy to the category, it requires more effort than just adding the policy to the default policy category. C. Correct: The default category is configured with the Mandate property enabled. In addition, you cannot disable the Mandate property. D. Incorrect: While you could check the policies manually, this requires more effort that adding the policy to the default category. 25 / 30

26. Dziękuję za uwagę Tomasz Gacek 26 / 30