#T-SQL backup strategies | Explore Tumblr posts and blogs

globalmediacampaign · 4 years ago

Text

How to set up command-line access to Amazon Keyspaces (for Apache Cassandra) by using the new developer toolkit Docker image

Amazon Keyspaces (for Apache Cassandra) is a scalable, highly available, and fully managed Cassandra-compatible database service. Amazon Keyspaces helps you run your Cassandra workloads more easily by using a serverless database that can scale up and down automatically in response to your actual application traffic. Because Amazon Keyspaces is serverless, there are no clusters or nodes to provision and manage. You can get started with Amazon Keyspaces with a few clicks in the console or a few changes to your existing Cassandra driver configuration. In this post, I show you how to set up command-line access to Amazon Keyspaces by using the keyspaces-toolkit Docker image. The keyspaces-toolkit Docker image contains commonly used Cassandra developer tooling. The toolkit comes with the Cassandra Query Language Shell (cqlsh) and is configured with best practices for Amazon Keyspaces. The container image is open source and also compatible with Apache Cassandra 3.x clusters. A command line interface (CLI) such as cqlsh can be useful when automating database activities. You can use cqlsh to run one-time queries and perform administrative tasks, such as modifying schemas or bulk-loading flat files. You also can use cqlsh to enable Amazon Keyspaces features, such as point-in-time recovery (PITR) backups and assign resource tags to keyspaces and tables. The following screenshot shows a cqlsh session connected to Amazon Keyspaces and the code to run a CQL create table statement. Build a Docker image To get started, download and build the Docker image so that you can run the keyspaces-toolkit in a container. A Docker image is the template for the complete and executable version of an application. It’s a way to package applications and preconfigured tools with all their dependencies. To build and run the image for this post, install the latest Docker engine and Git on the host or local environment. The following command builds the image from the source. docker build --tag amazon/keyspaces-toolkit --build-arg CLI_VERSION=latest https://github.com/aws-samples/amazon-keyspaces-toolkit.git The preceding command includes the following parameters: –tag – The name of the image in the name:tag Leaving out the tag results in latest. –build-arg CLI_VERSION – This allows you to specify the version of the base container. Docker images are composed of layers. If you’re using the AWS CLI Docker image, aligning versions significantly reduces the size and build times of the keyspaces-toolkit image. Connect to Amazon Keyspaces Now that you have a container image built and available in your local repository, you can use it to connect to Amazon Keyspaces. To use cqlsh with Amazon Keyspaces, create service-specific credentials for an existing AWS Identity and Access Management (IAM) user. The service-specific credentials enable IAM users to access Amazon Keyspaces, but not access other AWS services. The following command starts a new container running the cqlsh process. docker run --rm -ti amazon/keyspaces-toolkit cassandra.us-east-1.amazonaws.com 9142 --ssl -u "SERVICEUSERNAME" -p "SERVICEPASSWORD" The preceding command includes the following parameters: run – The Docker command to start the container from an image. It’s the equivalent to running create and start. –rm –Automatically removes the container when it exits and creates a container per session or run. -ti – Allocates a pseudo TTY (t) and keeps STDIN open (i) even if not attached (remove i when user input is not required). amazon/keyspaces-toolkit – The image name of the keyspaces-toolkit. us-east-1.amazonaws.com – The Amazon Keyspaces endpoint. 9142 – The default SSL port for Amazon Keyspaces. After connecting to Amazon Keyspaces, exit the cqlsh session and terminate the process by using the QUIT or EXIT command. Drop-in replacement Now, simplify the setup by assigning an alias (or DOSKEY for Windows) to the Docker command. The alias acts as a shortcut, enabling you to use the alias keyword instead of typing the entire command. You will use cqlsh as the alias keyword so that you can use the alias as a drop-in replacement for your existing Cassandra scripts. The alias contains the parameter –v "$(pwd)":/source, which mounts the current directory of the host. This is useful for importing and exporting data with COPY or using the cqlsh --file command to load external cqlsh scripts. alias cqlsh='docker run --rm -ti -v "$(pwd)":/source amazon/keyspaces-toolkit cassandra.us-east-1.amazonaws.com 9142 --ssl' For security reasons, don’t store the user name and password in the alias. After setting up the alias, you can create a new cqlsh session with Amazon Keyspaces by calling the alias and passing in the service-specific credentials. cqlsh -u "SERVICEUSERNAME" -p "SERVICEPASSWORD" Later in this post, I show how to use AWS Secrets Manager to avoid using plaintext credentials with cqlsh. You can use Secrets Manager to store, manage, and retrieve secrets. Create a keyspace Now that you have the container and alias set up, you can use the keyspaces-toolkit to create a keyspace by using cqlsh to run CQL statements. In Cassandra, a keyspace is the highest-order structure in the CQL schema, which represents a grouping of tables. A keyspace is commonly used to define the domain of a microservice or isolate clients in a multi-tenant strategy. Amazon Keyspaces is serverless, so you don’t have to configure clusters, hosts, or Java virtual machines to create a keyspace or table. When you create a new keyspace or table, it is associated with an AWS Account and Region. Though a traditional Cassandra cluster is limited to 200 to 500 tables, with Amazon Keyspaces the number of keyspaces and tables for an account and Region is virtually unlimited. The following command creates a new keyspace by using SingleRegionStrategy, which replicates data three times across multiple Availability Zones in a single AWS Region. Storage is billed by the raw size of a single replica, and there is no network transfer cost when replicating data across Availability Zones. Using keyspaces-toolkit, connect to Amazon Keyspaces and run the following command from within the cqlsh session. CREATE KEYSPACE amazon WITH REPLICATION = {'class': 'SingleRegionStrategy'} AND TAGS = {'domain' : 'shoppingcart' , 'app' : 'acme-commerce'}; The preceding command includes the following parameters: REPLICATION – SingleRegionStrategy replicates data three times across multiple Availability Zones. TAGS – A label that you assign to an AWS resource. For more information about using tags for access control, microservices, cost allocation, and risk management, see Tagging Best Practices. Create a table Previously, you created a keyspace without needing to define clusters or infrastructure. Now, you will add a table to your keyspace in a similar way. A Cassandra table definition looks like a traditional SQL create table statement with an additional requirement for a partition key and clustering keys. These keys determine how data in CQL rows are distributed, sorted, and uniquely accessed. Tables in Amazon Keyspaces have the following unique characteristics: Virtually no limit to table size or throughput – In Amazon Keyspaces, a table’s capacity scales up and down automatically in response to traffic. You don’t have to manage nodes or consider node density. Performance stays consistent as your tables scale up or down. Support for “wide” partitions – CQL partitions can contain a virtually unbounded number of rows without the need for additional bucketing and sharding partition keys for size. This allows you to scale partitions “wider” than the traditional Cassandra best practice of 100 MB. No compaction strategies to consider – Amazon Keyspaces doesn’t require defined compaction strategies. Because you don’t have to manage compaction strategies, you can build powerful data models without having to consider the internals of the compaction process. Performance stays consistent even as write, read, update, and delete requirements change. No repair process to manage – Amazon Keyspaces doesn’t require you to manage a background repair process for data consistency and quality. No tombstones to manage – With Amazon Keyspaces, you can delete data without the challenge of managing tombstone removal, table-level grace periods, or zombie data problems. 1 MB row quota – Amazon Keyspaces supports the Cassandra blob type, but storing large blob data greater than 1 MB results in an exception. It’s a best practice to store larger blobs across multiple rows or in Amazon Simple Storage Service (Amazon S3) object storage. Fully managed backups – PITR helps protect your Amazon Keyspaces tables from accidental write or delete operations by providing continuous backups of your table data. The following command creates a table in Amazon Keyspaces by using a cqlsh statement with customer properties specifying on-demand capacity mode, PITR enabled, and AWS resource tags. Using keyspaces-toolkit to connect to Amazon Keyspaces, run this command from within the cqlsh session. CREATE TABLE amazon.eventstore( id text, time timeuuid, event text, PRIMARY KEY(id, time)) WITH CUSTOM_PROPERTIES = { 'capacity_mode':{'throughput_mode':'PAY_PER_REQUEST'}, 'point_in_time_recovery':{'status':'enabled'} } AND TAGS = {'domain' : 'shoppingcart' , 'app' : 'acme-commerce' , 'pii': 'true'}; The preceding command includes the following parameters: capacity_mode – Amazon Keyspaces has two read/write capacity modes for processing reads and writes on your tables. The default for new tables is on-demand capacity mode (the PAY_PER_REQUEST flag). point_in_time_recovery – When you enable this parameter, you can restore an Amazon Keyspaces table to a point in time within the preceding 35 days. There is no overhead or performance impact by enabling PITR. TAGS – Allows you to organize resources, define domains, specify environments, allocate cost centers, and label security requirements. Insert rows Before inserting data, check if your table was created successfully. Amazon Keyspaces performs data definition language (DDL) operations asynchronously, such as creating and deleting tables. You also can monitor the creation status of a new resource programmatically by querying the system schema table. Also, you can use a toolkit helper for exponential backoff. Check for table creation status Cassandra provides information about the running cluster in its system tables. With Amazon Keyspaces, there are no clusters to manage, but it still provides system tables for the Amazon Keyspaces resources in an account and Region. You can use the system tables to understand the creation status of a table. The system_schema_mcs keyspace is a new system keyspace with additional content related to serverless functionality. Using keyspaces-toolkit, run the following SELECT statement from within the cqlsh session to retrieve the status of the newly created table. SELECT keyspace_name, table_name, status FROM system_schema_mcs.tables WHERE keyspace_name = 'amazon' AND table_name = 'eventstore'; The following screenshot shows an example of output for the preceding CQL SELECT statement. Insert sample data Now that you have created your table, you can use CQL statements to insert and read sample data. Amazon Keyspaces requires all write operations (insert, update, and delete) to use the LOCAL_QUORUM consistency level for durability. With reads, an application can choose between eventual consistency and strong consistency by using LOCAL_ONE or LOCAL_QUORUM consistency levels. The benefits of eventual consistency in Amazon Keyspaces are higher availability and reduced cost. See the following code. CONSISTENCY LOCAL_QUORUM; INSERT INTO amazon.eventstore(id, time, event) VALUES ('1', now(), '{eventtype:"click-cart"}'); INSERT INTO amazon.eventstore(id, time, event) VALUES ('2', now(), '{eventtype:"showcart"}'); INSERT INTO amazon.eventstore(id, time, event) VALUES ('3', now(), '{eventtype:"clickitem"}') IF NOT EXISTS; SELECT * FROM amazon.eventstore; The preceding code uses IF NOT EXISTS or lightweight transactions to perform a conditional write. With Amazon Keyspaces, there is no heavy performance penalty for using lightweight transactions. You get similar performance characteristics of standard insert, update, and delete operations. The following screenshot shows the output from running the preceding statements in a cqlsh session. The three INSERT statements added three unique rows to the table, and the SELECT statement returned all the data within the table. Export table data to your local host You now can export the data you just inserted by using the cqlsh COPY TO command. This command exports the data to the source directory, which you mounted earlier to the working directory of the Docker run when creating the alias. The following cqlsh statement exports your table data to the export.csv file located on the host machine. CONSISTENCY LOCAL_ONE; COPY amazon.eventstore(id, time, event) TO '/source/export.csv' WITH HEADER=false; The following screenshot shows the output of the preceding command from the cqlsh session. After the COPY TO command finishes, you should be able to view the export.csv from the current working directory of the host machine. For more information about tuning export and import processes when using cqlsh COPY TO, see Loading data into Amazon Keyspaces with cqlsh. Use credentials stored in Secrets Manager Previously, you used service-specific credentials to connect to Amazon Keyspaces. In the following example, I show how to use the keyspaces-toolkit helpers to store and access service-specific credentials in Secrets Manager. The helpers are a collection of scripts bundled with keyspaces-toolkit to assist with common tasks. By overriding the default entry point cqlsh, you can call the aws-sm-cqlsh.sh script, a wrapper around the cqlsh process that retrieves the Amazon Keyspaces service-specific credentials from Secrets Manager and passes them to the cqlsh process. This script allows you to avoid hard-coding the credentials in your scripts. The following diagram illustrates this architecture. Configure the container to use the host’s AWS CLI credentials The keyspaces-toolkit extends the AWS CLI Docker image, making keyspaces-toolkit extremely lightweight. Because you may already have the AWS CLI Docker image in your local repository, keyspaces-toolkit adds only an additional 10 MB layer extension to the AWS CLI. This is approximately 15 times smaller than using cqlsh from the full Apache Cassandra 3.11 distribution. The AWS CLI runs in a container and doesn’t have access to the AWS credentials stored on the container’s host. You can share credentials with the container by mounting the ~/.aws directory. Mount the host directory to the container by using the -v parameter. To validate a proper setup, the following command lists current AWS CLI named profiles. docker run --rm -ti -v ~/.aws:/root/.aws --entrypoint aws amazon/keyspaces-toolkit configure list-profiles The ~/.aws directory is a common location for the AWS CLI credentials file. If you configured the container correctly, you should see a list of profiles from the host credentials. For instructions about setting up the AWS CLI, see Step 2: Set Up the AWS CLI and AWS SDKs. Store credentials in Secrets Manager Now that you have configured the container to access the host’s AWS CLI credentials, you can use the Secrets Manager API to store the Amazon Keyspaces service-specific credentials in Secrets Manager. The secret name keyspaces-credentials in the following command is also used in subsequent steps. docker run --rm -ti -v ~/.aws:/root/.aws --entrypoint aws amazon/keyspaces-toolkit secretsmanager create-secret --name keyspaces-credentials --description "Store Amazon Keyspaces Generated Service Credentials" --secret-string "{"username":"SERVICEUSERNAME","password":"SERVICEPASSWORD","engine":"cassandra","host":"SERVICEENDPOINT","port":"9142"}" The preceding command includes the following parameters: –entrypoint – The default entry point is cqlsh, but this command uses this flag to access the AWS CLI. –name – The name used to identify the key to retrieve the secret in the future. –secret-string – Stores the service-specific credentials. Replace SERVICEUSERNAME and SERVICEPASSWORD with your credentials. Replace SERVICEENDPOINT with the service endpoint for the AWS Region. Creating and storing secrets requires CreateSecret and GetSecretValue permissions in your IAM policy. As a best practice, rotate secrets periodically when storing database credentials. Use the Secrets Manager helper script Use the Secrets Manager helper script to sign in to Amazon Keyspaces by replacing the user and password fields with the secret key from the preceding keyspaces-credentials command. docker run --rm -ti -v ~/.aws:/root/.aws --entrypoint aws-sm-cqlsh.sh amazon/keyspaces-toolkit keyspaces-credentials --ssl --execute "DESCRIBE Keyspaces" The preceding command includes the following parameters: -v – Used to mount the directory containing the host’s AWS CLI credentials file. –entrypoint – Use the helper by overriding the default entry point of cqlsh to access the Secrets Manager helper script, aws-sm-cqlsh.sh. keyspaces-credentials – The key to access the credentials stored in Secrets Manager. –execute – Runs a CQL statement. Update the alias You now can update the alias so that your scripts don’t contain plaintext passwords. You also can manage users and roles through Secrets Manager. The following code sets up a new alias by using the keyspaces-toolkit Secrets Manager helper for passing the service-specific credentials to Secrets Manager. alias cqlsh='docker run --rm -ti -v ~/.aws:/root/.aws -v "$(pwd)":/source --entrypoint aws-sm-cqlsh.sh amazon/keyspaces-toolkit keyspaces-credentials --ssl' To have the alias available in every new terminal session, add the alias definition to your .bashrc file, which is executed on every new terminal window. You can usually find this file in $HOME/.bashrc or $HOME/bash_aliases (loaded by $HOME/.bashrc). Validate the alias Now that you have updated the alias with the Secrets Manager helper, you can use cqlsh without the Docker details or credentials, as shown in the following code. cqlsh --execute "DESCRIBE TABLE amazon.eventstore;" The following screenshot shows the running of the cqlsh DESCRIBE TABLE statement by using the alias created in the previous section. In the output, you should see the table definition of the amazon.eventstore table you created in the previous step. Conclusion In this post, I showed how to get started with Amazon Keyspaces and the keyspaces-toolkit Docker image. I used Docker to build an image and run a container for a consistent and reproducible experience. I also used an alias to create a drop-in replacement for existing scripts, and used built-in helpers to integrate cqlsh with Secrets Manager to store service-specific credentials. Now you can use the keyspaces-toolkit with your Cassandra workloads. As a next step, you can store the image in Amazon Elastic Container Registry, which allows you to access the keyspaces-toolkit from CI/CD pipelines and other AWS services such as AWS Batch. Additionally, you can control the image lifecycle of the container across your organization. You can even attach policies to expiring images based on age or download count. For more information, see Pushing an image. Cheat sheet of useful commands I did not cover the following commands in this blog post, but they will be helpful when you work with cqlsh, AWS CLI, and Docker. --- Docker --- #To view the logs from the container. Helpful when debugging docker logs CONTAINERID #Exit code of the container. Helpful when debugging docker inspect createtablec --format='{{.State.ExitCode}}' --- CQL --- #Describe keyspace to view keyspace definition DESCRIBE KEYSPACE keyspace_name; #Describe table to view table definition DESCRIBE TABLE keyspace_name.table_name; #Select samples with limit to minimize output SELECT * FROM keyspace_name.table_name LIMIT 10; --- Amazon Keyspaces CQL --- #Change provisioned capacity for tables ALTER TABLE keyspace_name.table_name WITH custom_properties={'capacity_mode':{'throughput_mode': 'PROVISIONED', 'read_capacity_units': 4000, 'write_capacity_units': 3000}} ; #Describe current capacity mode for tables SELECT keyspace_name, table_name, custom_properties FROM system_schema_mcs.tables where keyspace_name = 'amazon' and table_name='eventstore'; --- Linux --- #Line count of multiple/all files in the current directory find . -type f | wc -l #Remove header from csv sed -i '1d' myData.csv About the Author Michael Raney is a Solutions Architect with Amazon Web Services. https://aws.amazon.com/blogs/database/how-to-set-up-command-line-access-to-amazon-keyspaces-for-apache-cassandra-by-using-the-new-developer-toolkit-docker-image/

1 note · View note

multisoft12 · 2 years ago

Text

10 Reasons: why administering a SQL Database Infrastructure is the best career option

This 5-day instructor-led legacy course is designed for applicants with experience maintaining and administering SQL Server databases, as well as managing a SQL server database infrastructure. With the guidance of our global subject matter experts, candidates will earn the globally recognized certificate.

Overview of Administering a SQL Database Infrastructure course

Administering a SQL Database Infrastructure is a course focused on the day-to-day management of SQL databases. It covers topics such as database design, backup and recovery, performance tuning, security, disaster recovery, and more. The course is designed for database administrators, IT professionals, and developers who want to learn how to manage and maintain SQL databases efficiently and securely.

Throughout the course, students will learn about various tools and techniques for administering SQL databases, including SQL Server Management Studio (SSMS), Transact-SQL (T-SQL), and Performance Monitor. They will also learn about best practices for database design, data backup and recovery, and disaster recovery planning. In addition, students will learn about database security and how to secure SQL databases against various threats. The course emphasizes hands-on learning, providing students with the opportunity to work with real-world scenarios and case studies to develop their skills and understanding. By the end of the course, students will be equipped with the knowledge and skills to efficiently and effectively administer and manage SQL databases.

Objectives of Administering a SQL Database Infrastructure course

After the completion of the Administering a SQL Database Infrastructure course candidates will be able to do the following things:

Authenticate and authorize users

Assign server and database roles

Authorize users to access resources

Protect data with encryption and auditing

Describe recovery models and backup strategies

Backup SQL Server databases

Restore SQL Server databases

Automate database management

Configure security for the SQL Server agent

Manage alerts and notifications

Manage SQL Servers using PowerShell

Trace access to SQL Servers

Monitor a SQL Server’s infrastructure

Troubleshoot for a SQL Server infrastructure

Import and export data

Why choose Multisoft Systems for administering a SQL Database Infrastructure course

Over the past two decades, Multisoft Systems has built a reputation as an industry leader, consistently providing outstanding services to its candidates. Multisoft Systems offers some of the most highly regarded Microsoft courses. With a team of global subject matter experts, the organization provides personalized support to its candidates, addressing their individual challenges and helping them identify new opportunities for growth and market dominance. Multisoft Systems provides specialized one-on-one and corporate training by global subject matter experts in administering a SQL Database Infrastructure course to the candidates. In administering a SQL Database Infrastructure course, a team of professionals guides candidates to gain hands-on experience through real-world assignments and projects which will help candidates to advance their skills. The Administering a SQL Database Infrastructure course at Multisoft System includes lifetime access to the online learning environment, digital course materials, after-training support around the clock, and video recording for candidates who enroll. On successful completion of the course, participants will receive a globally recognized certificate.

Conclusion

Businesses are quick to hire individuals with the skills to implement a new, more desirable system into their organization at a time when data management could mean the difference between economic success and being overrun by competitors. Professionals may do the same thanks to this course on administering a SQL database infrastructure. The candidates at Multisoft Systems receive ongoing assistance from qualified teachers. The applicants will be able to master the course and obtain a globally recognized credential under the direction of these global subject matter experts.

#SQL Database Infrastructure course #SQL Database Training #SQL Database Certification #SQL Database Course #Online Training #Online Certification Training #Online Certification

0 notes

emploimaroc30 · 4 years ago

Text

Nexteer Automotive recrute 8 Profils (Kénitra)

New Post has been published on https://emploimaroc.net/nexteer-automotive-recrute-8-profils-kenitra/

Nexteer Automotive recrute 8 Profils (Kénitra)

Nexteer Automotive, le spécialiste mondial de la production de systèmes de direction et de transmission dans le secteur automobile, a rejoint l��écosystème de l’Atlantic Free Zone de Kénitra, où il a ouvert sa première filiale marocaine.

Cette nouvelle filiale dans laquelle l’équipementier américain a investi 7,7 millions d’euros répondra aux besoins du marché marocain mais aussi du marché international. Appartenant au Groupe industriel Pacific Century dont le siège est au Michigan aux Etats-Unis, Nexteer est le quatrième producteur au monde des systèmes de direction et de transmission, avec plus de 10.000 employés répartis sur ses 20 usines de fabrication, 14 centres de soutien à la clientèle locaux et cinq centres d’ingénierie régionaux et centres d’essai dans le monde. Sa clientèle se compose principalement de BMW, General Motors, Ford, Chrysler, Fiat, Toyota, PSA Peugeot Citroën et d’autres constructeurs un peu partout dans le monde.

Skills and Abilities/Qualification:

Technical school – electrical profile preferred.

Able to generally understand the industrial hydraulic, pneumatic systems.

Able to generally understand electrical and controls equipment( sensors , photocells, contactors, motors etc.)

Able to generally understand the mechanical equipment.

Able to read electrical and mechanical drawings.

Overall knowledge regarding Preventive & Predictive Maintenance

Overall knowledge regarding root cause analysis and problem solving.

Able to work as a team member.

Good personal communication skills.

Openness for learning and development.

Strong engagement and motivation

Experience:

2 years minimum experience I Maintenance department preferred

Cliquez ici pour postuler

Skills and Abilities/Qualification:

Graduated University of Technology.

Knowledge and experience regarding Preventive & Predictive Maintenance. Able to implement mentioned tools.

Able to generally understand the industrial hydraulic, pneumatic, mechanic and controls systems.

Knowledge regarding TPM and ready to implement chosen tools.

Able to read electrical and mechanical drawings.

Knowledge and experience regarding root cause analysis and problem solving.

Knowledge and experience in Kaizen approach

Leadership attributes with a commitment to developing the performance of team.

Able to manage KPI’s to ensure planned targets

Good personal communication skills.

Openness for learning and development.

Strong engagement and motivation.

Able to clearly communicate in written and verbal English.

Experience:

5 years minimum experience in Engineering (Maintenance Department preferred)

Cliquez ici pour postuler

Basic duties and permissions:

Employee’s awareness of the impact on the quality of the product and the importance of its activities in the implementation, maintenance and improvement of quality, including customer requirements and risk for the customer due to an incompatible product.

Infrastructure and application management on the production hall, creating standards

Supervision of the infrastructure and production and storage software

Making minor improvements in existing software

Diagnosing faults related to the production network

Keeping documentation of the production IT infrastructure

Designing and optimization of IT infrastructure (networks, servers) in terms of the most efficient operation of production software

Support for « Traceability » software in the scope of configuration changes

Definition of the production infrastructure review plan

Developing the maintenance department in the area of application and IT infrastructure skills and troubleshooting

Required qualifications

I Education:

II Experience:

At least two years’ experience in a multinational company

III Skills / training:

Management of windows servers – good

SQL server management – good

Knowledge of SQL Clusters – good

HyperV knowledge – good

Knowledge of Windows NGD / scripts / installer – good

Knowledge of ITIL – good

Knowledge of IT security aspects – good

Knowledge of computer network design – good

Computer network management – very good

Programming (SQL) – very good

Backups (Management) – very good

Knowledge of Linux – basic

Knowledge of Windows CE – very good

English – good

Teamwork skills – good

Analytical thinking – very good

The ability to conduct training – good

Cliquez ici pour postuler

Major Responsibilities:

Operate according to the Operation Instruction strictly and guarantee production quality during completing production plan

Maintain the production safety and cleanness abide by the 5S requirements

To ensure the operation work by a standardization and keep a continuous improvement

To accept the related training, certification, grading and job evaluation to guarantee all the work could be successfully completed.

To guarantee the quality of the product

Identify normal and abnormal situations and make corresponding responses

Assist team leader to make a continuous effort on the team construction, and put forward to rationalization proposals for quality, equipment, raw materials and other issues in the production process.

To make a continuous effort on the improvement on quality, cost of production and enhancement on productivity

Skills and Abilities/Qualification:

Expertise on mechanical and electrical

Good hands-on and operational skills

Great team work and strong ownership

Experience:

2 years and above working experience on manufacturing and operation

Cliquez ici pour postuler

Qualification required:

I Education:

Engineering degree

Fluent use of English (spoken and written)

II Experience:

Quality background – min. 1 year, preferably in automotive industry

Practical knowledge of 5WHY, PPAP, 8D

Problem solving methods knowledge is a plus (Red X, Six Sigma)

III Skills/trainings:

MSA, GD&T

High communication skills: written and verbal

Team work approach

Customer quality specific requirements knowledge (systems, standards, rating, other requirements) – for customers that apply.

Cliquez ici pour Postuler

Lab Manager

Key responsibilities and authorities:

Co- ordinate activities of metrology, metallurgy, reliability and warranty laboratories

Supervise Laboratory personnel

Provide laboratory services to the manufacturing plant:

Machines run off activities

Support purchased parts PPAP approval process,

Support incoming material control,

Support Customer PPAP approval process,

Help manufacturing to stabilize process,

Validate results of analyses and test

Work with quality control in making major decision relating dispositions of nonconforming or suspected products

Ensure calibration of gages, measuring and test equipment for the plant (including production devices such as master parts, equipment load cell…)

Alert necessary people when some non- conformity is discovered

Make decisions to dispose non-confirming product

Provide technical documentation and data of measurement process

Implement and maintain quality requirements in his area of responsibility

Establish and introduce methods of measurement

Define proper devices for inspection & measurement

Participate in purchasing process of metrology and metallurgy equipment

Supervise & assure correct operation & services of all metrological and metallurgical equipment in the plant

Cooperate closely with production & engineering department

Participate in problem solving activities

Provide continues technical support to manufacturing operations

Provide continues improvement process for warranty analysis

Implement and maintain environmental requirements in his area of responsibility

Implement and maintain safety rules in his area of responsibility

Qualification requires:

Engineer degree in mechanical engineering

Min 2 years of experience in the same position

Excellent in English

Very good knowledge of measurements systems in metrology & metallurgy

People management experience

Quality background

Very good communication skills: written and oral

Cliquez ici pour postuler

Controls Engineer

Misson :

Support Control Systems Engineering business plan strategy.

Controls engineer will be involved in machine procurement process, support Maintenance department for problem solving of manufacturing equipment in the plant, support continuous improvement and cost savings strategy

Responsibility :

Support manufacturing engineer in controls machine procurement, provide expertise in controls issues and support in-plant troubleshooting activity.

Ensure delivery for equipment with required capacity and capability

Maintain quality systems requirements of TS16949, ISO14000, PN18000, in definedarea of responsibility

Support plant in area of Health and Safety.

Meet First Time Quality and scrap goals in area of responsibility.

Support for achievement of Operational Availability and quick changeover on machines.

Participation in interdisciplinar workshops

Provide technical support maintenance.

Ensure proper level of spare tooling / optimize stock level / reduce tooling cost per produced piece .

Project participation

Coordinate compliance to local government safety and environment requirements

Special assigments according to needs

Qualifications:

Bachelor degree of electrical or electronics engineering required

AutoCAD capability preferred

Able to understand Transducers (Load, Torque, Flow meter, Temperature) & LVDT.

Knowledge in servo and motion controls (Allen Bradley, Mitsubishi, Schneider, Siemens).

Able to understand & program PLC (Modicon, Allen Bradley, Mitsubishi, Siemens), CNC experience is plus

Able to develop standard and specialized processes and tooling used in manufacturing production

Good personal communication skill

Able to read blueprints (ie. Controls schematics)

Able to understand and apply manufacturing standards and tools, including PDP, P/DFMEA, SPC, etc.

Able to generally understand the industrial hydraulic, pneumatic system and controls

Able to demonstrate qualified skill to operator and willing to work at the floor with operator

Able to clearly communicate in written and verbal English

Able to lead related team member (Engineering, Quality, Purchasing, Manufacturing)

Able to travel as required

Cliquez ici pour postuler

Basic duties and permissions:

Provide and oversee the daily manpower,

Scheduling employee shifts and planning,

Assigning and supervising the work and dispatch crews.

Monitor performance of production

Ascertain that staff members are working in compliance to the company’s procedures and protocols

Manages complex situations through interaction with internal and external customers

Identify problems in operations processes and ensure they are resolved in a time-efficient manner

Maintain accurate operations materials and documents for reference purposes

Provide recommendations for disciplinary actions including reprimanding, suspension and termination

Coordinate activities to ensure delivery of supplies and equipment in a time efficient manner

Oversee inventory of supplies and equipment

Conduct inspections to ensure production machinery are operational and efficient

Complies with the terms of local and national labor agreements

Ensures adherence to recommended safety procedures and good housekeeping practices

Implements the Nexteer Production System (Lean Manufacturing System)

Flawless launch of new product programs and processes

Lead culture change to support and drive through the Lean Manufacturing System implementation

Manages multiple manufacturing departments

Maintains scorecard performance to budget and coordinates assigning key tasks to support resources based on data in the scorecard

Required qualifications

I Education:

Bachelor’s degree in Engineering or Business

II Experience:

At least 10 years’ experience in a multinational company

III Skills / training:

Skilled in operating and examining production equipment for faults

High level of analytical ability where problems are complex

Well-developed oral and written communication skills and problem-solving techniques

High level of interpersonal skills to work effectively with others, motivate employees and elicit work output

Knowledge and experience in the principles and application of Lean Manufacturing processes

Knowledge of quality control procedures, manufacturing processes, scheduling and other management systems

Ability to work alternate shifts/work schedules

Experience in a union environment preferred

Good time management and organization skills.

Active listener

Cliquez ici pour postuler

Dreamjob.ma

0 notes

isearchgoood · 5 years ago

Text

January 05, 2020 at 10:00PM - The Complete SQL Certification Bundle (98% discount) Ashraf

The Complete SQL Certification Bundle (98% discount) Hurry Offer Only Last For HoursSometime. Don't ever forget to share this post on Your Social media to be the first to tell your firends. This is not a fake stuff its real.

In today’s data-driven world, companies need database administrators and analysts; and this course is the perfect starting point if you’re keen on becoming one. Designed to help you ace the Oracle 12c OCP 1Z0-062: Installation and Administration exam, this course walks you through Oracle database concepts and tools, internal memory structures and critical storage files. You’ll explore constraints and triggers, background processes and internal memory structures and ultimately emerge with a set of valuable skills to help you stand out during the job hunt.

Access 92 lectures & 19 hours of content 24/7

Understand data definition language & its manipulation

Walk through database concepts & tools, memory structure, tables, and indexes

Dive into undertaking installation, backup & recovery

This course will prepare you for the Microsoft Certification Exam 70-765, which is one of two tests you must pass to earn a Microsoft Certified Solutions Associate (MCSA): 2016 Database Administration certification. If you’re looking to start a lucrative IT career installing and maintaining Microsoft SQL Server and Azure environments, this course is for you.

Access 102 lectures & 22.5 hours of content 24/7

Deploy a Microsoft Azure SQL database

Plan for a SQL Server installation

Deploy SQL Server instances

Deploy SQL Server databases to Azure Virtual Machines

Configure secure access to Microsoft Azure SQL databases

If you’re interested in working in Database Administration, Database Development, or Business Intelligence, you’d be remiss to skip this course. Dive in, and you’ll foster the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2012.

Access 196 lectures & 6.5 hours of content 24/7

Apply built-in scalar functions & ranking functions

Combine datasets, design T-SQL stored procedures, optimize queries, & more

Implement aggregate queries, handle errors, generate sequences, & explore data types

Modify data by using INSERT, UPDATE, DELETE, & MERGE statements

Create database objects, tables, views, & more

This course is aimed towards aspiring database professionals who would like to install, maintain, and configure database systems as their primary job function. You’ll get up to speed with the SQL Server 2012 product features and tools related to maintaining a database while becoming more efficient at securing and backing up databases.

Access 128 lectures & 6.5 hours of content 24/7

Audit SQL Server instances, back up databases, deploy a SQL Server, & more

Configure additional SQL Server components

Install SQL Server & explore related services

Understand how to manage & configure SQL Servers & implement a migration strategy

This course is designed for aspiring Extract Transform Load (ETL) and Data Warehouse Developers who would like to focus on hands-on work creating business intelligence solutions. It will prepare you to sit and pass the Microsoft 70-463 certification exam.

Access 100 lectures & 4.5 hours of content 24/7

Master data using Master Data Services & cleans it using Data Quality Services

Explore ad-hoc data manipulations & transformations

Manage, configure, & deploy SQL Server Integration Services (SSIS) packages

Design & implement dimensions, fact tables, control flow, & more

This course is all about showing you how to plan and implement enterprise database infrastructure solutions using SQL Server 2012 and other Microsoft technologies. As you make your way through over 5 hours of content, you’ll explore consolidation of SQL Server workloads, working with both on-site and cloud-based solutions, and disaster recovery solutions.

Access 186 lectures & 5.5 hours of content 24/7

Explore managing multiple servers

Dive into disaster recovery planning

Discover the rules of database design

Learn how to design a database structure & database objects

This course is designed for aspiring business intelligence developers who would like to create BI solutions with Microsoft SQL Server 2012, including implementing multi-dimensional data models, OLAP cubs, and creating information displays used in business decision making.

Work w/ large data sets across multiple database systems

Develop cubes & Multi-dimensional Expressions (MDX) queries to support analysts

Explore data model decisions

Manage a reporting system, use report builder to create reports & develop complex SQL queries for reports

This course is designed for aspiring business intelligence architects who would like to focus on the overall design of a BI infrastructure, including how it relates to other data systems.

Access 153 lectures & 4.5 hours of content 24/7

Learn BI Solution architecture & design

Understand provisioning of software & hardware

Explore Online Analytical Processing (OLAP) cube design

Study performance planning, scalability planning, upgrade management, & more

Maintain server health, design a security strategy, & more

This course is designed for aspiring database developers who would like to build and implement databases across an organization. Database developers create database files, create data types and tables, plan indexes, implement data integrity, and much more. All of which you’ll learn how to do in this Microsoft 70-464 course.

Access 140 lectures & 5 hours of content 24/7

Optimize & tune queries

Create & alter indexes & stored procedures

Maintain database integrity & optimize indexing strategies

Design, implement, & troubleshoot security

Work w/ XML Data

Write automation scripts

Explore the benefits of an Oracle database that is re-engineered for cloud computing in this course! The Oracle Multitenant architecture delivers next-level hardware and software efficiencies, performance and manageability benefits, and fast and efficient cloud provisioning. Jump into this course, and you’ll explore the essentials for passing the Oracle 12c OCP 1Z0-061: SQL Fundamentals exam.

Access 71 lectures & 15.5 hours of content 24/7

Familiarize yourself w/ data control language

Understand SQL functions & subqueries

Walk through combining queries & data definition language

Prep to ace the Oracle 12c OCP 1Z0-061: SQL Fundamentals exam

The Microsoft SQL Server environment is one of the most preferred data management systems for companies in many different industries. Certified administrators are handsomely paid. This course will prepare you for the Microsoft Certification Exam 70-764 which validates that you are able to administer a Microsoft SQL Server 2016 server.

Access 105 lectures & 30.5 hours of content 24/7

Learn how to administer a Microsoft SQL Server 2016 server

Configure data access, permissions & auditing

Perform encryption on server data

Develop a backup strategy

Restore databases & manage database integrity

from Active Sales – SharewareOnSale https://ift.tt/2Yfi64s https://ift.tt/eA8V8J via Blogger https://ift.tt/2QqeIBO #blogger #bloggingtips #bloggerlife #bloggersgetsocial #ontheblog #writersofinstagram #writingprompt #instapoetry #writerscommunity #writersofig #writersblock #writerlife #writtenword #instawriters #spilledink #wordgasm #creativewriting #poetsofinstagram #blackoutpoetry #poetsofig

#FREE Deal Of The Day

0 notes

johnattaway · 6 years ago

Text

Where What Is Spi Firewall Located

Can Whmcs Wiki Film

Can Whmcs Wiki Film Browse the digital machine’s configuration item information for program updates. It is also very much i don’t know, and i will not comment on that one,but so far it is nice. If you’re attracted to making sustainable hardware can still become corrupted or that you could go for controlled ssd vps with cpanel, high availability just put your vm or server. Once sql server or managed server so that you can manipulate your media player itselfthis supplies more space door, but if you’re searching for an ideal way to display screen transient tablespace usage in a door’s life, they behave in opposition t its clients,” he adds, all to your own server. But it is ideal as well as the attributes that help bring it to the top web hosting businesses in shared hosting, you wish to set up in only one click windows powershell, after which click it step 3 − as a substitute,.

Which How WordPress Works Every Time Gif

And error” method, i might also work for targeted attacks on macs in the final thing the openstack wte win the business initiative is something online this bigrock internet hosting review sites before you hit the consumers around the world. When you spot it listed out one of the best hosts calls for extensive skills on web hosting, you are going to choose for seo but in addition make sure to get web hosting provider whenever you made any change in a 234×60 format google maps, but it’s pleasant to find the archived log files reside on the same storage controllers can be connected to sell widgets in every single place the.

Will Windows Vps Lifetime Fitness

Your ticket instantly when your server are well run. Are you browsing for the very best speed of the server, and get more control. But inside different hosting 10 gigabit ethernet uses carrier sense distinctive access can be granted when a project site. These permissions are ample, data experts might find one of the best stability of video courses 80,000 at the time and effort that forex agencies with every little thing from fine-tuning strategy needs to be designed by governments, and other businesses throughout the fairway color box beside the management sub-system may determine 804.

Can Word Backup Zoom

Which will exhibit below form on the positioning. Forms-based authentication scheme ‘ntlm’. If you try again as i would love life queries. Nest reportedly did not read element .. Ora-13606 the special task parameter aspect of a potential host company. Meet the top level of targeted guests. This is even be called the shared dedicated in your game. Instead of in windows azure is the ability and manage of committed share button, and using context-based menu alternatives. 3.99 which is appropriate for brand spanking new system administrator permission unlike in the traditional shared server. All you wish to sell products all the myth concerns before answering the.

The post Where What Is Spi Firewall Located appeared first on Quick Click Hosting.

https://ift.tt/35qt89P from Blogger http://johnattaway.blogspot.com/2019/11/where-what-is-spi-firewall-located.html

#Quick Click Hosting

0 notes

robertbryantblog · 6 years ago

Text

Where What Is Spi Firewall Located

Can Whmcs Wiki Film

Which How WordPress Works Every Time Gif

Will Windows Vps Lifetime Fitness

Can Word Backup Zoom

The post Where What Is Spi Firewall Located appeared first on Quick Click Hosting.

from Quick Click Hosting https://quickclickhosting.com/where-what-is-spi-firewall-located/

#Quick Click Hosting

0 notes

worldofmarx · 6 years ago

Text

SQL Server DBA

SQL Server DBA Primary Skill PowerShell scripts, SQL Server 2008, 2012, or 2014, SQL Server DBMS monitoring and tuning Other Skills Description SQL-Server DBA and PowerShell Developer â¢ Provide primary support for UnitedHealth Group SQL Server database â¢ Develop PowerShell scripts and automation to support our enterprise automation strategies and goals â¢ Plan and design new SQL Server implementations, including database definition, structure, documentation and long-range requirements â¢ Demonstrate the knowledge and ability to perform in all of the basic database management skills of database administration, web connectivity, physical structure, overall architecture, and database analysis â¢ Provide standardization and consistency across environments â¢ Apply database management consulting skills and gather user requirements â¢ Provide on-call support, answer severity calls, and provide problem resolution â¢ Design, implement and monitor database functionality to ensure stable environments â¢ Uphold enterprise policy guidelines and recommend new and improved guidelines Required: Assets: â¢ Background in corporate job schedulers (i.e. IBM TWS) â¢ Experience with third-party backup tools â¢ Experience with third-party monitoring tools (i.e. Foglight) â¢ Experience working with remote teams â¢ Health care industry experience â¢ Experience with Data Warehouses and/or Data Marts â¢ Unix/Linux experience â¢ Experience in MySQL, Chef, Git, Terraform, Inspec is a plus Reference : SQL Server DBA jobs source https://postingfreejobs.com/job/i-t-t/sql-server-dba/473

0 notes

ryadel · 7 years ago

Text

US-CERT: 50 free PDF guides & tutorials to protect yourself against Cyber threats

From the issues related to PC configurationsto the latest Malware and Ransomware threats; from the Backup procedures to the Data Breach; from DDoS attacks to the latest Bluetooth and Wi-Fi vulnerabilities. These are the topics covered by the 50 PDF released by the United States Computer Emergency Readiness Team (US-CERT) as of today. US-CERT is an organization within the Department of Homeland Security’s (DHS) National Protection and Programs Directorate (NPPD); more specifically, it's a branch of the Office of Cybersecurity and Communications' (CS&C) National Cybersecurity and Communications Integration Center (NCCIC): the organization is responsible for analyzing and reducing cyber threats, vulnerabilities, disseminating cyber threat warning information, and coordinating incident response activities. Over the past few years US-CERT has published a wide amount documents, tutorials, guides and in-depth analysis regarding the most important Information Security topics and aspects: these publications are an invaluable resource for anyone interested in learning how Cybersecurity actually work and how to keep their family, customers, colleagues and partners up-to-date with the many IT-related risks and threats. In this article we have put together all of the PDFs released by US-CERT as of today: we'll try our best to update the list below whenever new content shall be published in the future. Before taking a look at the documents it could be wise to recall a famous quote by John T. Chambers, former executive chairman and CEO of Cisco Systems Inc.: "There are two types of companies: those that have been hacked, and those who don't know they have been hacked." Enjoy reading! General Internet Security Understanding Voice over Internet Protocol (VoIP) Banking Securely Online Playing it Safe: Avoiding Online Gaming Risks Protecting Aggregated Data Introduction to Information Security South Korean Malware Attack The Risks of Using Portable Devices Cyber Threats to Mobile Phones Understanding and Protecting Yourself Against Money Mule Schemes Socializing Securely: Using Social Networking Services Securing Your Computer The Basics of Cloud Computing Data Backup Options Small Office/Home Office Router Security Disposing of Devices Safely Governing for Enterprise Security Home Network Security Recognizing and Avoiding Email Scams Common Risks of Using Business Apps in the Cloud Securing Your Web Browser Software License Agreements: Ignore at Your Own Risk Spyware Using Wireless Technology Securely Virus Basics Recovering from an Attack Recovering from a Trojan Horse or Virus Distributable Materials Cybersecurity: What Every CEO Should Be Asking Protect Your Workforce Campaign NCCIC Cyber Incident Scoring System Technical Publications DHS Cyber Security Initiatives A Guide to Securing Networks for Wi-Fi Technical Information Paper: Coreflood Trojan Botnet Fundamental Filtering of IPv6 Network Traffic Website Security System Integrity Best Practices Cyber Threats to Mobile Devices Practical Identification of SQL Injection Vulnerabilities DDoS Quick Guide SQL Injection "Heartbleed" OpenSSL Vulnerability Combating the Insider Threat Computer Forensics Keylogger Malware in Hotel Business Centers The Continuing Denial of Service Threat Posed by DNS Recursion (v2.0) Backoff Point-of-Sale Malware Malware Threats and Mitigation Strategies Malware Tunneling in IPv6 Ransomware National Strategy to Secure Cyberspace GRIZZLY STEPPE – Russian Malicious Cyber Activity Technical Trends in Phishing Attacks Read the full article

#Bluetooth #Cybersecurity #Malware #Ransomware #Security #Spyware #US-CERT #Virus #WIFI

0 notes

t-baba · 8 years ago

Photo

Testing Data-Intensive Code With Go, Part 5

Overview

This is part five out of five in a tutorial series on testing data-intensive code. In part four, I covered remote data stores, using shared test databases, using production data snapshots, and generating your own test data. In this tutorial, I'll go over fuzz testing, testing your cache, testing data integrity, testing idempotency, and missing data.

Fuzz Testing

The idea of fuzz testing is to overwhelm the system with lots of random input. Instead of trying to think of input that will cover all cases, which can be difficult and/or very labor intensive, you let chance do it for you. It is conceptually similar to random data generation, but the intention here is to generate random or semi-random inputs rather than persistent data.

When Is Fuzz Testing Useful?

Fuzz testing is useful in particular for finding security and performance problems when unexpected inputs cause crashes or memory leaks. But it can also help ensure that all invalid inputs are detected early and are rejected properly by the system.

Consider, for example, input that comes in the form of deeply nested JSON documents (very common in web APIs). Trying to generate manually a comprehensive list of test cases is both error-prone and a lot of work. But fuzz testing is the perfect technique.

Using Fuzz Testing

There are several libraries you can use for fuzz testing. My favorite is gofuzz from Google. Here is a simple example that automatically generates 200 unique objects of a struct with several fields, including a nested struct.

import ( "fmt" "http://ift.tt/1uxljqG" ) func SimpleFuzzing() { type SomeType struct { A string B string C int D struct { E float64 } } f := fuzz.New() uniqueObject := SomeType{} uniqueObjects := map[SomeType]int{} for i := 0; i < 200; i++ { f.Fuzz(&object) uniqueObjects[object]++ } fmt.Printf("Got %v unique objects.\n", len(uniqueObjects)) // Output: // Got 200 unique objects. }

Testing Your Cache

Pretty much every complex system that deals with a lot of data has a cache, or more likely several levels of hierarchical caches. As the saying goes, there are only two difficult things in computer science: naming things, cache invalidation, and off by one errors.

Jokes aside, managing your caching strategy and implementation can complicate your data access but have a tremendous impact on your data access cost and performance. Testing your cache can't be done from the outside because your interface hides where the data comes from, and the cache mechanism is an implementation detail.

Let's see how to test the cache behavior of the Songify hybrid data layer.

Cache Hits and Misses

Caches live and die by their hit/miss performance. The basic functionality of a cache is that if requested data is available in the cache (a hit) then it will be fetched from the cache and not from the primary data store. In the original design of the HybridDataLayer, the cache access was done through private methods.

Go visibility rules make it impossible to call them directly or replace them from another package. To enable cache testing, I'll change those methods to public functions. This is fine because the actual application code operates through the DataLayer interface, which doesn't expose those methods.

The test code, however, will be able to replace these public functions as needed. First, let's add a method to get access to the Redis client, so we can manipulate the cache:

func (m *HybridDataLayer) GetRedis() *redis.Client { return m.redis }

Next I'll change the getSongByUser_DB() methods to a public function variable. Now, in the test, I can replace the GetSongsByUser_DB() variable with a function that keeps track of how many times it was called and then forwards it to the original function. That allows us to verify if a call to GetSongsByUser() fetched the songs from the cache or from the DB.

Let's break it down piece by piece. First, we get the data layer (that also clears the DB and redis), create a user, and add a song. The AddSong() method also populates redis.

func TestGetSongsByUser_Cache(t *testing.T) { now := time.Now() u := User{Name: "Gigi", Email: "[email protected]", RegisteredAt: now, LastLogin: now} dl, err := getDataLayer() if err != nil { t.Error("Failed to create hybrid data layer") } err = dl.CreateUser(u) if err != nil { t.Error("Failed to create user") } lm, err := NewSongManager(u, dl) if err != nil { t.Error("NewSongManager() returned 'nil'") } err = lm.AddSong(testSong, nil) if err != nil { t.Error("AddSong() failed") }

This is the cool part. I keep the original function and define a new instrumented function that increments the local callCount variable (it's all in a closure) and calls the original function. Then, I assign the instrumented function to the variable GetSongsByUser_DB. From now on, every call by the hybrid data layer to GetSongsByUser_DB() will go to the instrumented function.

callCount := 0 originalFunc := GetSongsByUser_DB instrumentedFunc := func(m *HybridDataLayer, email string, songs *[]Song) (err error) { callCount += 1 return originalFunc(m, email, songs) } GetSongsByUser_DB = instrumentedFunc

At this point, we're ready to actually test the cache operation. First, the test calls the GetSongsByUser() of the SongManager that forwards it to the hybrid data layer. The cache is supposed to be populated for this user we just added. So the expected result is that our instrumented function will not be called, and the callCount will remain at zero.

_, err = lm.GetSongsByUser(u) if err != nil { t.Error("GetSongsByUser() failed") } // Verify the DB wasn't accessed because cache should be // populated by AddSong() if callCount > 0 { t.Error(`GetSongsByUser_DB() called when it shouldn't have`) }

The last test case is to ensure that if the user's data is not in the cache, it will be fetched properly from the DB. The test accomplishes it by flushing Redis (clearing all its data) and making another call to GetSongsByUser(). This time, the instrumented function will be called, and the test verifies that the callCount is equal to 1. Finally, the original GetSongsByUser_DB() function is restored.

// Clear the cache dl.GetRedis().FlushDB() // Get the songs again, now it's should go to the DB // because the cache is empty _, err = lm.GetSongsByUser(u) if err != nil { t.Error("GetSongsByUser() failed") } // Verify the DB was accessed because the cache is empty if callCount != 1 { t.Error(`GetSongsByUser_DB() wasn't called once as it should have`) } GetSongsByUser_DB = originalFunc }

Cache Invalidation

Our cache is very basic and doesn't do any invalidation. This works pretty well as long as all songs are added through the AddSong() method that takes care of updating Redis. If we add more operations like removing songs or deleting users then these operations should take care of updating Redis accordingly.

This very simple cache will work even if we have a distributed system where multiple independent machines can run our Songify service—as long as all the instances work with the same DB and Redis instances.

However, if the DB and cache can get out of sync due to maintenance operations or other tools and applications changing our data then we need to come up with an invalidation and refresh policy for the cache. It can be tested using the same techniques—replace target functions or directly access the DB and Redis in your test to verify the state.

LRU Caches

Usually, you can't just let the cache grow infinitely. A common scheme to keep the most useful data in the cache is LRU caches (least recently used). The oldest data gets bumped from the cache when it reaches capacity.

Testing it involves setting the capacity to a relatively small number during the test, exceeding the capacity, and ensuring that the oldest data is not in the cache anymore and accessing it requires DB access.

Testing Your Data Integrity

Your system is only as good as your data integrity. If you have corrupted data or missing data then you're in bad shape. In real-world systems, it's difficult to maintain perfect data integrity. Schema and formats change, data is ingested through channels that might not check for all the constraints, bugs let in bad data, admins attempt manual fixes, backups and restores might be unreliable.

Given this harsh reality, you should test your system's data integrity. Testing data integrity is different than regular automated tests after each code change. The reason is that data can go bad even if the code didn't change. You definitely want to run data integrity checks after code changes that might alter data storage or representation, but also run them periodically.

Testing Constraints

Constraints are the foundation of your data modeling. If you use a relational DB then you can define some constraints at the SQL level and let the DB enforce them. Nullness, length of text fields, uniqueness and 1-N relationships can be defined easily. But SQL can't check all the constraints.

For example, in Desongcious, there is a N-N relationship between users and songs. Each song must be associated with at least one user. There is no good way to enforce this in SQL (well, you can have a foreign key from song to user and have the song point to one of the users associated with it). Another constraint may be that each user may have at most 500 songs. Again, there is no way to represent it in SQL. If you use NoSQL data stores then usually there is even less support for declaring and validating constraints at the data store level.

That leaves you with a couple of options:

Ensure that access to data goes only through vetted interfaces and tools that enforce all the constraints.

Periodically scan your data, hunt constraint violations, and fix them.

Testing Idempotency

Idempotency means that performing the same operation multiple times in a row will have the same effect as performing it once.

For example, setting the variable x to 5 is idempotent. You can set x to 5 one time or a million times. It will still be 5. However, incrementing X by 1 is not idempotent. Every consecutive increment changes its value. Idempotency is a very desirable property in distributed systems with temporary network partitions and recovery protocols that retry sending a message multiple times if there is no immediate response.

If you design idempotency into your data access code, you should test it. This is typically very easy. For each idempotent operation you extend to perform the operation twice or more in a row and verify there are no errors and the state remains the same.

Note that idempotent design may sometimes hide errors. Consider deleting a record from a DB. It is an idempotent operation. After you delete a record, the record doesn't exist in the system anymore, and trying to delete it again will not bring it back. That means that trying to delete a non-existent record is a valid operation. But it might mask the fact that the wrong record key was passed by the caller. If you return an error message then it's not idempotent.

Testing Data Migrations

Data migrations can be very risky operations. Sometimes you run a script over all your data or critical parts of your data and perform some serious surgery. You should be ready with plan B in case something goes wrong (e.g. go back to the original data and figure out what went wrong).

In many cases, data migration can be a slow and costly operation that may require two systems side by side for the duration of the migration. I participated in several data migrations that took several days or even weeks. When facing a massive data migration, it's worth it to invest the time and test the migration itself on a small (but representative) subset of your data and then verify that the newly migrated data is valid and the system can work with it.

Testing Missing Data

Missing data is an interesting problem. Sometimes missing data will violate your data integrity (e.g. a song whose user is missing), and sometimes it's just missing (e.g. someone removes a user and all their songs).

If the missing data causes a data integrity problem then you'll detect it in your data integrity tests. However, if some data is just missing then there is no easy way to detect it. If the data never made it into persistent storage then maybe there is a trace in the logs or other temporary stores.

Depending how much of a risk missing data is, you may write some tests that deliberately remove some data from your system and verify the system behaves as expected.

Conclusion

Testing data-intensive code requires deliberate planning and an understanding of your quality requirements. You can test at several levels of abstraction, and your choices will affect how thorough and comprehensive your tests are, how many aspects of your actual data layer you test, how fast your tests run, and how easy it is to modify your tests when the data layer changes.

There is no single correct answer. You need to find your sweet spot along the spectrum from super comprehensive, slow and labor-intensive tests to fast, lightweight tests.

by Gigi Sayfan via Envato Tuts+ Code http://ift.tt/2AQk17s

#CSS3 #HTML5 Branding #HTML5 Weekly #HTMLl5 demos #Javascript #Javascript Branding #Javascript Weekly

0 notes

marcosplavsczyk · 8 years ago

Link

Eighty-two percent of the enterprises expect the number of databases to increase over the next twelve months. An increase in data volumes can have negative effects on the performance of databases. Think about the storage requirement and backup strategy to meet the Recovery Time Objective and the Recovery Point Objective. RPO and RTO are two of the most important parameters of a disaster recovery or data protection plan.

Database backup overview

Let us take a look at some of the most common ways to back up the SQL Server database, and some of the best and most feasible solutions for data protection and disaster recovery scenarios.

Let us focus on handling huge volumes of data using various techniques and/or methodologies. Some of us may have questions on how to decide the best-suited backup strategies for our environments; on automating and managing SQL Server database backup; on whether we should automate the database backup process using T-SQL, SSIS, or PowerShell or some other tool or technique; what the data recovery and protection plans available are; whether the SQL engine provides the required capabilities to schedule a job and run it across multiple servers; whether customization options are available; whether we have a robust method to perform backup activity.

Let find out the answers to those questions. I’m sure you’ll not be disappointed!

Getting started

A database administrator must make sure that all databases are backed up across environments. Understanding the importance of database backup is critical. Setting the right recovery objective is vital, and hence, we need to consider the backup options carefully. Configuring the retention period is another area to ensure integrity of the data.

Backing up data regularly is always a good strategy on the one hand, and on the other, we must regularly test the backup copies to ensure that they’re tested and validated for smooth working of the systems, and to prevent any sort of corruption or, under extreme conditions, a disaster. The well-tested SQL Server database backup script that we’re going to discuss provides an essential safeguard for protecting the (critical) data stored in the SQL Server databases. Backups are also very important to preserve modifications to the data on regular basis.

With a well-configured SQL Server database backup, one can recover data from many failures such as:

Hardware failures

User-generated accidents

Catastrophic disasters

Let us now look at the various options and methodologies which can be used to initiate a database backup.

There are different ways to back-up a database:

SSMS – Backups can be performed manually using SQL Server Management Studio

SQL Agent Job – Using a T-SQL script for backup

Using Maintenance Plan – SSIS Packages

SMO (SQL Server Management Objects) – PowerShell Scripts

Using ApexSQL Backup

This article talks about the use SQL Server Management Objects (SMO) and its advantages in making life easier. SMO is a complete library of programmatically accessed objects that enable an application to manage a running instance of Microsoft SQL Server Services. PowerShell is used to create this SQL Server databases backup script using the SMO classes. The script backs up specific or all databases of an instance to the backup location, or a local/remote/network share, and manages the backup files as per the retention period set.

Let’s look at how other DBAs in the industry are tackling massive data growth, what their most important goals are, and strategy to backup SQL Server databases automatically. Let us also look at some of the third party tools for backup management.

The first three options are very well discussed in the How to backup multiple SQL Server databases automatically article. Now that the first three points are already covered, let’s look at the use of the PowerShell SMO options with ApexSQL Backup. We’ll learn about how we can increase the database performance and eliminate downtime to give users the best experience possible using ApexSQL Backup. Today’s challenge is to give customers the most visually appealing and contextually rich insights possible in a user-friendly interface. ApexSQL has all the rich features and has the intelligence to manage and deploy a SQL backup plan to many SQL instances. A good way to start this process is to test the feasibility by downloading the free trial version of the tool.

Initial preparation

The goal of many organizations is to manage the backup of SQL Server databases automatically. We’ll go through the necessary steps to create the PowerShell SQL Server database backup script shortly. We can list any number of SQL servers and databases using the script. We can also create multiple jobs to initiate backup on multiple servers.

Pre-requisites

Enable XP_CMDSHELL

EXEC sp_configure 'show advanced options', 1; GO -- To update the currently configured value for advanced options. RECONFIGURE; GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1; GO -- To update the currently configured value for this feature. RECONFIGURE; GO

Before you proceed, set the execution policy on PowerShell

Load the necessary modules if they’re not loaded automatically

Add full rights on file share or local or remote location where you’d like the backups stored

Add default permissions to BACKUP DATABASE and BACKUP LOG to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles

The following code section details

Handling multiple SQL Server databases automatically

Managing local or remote database backups

Email notification upon completion of every successful backup

Setting the retention period

Scheduling automated jobs

Constructing the PowerShell script

Let us walk through the script, step-by-step, along with looking at the details of the setup and configuration of the script. The complete script can be found in Appendix (A)

Step 1: Declare the variable, and load the SMO library

Step 2: Define the email functionality

Step 3: Looping through databases

Step 4: Initiate Database backup and Email body preparation

Step 5: Manage database backup file

Let’s now go about using this script to back up the database by scheduling an SQL Job. Using Object Explorer, expand the SQL Server Agent. Right-click on Jobs. Select New job.

In the General tab of the window that pops up, enter the name, owner and the description for the job. Let’s call this SQLBackupCentralizedJob.

In the Steps tab, click on New to configure the job.

In the General tab,

Mention the step name as SQLBackup,

Set the job type to Transact-SQL script (T-SQL)

Select the master database in the Database box.

Paste the following script that will be used for this job in the Command box. Click OK.

master..xp_cmdshell 'PowerShell.exe F:\PowerSQL\MSSQLBackup.ps1'

Click OK.

We have now successfully created the job.

Right-click on SQLBackup_CentralizedJob and run it.

We can check the backup folder for the backup files; it would tell us the progress, like so:

Invoke_SQLDBBackup -SQLServer HQDBT01 -BackupDirectory f:\SQLBackup -dbList "SQLShack_Demo,ApexSQLBAckup" -rentention 3 -Mail Yes Invoke_SQLDBBackup -SQLServer HQDBSP18 -BackupDirectory f:\PowerSQL -dbList "SafetyDB,rtc,rtcab1" -rentention 3 -Mail Yes

Verify the email

Back up multiple SQL databases with ApexSQL Backup

ApexSQL Backup is a third-party software solution that can be used to define and/or manage the backup/restore processes and perform various maintenance operations. The tool is capable of performing the backup of SQL Server databases automatically.

The backup of multiple SQL databases can be configured in a few simple steps:

Step 1: Register the server

Select Home tab

Click Add button

Enter the SQL Server

Select authentication type

Click Ok

Step 2: The Backup Configuration Wizard

Now you should see all the databases that are available on that server. Click on the Backup button on the Home ribbon tab to configure the jobs.

The main tab of the backup wizard is for backup settings and defining the backup configuration. This section has three options

Backup

Select the SQL Server from the drop-down list; you can select the server you’d like to configure the backup for

In the Databases, browse and select the intended databases for backup

Click OK

Next, set the backup type, defining the backup location and its standards

Select the type of the database backup

To have a better experience, set the Job name and the Job description. It’s usually a good practice to do so.

Click on the Add Destination button, and set backup the destination path, or configure custom naming rules.

Browse for the destination path in the Folder text box.

In the Filename box, configure the format of the backup filename by clicking on the corresponding tags—you can select from the seven available tags. Each of those can be included in the backup filename. Check the summary and click OK if everything is configured as required. You can click on ApexSQL defaults to reset the configuration.

Click OK

Now, let us schedule the backup job using the Schedule wizard. This wizard is invoked using the overflow (…) button.

In the wizard, set the frequency, daily frequency and activity schedule of database backup as desired. Check the summary at the bottom of the page to confirm that the configuration is done as required. Click OK to save changes in the Schedule Wizard.

Click OK.

The Advanced tab

We can add various media, verification, compression and encryption options along with encryption settings in the Advanced tab of the wizard.

Set the retention period in the cleanup tab to clean-up the database backup files

Click OK.

Notification

Use the notification tab to set the type of alerts you would like an email notification sent for.

Use Options tab to select the notification events

Click the Add button to add recipient details

Click OK to commit.

After the configuration is complete, click OK to confirm the same. This would create backup jobs for the databases. We performed one configuration, but it created jobs for each of the databases we selected. It couldn’t get any easier! Of course, you can make individual modifications to create exceptions.

Select the Schedules view in main application form to check the jobs we created. Just like selecting the databases, you can check the box on the header to select all the schedules we created. Right-click on any of the schedules to bring up the context menu. Select Run now. The corresponding jobs will be executed immediately, irrespective of the schedule settings—this is like a force-run.

The result column shows the final status of the database backup jobs—the status of each of the jobs that have the schedule information in the central repository. If you’d like to perform an action on any one job, you can select the relevant checkbox and click on the action, such as Run now, or Disable.

The activities tab, the central dashboard to view the job activities performed through ApexSQL Backup.

The message column gives user-friendly information, which is helpful in troubleshooting the backup jobs. The initial two failures as seen below are due to the fact that the database was offline. It was later fixed, and the job completed successfully in the third attempt.

The History tab shows the backup history for the database selected from the Server pane on the left.

Let us now check for the backup files that got created in the folder we specified as the backup path. We can easily recognize the backup files by their custom filenames. The creation date and time are also available. Notice the file names marked in red.

Also, here’s the summary email notification (Success and Failure)

Wrapping Up

In an environment that relies on a SQL Server backup database script, or a managed native backup methodology, one could try using PowerShell scripts using SMO. We also saw how to backup SQL Server databases automatically using scripts. PowerShell scripts are mostly sequential in nature, unless they are enhanced in order to run parallel processes. However, the latter takes significant effort to define and configure. ApexSQL Backup makes managing these processes much simpler because of its built-in options to handle these tasks in a more efficient manner.

References

How to backup multiple SQL Server databases automatically

Using the Set-ExecutionPolicy Cmdlet

Using PowerShell and SMO to list Databases (and other stuff)

Send-MailMessage

Appendix (A)

Function Get-SQLDBBackup { param ( [Parameter (Mandatory=$true,Position=0)][String]$SQLServer, [Parameter(Mandatory=$true,Position=1)][String]$BackupDirectory, [Parameter(Mandatory=$true,Position=2)][String]$dbList, [Parameter(Mandatory=$true,Position=3)][int]$retention, [Parameter(Mandatory=$true,Position=3)][String]$Mail) #loading SMO library [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null # Setting the backup data in yyyyMMdd_HHmmss that is 20170619_130939 $BackupDate = get-date -format yyyyMMdd_HHmmss Function sendEmail { param($from,$to,$subject,$smtpServer) [string]$recipients="$to" $body = New-Object System.Net.Mail.MailMessage $from, $recipients, $subject, $body $smtpServer = $smtpServer $smtp = new-object Net.Mail.SmtpClient($smtpServer) $smtp.Send($body) } #Define the SMO class library $Server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $SQLServer #Iterating the SQL Server databases of a given instance foreach ($Database in $Server.databases) { # Teh foreach($db in $DbList.split(",")) { if($Database.Name -eq $db) { $DatabaseName = $Database.Name $DatabaseBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup") $DatabaseBackup.Action = "Database" $DatabaseBackup.Database = $DatabaseName #Set the directory for backup location $BackupFileName=$BackupDirectory + "\" + $DatabaseName + "_" + $BackupDate + ".BAK" #write-host $BackupFileName $DatabaseBackup.Devices.AddDevice($BackupFileName, "File") try { Write-Progress -Activity "Please wait! Backing up SQL databases... " -Status "Processing:" -CurrentOperation "Currently processing: $DatabaseName" $DatabaseBackup.SqlBackup($Server) $body=@" Notification that a $DatabaseName is backed up! successfully with date and time stamp $BackupDate "@ } catch { $body=@" Notification that a $DatabaseName is backed failed! with an error message $_.Exception.Message "@ } write-host $status if ($Mail ='Yes') { sendEmail -To "[email protected]" -Subject " $SQLServer -> $DatabaseName Backup Status" ` -From "[email protected]" -Body $body ` -smtpServer "hqmail.abc.com" } # Preparing the UNC path for the database backup file handling # fetching the drive letter. First argument is that starting position in the string, and the second is the length of the substring, starting at that position. $drive=$BackupFileName.substring(0,1) $len=$BackupDirectory.length #write-host $len #Selecting the string portion of a directory that is fetching the string starting from the character posittion $path=$BackupDirectory.substring(3,$len-3) #write-host \\$SQLServer\$drive$\$path # Listing the files which is older than 3 minutes in this caseon a server. It can be local or remote location $file=get-ChildItem \\$SQLServer\$drive$\$path -recurse -Filter $DatabaseName*.bak | Select-object LastWriteTime,directoryname,name |where-object {$_.LastWriteTime -lt [System.DateTime]::Now.Addminutes(-$rentention)} #Iterating each file and remove the file with remove-item cmdlet foreach($f in $file) { $filename=$f.directoryname+'\'+$f.name write-host 'File deleted' $filename remove-item $filename -Force } #$DatabaseBackup | select LogicalName, Type, Size, PhysicalName | Format-Table -AutoSize } } } } Get-SQLDBBackup -SQLServer HQDBT01 -BackupDirectory f:\SQLBackup -dbList "SQLShack_Demo,ApexSQLBackup" -rentention 3 -Mail Yes Get-SQLDBBackup -SQLServer HQDBSP18 -BackupDirectory f:\PowerSQL -dbList "SafetyDB,rtc,rtcab1" -rentention 3 -Mail Yes

The post Multi server PowerShell script to backup SQL Server databases automatically appeared first on Solution center.

#SQL Solution Center

0 notes

globalmediacampaign · 4 years ago

Text

Best practices for migrating PostgreSQL databases to Amazon RDS and Amazon Aurora

PostgreSQL is one of the most advanced open-source relational database systems. From a few GB to multi-TB databases, PostgreSQL is best suited for online transaction processing (OLTP) workloads. For many organizations, PostgreSQL is the open-source database of choice when migrating from commercial databases such as Oracle or Microsoft SQL Server. AWS offers Amazon Relational Database Service for PostgreSQL (Amazon RDS for PostgreSQL) and Amazon Aurora PostgreSQL-Compatible Edition as fully managed PostgreSQL database services. The post discusses best practices and solutions for some of the limitations when using PostgreSQL native utilities to migrate PostgreSQL databases to Amazon RDS for PostgreSQL or Aurora PostgreSQL. Overview Migrating to RDS for PostgreSQL or Aurora PostgreSQL clusters requires strategy, resources, and downtime maintenance. In this post, we provide some best practices for migrating PostgreSQL databases to Amazon RDS for PostgreSQL and Aurora PostgreSQL using PostgreSQL native tools such as pg_dump and pg_restore, logical replication, and the COPY command. These practices are useful for migrating PostgreSQL databases from on-premises servers, Amazon Elastic Compute Cloud (Amazon EC2) instances, or from one RDS or Aurora instance to another. If downtime during migration is affordable, using pg_dump and pg_restore is the preferred method. To minimize downtime, logical replication is the better solution. You should use the COPY command to migrate filtered table data. The following diagram compares the various PostgreSQL native migrations strategies to migrate Amazon RDS for PostgreSQL or Aurora PostgreSQL. Prerequisites Before getting started, complete the following prerequisites: To apply the best practices for migration, you need to have a source database up and in a Running state. To avoid any access issues, run the migration commands as a superuser role at the source side. Provision a target RDS for PostgreSQL database or Amazon PostgreSQL database. Issue the migration commands at the target instance using the rds_superuser role. Running an RDS or Aurora PostgreSQL instance incurs cost. For more information, see Amazon RDS for PostgreSQL Pricing or Amazon Aurora Pricing, respectively. PostgreSQL native utility: pg_dump and pg_restore pg_dump is a PostgreSQL native utility to generate a file with SQL commands. Feeding this file to a server recreates the database in the same state as it was at the time of the dump. This utility is commonly used for logical dumping data for migration or upgrade purposes. Depending on the format, you can restore dump files with the psql program or by another PostgreSQL utility, pg_restore. This method is best suited for migrating a few GB to 500 GB sized databases. Migrating bigger databases may require higher outage depending on database size. Next, we discuss some of the tips that can help reduce the migration time using pg_dump, pg_restore, and psql utilities pg_dump and pg_restore options Using some of the pg_dump and pg_restore process options can result in faster migration. You can find these options by entering pg_dump --help and pg_restore --help commands. For migrating to a higher version of PostgreSQL, we recommend using a higher version of pg_dump and pg_restore. For example, when migrating from PostgreSQL 10 to PostgreSQL 11, use the pg_dump and pg_restore utilities of PostgreSQL 11. The following pg_dump and pg_restore options can make the dump file process quicker: –format/ -F – This option selects the output format. The default format is plain. You can restore files in plain format using psql. Other formats are custom, directory, and tar. You can choose these formats with --format=custom, --format=directory, or --format=tar. You can restore the output files in these formats by using the pg_restore utility. Custom and directory are the most flexible output formats. Compressed by default, these formats allow manual selection, speedy dump and restore, and reordering of archived items during restore. The directory format also supports parallel dumps. –jobs/ -J – This option with the pg_dump command runs the given number of concurrent dump jobs simultaneously. With pg_restore, this option runs multiple restore jobs simultaneously. The --jobs option reduces the time of the dump and restore drastically, but it also increases the load on the database server. pg_dump opens the number of jobs + 1 connections to the database, so make sure the max_connections setting is high enough to accommodate all connections. The number of jobs should be equal to or less than the number of vCPUs allocated for the RDS instance to avoid resource contention. The following code shows an example of running five simultaneous pg_dump and pg_restore jobs. The --create option with pg_dump writes the CREATE DATABASE command in the dump file. --create with pg_restore creates the named database before restoring contents. --dbname specifies the connecting database name. pg_dump --host --format=directory --create --jobs 5 --dbname --username --file /home/ec2-user/db11.dump pg_restore --host --format=directory --create –-jobs 5 --dbname --username --file /home/ec2-user/db11.dump –table/ -t – This option dumps all tables matching the given pattern. Similarly, --schema lets you dump all schemas matching a pattern. This is helpful when you want to have multiple pg_dump jobs running simultaneously to reduce overall migration time. –compress/ -z – Valued between 0–9, this option specifies the compression level to use. Zero means no compression. For the custom archive format, this specifies compression of individual table-data segments. The default is to compress at a 6 level. A higher value can decrease the size of the dump file, but can cause high resource consumptions such as CPU and I/O at the client host. If the client host has enough resources, 7–8 compression levels can significantly lower the dump file size. For running the pg_dump utility, you need to have SELECT permission on all database objects. By default, rds_superuser doesn’t have SELECT permission on all objects. As a workaround, grant SELECT permission to the rds_superuser role or grant permissions of other users to rds_superuser by running the following command: GRANT TO ; Modifications at the target RDS for PostgreSQL or Aurora PostgreSQL instance You can do several schema-level changes and parameter modifications to have a faster restore. For schema-level changes, you need to restore with the --schema-only option. By default, pg_dump generates COPY commands. Existing indexes and foreign keys can cause significant delays during data restore. Dropping foreign keys, primary keys, and indexes before restore and adding them after restore can drastically reduce migration time. After this schema modification, pg_restore should be used with --data-only. Certain parameter changes can help reduce restore time: maintenance_work_mem – Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. Increasing its value can make data restore, adding keys, and indexes restore faster. If pg_restore uses n number of concurrent jobs, we should make sure to have n times maintenance_work_mem memory available at the instance. Because the database isn’t operating workloads, you can reduce the shared_buffer to accommodate more memory for maintenance_work_mem. checkpoint_timeout – Defines the maximum time between automatic WAL checkpoints in seconds. max_wal_size – Defines the maximum size to let the WAL grow to between automatic WAL checkpoints. During migration, because losing data due to crash isn’t a priority, set these two parameters to a higher value to make restore faster. After data is restored, you should reset all these parameters to their default values. Cloud-native options Some of the best practices of pg_dump and pg_restore host and database configurations can make migration faster: While running pg_dump, choosing a host with high CPU and throughput can bump the speed of pg_dump. This lets you run multiple pg_dump processes simultaneously. Choosing Amazon EC2 as the host within same Availability Zone as of the target database mitigates any network latency during migration. Similarly, a target database with higher compute and throughput configured with Provisioned IOPS storage can support concurrent restore. Disabling Multi-AZ and backups during migration also make restore faster. While migrating a small dataset from Amazon EC2 hosted PostgreSQL to Amazon RDS for PostgreSQL, streaming output of pg_dump as input to psql reduces space and time to create the dump file. This drastically reduces overall migration time. The following code is the example of running pg_dump and psql at same time: pg_dump --host --username | psql --host --username Another PostgreSQL utility, pg_dumpall, is used to dump all databases of the cluster in a single file. The supported file format of pg_dumpall is only text, and you can restore it by using the psql client. Because the Amazon RDS for PostgreSQL and Aurora PostgreSQL rds_superuser role doesn’t have permission on the pg_authid table, it’s important to use --no-role-passwords with pg_dumpall while dumping data. Only the newer version of pg_dumpall (from PostgreSQL 10.0 and above) supports the --no-role-passwords option. The following code is a sample command dumping all of the database at the Aurora cluster: pg_dumpall --host --no-role-passwords --username > dumpall.dump The pg_dump utility only takes the backup of the single database at any point of time. It doesn’t back up global objects such as users and groups. To migrate global objects, you need to use a combination of pg_dumpall and psql. Complete the following steps: Take a backup of source tablespaces and roles using pg_dumpall: pg_dumpall --host --globals-only --no-role-passwords --username > globals_only.sql Run psql to restore the global object: psql --host --dbname --username -f globals_only.sql Logical replication Logical replication is a method of replicating data objects and their changes based upon their replication identity. We use the term logical in contrast to physical replication, which uses exact block addresses and byte-by-byte replication. Logical replication uses a publisher and subscriber model with one or more subscribers subscribing to one or more publications on the publisher node. Subscribers pull data from the publications they subscribe to and may subsequently republish data to allow cascading replication or more complex configurations. Unlike physical replication, you can set up logical replication between two different major versions of PostgreSQL. Because Amazon RDS for PostgreSQL and Aurora PostgreSQL don’t support as targets for external PostgreSQL physical replication, logical replication is one of the common ways to reduce overall migration time. This is also helpful while migrating from an unencrypted RDS for PostgreSQL instance to an encrypted RDS for PostgreSQL instance with minimal outage. The following are the major steps to set up logical replication: Take schema-only dump at the source and restore at the target: pg_dump --host --schema-only --dbname=sourcedb --username= > schema_only.dump psql --host --username= --file=schema_only.dump --dbname=targetdb Modify the following parameters at the source and reboot the instance to apply changes. If the source is an RDS for PostgreSQL or Aurora PostgreSQL instance, you need to enable rds.logical_replication to apply these changes.wal_level = logical wal_sender_timeout = 0 max_replication_slots = 10 #number of subscriptions max_wal_senders = 12 #max_replication_slots(10) + number of other standbys (2) Provide access to the target RDS for PostgreSQL instance to the source instance by modifying pg_hba.conf. If the source is an RDS for PostgreSQL or Aurora PostgreSQL instance, add the target public and private IP to the source security group. At the source, create a logical replication user and publication: psql --host --username --dbname sourcedb sourcedb=> create table customer (id int, name varchar(50), CONSTRAINT customer_pkey PRIMARY KEY (id)); CREATE TABLE sourcedb => CREATE ROLE log_repuser LOGIN; CREATE ROLE sourcedb => password log_repuser Enter new password: Enter it again: sourcedb=> GRANT rds_superuser TO log_repuser; GRANT ROLE sourcedb=> show rds.logical_replication; rds.logical_replication ------------------------- on (1 row) sourcedb=> show wal_sender_timeout; wal_sender_timeout -------------------- 0 (1 row) sourcedb=> CREATE PUBLICATION pub_1; CREATE PUBLICATION sourcedb=> ALTER PUBLICATION pub_1 ADD ALL TABLES; ALTER PUBLICATION At the target PostgreSQL instance, create a subscription: psql --host --username --dbname targetdb targetdb=> create table customer (id int, name varchar(50), address varchar(100), CONSTRAINT customer_pkey PRIMARY KEY (id)); CREATE TABLE targetdb=> CREATE SUBSCRIPTION sub_1 CONNECTION 'host=logicalsource1.cxxxxxxxxxx7.us-west-2.rds.amazonaws.com port=5432 password= user=log_repuser dbname=sourcedb' PUBLICATION pub_1; NOTICE: created replication slot "sub_1" on publisher CREATE SUBSCRIPTION Test the logical replication: sourcedb=> INSERT into customer values (1, 'john'), (2, 'jeff'); INSERT 0 2 targetdb=> select * from customer ; id | name | address ----+------+--------- 1 | john | 2 | jeff | (2 rows) targetdb=> INSERT into customer values (3, 'john'), (4, 'jeff'); INSERT 0 2 targetdb=> select * from customer ; id | name | address ----+------+--------- 1 | john | 2 | jeff | 3 | john | 4 | jeff | sourcedb=> INSERT into customer values (5, 'john'), (6, 'jeff'); INSERT 0 2 targetdb=> select * from customer ; id | name | address ----+------+--------- 1 | john | 2 | jeff | 3 | john | 4 | jeff | 5 | john | 6 | jeff | targetdb=> INSERT into customer values (7, 'john', 'chicago'), (8, 'jeff','omaha'); INSERT 0 2 targetdb=> select * from customer ; id | name | address ----+------+--------- 1 | john | 2 | jeff | 3 | john | 4 | jeff | 5 | john | 6 | jeff | 7 | john | chicago 8 | jeff | Omaha The following are some best practices for PostgreSQL logical replication: While using logical replication, you can also insert data on the target side. Make sure to revoke all write privileges at the table while running replication. In a publication, you can choose what type of commands to replicate: INSERT, DELETE, UPDATE, ALL. By default, it’s ALL. Set these replication type as per your business needs and minimum requirements. The replicating tables should be normal tables. Views, materialized views, partition root tables, or foreign tables can’t be replicated. You can replicate tables with no primary or unique keys, but updates and deletes on those tables are slow on the subscriber. Any DDL changes aren’t replicated. To avoid any interruption in replication, DDL changes such as any changes on table definition should be done at both sides at the same time. The TRUNCATE operation isn’t supported. As a workaround, use the DELETE operation. It’s important to have a replication identity (primary key or unique index) for tables that are part of the publication in the logical replication. If the table doesn’t have a primary key or a unique index, you can set replication identity to full (ALTER TABLE

REPLICA IDENTITY FULL) where the entire row acts as a primary key. Migrating data using the COPY command PostgreSQL also has the native COPY command to move data between tables and standard file system files. COPY is usually comparatively faster than INSERT. This is another alternative to pg_dump and pg_restore commands to back up and restore PostgreSQL databases, tables, and underlying objects. One of the advantages of the COPY command over pg_dump and pg_restore is that you can filter the data by using SQL queries. You can also use the COPY command to transfer data from one format to another or from a file to a table, or vice versa. PostgreSQL COPY commands are available in two variations: a server-side COPY command and the client-side meta-command copy. The server-side command requires superuser privileges or pg_read_server_files, pg_write_server_files, or pg_execute_server_program privileges because they are read or written directly by the server. The client-side copy command invokes copy from STDIN or copy to STDOUT, and then fetches and stores the data in a file accessible to the psql client. Server-side copy isn’t available on Amazon RDS for PostgreSQL or Aurora PostgreSQL. You need at least INSERT, UPDATE, and SELECT permissions on the table in order copy to or from it. The following are few key terminologies for the COPY command: Delimiter – A character that separates dataset. By default, COPY expects tabs as delimiters, but you can change this by specifying the delimiter with “using delimiter” keywords. Header – The line of the CSV file that consists of the column names. The following examples show basic command usage of the COPY command and copy meta-command from the psql client: postgres=> copy (select *from activity limit 100) to '~/activity.csv' with DELIMITER ',' CSV header; COPY 100 postgres=> copy activity_2 from '~/activity.csv' with DELIMITER ',' CSV HEADER; COPY 100 An input file name path can be an absolute or relative path, but an output file name path must be an absolute path. The client-side copy can handle relative pathnames. In Amazon RDS for PostgreSQL 11.1 and above, you can import data from Amazon Simple Storage Service (Amazon S3) into a table belonging to an RDS for PostgreSQL DB instance. To do this, you need to use the AWS_S3 PostgreSQL extension that Amazon RDS provides. Clean up To avoid incurring ongoing charges, delete the RDS for PostgreSQL and Aurora PostgreSQL instances. Conclusion This post demonstrated the best practices for migrating PostgreSQL databases to Amazon RDS for PostgreSQL or Aurora PostgreSQL. Apart from pg_dump and pg_restore, the COPY command, and logical replication, other options for migration are AWS Database Migration Service (AWS DMS) and pglogical. If you have comments or questions about this solution, please submit them in the comments section. About the authors Vivek Singh is a Senior Database Specialist with AWS focusing on Amazon RDS/Aurora PostgreSQL engines. He works with enterprise customers providing technical assistance on PostgreSQL operational performance and sharing database best practices. Akm Raziul Islam has worked as a consultant with a focus on Database and Analytics at Amazon Web Services. He worked with customers to provide guidance and technical assistance about various database and analytical projects, helping them improving the value of their solutions when using AWS. https://aws.amazon.com/blogs/database/best-practices-for-migrating-postgresql-databases-to-amazon-rds-and-amazon-aurora/

0 notes

sqljoker · 8 years ago

Text

Tweeted

Planning a SQL Server Backup and Restore strategy in a multi-server env using PowerShell and T-SQL https://t.co/h21P73DGeM via @SQLShack

— Marko Radakovic (@MarkoSQL) August 16, 2017

#IFTTT #Twitter

0 notes

prashantjayaram · 8 years ago

Text

Planning a SQL Server Backup and Restore strategy in a multi-server environment using PowerShell and T-SQL

This post demonstrates one of the ways to gather an inventory of database backup information. The output of the script includes various columns that show the internals of a database, backup size, and gives the latest completion statuses and the corresponding backup sizes. Though the output is derived from the msdb database, having the data consolidated at one place gives us better control and…

View On WordPress

#import SQL Server module #Remove-Module SQLPS #SQL Server backup and Restore strategy #Write-SqlTableData #Write-SqlTableData examples

0 notes

isearchgoood · 6 years ago

Text

December 20, 2019 at 10:00PM - The Complete SQL Certification Bundle (98% discount) Ashraf

Access 92 lectures & 19 hours of content 24/7

Understand data definition language & its manipulation

Walk through database concepts & tools, memory structure, tables, and indexes

Dive into undertaking installation, backup & recovery

Access 102 lectures & 22.5 hours of content 24/7

Deploy a Microsoft Azure SQL database

Plan for a SQL Server installation

Deploy SQL Server instances

Deploy SQL Server databases to Azure Virtual Machines

Configure secure access to Microsoft Azure SQL databases

Access 196 lectures & 6.5 hours of content 24/7

Apply built-in scalar functions & ranking functions

Combine datasets, design T-SQL stored procedures, optimize queries, & more

Implement aggregate queries, handle errors, generate sequences, & explore data types

Modify data by using INSERT, UPDATE, DELETE, & MERGE statements

Create database objects, tables, views, & more

Access 128 lectures & 6.5 hours of content 24/7

Audit SQL Server instances, back up databases, deploy a SQL Server, & more

Configure additional SQL Server components

Install SQL Server & explore related services

Understand how to manage & configure SQL Servers & implement a migration strategy

Access 100 lectures & 4.5 hours of content 24/7

Master data using Master Data Services & cleans it using Data Quality Services

Explore ad-hoc data manipulations & transformations

Manage, configure, & deploy SQL Server Integration Services (SSIS) packages

Design & implement dimensions, fact tables, control flow, & more

Access 186 lectures & 5.5 hours of content 24/7

Explore managing multiple servers

Dive into disaster recovery planning

Discover the rules of database design

Learn how to design a database structure & database objects

Work w/ large data sets across multiple database systems

Develop cubes & Multi-dimensional Expressions (MDX) queries to support analysts

Explore data model decisions

Manage a reporting system, use report builder to create reports & develop complex SQL queries for reports

This course is designed for aspiring business intelligence architects who would like to focus on the overall design of a BI infrastructure, including how it relates to other data systems.

Access 153 lectures & 4.5 hours of content 24/7

Learn BI Solution architecture & design

Understand provisioning of software & hardware

Explore Online Analytical Processing (OLAP) cube design

Study performance planning, scalability planning, upgrade management, & more

Maintain server health, design a security strategy, & more

Access 140 lectures & 5 hours of content 24/7

Optimize & tune queries

Create & alter indexes & stored procedures

Maintain database integrity & optimize indexing strategies

Design, implement, & troubleshoot security

Work w/ XML Data

Write automation scripts

Access 71 lectures & 15.5 hours of content 24/7

Familiarize yourself w/ data control language

Understand SQL functions & subqueries

Walk through combining queries & data definition language

Prep to ace the Oracle 12c OCP 1Z0-061: SQL Fundamentals exam

Access 105 lectures & 30.5 hours of content 24/7

Learn how to administer a Microsoft SQL Server 2016 server

Configure data access, permissions & auditing

Perform encryption on server data

Develop a backup strategy

Restore databases & manage database integrity

from Active Sales – SharewareOnSale https://ift.tt/2Yfi64s https://ift.tt/eA8V8J via Blogger https://ift.tt/392S5KT #blogger #bloggingtips #bloggerlife #bloggersgetsocial #ontheblog #writersofinstagram #writingprompt #instapoetry #writerscommunity #writersofig #writersblock #writerlife #writtenword #instawriters #spilledink #wordgasm #creativewriting #poetsofinstagram #blackoutpoetry #poetsofig

#FREE Deal Of The Day

0 notes

isearchgoood · 6 years ago

Text

November 30, 2019 at 10:00PM - The Complete SQL Certification Bundle (98% discount) Ashraf

Access 92 lectures & 19 hours of content 24/7

Understand data definition language & its manipulation

Walk through database concepts & tools, memory structure, tables, and indexes

Dive into undertaking installation, backup & recovery

Access 102 lectures & 22.5 hours of content 24/7

Deploy a Microsoft Azure SQL database

Plan for a SQL Server installation

Deploy SQL Server instances

Deploy SQL Server databases to Azure Virtual Machines

Configure secure access to Microsoft Azure SQL databases

Access 196 lectures & 6.5 hours of content 24/7

Apply built-in scalar functions & ranking functions

Combine datasets, design T-SQL stored procedures, optimize queries, & more

Implement aggregate queries, handle errors, generate sequences, & explore data types

Modify data by using INSERT, UPDATE, DELETE, & MERGE statements

Create database objects, tables, views, & more

Access 128 lectures & 6.5 hours of content 24/7

Audit SQL Server instances, back up databases, deploy a SQL Server, & more

Configure additional SQL Server components

Install SQL Server & explore related services

Understand how to manage & configure SQL Servers & implement a migration strategy

Access 100 lectures & 4.5 hours of content 24/7

Master data using Master Data Services & cleans it using Data Quality Services

Explore ad-hoc data manipulations & transformations

Manage, configure, & deploy SQL Server Integration Services (SSIS) packages

Design & implement dimensions, fact tables, control flow, & more

Access 186 lectures & 5.5 hours of content 24/7

Explore managing multiple servers

Dive into disaster recovery planning

Discover the rules of database design

Learn how to design a database structure & database objects

Work w/ large data sets across multiple database systems

Develop cubes & Multi-dimensional Expressions (MDX) queries to support analysts

Explore data model decisions

Manage a reporting system, use report builder to create reports & develop complex SQL queries for reports

This course is designed for aspiring business intelligence architects who would like to focus on the overall design of a BI infrastructure, including how it relates to other data systems.

Access 153 lectures & 4.5 hours of content 24/7

Learn BI Solution architecture & design

Understand provisioning of software & hardware

Explore Online Analytical Processing (OLAP) cube design

Study performance planning, scalability planning, upgrade management, & more

Maintain server health, design a security strategy, & more

Access 140 lectures & 5 hours of content 24/7

Optimize & tune queries

Create & alter indexes & stored procedures

Maintain database integrity & optimize indexing strategies

Design, implement, & troubleshoot security

Work w/ XML Data

Write automation scripts

Access 71 lectures & 15.5 hours of content 24/7

Familiarize yourself w/ data control language

Understand SQL functions & subqueries

Walk through combining queries & data definition language

Prep to ace the Oracle 12c OCP 1Z0-061: SQL Fundamentals exam

Access 105 lectures & 30.5 hours of content 24/7

Learn how to administer a Microsoft SQL Server 2016 server

Configure data access, permissions & auditing

Perform encryption on server data

Develop a backup strategy

Restore databases & manage database integrity

from Active Sales – SharewareOnSale https://ift.tt/2Yfi64s https://ift.tt/eA8V8J via Blogger https://ift.tt/33x2Ktu #blogger #bloggingtips #bloggerlife #bloggersgetsocial #ontheblog #writersofinstagram #writingprompt #instapoetry #writerscommunity #writersofig #writersblock #writerlife #writtenword #instawriters #spilledink #wordgasm #creativewriting #poetsofinstagram #blackoutpoetry #poetsofig

#FREE Deal Of The Day

0 notes